Freitag, Dezember 27, 2013

SQL Server 2014: neuer Cardinality Estimator

Benjamin Nevarez weist auf den neuen cardinality estimator hin, der im SQL Server 2014 neben das alte Schätzungsverfahren tritt, dessen initiale Version mit dem SQL Server 7.0 im Dezember 1998 erstmals auftrat. Leider weist der Artikel auch gleich darauf hin, dass es nicht allzu wahrscheinlich ist, dass Microsoft detailliert Auskunft über das neue Verfahren geben wird:
I am not sure if more details about the new cardinality estimator are going to be published by Microsoft. After all, not so many details were ever published about the old cardinality estimator in 15 years; for example, how some specific cardinality estimation is calculated. On the other hand, there are new extended events which we can use to troubleshoot problems with cardinality estimation, or just to explore how it works.
In jedem Fall bleibt festzuhalten, dass Änderungen an den geschätzten Größen von Ergebnismengen massive Wirkungen auf Ausführungspläne haben, so dass es wohl ratsam wäre, die Grundregeln der neuen Schätzungen gelegentlich in Erfahrung zu bringen.

Nachtrag 10.04.2014: ein paar Details zu den Veränderungen liefert der SQL Server Blog - und fasst sie im Summary des Artikels kompakt zusammen. 

Donnerstag, Dezember 26, 2013

Parallel Full Segment Scans über den Buffer Cache

Tanel Poder weist in seinem Blog in zwei Artikeln darauf hin, dass Parallel Execution Slaves vor 11.2 bei der Durchführung von full segment scans (FTS, IFFS) immer direct path reads verwendeten (wobei allerdings zusätzliche single block und multi block Zugriffe aus dem Buffer Cache erfolgen konnten - etwa für chained rows, prefetching etc.). Seit 11.2 gibt es die Möglichkeit, dass solche Operationen komplett über den Buffer Cache durchgeführt werden, was als In-Memory Parallel Execution bezeichnet wird:

Dienstag, Dezember 24, 2013

Plötzliche Änderung von Query-Laufzeiten

Jonathan Lewis hat eine interessante Liste veröffentlicht, die mögliche Gründe für eine plötzliche Verlangsamung einer Query, die vorher deutlich schneller ausgeführt wurde, aufführt - der umgekehrte Fall ist natürlich auch denkbar, führt aber seltener zu Beschwerden.

Donnerstag, Dezember 19, 2013

NULL IS NOT NULL-Filter und Bindevariablen

Der folgende Test verdankt sich wieder einer Fragestellung im OTN-Forum, wobei ich die eigentliche Anfrage möglicherweise etwas frei interpretiert habe. Gegeben ist eine Query, in der in einer inline-View mehrere Subqueries über UNION ALL verknüpft sind, und in der äußeren Abfrage eine Einschränkung auf einen konstanten Wert erfolgt, der einige der Subqueries bedeutungslos macht, da er ihre Ergebnisse filtert. Aber wahrscheinlich erklärt ein Beispiel die Fragestellung sehr viel deutlicher:

drop table t1;
drop table t2;
drop table t3;
 
create table t1
as
select rownum col1
  from dual
connect by level <= 1000;
 
create table t2
as
select rownum col1
  from dual
connect by level <= 1000;
 
create table t3
as
select rownum col1
  from dual
connect by level <= 1000;

exec dbms_stats.gather_table_stats(user, 'T1')
exec dbms_stats.gather_table_stats(user, 'T2')
exec dbms_stats.gather_table_stats(user, 'T3') 

explain plan for
select coll, col1
  from (select 'AAA' coll, col1 from t1
          union all
        select 'BBB' coll, col1 from t2
          union all
        select 'CCC' coll, col1 from t3
       )
 where coll = 'AAA';

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1002 | 18036 |     4   (0)| 00:00:01 |
|   1 |  VIEW                |      |  1002 | 18036 |     4   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL | T1   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|   5 |     TABLE ACCESS FULL| T2   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  6 |    FILTER            |      |       |       |            |          |
|   7 |     TABLE ACCESS FULL| T3   |  1000 |  4000 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(NULL IS NOT NULL)
   6 - filter(NULL IS NOT NULL)

Der Plan zeigt deutlich, dass nur aus T1 Werte ausgelesen werden, während für T2 und T3 ein Filter-Prädikat "filter(NULL IS NOT NULL)" erscheint, das immer false liefern muss. Dazu passt (zumindest ungefähr) auch die Prognose der Größe der Ergebnismenge (rows = 1002). Aber was passiert, wenn statt des Literals ein Bindewert als Einschränkung verwendet wird?

var test varchar2(30);
exec :test := 'AAA';

explain plan for
select coll, col1
  from (select 'AAA' coll, col1 from t1
          union all
        select 'BBB' coll, col1 from t2
          union all
        select 'CCC' coll, col1 from t3
       )
 where coll = :test

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  3000 | 54000 |    12   (0)| 00:00:01 |
|   1 |  VIEW                |      |  3000 | 54000 |    12   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  5 |    FILTER            |      |       |       |            |          |
|   6 |     TABLE ACCESS FULL| T2   |  1000 |  4000 |     4   (0)| 00:00:01 |
|*  7 |    FILTER            |      |       |       |            |          |
|   8 |     TABLE ACCESS FULL| T3   |  1000 |  4000 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter('AAA'=:TEST)
   5 - filter('BBB'=:TEST)
   7 - filter('CCC'=:TEST)

Anstelle der "filter(NULL IS NOT NULL)" erscheinen hier ganz konventionelle Prädikate, die den Eindruck erwecken, als wäre hier eine frühzeitige Filterung ausgeschlossen - also ein Verzicht auf den Tabellenzugriff nicht möglich (was sich auch in der rows-Angabe 3000 widerspiegelt). Das ist insofern einleuchtend, als der Optimizer zum Zeitpunkt der Optimierung die Bindewerte nicht kennt und folglich auch keine Vorstellung von ihren Filtereigenschaften haben kann. Ein Blick auf die (hier nicht aufgeführten autotrace-) Statistiken der Ausführung macht aber deutlich, dass die Variante mit dem Bindewert die gleiche Anzahl an LIOs hervorruft wie die Variante mit Literal. Offenbar ist der Plan der Bindewert-Query ein wenig irreführend.

Dazu hat Randolf Geist im Thread die Erklärung geliefert:
the FILTER operation is always checked first if it can be fully evaluated without having to refer to the child operations of the FILTER - and in case of the expression :TEST = this is possible - so the underlying operations won't be started if the expression can be evaluated to FALSE. This could be confirmed by running the same test with rowsource statistics enabled (or via Real-Time SQL Monitoring) - the corresponding skipped child operations should be shown with STARTS = 0.
Auf die Idee der Verwendung von rowsource Statistiken hätte ich vielleicht auch kommen können - schließlich empfehle ich diese Technik im OTN-Forum regelmäßig selbst. In jedem Fall hat der Herr Geist recht, was die Vorhersage des Trace-Ergebnisses angeht:

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |   1000 |00:00:00.01 |      71 |
|   1 |  VIEW                |      |      1 |   3000 |   1000 |00:00:00.01 |      71 |
|   2 |   UNION-ALL          |      |      1 |        |   1000 |00:00:00.01 |      71 |
|*  3 |    FILTER            |      |      1 |        |   1000 |00:00:00.01 |      71 |
|   4 |     TABLE ACCESS FULL| T1   |      1 |   1000 |   1000 |00:00:00.01 |      71 |
|*  5 |    FILTER            |      |      1 |        |      0 |00:00:00.01 |       0 |
|   6 |     TABLE ACCESS FULL| T2   |      0 |   1000 |      0 |00:00:00.01 |       0 |
|*  7 |    FILTER            |      |      1 |        |      0 |00:00:00.01 |       0 |
|   8 |     TABLE ACCESS FULL| T3   |      0 |   1000 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter('AAA'=:TEST)
   5 - filter('BBB'=:TEST)
   7 - filter('CCC'=:TEST)

Die Filter-Prädikate werden also zur Laufzeit tatsächlich vor dem Zugriff auf das zugehörige Segment ausgewertet und verhindern den Zugriff - was eine effektive Strategie darstellt. Das einzige kleine Problem dabei ist, dass man den Prädikaten dieses Verhalten nicht ansieht.

Freitag, Dezember 13, 2013

Join Cardinality bei dynamic sampling

Im OTN Forum wurde heute die Frage gestellt, warum die join cardinality für eine Query mit einem Join zweier Tabellen über Spalten ohne übereinstimmende Werte bei Verwendung von dynamic sampling in 11g komplett falsch berurteilt wurde: statt einer Selektivität von 0 erwartet der Optimizer im Beispiel eine Selektivität von 10%. Ein CBO Trace zur Query erklärt das Verhalten: dynamic sampling liefert zwar akkurate cardinalities, führt aber keine Bestimmung der Wertebereiche der Join-Spalten durch und muss deshalb auf die Standardformel zur Berechnung von join selectivities zurückgreifen. Bei Verwendung persistierter Statistiken kann der Optimizer hingegen die über die HIGH_VALUE und LOW_VALUE-Angaben in user_tab_cols angezeigten Wertebereiche berücksichtigen und feststellen, dass der Join keine Ergebnisse liefern dürfte. Da Jonathan Lewis meine Erklärung mit dem Kommentar "Nice explanation" versehen hat, gehe ich davon aus, dass sie zutreffend ist.

Nachtrag 16.12.2013: die sich im Thread anschließende Diskussion und genauere Analyse des Verhaltens liefert noch eine ganze Reihe interessanter Erkenntnisse:
  • bei Verwendung des neuen Auto-Werts (11) für das dynamic sampling gibt es in 12.1 recht merkwürdige Schätzungen, die auf den ersten Blick inkohärent wirken. Ursache ist die Verknüpfung von default-werten für die Basis-Tabellenstatistiken mit einem sampling der join cardinality (wobei im gegebenen Fall eigentlich kein sampling erfolgt, sondern der user query eine nahezu identische count-Operation vorausgeschickt wird). Dabei wird der korrekte Angabe allerdings nicht in allen Fällen zur Überschreibung eingesetzt.
  • das CBO-Trace (Event 10053) verschweigt sampling Operationen zur Bestimmung von join cardinalities. Allerdings erscheint im Trace die Angabe "Adjusted Join Cards", die die Überschreibung der berechneten Cardinality durch den ermittelten Wert anzeigt (allerdings ohne Angabe von Gründen). Randolf Geist verweist in diesem Zusammenhang auf das (halbwegs) neue Trace Framework in 11g (das ich mir gelegentlich genauer anschauen sollte).
Ich spare mir an dieser Stelle eine detailliertere Wiedergabe der Analyseschritte und verweise auf den Thread - obwohl der Fall eine meiner besseren detektivischen Leistungen der letzten Monaten gewesen sein dürfte.


Mittwoch, Dezember 11, 2013

compression in 12c

Angesichts der vorhin erwähnten Untersuchung von Julian Dyke habe ich noch mal überprüft, ob sich hinsichtlich des Themas compression in 12c Grundsätzliches verändert hat, und dazu folgendes Script verwendet:

drop table t_no_compress;
drop table t_basic_compress;
drop table t_oltp_compress;

create table t_no_compress
as
select rownum id
     , 10 col1
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 100000;

create table t_basic_compress compress
as
select *
  from t_no_compress;
  
create table t_oltp_compress compress for oltp
as
select *
  from t_no_compress;
  

exec dbms_stats.gather_table_stats(user, 't_no_compress', estimate_percent=>0)
exec dbms_stats.gather_table_stats(user, 't_basic_compress', estimate_percent=>0)
exec dbms_stats.gather_table_stats(user, 't_oltp_compress', estimate_percent=>0)  

select table_name
     , num_rows
     , blocks 
     , compression
     , compress_for
  from user_tables where table_name in ('T_NO_COMPRESS', 'T_BASIC_COMPRESS', 'T_OLTP_COMPRESS')
 order by table_name;
  
update t_no_compress set col1 = col1 + 1;  
update t_basic_compress set col1 = col1 + 1;  
update t_oltp_compress set col1 = col1 + 1;  

exec dbms_stats.gather_table_stats(user, 't_no_compress', estimate_percent=>0)
exec dbms_stats.gather_table_stats(user, 't_basic_compress', estimate_percent=>0)
exec dbms_stats.gather_table_stats(user, 't_oltp_compress', estimate_percent=>0)  

select table_name
     , num_rows
     , blocks 
     , compression
     , compress_for
  from user_tables where table_name in ('T_NO_COMPRESS', 'T_BASIC_COMPRESS', 'T_OLTP_COMPRESS')
 order by table_name;

Das Script liefert mir folgende Ergebnisse:

-- 11.1.0.7
-- vor dem Update
TABLE_NAME                       NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
T_BASIC_COMPRESS                   100000        162 ENABLED  BASIC
T_NO_COMPRESS                      100000        909 DISABLED
T_OLTP_COMPRESS                    100000        179 ENABLED  OLTP

-- nach dem Update
TABLE_NAME                       NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
T_BASIC_COMPRESS                   100000       1138 ENABLED  BASIC
T_NO_COMPRESS                      100000        909 DISABLED
T_OLTP_COMPRESS                    100000        508 ENABLED  OLTP

-- 12.1.0.1
-- vor dem Update
TABLE_NAME                       NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------------------------
T_BASIC_COMPRESS                   100000        151 ENABLED  BASIC
T_NO_COMPRESS                      100000        909 DISABLED
T_OLTP_COMPRESS                    100000        166 ENABLED  ADVANCED

-- nach dem Update
TABLE_NAME                       NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------------------------
T_BASIC_COMPRESS                   100000       1138 ENABLED  BASIC
T_NO_COMPRESS                      100000        909 DISABLED
T_OLTP_COMPRESS                    100000        508 ENABLED  ADVANCED

Ich will nicht behaupten, dass dieser Test alle denkbaren Fragen zum Thema beantwortet, aber auf den ersten Blick wirken die Ergebnisse ähnlich genug, um mich davon zu überzeugen, dass hier keine dramatische Verhaltensänderung im Spiel ist. Eine solche wäre aber vermutlich auch schon irgendwo erwähnt worden.

Noch einmal OLTP compression

Julian Dyke erklärt "why OLTP compression is still always not a good idea." Dazu gibt's ein kompaktes Beispiel, dass einmal mehr zeigt, dass OLTP compression bei reinen Update workloads kontraproduktiv ist.

#describe in sqlplus

Ein nettes kleines sqlplus Feature auf das Neil Chandler dieser Tage in seinem Blog hinweist: mit #desc kann man in sqlplus das describe Kommando aufrufen, während man an einem pl/sql Block arbeitet, ohne dazu die Editieroperation beenden zu müssen:

SQL> declare
  2     cnt number;
  3  begin
  4     select
  5  #desc t
 Name
 ----------------------------------------------------

 COL1

  5            count(distinct col1) into cnt from t;
  6     dbms_output.put_line(cnt);
  7  end;
  8  /
500000

PL/SQL-Prozedur erfolgreich abgeschlossen.


Donnerstag, Dezember 05, 2013

Repräsentation Adaptiver Pläne in ASH

Christian Antognini untersucht in seinem Blog die Darstellung adaptiver Pläne in ASH. Dabei weist er zunächst darauf hin, dass zu den unterschiedlichen Plan-Varianten unterschiedliche plan hash values vorliegen (was keine besondere Überraschung darstellt). Wenn die runtime engine während der Ausführung der Query auf den Alternativplan ausweicht, dann erscheint anschließend der plan hash value der neuen Variante in v$active_session_history. Interessant ist dabei, dass der Wechsel des hash values offenbar mit einer leichten Verzögerung dargestellt wird, also nicht exakt mit der tatsächlichen Plan-Verwendung korrespondiert, was durch das clevere Test-Arrangement sichtbar wird (bei dem für jede verarbeitete Zeile eine Wartezeit eingebaut ist). Abgesehen von dieser Ungenauigkeit entspricht das Verhalten ziemlich genau dem, was ich erwartet hätte, aber es ist immer nützlich, wenn solche Annahmen verifiziert werden können.

Montag, Dezember 02, 2013

BATCHED im Execution Plan für 12c

Im Pythian-Blog schreibt Timur Akhmadeev über seine Tests zur Bestimmung der Bedeutung der Operation
TABLE ACCESS BY INDEX ROWID BATCHED
im Exceution Plan für 12c. Grundsätzlich ist die Sammlung und Sortierung der im Index aufgefundenen rowids vor dem Zugriff auf die Tabelle nichts Neues: sie tritt auch schon in 11g als NL-Join Batching auf - und daneben gibt es noch weitere Prefetching-Mechanismen (etwa im Kontext von db file parallel read). Der Artikel liefert eine ganze Reihe von Detailbeobachtungen, die sich allerdings nicht ohne Weiteres auf einen gemeinsamen Nenner bringen lassen, weshalb ich mir die Nacherzählung erst mal spare und stattdessen das Fazit zitiere:
In 12c Oracle changed some internal code path which deals with the batched table access. But important thing is that the batched table access is not new, so even if you disable it either explicitly with _optimizer_batch_table_access_by_rowid or implicitly with optimizer_features_enable, Oracle will still be able to utilize a similar approach as it was in 11g.
Mal sehen, ob gelegentlich jemand einen eingängigeren Reim darauf findet.

Sonntag, November 24, 2013

Unsichtbare Spalten

Richard Foote erläutert in seinem Blog die grundlegenden Eigenschaften von invisible columns und weist dabei insbesondere auf einen interessanten Nebeneffekt hin: durch die Änderung der Sichtbarkeit kann man die Reihenfolge der Spalten in der Anzeige ändern. Dazu ein Beispiel:

drop table t;

create table t (
    a number
  , b number 
  , c number
);

insert into t(a, b, c) values (1, 2, 3);

select * 
  from t;

         A          B          C
---------- ---------- ----------
         1          2          3

alter table t modify a invisible;

select * 
  from t;

         B          C
---------- ----------
         2          3

alter table t modify a visible;

select * 
  from t;

         B          C          A
---------- ---------- ----------
         2          3          1

Die Spalte A erscheint nach der erneuten Setzung auf VISIBLE an letzter Stelle. Ein Blick auf die Informationen im data dictionary liefert die zugehörigen Metadaten:

select column_name
     , segment_column_id 
     , column_id
  from dba_tab_cols
 where table_name = 'T'
 order by segment_column_id;

COLUMN_NAME      SEGMENT_COLUMN_ID  COLUMN_ID
---------------- ----------------- ----------
A                                1          3
B                                2          1
C                                3          2

Während sich an der Ablage im Block (natürlich) nichts ändert (also an der segment_column_id), ergibt sich eine veränderte Reihenfolge in der Darstellung (column_id). In manchen Situationen könnte diese Möglichkeit recht nützlich sein.

Donnerstag, November 21, 2013

Suche in Tabellen ähnlicher Struktur

Wieder war es eine Frage im OTN-Forum, die mich an etwas erinnert hat, das ich fast vergessen hatte: die Möglichkeit, mit Hilfe von XML-Funktionen innerhalb einer Query auf alle Tabellen zuzugreifen, die eine Spalte mit einem bestimmten Namen haben. Wenn ich z.B. herausfinden will, wie viele Datensätze in welchen Tabellen einer Bedingung WHERE DEPTNO = 20 genügen, dann erhalte ich die Antwort über:

-- 11.1.0.7
select owner
     , table_name
     , to_number(
          extractvalue(
                    xmltype(
                            dbms_xmlgen.getxml('select count(*) c from '
                                                || owner || '.'|| table_name 
                                                || ' where deptno = 20')
                            )
                    ,'/ROWSET/ROW/C')) row_count
  from dba_tab_cols
 where column_name = 'DEPTNO'
 order by owner, table_name;
 
OWNER                          TABLE_NAME                      ROW_COUNT
------------------------------ ------------------------------ ----------
SCOTT                          DEPT                                    1
SCOTT                          EMP                                     5

Google erinnert mich gerade daran, dass ich einen Verweis auf Marco Gralikes entsprechenden Artikel schon vor zwei Jahren hier untergebracht hatte, damals allerdings ohne Beispiel. Schon damals war dbms_xmlgen anscheinend nicht mehr unbedingt das Mittel der Wahl, erfüllt aber nach wie vor seinen Zweck.

Montag, November 18, 2013

PIVOT und UNPIVOT

Gestern hatte ich hier Frank Kulashs schöne PIVOT+UNPIVOT-Lösung zu einem relativ seltsamen SQL-Problem untergebracht und passend dazu liefert heute Alex Nuijten ein umfassenderes Beispiel zum Thema.

Sonntag, November 17, 2013

Umsortierung von Werten in einem Datensatz

Wieder mal ein aussageschwacher Titel - aber in diesem Fall glaube ich, dass die Fragestellung tatsächlich schwer auf den Punkt zu bringen ist. Worum es geht, ist Folgendes: im OTN-Forum SQL and PL/SQL wurde dieser Tage die Frage gestellt, wie man die Daten der folgenden Tabelle so umordnen kann, dass die nach der ersten Spalte (col_1_rank) sortierte Kombination der ersten beiden Spalten (col_1_rank, col_1_value) mit der nach der dritten Spalte (col_2_rank) sortierten Kombination der Spalten drei und view (col_2_rank, col_2_value) verknüpft wird.

col_1_rank col_1_value col_2_rank col_2_value
1 AAA 3 HHH
2 BBB 2 GGG
3 CCC 5 JJJ
4 DDD 1 FFF
5 EEE 4 III

Als Ergebnis sollte sich also die folgende Tabelle ergeben:

col_1_rank col_1_value col_2_rank col_2_value
1 AAA 1 FFF
2 BBB 2 GGG
3 CCC 3 HHH
4 DDD 4 III
5 EEE 5 JJJ

Dabei galt die zusätzliche Einschränkung, dass zur Umsetzung kein Join verwendet werden durfte, weil nur ein Scan der Tabelle durchgeführt werden sollte.

Nun könnte man sicher einwenden, dass das eine recht bizarre Anforderung ist, und dass der einfache table scan mit komplexeren analytischen Funktionen unter Umständen teurer sein könnte, als ein Join der Daten der beiden logischen Blöcke in der Tabelle - aber als merkwürdige SQL-Fragestellung fand ich die Anforderung interessant genug, um eine Lösung zum Thema beizusteuern:

drop table t;
 
create table t (
    col_1_rank number
  , col_1_value varchar2(20)
  , col_2_rank number
  , col_2_value varchar2(20)
);
 
insert into t values (1, 'AAA', 3, 'HHH');
insert into t values (2, 'BBB', 2, 'GGG');
insert into t values (3, 'CCC', 5, 'JJJ');
insert into t values (4, 'DDD', 1, 'FFF');
insert into t values (5, 'EEE', 4, 'III');
 
with
basedata as (
select col_1_rank
     , col_1_value
     , ',' || listagg(col_2_value, ',') within group (order by col_2_rank) over() || ',' col_2_value
  from t
)
select col_1_rank
     , col_1_value
     , substr( col_2_value
             , instr(col_2_value, ',', 1, col_1_rank) + 1
             , (instr(col_2_value, ',', 1, col_1_rank + 1) - 1) - (instr(col_2_value, ',', 1, col_1_rank))
              ) col_2_value
  from basedata
order by col_1_rank;

COL_1_RANK COL_1_VALUE          COL_2_VALUE
---------- -------------------- ------------------------------
         1 AAA                  FFF
         2 BBB                  GGG
         3 CCC                  HHH
         4 DDD                  III
         5 EEE                  JJJ

Mir schien das als obskure Lösung zu einer obskuren Fragestellung ganz angemessen, aber kurz nach meiner Antwort kam Frank Kulashs Lösungsvorschlag:

SELECT   *
FROM      t
UNPIVOT   (               (mutual_rank, val)
          FOR  label IN ( (col_1_rank,  col_1_value)  AS 1
                        , (col_2_rank,  col_2_value)  AS 2
                        )
          )
PIVOT     (    MIN (val)
          FOR  label  IN ( 1  AS col_1_value
                         , 2  AS col_2_vlaue
                         )
          )
ORDER BY  mutual_rank
;

MUTUAL_RANK COL_1_VALUE          COL_2_VLAUE
----------- -------------------- --------------------
          1 AAA                  FFF
          2 BBB                  GGG
          3 CCC                  HHH
          4 DDD                  III
          5 EEE                  JJJ

-- wobei der UNPIVOT-Abschnitt die eingehenden Datensätze
-- in ihre beiden logischen Abschnitte teilt
-- und die PIVOT-Klausel für die neue Verknüpfung zuständig ist
SELECT   *
FROM      t
UNPIVOT   (               (mutual_rank, val)
          FOR  label IN ( (col_1_rank,  col_1_value)  AS 1
                        , (col_2_rank,  col_2_value)  AS 2
                        )
          );

     LABEL MUTUAL_RANK VAL
---------- ----------- --------------------
         1           1 AAA
         2           3 HHH
         1           2 BBB
         2           2 GGG
         1           3 CCC
         2           5 JJJ
         1           4 DDD
         2           1 FFF
         1           5 EEE
         2           4 III

Da die LISTAGG-Variante spätestens am 4000 Byte-Limit (= maximale Größe für VARCHAR2) des Ergebnisses an ihre Grenzen stößt, ist die PIVOT/UNPIVOT-Lösung deutlich nützlicher. Ohnehin ist es mein Prinzip im OTN-Forum nichts in Threads zu schreiben, an denen der Herr Kulash beteiligt ist, weil der normalerweise ohnehin schon die passende Antwort gegeben hat...

Freitag, November 15, 2013

Vereinfachte Administration für Partitionierte Tabellen in 12c

Gwen Lazenby zeigt im Blog der Oracle University ein paar nette Verbesserungen bei der Administration partitionierter Tabellen, die mit Release 12c eingeführt wurden. Hauptsächlich geht es dabei um die Möglichkeit, diverse Partitionen mit einem einzelnen Kommando zu behandeln - also zu erzeugen, zu splitten, zu verschmelzen oder zu löschen. Dabei enthält der Artikel recht umfangreiche Beispiele. Ich vermute, dass da relativ wenig Magie im Spiel ist - sprich: keine neue interne Logik -, sondern nur eine syntaktische Vereinfachung integriert wurde (habe mir die Details aber nicht angeschaut); aber gerade solche Vereinfachungen ersparen ermüdende Routinearbeit und reduzieren damit auch die Fehleranfälligkeit administrativer Operationen.

Montag, November 11, 2013

Reverse Indizes und Remote-Zugriffe

Jonathan Lewis zeigt ein recht erstaunliches Phänomen, das einerseits merkwürdig und andererseits dokumentiert ist - seit den Tagen von Oracle 8.1.5 gilt: "Reverse indexes on remote tables are not visible to the optimizer. This can prevent nested-loop joins from being used for remote tables if there is an equijoin using a column with only a reverse index." Was man als ein weiteres Argument dafür ansehen kann, reverse key indexes mit Bedacht einzusetzen. Im Artikel wird das Verhalten (natürlich) unter Einsatz eines Beispiels vorgeführt.

Sonntag, November 10, 2013

Bytes Angaben für View-Operatoren im Ausführungsplan

Manchmal stellt mich die Aufgabe der Wahl eines passenden Titels für einen Blog-Eintrag vor unlösbare Schwierigkeiten...

Vor knapp einem Monat hat Randolf Geist einen Artikel mit dem deutlich griffigeren Titel View Data Volume Estimates veröffentlicht, den ich an dieser Stelle kurz zusammenfasse, um die Chance zu erhöhen, mich bei Bedarf an das angesprochenen Verhalten zu erinnern. Worum es geht, ist Folgendes: normalerweise verwendet der Optimizer die column statistics zur Berechnung der bytes-Angabe im execution plan. Im Fall des Auftretens eines View-Operators im Plan für eine unmerged view ist dieses Vorgehen aber nicht möglich und der Optimizer muss stattdessen auf default-Werte ausweichen, die aus den Spaltendefinitionen abgeleitet werden - in der Regel scheint dabei ein Füllgrad von 50% angenommen zu werden (also z.B. 50 Byte für eine Spalte vom Typ VARCHAR2(100)). Im Fall der Verwendung von multi-byte-Zeichensätzen werden die Schätzung daher noch weiter erhöht.

Gefährlich können falsche Schätzungen des Datenvolumens bei allen Operationen sein, die sich auf diese Angabe beziehen, vor allem aber bei HASH JOINs, bei denen die Bestimmung von input und probe set davon abhängt. Im Artikel folgen einige Beispiele, die das Verhalten vorführen. Interessant ist dabei auch der Hinweis, dass in diesem Fall dynamic sampling keine Verbesserung bringt, da dabei ähnliche Kalkulationen ins Spiel kommen wie im Fall der Verwendung von Statistiken. Der Herr Geist schließt mit dem Hinweis, dass das Verhalten auch in 12c unverändert ist.

Mittwoch, November 06, 2013

Postgres: Database Links und Foreign Data Wrapper

Es gibt viele Dinge, die mir an postgres ausgesprochen gut gefallen: die Datenbank hat einen sehr ausgereiften SQL-Dialekt, eine erstaunliche Erweiterbarkeit, zahllose Datentypen und mit psql ein sehr schönes command-line interface. Wenn ich allerdings darüber nachdenke, was mir an diesem RDBMS weniger gut gefällt, dann gehört dazu die Tatsache, dass die Datenbanken eines postgres Clusters ganz strikt voneinander getrennt sind, da ihre data dictionaries nichts voneinander wissen. Das FAQ im PostgreSQL Wiki sagt dazu: "There is no way to directly query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave." Wahrscheinlich kann man das Verhalten also theoretisch begründen - gefallen muss es mir deshalb aber noch nicht. Immerhin gibt es aber zwei Möglichkeiten, diese Beschränkung zu umgehen: die Verwendung von database links und den Einsatz eines foreign data wrappers.

Database Link

Die ältere Lösung ist dabei der database link, der es ermöglicht, eine Query zu definieren, die in einer entfernten Datenbank ausgeführt wird. Dabei gibt man eine Connection an und ergänzt das auszuführende Statement sowie eine Typ-Beschreibung der Ergebnisspalten. Dazu ein kleines Beispiel. Zunächst lege ich drei Datenbanken an, von denen zwei die Rolle von Quellsystemen spielen, während die dritte als Reporting-Datenbank fungiert. Im Beispiel liegen alle Datenbanken im gleichen postgres-Cluster (also dem, was man in anderen RDBMS als Instanz bezeichnet), aber sie könnten auch auf entfernten Rechnern und in unterschiedlichen Clustern liegen. Aus Gründen der Vereinfachung habe ich mich auch nicht um eine plausible Benutzerzuordnung gekümmert, sondern agiere überall als postgres:

-- Anlage von Test-Datenbanken
-- Reporting-DB
postgres=# create database dbadmin;
CREATE DATABASE
-- Quell-DBs
postgres=# create database t1;
CREATE DATABASE
postgres=# create database t2;
CREATE DATABASE

-- Quell-Tabellen
t1=# create table t1_tab1 as select generate_series(1, 10) id;
SELECT 10
t2=# create table t2_tab2 as select generate_series(1, 100) id;
SELECT 100

Dazu nun ein Zugriff über database link:

dbadmin=# SELECT *
dbadmin-#   FROM dblink('dbname=t1 user=postgres password=XXXXXX'
dbadmin-#             , 'select id from t1_tab1')
dbadmin-#     AS t1(id int)
dbadmin-# ;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 Zeilen)

In diesem kleinen Beispiel ist das Verfahren noch relativ handhabbar, aber für größere Queries kann es recht mühsam werden, die Spaltendefinitionen aufzuführen. Die Dokumentation empfiehlt, solche database links in Views zu packen, aber beim Zugriff auf eine größere Anzahl von Datenbanken wird auch diese Vorgehensweise sperrig.

Foreign Data Wrapper

Zur Vereinfachung des Vorgehens (und aus einer Reihe weiterer Gründe wie Transaktionsmanagement, Connection-Management und Performance, die in der Dokumentation detailliert erläutert werden) wurde mit postgres 9.3 die extension postgres_fdw in den Standard aufgenommen (für 9.2 gab es einen Backport). Diese Erweiterung erlaubt die Definition umfangreicherer statischer Objekte, die den Zugriff auf entfernte Datenbanken vereinfachen. Das folgende Beispiel basiert auf den Datenbank- und Tabellendefinitionen des database link Beispiels:

-- Installation der extension
dbadmin=# create extension postgres_fdw;
CREATE EXTENSION

-- Anlage von Metadaten-Objekten in der Reporting-Datenbank (DBADMIN)
-- Definition der Server
dbadmin=# CREATE SERVER fdw_t1 FOREIGN DATA WRAPPER postgres_fdw 
dbadmin=# OPTIONS (dbname 't1', host 'localhost');
CREATE SERVER
dbadmin=# CREATE SERVER fdw_t2 FOREIGN DATA WRAPPER postgres_fdw 
dbadmin=# OPTIONS (dbname 't2', host 'localhost');
CREATE SERVER

-- Definition von user-mappings
dbadmin=# CREATE USER MAPPING for postgres
dbadmin-#   SERVER fdw_t1
dbadmin-#   OPTIONS (user 'postgres', password 'XXXXXX');
CREATE USER MAPPING

dbadmin=# CREATE USER MAPPING for postgres
dbadmin-#   SERVER fdw_t2
dbadmin-#   OPTIONS (user 'postgres', password 'XXXXXX');
CREATE USER MAPPING

-- Definition von foreign tables
dbadmin=# CREATE FOREIGN TABLE t1_tab1
dbadmin-#   (
dbadmin(#     id integer
dbadmin(#   )
dbadmin-#   SERVER fdw_t1 OPTIONS (table_name 't1_tab1');
CREATE FOREIGN TABLE

dbadmin=# CREATE FOREIGN TABLE t2_tab2
dbadmin-#   (
dbadmin(#     id integer
dbadmin(#   )
dbadmin-#   SERVER fdw_t2 OPTIONS (table_name 't2_tab2');
CREATE FOREIGN TABLE

-- eine übergreifende Abfrage auf Zieltabellen 
-- aus zwei unterschiedlichen Datenbanken
dbadmin-# select 't2_tab2' table_name, count(*) cnt from t2_tab2;
 table_name | cnt
------------+-----
 t1_tab1    |  10
 t2_tab2    | 100
(2 Zeilen)

Natürlich bleibt auch in diesem Fall das Problem, dass man zur Anlage der foreign tables deren Definitionen im Quellsystem kennen muss, aber um diese Information zu bekommen, könnte man zusätzlich eine foreign table für information_schema.tables einrichten. Nützlich ist dabei auch, dass die Definition für eine foreign table nicht alle Spalten der Quelltabelle enthalten muss, so dass man sich auf die relevanten Angaben beschränken kann:

dbadmin=# CREATE FOREIGN TABLE t2_columns
dbadmin-#   (
dbadmin(#     table_name text
dbadmin(#   , column_name text
dbadmin(#   , data_type text
dbadmin(#   )
dbadmin-# SERVER fdw_t2 OPTIONS (schema_name 'information_schema', table_name 'columns');
CREATE FOREIGN TABLE
dbadmin=# select * from t2_columns limit 10;
  table_name  | column_name | data_type
--------------+-------------+-----------
 pg_statistic | starelid    | oid
 pg_statistic | staattnum   | smallint
 pg_statistic | stainherit  | boolean
 pg_statistic | stanullfrac | real
 pg_statistic | stawidth    | integer
 pg_statistic | stadistinct | real
 pg_statistic | stakind1    | smallint
 pg_statistic | stakind2    | smallint
 pg_statistic | stakind3    | smallint
 pg_statistic | stakind4    | smallint
(10 Zeilen)

Ich will nicht behaupten, dass damit alle Probleme gelöst wären - und finde immer noch, dass die entsprechenden Optionen anderer RDBMS (z.B. database links bei Oracle, linked server im SQL Server) etwas besser zu handhaben sind, aber ich denke, dass postgres_fdw einen signifikanten Fortschritt darstellt.

Übrigens fällt mir gerade noch ein Punkt ein, der mich bei postgres deutlich mehr stört als der gerade ausgeführte, nämlich das Fehlen detaillierter Informationen zu den ausgeführten Queries und ihrer Performance. Aber das ist wieder einmal eine Geschichte, die ein andermal erzählt werden soll.

Dienstag, November 05, 2013

Jonathan Lewis über Parallel Execution

Vor ein paar Wochen hat Jonathan Lewis eine Artikelserie begonnen, in der er erklärt, wie parallele Operationen durchgeführt werden und wie man parallele Pläne interpretieren kann:
  • Parallel Execution – 1
    • weist zunächst darauf hin, dass serielle Pläne in der Regel relativ harmlos bleiben und liefert ein paar Hinweise auf Fälle, in denen das nicht der Fall ist (subquery pushing, Repräsentation von scalar subqueries, Abweichende Reihenfolge für join order und order of operation).
    • entscheidend für parallele Ausführung ist, dass möglichst alle Teile des Plans sinnvoll parallelisierbar sind, damit ein Parallelisierungsgrad von N dazu führen kann, dass die Ausführungszeit auf 1/N sinkt. Dabei gibt es vier mögliche Punkte, die diese einfache Rechnung beeinflussen können:
      • der setup overhead der Parallelisierung ist nicht kostenlos.
      • für parallel query gibt es immer den Schritt der Weitergabe der Daten an das frontend, der eine finale Serialisierung erfordert (und damit ein bottleneck darstellen kann).
      • für eine Query mit Parallelisierungsgrad N werden 2 * N slaves verwendet.
      • für Exadata ergibt sich eine Parallelisierung auf Ebene der storage server unter Umständen auch für serielle Queries.
    • zunächst teilt der query coordinator die Tabelle in N Stücke (bzw. in 13 Stücke, da hier eine - in einem Kommentar des Autors erläuterte - 9:3:1-Strategie eingesetzt wird).
    • für Join-Operationen ist es nicht möglich, sicherzustellen, dass die N Teile der einen Tabelle zu den N Teilen der anderen Tabelle passen. Daher werden zwei sets von N parallel exececution slaves eingesetzt, wobei die erste Gruppe die erste Tabelle so effizient wie möglich einliest und die Ergebnisse an die zweite Gruppe übergibt, wobei ein Mechanismus verwendet wird, der einen möglichst effizienten Zugriff auf die zweite Tabelle (bzw. Datenmenge) gewährleisten soll.
    • eine Variante ist die Weitergabe der kompletten Ergebnismenge aus der ersten Tabelle an alle slaves der zweiten Gruppe. Diese "(broadcast, none)"-Operation ist dann sinnvoll, wenn nur eine sehr beschränkte Datenmenge aus der ersten Tabelle gelesen wird. Für große Datenmenge werden die Kosten der Vervielfältigung jedoch ein Problem.
    • wenn der Zugriff auf die erste Tabelle eine große Ergebnismenge liefert, wird statt der Broadcast-Strategie ein anderes Verfahren verwendet. In diesem Fall wird eine hash Funktion auf die Join-Spalten angewandt, um sicherzustellen, dass die parallel gelesenen Teilbereiche der verknüpften Tabellen zueinander passen. Diese "(hash, hash)"-Operation kann von der Verwendung von Bloom-Filtern profitieren, die eine Filterung vor dem Datentransfer ermöglichen (aber false positives zulassen).
  • Parallel Execution – 2
    • enthält ein handliches Beispiel mit einem Star Schema bestehend aus einer Fakten- und drei zugehörigen Dimensionstabellen, zu dem eine Query mit einem geeigneten seriellen Plan mit mehreren Hash Joins vorgestellt wird. Dieser Plan wird ausführlich erklärt und besteht im wesentlichen daraus, dass zunächst die Hash Maps der Dimensionstabellen im Speicher aufgebaut und dann die Sätze der Faktentabelle gelesen und gegen die Dimensionsdaten geprüft werden, so dass alle rows für die der probe step erfolgreich verläuft, unmittelbar in der Ergebnismenge landen. Der Hash Join ist in diesem Fall also keine blocking operation.
  • Parallel Execution – 3
    • liefert den parallelen Plan zur Query, die im vorangehenden Artikel vorgestellt wurde.
    • dabei werden einige zusätzliche Hints vorgestellt, die zur expliziten Definition des Parellelisierungsverfahrens verwendet werden können:
      • parallel(alias, 2) in Ergänzung zu full(alias): fordert einen parallelen FTS.
      • pq_distribute(alias none broadcast) in Ergänzung zu use_hash(alias) und swap_join_inputs(alias): definiert das parallelisierte Vorgehen beim Hash Join, in dem es die Methode der Weitergabe der slave sets festlegt.
    • es folgt eine detaillierte Analyse des parallelen Zugriffsplans:
      • die zu den FTS gehörigen steps PX BLOCK ITERATOR geben an, dass der parallele Scan über rowid ranges erfolgt.
      • Paare der Operationen PX SEND / PX RECEIVE zeigen an, dass die parallel slaves ihre Ergebnisse an den nächsten Schritt weitergeben. Die Weitergabe erfolgt dabei über virtuelle Tabellen, deren Namen TQ10000, TQ10001 etc. lauten (wobei TQ für table queue steht).
      • die Nummern in den Namen der TQ-Objekte entsprechen der Reihenfolge der Verwendung: TQ10000 wird also als erstes Objekt verwendet, dann TQ10001 usw.
      • die TQ-Spalte im Plan gibt an, welche steps für die Füllung einer virtuellen Tabelle verantwortlich sind: Q1,00 verweist dabei auf TQ10000.
      • abgesehen von der Parallelisierung entspricht die Verarbeitungsfolge im Beispiel recht exakt der Vorgehensweise der seriellen Ausführung.
    • in 12c gibt es weitere Informationen im Plan (etwa zu Bloom-Filtern) und neue Hints (pq_replicate) zur detaillierten Ablaufsteuerung.
  • Parallel Execution – 4
    • erläutert die in Teil 3 erwähnten Bloom-Filter genauer.
    • außerdem werden noch ein paar Details zu den virtual tables und zur parallel execution message size nachgeliefert.
      • wichtig ist zunächst, dass ein parallel slave nicht zur gleichen Zeit aus einer virtual table lesen und in eine andere virtual table schreiben kann.
      • dabei ist eine virtual table grundsätzlich einfach eine (relativ kleine) Menge von Pages im Speicher, deren Größe durch den Parameter _parallel_execution_message_size bestimmt wird (der unterschiedliche Default-Werte in unterschiedlichen Versionen hat und dessen Größe auch noch von anderen Parametern bestimmt wird, dabei zwischen 2K und 16K liegt).
      • Da dieser Speicherbedarf pro communication channel anfällt und jeder slave in einem slave set Daten an jeden slave der anderen slave sets weitergeben kann, ergibt sich in der Summe unter Umständen ein recht hoher Speicherbedarf (errechnet als: * * 3 (= erforderliche Pages) * _parallel_execution_message_size).
      • der Speicherplatz wird im shared pool oder in einem large pool "PX msg pool" bereitgestellt.
      • die relativ geringe Anzahl verfügbarer Pages in jedem channel führt dazu, dass es zu Wait-Events zwischen lesenden und schreibenden Zugriffen kommt. Diese Events sind: "PX Deq Credit: send blkd" und "PX Deq Credit: need buffer".
    • Bloom Filter helfen dabei, das zu transferierende Datenvolumen zu begrenzen und damit die "send blkd" und "need buffer" Waits zu minimieren.
      • besonders relevant ist dieser Filterung beim Hash Join (und dabei insbesondere beim Scan der zweiten Tabelle, deren Inhalte mit der in-memory hash table verglichen werden).
      • dabei wird der Bloom Filter während des Aufbaus der hash table bestückt und kann dann an die slaves, die den Scan der zweiten Tabelle durchführen, weitergegeben werden. Dabei kann ein Bloom Filter bekanntlich false positives enthalten (was in Christian Antogninis Artikel zum Thema genauer erklärt wird, auf den Jonathan Lewis verweist, und den ich hier auch schon gelegentlich erwähnt habe).
      • es folgt ein umfangreiches Beispiel mit Ausführungsplänen. Im Plan erscheint der Bloom Filter als JOIN FILTER CREATE Schritt auf Seite der Erzeugung der hash table und als JOIN FILTER USE beim Scan der zweiten Tabelle. In der predicate section erscheint dabei der Funktionsaufruf sys_op_bloom_filter().
  • Parallel Execution – 5
      • erläutert die Veränderungen, die sich ergeben, wenn man das Verteilungsverfahren von broadcast auf hash umstellt.
      • im Beispiel wird die Änderung des Verfahrens über pq_distribute-Hints hervorgerufen.
      • der Plan mit Hash-Verteilung ist deutlich komplizierter als der broadcast-Plan: es erscheinen sieben virtuelle Tabellen (statt vier) und die Verwendung von Bloom Filtern wird intensiver (wobei die sys_op_bloom_filter-Prädikate nicht vollständig sind).
      • ergänzend zum Plan mit rowsource-Statistiken werden die zugehörigen Informationen auf v$pq_tqstat aufgeführt.
      • Die Analyse folgt wieder der Nummerierung der virtuellen Tabellen. Ich spare mir an dieser Stelle die detaillierte Nacherzählung (die ziemlich ausufernd werden müsste) und erwähne nur, dass die Angaben zu den Bloom-Filtern im Plan nicht notwendig an den richtigen Stellen erscheinen. Zusätzlich gibt's noch eine Grafik zur Visualisierung der Operationen und ihrer Reihenfolge.

Donnerstag, Oktober 31, 2013

inoffizielle display_cursor Dokumentation

Eines der wichtigsten - aber leider offiziell nicht besonders gut dokumentierten - Hilfsmittel bei der Analyse von SQL-Zugriffsproblemen mit Oracle ist sei Release 10 die Prozedur dbms_xplan.display_cursor, zu der Kyle Hailey in seinem Blog eine ziemlich umfassende Erläuterung liefert (inklusive seiner Hilfs-Scripts zur grafischen Darstellung der Abweichung von E- und A-rows bei der Verwendung von rowsource statistics). Da mein Standard-Vorschlag bei Performance-Problemen die Erzeugung eines Plans mit rowsource Statistiken ist (über den Parameter statistics_level oder einfach mit Hilfe des gather_plan_statistics Hints), werde ich auf diesen Artikel im OTN-Forum in Zukunft vermutlich häufiger verweisen.

sqlplus output Komprimierung

Eine nette Idee, die Frits Hoogland in seinem Blog vorstellt: die Komprimierung von sqlplus output mit Hilfe einer named pipe:
This solution will look familiar to “older” Oracle DBA’s: this was how exports where compressed from the “original” export utility (exp).
Der Vorteil dieser Variante ist, dass eine Komprimierung während der Erzeugung des Ergebnisses möglich ist - die Ziel-Datei muss also nicht erst in normaler Größe angelegt und dann komprimiert werden.

Freitag, Oktober 25, 2013

Oracle 12c: in database archiving

Julian Dontcheff hat dieser Tage in seinem Blog eine ganze Reihe von Links zum Thema in-database archiving zusammengetragen und ein paar Bemerkungen zu den Performance-Aspekten dieses neuen Features gemacht - und mich damit auf die Idee gebracht, das Verhalten dieser Option etwas genauer zu betrachten. Ausgangspunkt war dabei die folgende Definition aus der Dokumentation:
In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.
With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance.
Was genau bedeutet dabei "without compromising application performance"? Dazu ein kleiner Test mit 12.1.0.1 auf meinem Windows-PC. Ich lege eine Tabelle mit zwei Spalten und der Option row archival an, die dafür sorgt, dass man Datensätze vor dem Optimizer verbergen kann:

drop table t1;

create table t1 (
    id number
  , padding varchar2(20)
) row archival;

insert /*+ append */ into t1(id, padding)
select rownum id, lpad('?', 20 , '?') padding
  from dual
connect by level <= 10000;

commit;

select file_id, block_id, blocks
  from dba_extents
where segment_name = 'T1';

FILE_ID   BLOCK_ID     BLOCKS
------- ---------- ----------
      9       1096          8
      9       1104          8
      9       1112          8
      9       1120          8
      9       1128          8
      9       1136          8
      9       1144          8

alter system dump datafile 9 block 1104;

Ein Blick in den block dump zeigt, dass die row archival Option intern über die Anlage einer zusätzlichen Spalte realisiert ist:

data_block_dump,data header at 0x777007c
===============
tsiz: 0x1f80
hsiz: 0x1d6
pbl: 0x0777007c
     76543210
flag=--------
ntab=1
nrow=226
frre=-1
fsbo=0x1d6
fseo=0x506
avsp=0x330
tosp=0x330

...

block_row_dump:
tab 0, row 0, @0x1f62
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  30
col  1: [ 3]  c2 0c 23
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f
tab 0, row 1, @0x1f44
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  30
col  1: [ 3]  c2 0c 24
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f

Vor die Spalten Id und Padding wird somit eine interne Spalte gestellt. Besonders überraschend ist diese Beobachtung eher nicht, wenn man bedenkt, dass die Archivierungs-Markierung eines Datensatzes als Update durchgeführt wird:

update t1 set ORA_ARCHIVE_STATE = 2 where id <= 5000;
commit;

Das Update setzt die Hälfte der Datensätze in der Tabelle auf einen ORA_ARCHIVE_STATE <> 0 und macht sie damit für den Optimizer unsichtbar. Eine einfache Query ohne Einschränkung bringt anschließend eine sinnvolle cardinality-Schätzung:

explain plan for
select *
  from t1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 |   131K|    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  5000 |   131K|    16   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."ORA_ARCHIVE_STATE"='0')

Ganz offensichtlich ist hier also keine schwarze Magie im Spiel, sondern nur eine virtuelle Spalte, die als default-Filterkriterium eingesetzt wird. Die Frage, die sich mir im Anschluss stellt, ist: gibt es jenseits der simplen Handhabung (und wahrscheinlich des Verhaltens bei komplexeren Operationen) einen prinzipiellen Unterschied zwischen dieser neuen Option und der Verwendung einer expliziten sichtbaren Spalte zur Kennzeichnung historischer Daten? Dazu das komplementäre Beispiel:

drop table t2;

create table t2 (
    archive_flag number
  , id number
  , padding varchar2(20)
) ;

insert /*+ append */ into t2(archive_flag, id, padding)
select 0 archive_flag
     , rownum id
     , lpad('?', 20 , '?') padding
  from dual
connect by level <= 10000;

select file_id, block_id, blocks
  from dba_extents
 where segment_name = 'T2';

FILE_ID   BLOCK_ID     BLOCKS
------- ---------- ----------
      9       3904          8
      9       3912          8
      9       3920          8
      9       3928          8
      9       3936          8
      9       3944          8
      9       3952          8

alter system dump datafile 9 block 3912;

Der zugehörige block dump scheint mir von der Version des impliziten Archivierungsverfahren kaum unterscheidbar zu sein: die Satzanzahl und die Angaben zum verwendeten Speicherplatz sind identisch und auch bei der Beschreibung der Datensätze gibt es nur marginale Differenzen (die sich daraus ergeben, dass ich mit einem NUMBER-Wert gearbeitet habe, statt mit VARCHAR2(4000) als was ORA_ARCHIVE_STATE tatsächlich definiert ist).

data_block_dump,data header at 0x1545807c
===============
tsiz: 0x1f80
hsiz: 0x1d6
pbl: 0x1545807c
     76543210
flag=--------
ntab=1
nrow=226
frre=-1
fsbo=0x1d6
fseo=0x506
avsp=0x330
tosp=0x330

...

block_row_dump:
tab 0, row 0, @0x1f62
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  80
col  1: [ 3]  c2 0c 23
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f
tab 0, row 1, @0x1f44
tl: 30 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  80
col  1: [ 3]  c2 0c 24
col  2: [20]  3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f 3f

Natürlich muss man in diesem Fall das Flag bei Zugriffen explizit angeben, aber davon abgesehen, scheint der Unterschied bei den internen Strategien zunächst eher marginal zu sein:

update t2 set archive_flag = 2 where id <= 5000;
commit;

exec dbms_stats.gather_table_stats(user, 'T2')

explain plan for
select *
  from t2
 where archive_flag = 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 |   136K|    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |  5000 |   136K|    16   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ARCHIVE_FLAG"=0)

Insofern würde ich auf Anhieb erst einmal behaupten, dass das Feature sicherlich administrative Vereinfachungen mit sich bringt - aber große Geheimnisse scheinen mir hinsichtlich der Performance-Eigenschaften der Operation eher nicht zu bestehen. Ich muss allerdings zugeben, dass ich über die elaborierteren Effekte des Features ("updates to archived data can be deferred during application upgrades to improve the performance of upgrades") und die vorgeschlagenen Komprimierungsverfahren noch nicht nachgedacht habe.

Donnerstag, Oktober 24, 2013

Postgres: csv Export

Das Thema ist andernorts schon hundertfach erläutert worden, aber da ich inzwischen mehrfach danach gesucht habe, hier eine kurze Notiz zu den beiden Möglichkeiten, csv-Dateien für die Ergebnisse von postgres-Queries zu erzeugen.

COPY-Kommando in PSQL

In diesem Fall erfolgt der Aufruf in psql und schreibt die csv-Datei test.csv ins aktuelle Arbeitsverzeichnis:

-- in psql
-- mit header und Semikolon als Feld-Delimiter
test=# \copy (select * from t) to test.csv with csv header delimiter ';'

PSQL-Aufruf mit Ausgabe in Datei

Hier erfolgt der Aufruf in der Shell und erzeugt die angegebene csv-Datei:

-- Beispiel mit windows cmd
-- --pset footer: unterdrückt feedback zur Anzahl glieferter Zeilen
-- -A: unaligned output mode; ohne die Einstellung liefert psql eine tabellarische Darstellung
-- -F: definiert den Feldseparator (hier also das Semikolon)
-- -c: Angabe der auszuführenden Query
psql.exe -U postgres -d test --pset footer -A -F; -c "select * from t" > c:\temp\test.csv

Sonntag, Oktober 20, 2013

SSAS: Sortierung und Compression

Wobei ich neidlos anerkenne, dass der Titel, den Danny Lee seinem Artikel, auf den ich mich hier beziehe, gegeben hat, deutlich hübscher ist: Analysis Services Multidimensional: It is the Order of Things (was sich übrigens nicht auf Michel Foucaults berühmtes Buch, sondern auf einen Dialog von Commander Sisko mit einem Jem'Hadar-Krieger bezieht, was nicht verwundert, denn wir sind hier ja nicht mehr in Kansas...).

Worum es dabei geht? Um den Zusammenhang zwischen Sortierung der relationalen Basisdaten und Größe des erzeugten Cubes (und insbesondere der MeasureGroups) - oder anders ausgedrückt: um den Einfluss der Sortierung auf die Komprimierbarkeit der Daten. In einem vorgestellten Beispiel reduziert die Sortierung die Cube-Größe dabei um 43% (was zu meinen eigenen - internen - Notizen passt, die ich gerade noch mal nachgeschlagen habe). Dabei gilt grundsätzlich, dass die compression um so stärker ausfällt, je kleiner der Range von DataIds ist, die für ein Segment berücksichtigt werden müssen. Neben der verbesserten Komprimierung erlaubt die Sortierung dabei auch eine effektivere Segment Elimination bei der Filterung nach Attributen. Da die Ordnung nach einem Attribut in der Regel zu einer größeren Unordnung für andere Attribute führt, gilt für Fakten, dass man bei ihrer Sortierung mit den Attributen beginnen sollte, die sich am häufigsten wiederholen - im Beispiel des Artikels ergäbe sich die Sortierreihenfolge (Date, Time, Product, Sales).

Das sind natürlich keine ganz neuen Einsichten, sondern Informationen, die man auch in den einschlägigen Publikationen zum Thema finden kann (die im Artikel auch verlinkt sind), aber furchtbar viel Neues gibt's zum Thema (multidimensional) SSAS wohl auch nicht zu sagen, was ich immer noch bedauere.

Freitag, Oktober 18, 2013

Falsche Ergebnisse durch Partition Exchange ohne Validation

Jonathan Lewis schreibt dieser Tage so viel, dass ich Mühe habe, mit der Lektüre zu folgen - vom Aufschreiben der zentralen Punkte ganz zu schweigen. Aber das heutige Quiz zeigt einen ziemlich gemeinen Effekt, der mir nur noch ganz vage in Erinnerung war und dort lieber einen besseren Platz bekommen sollte, nämlich die Tatsache, dass sich Oracle beim Partition Exchange without validation darauf verlässt, dass der Auftraggeber schon wissen wird, warum er etwas tut - und deshalb auch abwegige Ergebnisse liefern kann. Im Beispiel lieferte ein SELECT DISTINCT den gleichen Wert doppelt, da er einmal aus der korrekten Partition und einmal aus einer anderen Partition gelesen wurde, die eigentlich für ganz andere Werte vorgesehen war, aber ohne Prüfung durch den Partitionsaustausch an die unpassende Stelle gelangte. Wahrscheinlich irritieren mich solche Effekte in relationalen Datenbanken deutlich mehr als anderswo, weil ich damit rechne, dass das RDBMS mich schon davon abhalten wird, ungeheuren Blödsinn anzustellen.

Freitag, Oktober 11, 2013

Systemstatistiken im Exadata Modus

Seit Release 11.2.0.4 gibt es die Systemstatistiken in einer neuen Geschmacksrichtung: dem "exadata mode". Das hört sich vielleicht dramatischer an als es ist, da die Ermittlung von "exadata mode" system statistics exakt der Ermittlung von noworkload system statistics entspricht - abgesehen davon, dass der MBRC auf den Wert des Parameters db_file_multiblock_read_count gesetzt wird. Daher ergeben sich nur dann Unterschiede zwischen dem "exadata mode" und den herkömmlichen noworkload system statistics, wenn der Initialisierungsparameter db_file_multiblock_read_count nicht explizit gesetzt wurde, denn in diesem Fall verwendet Oracle den Standardwert 8 (im dictionary als hidden parameter _db_file_optimizer_read_count hinterlegt). Diese Zusammenfassung basiert auf der Grundlage von:
Christian Antogninis Artikel ist ein hervorragender Einstieg zum Thema CBO-Kalkulationen - und wahrscheinlich werde ich darauf noch häufiger verweisen (so wie auf seine Anleitung zum Lesen von Ausführungsplänen).

Mittwoch, Oktober 09, 2013

Zur Definition von Hints

So wie der Herr Kyte gerne über bind variables - bzw. ihr Fehlen - zu reden (fast hätte ich gesagt: zu schwadronieren) pflegt, wird Jonathan Lewis nicht müde zu betonen, dass Hints keine vagen Vorschläge, sondern Direktiven für den Optimizer sind, denen der CBO zu folgen hat (sofern sie syntaktisch korrekt und für den transformierten plan relevant sind und kein Bug im Spiel ist). In einem aktuellen Artikel sammelt er die Aussagen zum Thema aus Dokumentationen von 8 bis 12, wobei merkwürdigerweise ausgerechnet Version 12 die Aussagen abschwächt und von "instruct" statt von "force" spricht. Aber vielleicht ist das nur ein Akt der Höflichkeit.

Dienstag, Oktober 08, 2013

Komplexe SQL-Lösungen

Rob van Wijk hat endlich mal wieder einen Artikel veröffentlicht. Darin liefert er eine Lösung für das Problem der Verteilung von Tabellen stark unterschiedlicher Größe in gleichgroße Gruppen (wobei Größe einfach die Anzahl an Bytes ist). Aufgrund der deutlichen Größenunterschiede genügt es nicht, die Tabellen einfach über Modulo n Gruppen zuzuordnen, da dabei die erste Gruppe sehr viel größer werden würde als die n-te. Seine Lösung verwendet daher eine Model clause und folgende Vorgehensweise:
  • order all tables by size in descending order
  • place the N largest tables in groups 1 .. N
  • iterate over the tables with a non-empty size in descending order and add the table to the first group encountered whose size is below the running average size of the groups
  • iterate over the empty tables and add the table to the first group encountered whose total number of tables is below the average number of tables per group
Wie bei Verwendung der Model clause üblich, ist das verwendete SQL alles andere als intuitiv - jedenfalls für mich - , aber effektiv.

In einem Nachtrag zum Artikel verweist der Herr van Wijk dann noch auf einen ähnlichen Artikel im Blog von Brendan Furey, der mir bisher komplett entgangen war, und der eine wahre Fundgrube für SQL-Lösungen zu klassischen Optimierungsproblemen darstellt (etwa zum Travelling Salesman Problem). Dabei liefern die Artikeln nicht nur komplexe SQL-Lösungen, sondern auch sehr hübsche grafische Visualisierungen zu den Fragestellungen. Wenn ich mal wieder an die Grenzen meiner algorithmischen Möglichkeiten komme, wäre das ein guter Platz zum Suchen.

Samstag, Oktober 05, 2013

View-Export mit 12c

Carsten Czarski stellt in seinem Blog ein nettes kleines Feature des Data Pump in 12c vor: die Möglichkeit, Views als Tabellen zu exportieren, was die Notwendigkeit einer Materialisierung solcher Views über CTAS beseitigt. Dieses Feature hätte mir dieser Tage Arbeit sparen können, aber bei meinem Applikations-Umzug von einem RDBMS in ein anderes war Oracle 12c nicht im Spiel - eigentlich nicht einmal Oracle, es sei denn, man ließe MySQL unter dieser Flagge segeln...

Der Artikel des Herrn Czarski enthält neben allerlei Details noch einige Links auf seine älteren Erläuterungen zum DBMS_DATAPUMP Package, das die Einbindung des Exports in PL/SQL ermöglicht, und zur Verknüpfung von Data Pump und DBMS_SCHEDULER.

Sonntag, September 29, 2013

MySQL-Review von Thomas Kejser

Thomas Kejser - hab ich schon mal erwähnt, dass ich seine Artikel sehr schätze? - schreibt über MySQL aus der Sicht eines SQL Server Experten und liefert dabei eine schöne Liste zum Reifegrad der Implementierung von Features. Dabei kommt er zum (nicht besonders überraschenden) Ergebnis, dass MySQL keine Data Warehouse Engine ist, da es unter anderem keine Parallelisierung von Queries und keine Hash Joins anbietet. Auch die Instrumentierung kann ihn nicht überzeugen, während er die Partitionierungs-Features des Systems lobend erwähnt. Sein Fazit lautet:
Even after some exposure to MySQL I am still split in my opinion about the product. It it obvious that the features of MySQL are targeted towards coders who want to “whip up a quick database” and that the design philosophy is one inspired deeply by OLTP systems. This lack of warehouse database features sets some limits on what it can be used for. On the other hand, I am happy to see that MySQL doesn’t try to be everything to everyone. [...] We live in a world where the Web 2.0 developers have forsworn the beauty of relational algebra (mainly, I suspect, because they don’t actually grok single box parallelism) and where the big vendors have their heads so high up in the cloud that they have forgotten their customer base. It is encouraging to see MySQL keeping the relational candle lit, so that the lost NoSQL souls have somewhere to turn when the ACID burns leave them in the dark.
Sehr hübsch - die zugehörige Illustration sei Hieronymus Bosch überlassen...

Freitag, September 27, 2013

Shared-Pool-Verschmutzung durch Autotrace

Dass Autotrace nicht immer mit zuverlässigen Aussagen zur Ausführung von Queries dienen kann, ist weithin bekannt, und auch, dass die Ursache dieses Verhaltens darin liegt, dass Autotrace kein Bind Peeking durchführt. Weniger bekannt ist, dass die durch Autotrace erzeugten Pläne von folgenden Ausführungen wiederverwendet werden können, worauf Jonathan Lewis in seinem Blog hinweist. Randolf Geist hat das Thema vor einiger Zeit auch schon mal beleuchtet und festgestellt, dass die Gefahr, die von diesen wiederverwendeten Plänen ausgeht, nicht ganz so groß ist, da es sich dabei um spezielle EXPLAIN PLAN cursor handelt.

Mittwoch, September 25, 2013

SQL_ID aus SQL_TEXT erzeugen

Dass die SQL_ID als Hash für einen gegebenen SQL_TEXT erzeugt wird, konnte man vor längerer Zeit bei Tanel Poder erfahren. Jetzt hat Carlos Sierra eine handliche PL/SQL-Funktion veröffentlicht, mit der man die SQL_ID zu einer Query vor ihrer Ausführung berechnen kann (und im Artikel auch die Links auf den Artikel des Herrn Poder und eine entsprechende Python Funktion von Slavik Markovich aufgenommen).

Sonntag, September 22, 2013

Cardinality ohne Spaltenstatistiken

Ein Exkurs im Rahmen einer Anfrage im OTN Forum hat mir mal wieder gezeigt, wie viele Lücken mein Wissen über die Kalkulationsregeln des CBO hat. Die eigentliche Ausgangsfragestellung lasse ich dabei ausgeklammert, aber im Rahmen der Analyse wurde der Vorschlag gemacht, die column statistics einer Index-Spalte zu löschen. Die Frage ist jetzt, wie der CBO die Cardinality des Zugriffs bestimmt. Dazu das Beispiel, das ich auch im OTN-Thread beigesteuert habe, und in dem ich die cardinalities für IN-list-Zugriffe mit einem, zwei und zwanzig Elementen jeweils mit und ohne column statistics untersuche:

-- 11.1.0.7
drop table t;

create table t
as
select 'testtesttesttesttest' || rownum id
     , mod(rownum, 5) col2
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 100000;

alter table t add constraint t_pk primary key (id, col2);

exec dbms_stats.gather_table_stats(user, 'T')

-- with column statistics
explain plan for
select *
  from t
where id in ('testtesttesttesttest1'
              );

select * from table(dbms_xplan.display);

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    80 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    80 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_PK |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"='testtesttesttesttest1')

explain plan for
select *
  from t
where id in ('testtesttesttesttest1'
            , 'testtesttesttesttest2'
              );

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     2 |   160 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     2 |   160 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |     2 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"='testtesttesttesttest1' OR "ID"='testtesttesttesttest2')


explain plan for
select *
  from t
where id in ('testtesttesttesttest1'
            , 'testtesttesttesttest2'
            , 'testtesttesttesttest3'
            , 'testtesttesttesttest4'
            , 'testtesttesttesttest5'
            , 'testtesttesttesttest6'
            , 'testtesttesttesttest7'
            , 'testtesttesttesttest8'
            , 'testtesttesttesttest9'
            , 'testtesttesttesttest10'
            , 'testtesttesttesttest11'
            , 'testtesttesttesttest12'
            , 'testtesttesttesttest13'
            , 'testtesttesttesttest14'
            , 'testtesttesttesttest15'
            , 'testtesttesttesttest16'
            , 'testtesttesttesttest17'
            , 'testtesttesttesttest18'
            , 'testtesttesttesttest19'
            , 'testtesttesttesttest20'
              );

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    20 |  1600 |    26   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    20 |  1600 |    26   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |    20 |       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"='testtesttesttesttest1' OR "ID"='testtesttesttesttest10'
              OR "ID"='testtesttesttesttest11' OR "ID"='testtesttesttesttest12' OR
              "ID"='testtesttesttesttest13' OR "ID"='testtesttesttesttest14' OR
              "ID"='testtesttesttesttest15' OR "ID"='testtesttesttesttest16' OR
              "ID"='testtesttesttesttest17' OR "ID"='testtesttesttesttest18' OR
              "ID"='testtesttesttesttest19' OR "ID"='testtesttesttesttest2' OR
              "ID"='testtesttesttesttest20' OR "ID"='testtesttesttesttest3' OR
              "ID"='testtesttesttesttest4' OR "ID"='testtesttesttesttest5' OR
              "ID"='testtesttesttesttest6' OR "ID"='testtesttesttesttest7' OR
              "ID"='testtesttesttesttest8' OR "ID"='testtesttesttesttest9')

-- without column statistics
exec dbms_stats.delete_column_stats(user, 'T', 'ID')

explain plan for
select *
  from t
where id in ('testtesttesttesttest1'
              );

select * from table(dbms_xplan.display);

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |  1000 | 80000 |    86   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |  1000 | 80000 |    86   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_PK |   400 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"='testtesttesttesttest1')

explain plan for
select *
  from t
where id in ('testtesttesttesttest1'
            , 'testtesttesttesttest2'
              );

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |  1000 | 80000 |    87   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |  1000 | 80000 |    87   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |   400 |       |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"='testtesttesttesttest1' OR "ID"='testtesttesttesttest2')

explain plan for
select *
  from t
where id in ('testtesttesttesttest1'
            , 'testtesttesttesttest2'
            , 'testtesttesttesttest3'
            , 'testtesttesttesttest4'
            , 'testtesttesttesttest5'
            , 'testtesttesttesttest6'
            , 'testtesttesttesttest7'
            , 'testtesttesttesttest8'
            , 'testtesttesttesttest9'
            , 'testtesttesttesttest10'
            , 'testtesttesttesttest11'
            , 'testtesttesttesttest12'
            , 'testtesttesttesttest13'
            , 'testtesttesttesttest14'
            , 'testtesttesttesttest15'
            , 'testtesttesttesttest16'
            , 'testtesttesttesttest17'
            , 'testtesttesttesttest18'
            , 'testtesttesttesttest19'
            , 'testtesttesttesttest20'
              );

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |  1000 | 80000 |   102   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |  1000 | 80000 |   102   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |   400 |       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"='testtesttesttesttest1' OR "ID"='testtesttesttesttest10'
              OR "ID"='testtesttesttesttest11' OR "ID"='testtesttesttesttest12' OR
              "ID"='testtesttesttesttest13' OR "ID"='testtesttesttesttest14' OR
              "ID"='testtesttesttesttest15' OR "ID"='testtesttesttesttest16' OR
              "ID"='testtesttesttesttest17' OR "ID"='testtesttesttesttest18' OR
              "ID"='testtesttesttesttest19' OR "ID"='testtesttesttesttest2' OR
              "ID"='testtesttesttesttest20' OR "ID"='testtesttesttesttest3' OR
              "ID"='testtesttesttesttest4' OR "ID"='testtesttesttesttest5' OR
              "ID"='testtesttesttesttest6' OR "ID"='testtesttesttesttest7' OR
              "ID"='testtesttesttesttest8' OR "ID"='testtesttesttesttest9')

Meine vorläufige Interpretation dazu lautet:
  • mit (akkuraten) column statistics kann der CBO die cardinalities in allen Fällen (1, 2, 20) exakt bestimmen.
  • nach der Löschung der Statistiken der in der condition verwendeten Spalte wird in allen Fällen eine cardinality von 400 angenommen (wobei die cost Angaben sich sukzessive erhöhen, wenn sich die Länge der IN-list vergrößert).
  • die 400 bleiben übrigens auch erhalten, wenn man auch die Statistiken der zweiten PK-Spalte löscht und sogar, wenn man die zweite Spalte nicht in den PK aufnimmt (die zugehörigen Tests habe ich hier unterschlagen, da sie mit geringfügigem Aufwand aus dem bestehenden Test erzeugt werden können). In diesem Fall wird allerdings aus dem range scan ein unique scan und die cardinality ist für die Variante mit einem Element korrekt mit 1 bestimmt - offenbar erkennt der CBO in diesem Fall, dass ein unique scan auf einen Wert auch nur (höchstens) einen Satz liefern kann.
  • Anscheinend ist dieser Faktor 400/100000 = 0,004 ein fixierter Wert für diesen Fall.
  • wenn man den Index komplett entfernt, kommt der CBO im Test stabil auf eine cardinality von 1000 also 0,001.
Ich vermute, dass diese Annahmen des CBO irgendwo dokumentiert und bekannt sind, aber den einzigen Hinweis, den ich auf Anhieb finden konnte, liefert Richard Foote, der einen 0,4% bzw. 1% guess des CBO erwähnt, aber nicht näher erläutert. Möglicherweise trage ich hier die Details noch nach, sollte ich sie irgendwo finden.

Samstag, September 21, 2013

Skalare Subqueries in der WHERE clause

Vor neunundzwanzig Tagen hat Tanel Poder seinen Artikel Scalar Subqueries in Oracle SQL WHERE clauses (and a little bit of Exadata stuff too) veröfffentlicht und ehe der aus meinem reader verschwindet, sollte ich dazu ein paar Notizen machen.
  • eine scalar subquery in der WHERE clause hat die Form: WHERE ... = (select * from ...). Entscheidend ist dabei das Gleichheitszeichen, das diesen Fall von anderen Subqueries unterscheidet.
  • im Plan erscheint der Zugriff eingerückt unter dem Zugriff auf die Daten der main query, wenn in der Transformation eine push subquery operation durchgeführt wird (sichtbar als PUSH_SUBQ in den outline Hints): "So, as subqueries in a WHERE clause exist solely for producing data for filtering the parent query blocks rows, the PUSH_SUBQ means that we push the subquery evaulation deeper in the plan, deeper than the parent query block’s data access path, between the access path itself and data layer, which extracts the data from datablocks of tables and indexes. This should allow us to filter earlier, reducing the row counts in an earlier stage in the plan, thus not having to pass so many of them around in the plan tree, hopefully saving time and resources."
  • wenn man die Transformation mit einem NO_PUSH_SUBQ verbietet, ergibt sich ein Plan, in dem ein Filter step erscheint und darunter der Hauptabellenzugriff und der Zugriff für die Subquery auf einer Einrückungsebene (so wie Filter-Prädikate in der Regel auftreten - oder, vorsichtiger ausgedrückt, wie sie mir normalerweise begegnen), wobei in den Prädikaten die Filter-Query explizit aufgeführt ist.
  • mit PUSH_SUBQ erfolgt die Filterung beim Tabellenzugriff auf die Haupttabelle (die trotzdem komplett gelesen werden muss), während NO_PUSH_SUBQ die Filterung in den Filter step verschiebt. Für gewöhnliche Oracle-Datenbanken ist der Performance-Unterschied nicht allzu groß, aber in einem Exadata-System kann der Smart Scan die Filterung in die storage cells verschieben, was die Laufzeit der Test-Query dramatisch reduziert.
Klingt mal wieder, als wäre Exadata ein spannendes Thema...

Freitag, September 20, 2013

_fifteenth_spare_parameter

Security ist ein Thema, dem mit dem ich mich nicht unbedingt besonders gerne und besonders häufig beschäftige, aber auf einen Verweis auf den Artikel Fix for oradebug disable auditing available (11.2.0.3/11.2.0.4/12.1.0.1) von Alexander Kornbrust kann ich nicht verzichten, da dort der wunderbare verborgene Parameter _fifteenth_spare_parameter erscheint, den Oracle mit dem ebenbürtigen Kommentar "fifteenth spare parameter" versehen hat. Inhaltlich geht es im Artikel übrigens darum, dass man via ORADEBUG Auditing mit Hilfe von OS-Kommandos deaktivieren kann - und mit einer passenden Einstellung von _fifteenth_spare_parameter lässt sich die ORADEBUG-Funktion einschränken oder deaktivieren. Das hätte ich normalerweise vermutlich gleich wieder vergessen, aber eine bessere Erinnerungsstütze als _fifteenth_spare_parameter kann ich mir kaum vorstellen...

Samstag, September 14, 2013

MV Refresh über Partition Exchange

Jonathan Lewis beschreibt in seinem Blog ein Verfahren, das ich in ähnlicher Weise gelegentlich auch schon verwendet hatte - und mich dabei immer gewundert habe, dass es nicht häufiger eingesetzt bzw. in technischen Blogs beschrieben wird: die Verwendung von Partition Exchange zum performanten Austausch einer Materialized View gegen eine neu erzeugte prebuilt table. Die Idee dabei ist einfach, dass eine MV, die permanent für Abfragen verfügbar bleiben muss, nicht einfach über truncate und insert append neu befüllt werden kann (wie es die atomic_refresh Option erlaubt). Stattdessen kann man aber den Neuaufbau der MV in einer Hilfstabelle durchführen, die man dann gegen die bisher verwendete Tabelle per Partition Exchange austauscht, was natürlich voraussetzt, dass die MV als partitionierte Tabelle mit einer einzigen Partition angelegt wurde. Da in der MV nach dem Aufbau keine weiteren DML-Operationen durchgeführt werden, kann man die Segmente der Tabelle und zugehöriger Indizes so klein wie möglich machen und zur Beschleunigung des Aufbaus kann man diesen auch noch als nologging durchführen, um die Generierung von undo und redo zu verringern. Der Artikel beruhigt mich, denn ich hatte immer das vage (und unangenehme) Gefühl, bei meiner Implementierung - die allerdings keine echten MVs, sondern reguläre Dimensionstabellen erzeugte - irgendetwas Wichtiges übersehen zu haben, was aber anscheinend nicht der Fall ist.

Donnerstag, September 12, 2013

Fehlende Partitions-Statistiken

Doug Burns schrieb es dieser Tage in seinem Blog: "blogging is over!" Möglicherweise stimmt das sogar, aber solange ich keine Beispiele dafür finde, dass jemand eine umfassende technische Erläuterung auf 140 Zeichen unterbringt, bleibe ich Blog-Schreiber und -Leser. Möglicherweise neige ich inzwischen auch zu Sentimentalität - oder einfach zur Senilität; die Grenze dazwischen ist vermutlich nicht immer ganz scharf.

Immerhin hat der Herr Burns seine Bemerkung mit dem Vorsatz verbunden, wieder häufiger zu bloggen und das fände ich gerade in seinem Fall auch sehr erfreulich. Sein erster technischer Beitrag nach einer längeren Pause trägt den Titel 10053 Trace Files - Global Stats on Partitioned Tables und beschäftigt sich mit den Analyse-Möglichkeiten von Optimizer Trace Files zur Beantwortung von Fragen nach der Verwendung lokaler oder globaler Statistiken. Dabei ist klar, dass der CBO globale Statistiken verwendet, sobald auf mehr als eine Partition zugegriffen wird, und dass lokale Partitions-Statistiken verwendet werden, wenn der Zugriff sich auf die fragliche Partition beschränkt. Die Frage im Artikel lautet: was passiert, wenn beim Zugriff auf die einzelne Partition keine Statistiken für diese Partition vorliegen? In diesem Fall weicht der CBO auf die globalen Statistken aus (und nicht etwa auf dynamic sampling), was im CBO-Trace durch den Hinweis "(Using composite stats)" ausgewiesen ist. Eine große Überraschung ist das eher nicht, aber es ist schön, einen Test als expliziten Beleg dafür zu haben.

Mittwoch, September 11, 2013

Postgres Marginalien

Zwei Kleinigkeiten, denen ich bei meiner Arbeit mit postgres begegnet bin:

Mit dem Nummerngenerator generate_series lassen sich sehr komfortabel Testdaten erzeugen:

-- aufsteigend in einer Schritten
test_import=# select * from generate_series(1,10);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 Zeilen)

-- absteigend in zweier Schritten
test_import=# select * from generate_series(10,0,-2);
 generate_series
-----------------
              10
               8
               6
               4
               2
               0
(6 Zeilen)

Das scheint mir doch etwas komfortabler zu sein, als meine übliche connect-by-dual-Methode für Oracle, obwohl ich auch mit der sehr zufrieden bin.

Auch nett sind temporary sequences  die ich dieser Tage an Stelle einer Variablen in einem Script verwendet habe. Für temporary sequences gilt:
If specified, the sequence object is created only for this session, and is automatically dropped on session exit. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists, unless they are referenced with schema-qualified names.
Normalerweise hätte ich das als eher seltsames Feature angesehen, aber vorgestern brauchte ich einfach ein paar Ids, die ich bei der initialen Füllung von Tabellen verwenden wollte - eine permanente Sequence schien mir etwas übertrieben, aber das temporäre Objekt passte in diesem Fall erstaunlich gut. Hier noch ein kleiner Test, der zeigt, dass die Sequenz angelegt und im Dictionary registriert wird, aber nach dem Ende der Session verschwindet.

test_import=# create temporary sequence test_seq;
CREATE SEQUENCE
test_import=# select * FROM pg_class c WHERE c.relkind = 'S';
 relname  | relnamespace | reltype | reloftype | relowner | relam |
----------+--------------+---------+-----------+----------+-------+
 test_seq |        33778 |   33781 |         0 |       10 |     0 |
(1 Zeile)

test_import=# \q
--> Neuanmeldung

postgres=# select * FROM pg_class c WHERE c.relkind = 'S';
 relname | relnamespace | reltype | reloftype | relowner | relam |
---------+--------------+---------+-----------+----------+-------+
(0 Zeilen)

Übrigens gefällt mir psql als Kommandozeilen-Tool ziemlich gut - man merkt, dass sich da jemand in Sachen Dokumentation und Bedienbarkeit einige Mühe gegeben hat.

Samstag, September 07, 2013

Histogramm Grundlagen

Jonathan Lewis hat bei AllThingsOracle eine Serie von Artikeln zum Thema Histogramme begonnen. Und wenn der Herr Lewis sich die Mühe macht, ein solches Thema in einer Artikelserie zu behandeln, dann kann man davon ausgehen, dass sich eine Verlinkung lohnt. Zumindest der erste Artikel erweckt dabei den Eindruck, dass es tatsächlich eher um Konzepte und Grundlagen geht als um Spezial- und Sonderfälle, aber ich höre dem Autor auch dann gerne zu, wenn ich das, was er erzählt, schon weiß - vielleicht höre ich dann sogar besonders gerne zu ...
  • Histograms Part 1 – Why?: beschäftigt sich mit den Grundlagen, der Gleichverteilungsannahme des CBO und den sich daraus ergebenden Problemen bei Ungleichverteilung (data skew). Als Lösung für solche Probleme kommen neben Histogrammen auf virtual columns in Frage. Vorgestellt wird das Konzept der frequency histograms und die klassischen Probleme der Histogramme werden erläutert (sie vertragen sich nicht mit Bindewerten; ihre exakte Ermittlung ist teuer; sampling führt oft zu schwachen Ergebnissen - Stichwort: auto_sample_size; man muss den richtigen Zeitpunkt für die Ermittlung treffen).
  • Histograms Part 2: mit einer umfassenden Diskussion von height-balanced histograms - und vor allem ihrer Schwächen. Der Artikel beginnt mit einer anschaulichen Erläuterung des Prinzips dieses Histogrammtyps, der dann verwendet wird, wenn die Anzahl unterschiedlicher Werte eine bestimmte Schwelle überschreitet (vor 12c waren das 254 mögliche Buckets), so dass nicht mehr jeder einzelne Wert ins Histogramm eingefügt werden kann, sondern die Endpunktwerte für gleichgroße Abschnitte der (sortierten) Gesamtmenge bestimmt werden müssen. Dabei werden Werte als populär gekennzeichnet, wenn sie mindestens zwei Buckets umspannen. Für extrem häufige Werte ist das kein Problem, aber Werte, die knapp weniger als zwei Buckets umfassen (und somit nahezu populär sind), werden in ihrer Kardinalität falsch eingeschätzt. Für die Schwächen der height-balanced histograms gibt es zwei Abhilfen: die manuelle Erzeugung geeigneter Histogramme und die in 12c ergänzten Histogrammtypen (top n histograms bzw. die Erhöhung der maximal möglichen Bucket-Anzahl).
  • 12c Histograms pt.3: ja, es ist kleinlich, aber was die einheitliche Benamung von Artikeln einer Serie angeht, gewinnt der Herr Lewis diesmal keine Punkte. Teil 3 jedenfalls erläutert die Implementierung von hybriden Histogrammen. Diese Strategie verschiebt die Endpunkte der Buckets bei einer Wertwiederholung und erfasst neben den Endpunkten der Buckets auch eine Information darüber, wie oft ein Endpunktwert wiederholt wurde, was die Bestimmung populärer Werte massiv verbessert. Wenn ich den vorangehenden Satz noch einmal lese, wird mir klar, dass er rein gar nichts erklärt - aber zu meiner Verteidigung muss ich sagen, dass mir auch die Erläuterung im Artikel erst nach wiederholter Lektüre und intensiverem Nachdenken klar wurde: die Erläuterung des Verfahrens ist also nicht ganz einfach...
Bei Erscheinen der folgenden Artikel werde ich diesen Eintrag wahrscheinlich ergänzen (was für Teil 2 und 3 nun erledigt wäre).