MDX-Syntax-Check

Chris Webb zeigt, wie man im SSMS überprüfen kann, ob eine MDX-Query syntaktisch korrekt ist, ohne sie tatsächlich auszuführen. Ich bin zwar vom Ausführen auch syntaktisch korrekter MDX-Queries inzwischen weitgehend abgekommen, halte das aber trotzdem für recht nützlich. Herr Webb jedenfalls sieht folgende Einsatzmöglichkeiten:

This functionality could be useful in situations where you wanted to test the syntax of an MDX query or indeed just a calculation – it would allow you to do this without actually running the query and then killing it (and some queries don’t die immediately when they’re cancelled, as you might know).

Hash table size für Lookup-Ergebnisspeicherung

Jonathan Lewis hat dieser Tage in seinem Blog einen Fall untersucht, in dem ein Funktionsaufruf einen Tabellenzugriff enthält, der im Execution Plan nicht erscheint (was auch bei FILTER-Operationen vorkommen kann). Wenn man die rowsource-Statistiken und die projection-Informationen betrachtet, wird deutlich, dass der Funktionsaufruf in einem SORT UNIQUE step untergebracht ist. Festhalten kann man auf jeden Fall, dass der Plan die Operation nicht besonders deutlich abbildet.

Ausgehend vom gegebenen Beispiel habe ich ein paar Experimente durchgeführt, die ein Ergebnis lieferten, das ich so nicht erwartet hatte:

drop table t1;
drop table t2;

-- nur 250 unterschiedliche IDs, statt 2500 im Original
create table t1 tablespace test_ts
as
select
    mod(rownum, 250)          id,
    lpad(rownum,200)    padding
from    all_objects
where   rownum <= 2500
;

create table t2 tablespace test_ts
as
select  * from t1
;

exec dbms_stats.gather_table_stats(user, 't1')
exec dbms_stats.gather_table_stats(user, 't2')

-- Funktion als DETERMINISTIC definiert
create or replace function f (i_target in number)
return number deterministic
as
    m_target    number;
begin
    select max(id) into m_target from t1 where id <= i_target;
    return m_target;
end;
/

select  /*+ gather_plan_statistics */
    id
from    t1
minus
select
    f(id)
from    t2
;

-- letzte Ergebnisspalten aus Gründen der Übersichtlichkeit abgeschnitten
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.16 |   54747 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.16 |   54747 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.16 |   54670 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - STRDEF[22]
   2 - (#keys=1) "ID"[NUMBER,22]
   3 - "ID"[NUMBER,22]
   4 - (#keys=1) "F"("ID")[22]
   5 - "ID"[NUMBER,22]

Die Änderung im Test liegt nur in der Reduzierung der ID-Werte und der Definition der Funktion als DETERMINISTIC. Meine Annahme war, dass der Funktionsaufruf auf diese Weise nur einmal für jeden distinkten Wert ausgeführt werden müsste, woraus sich 250 FTS auf die Tabelle T1 ergeben sollten. Daraus ergab sich die Erwartung, dass die Buffers-Angabe in step 4 bei 19250 (= 250 * 77) liegen würde. Tatsächlich lautete das Ergebnis aber 54670 (= 710 * 77). Woher kommt die Abweichung? Die Antwort lieferten mir die Kommentare von Sayan Malakshinov und Kapitel 9 (Query Transformation) in Cost Based Oracle: die intern zur Speicherung der Zwischenergebnisse des Lookups verwendete HASH TABLE ist nicht groß genug, um hash collision zu vermeiden. Um die Größe dieser Struktur zu verändern, kann man den Parameter _query_execution_cache_max_size anpassen (default: 65536; wie immer gilt, dass die Änderung von underscore-Parametern auf eigene Gefahr erfolgt):

alter session set "_query_execution_cache_max_size"=262144;

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.09 |   29820 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.09 |   29820 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.08 |   29743 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------

alter session set "_query_execution_cache_max_size"=2097152;

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.06 |   19425 |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.06 |   19425 |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.06 |   19348 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------

Mit den 2M für _query_execution_cache_max_size bin ich dann mit 19348 Buffers schon recht nah am erwarteten Ergebnis von 19250 - und das genügt mir in diesem Fall.

Baseline-Zuordnung in SPM

Maria Colgan erläutert, dass die Zuordnung von Baselines zu SQL-Queries über Signaturen (das SQL_HANDLE in DBA_SQL_PLAN_BASELINES) erfolgt, die über Schema-Grenzen hinweg identisch sind. Da bei der Berücksichtigung von Baselines aber immer auch das Schema ausgewertet wird, ist das kein Problem - alles andere hätte mich auch gewundert.

Testdatengenerierung im SQL Server

Aaron Bertrand stellt im SQLPerformance-Blog einige Möglichkeiten zur Generierung von Sequenzen und Testdaten im SQL Server vor:

• Generate a set or sequence without loops – part 1: stellt einige Verfahren zur Datengenerierung vor (Verwendung einer vorliegenden Tabelle, CTE, rekursive CTE) und misst die Laufzeiten zur Erzeugung von 1000 rows.
• Generate a set or sequence without loops – part 2: testet die Laufzeiten bei der Genereirung größerer Datenvolumen. Dabei ist das Verfahren mit der Verwendung einer rekursiven CTE konkurrenzlos langsam, während die übrigen Vorgehensweisen eine ähnliche Performance aufweisen.
  
• Generate a set or sequence without loops – part 3: zeigt, wie man mit unterschiedlichen Generierungsverfahren lückenlose Sequenzen erzeugen kann, und vergleicht die Performance dieser Verfahren.

Gelegentlich könnte ich die Ergebnisse mal mit Jonathan Lewis' Überlegungen zur Testdatenerzeugung im SQL Server vergleichen.

Cardinalities für HAVING

Randolf Geist erläutert in seinem Blog die Cardinality-Schätzungen des CBO für Gruppierungen mit einer HAVING clause. Während die Kalkulation für GROUP BY in vielen (aber längst nicht allen) Fällen recht solide sind, kommen beim HAVING immer default-Werte ins Spiel, was natürlich zu recht unerfreulichen Resultaten führen kann. Problematisch wird der Effekt vor allem dann, wenn die Gruppierung nicht am Ende der auszuführenden Operationen steht und von ihr weitere Schritte abhängen (was sich z.B. auch durch GROUP BY PLACEMENT-Transformationen ergeben kann), die dann auf fehlerhaften Cardinalities beruhen. Als Workaround für das im Artikel vorgestellte Problem kommen die (nicht dokumentierten, aber immer wieder nützlichen) Hints CARDINALITY und OPT_ESTIMATE in Frage, die aber natürlich das Wissen um die tatsächliche Größe der Ergebnismenge voraussetzen - und in Produktionssystemen in der Regel nichts zu suchen haben - oder die Verwendung eines zusätzlichen ROWNUM-Prädikates, das das Pushing der Bedingung einer rahmenden Query als HAVING-caluse in die umrahmte View verhindert; wobei diese Variante dann wiederum andere Schwierigkeiten hervorrufen kann.

Details zur INTERNAL_FUNCTION

Tanel Poder liefert in seinem Blog einige Informationen zum Auftreten der INTERNAL_FUNCTION in den Prädikat-Informationen eines execution plans. Sie tritt einerseits bei impliziten Typkonvertierungen auf, kann aber auch an Stelle komplexerer OR-Verknüpfungen erscheinen und ist das Ergebnis einer UNPARSE-Operation, mit der DBMS_XPLAN die textuelle Repräsentation des Ausführungsplans aus dem internen Plan erstellt. Dabei gilt:

By the way, why do I keep saying “binary” execution plan and in double quotes? It’s because I want to emphasize that the real execution plan that Oracle executes is not in the text form like we see on the screen, the text is just generated for humans, for troubleshooting reasons. The execution plan is not a real executable binary (as in oracle.exe) either, it’s not directly fed to the CPUs for execution. The physical execution plan in the library cache child cursor is a bunch of opcodes, object_ids and pointers for defining the hierarchy and order of rowsource execution. It’s the SQL execution engine, which then loops through these opcodes, decodes them and knows what to do (which rowsource function to call) next.

Interessanterweise ist EXPLAIN PLAN bei der UNPARSE-Operation anscheinend manchmal geschickter und schafft es, OR-Prädikate aufzulösen, die DBMS_XPLAN in die INTERNAL_FUNCTION übersetzt.

Compression in Oracle

Jonathan Lewis hat mit einer Artikelserie zum Thema Compression in Oracle für redgates großartige All Things Oracle Seite begonnen. Hier ein paar Punkte daraus, die mir besonders erinnerungswürdig erscheinen (aber kein vollständiges Exzerpt darstellen sollen):

Compression in Oracle – Part 1: Basic Table Compression

• enthält eine Testreihe mit diversen unterschiedlichen Operationen, die zeigt, dass basic compression nur bei direct path operationen (CTAS, INSERT APPEND, ALTER TABLE ... MOVE) wirksam ist und dass sie implizit den pctfree-Wert auf 0 setzt.
• basic (und OLTP) compression führen eigentlich keine compression, sondern eine deduplication durch, indem sie wiederholte token im block in Symboltabellen speichern und dann darauf referenzieren.
• Oracle kann die Reihenfolge der Werte im Block umstellen und mehrere Elemente in einer Kombination zusammenfassen, um die Komprimierung zu verbessern - "Oracle can rearrange the order that the columns are stored in this block to put those tokens side by side, and create a new token the represents the combination of the two individual tokens". Solche Umstellungen der Spaltenreihenfolge sind in der Angabe "perm_9ir2[4]={ 2 0 1 3 }" im Block dump sichtbar: "for this block Oracle has rearranged (permuted) the order the columns are stored so that the thing stored at column 2 in this block is column 0 in the table definition, column 0 is really column 1, column 1 is column 2 and column 3 is (still) column 3".
• in den row-informationen des block dumps kann man die Wirkung der compression in der Differenz zwischen der Angabe tl (= total length) und den Angaben der Spaltenlänge + 1 lock byte + 1 flag byte + 4 byte für die 4 column length Angaben + 1 byte column count (cc) erkennen. Im Beispiel beträgt die tl tatsächlich nur 5 byte, die die bindmp (bind map) Angabe enthalten: "These five bytes are the flag byte (0x2c = ‘–H-FL’), the lock byte, the “stored” column count – i.e. the number of columns stored at this location, which is just one – and the next two bytes tell us that that one “column” is a token representing 4 consecutive and we need to look at token 0×31 of the token table".
• im Beispiel verweist die bindmp-Angabe wiederum auf weitere (sub)token und enthält auch Angaben zur Anzahl der Verwendung von token (also zur Häufigkeit der Ersetzungen).
• für das Lesen der Spalten-Werte ist somit keine decompression erforderlich, Oracle "simply re-constructs the row you need by hopping back and forth between the row directory and the row pieces (the code may even avoid visiting some tokens – the single column ones – if the column values aren’t needed for the SQL statement)".
• das Springen zwischen den token ist CPU-intensiv. "As a side effect, because Oracle will have to hold (pin) the block for some time to reconstruct rows, you may find that your code will do fewer “consistent gets – examination” which means more activity on the “cache buffers chains” latch. Of course, we hope that the extra CPU time will be offset by the smaller number of physical reads we may have to do because we’ve packed our rows into a smaller number of blocks, which may allow us to keep more data cached for longer."

Compression in Oracle – Part 2: Read-Only Data

• beim Löschen eines Satzes müssen zusätzlich die Angaben zur Häufigkeit der Verwendung eines Komprimierungs-Tokens korrigiert werden: der Aufwand von DELETEs ist demnach im Fall komprimierter Tabellen größer als in Fällen ohne Komprimierung.
• es kann vorkommen, dass ein nicht mehr verwendetes Token mit der Verwendungshäufigkeitsangabe 0 im Block stehen bleibt.
• UPDATEs führen zur Deaktivierung der compression für den veränderten Satz (also zu einer row expanison) - allerdings nur, wenn das UPDATE komprimierte Attribute betrifft. Ein Rollback bringt einen solchen Satz allerdings wieder zurück in den komprimierten Zustand.
• Oracle lässt trotz PCTFREE = 0 einige Byte pro Block für minimale Reorganisationsoperationen frei. Für moderate UPDATE-Häufigkeiten wäre ein PCTFREE-Wert > 0 sinnvoll.
• seit 11.2.0.3 sollte ein Update, das keine tatsächliche Änderung darstellt, auch keine expansion ausführen. Allerdings scheint das nicht immer zu funktionieren.

Compression in Oracle – Part 3: OLTP Compression

• OLTP compression lässt den Standardwert für PCTFREE stehen (also in der Regel 10)
• sie wirkt (erwartungsgemäß) auch bei conventional path operations
• die Statistiken zum Thema heap block compress haben eigentlich nicht unmittelbar mit dem Feature compression zu tun, sondern zählen, wie häufig der free space im Block reorganisiert wurde (und die row pieces zum Ende des Blocks hin verschoben wurden).
  
• die Anzahl der heap block compress Operationen ist im Beispiel deutlich höher als die Blockanzahl, was ein Hinweis darauf ist, dass jeder Block mehrfach reorganisiert werden muss
• die compression erfolgt sukzessive, wenn neue Einfügeoperationen Platz benötigen. Dabei wird der neu hinzugefügte Satz, der die Operation auslöst, selbst nicht komprimiert.
• reine Update-Operationen triggern die OLTP compression nicht, wie Randolf Geist gelegentlich gezeigt hat.
• OLTP compression "doesn’t compress for all operations, it compresses only for inserts, and the benefits it has over basic compression are that (a) it leaves 10% of the block free for updates, and (b) it doesn’t require direct path inserts to trigger compression. Given the limitations on how it works you may find that the problems it brings might make it something you want to avoid."
• der Komprimierungseffekt für OLTP compression ist (etwas) niedriger als der für direct path compression.

Compression in Oracle – part 4: Basic Index Compression

• index compression betrifft nur die führenden Index-Spalten.
• die Informationen im row heap eines Index-Blocks sind nicht (notwendigerweise) sortiert (können es temporär aber sein): die Sortierung liegt nur im row directory vor.
• die Komprimierung ist eine Deduplizierung der n führenden Spalten.
• anders als im Fall der table compression kann ein token im Fall der index compression nur an der passenden Stelle (= Spalte) verwendet werden.
• im block dump erscheinen für die dedulizierten token prefixes: "each takes 7 bytes (len = 7) consisting of the flag byte (flag:) that shows it’s a prefix entry, a lock byte (that never seems to get used) and, for each column (and there is only one), a length byte and the four bytes that make up the prefix". Dazu kommt eine Angabe prc: "(possibly “prefix usage count”) reporting the number of suffixes associated with this prefix."
• für den Rest des Index-Eintrags findet sich die Angabe "psno (possibly “prefix sequence number”), that tells us which prefix the suffix belongs to; again this item is derived, not something stored with the row."
• die byte Adresse eines Index-Eintrags erscheint in Brackets [...] hinter der row#-Angabe. Diese Angaben erscheinen (als hex Werte) im row directory (in umgekehrter Reihenfolge). Dort finden sich auch die Angaben zu den Startwerten der deduplizierten token (ebenfalls in umgekehrter Reihenfolge). Aus den Angaben kann Oracle die prc- und psno-Werte ableiten.

Compression in Oracle – part 5: Costs of Index Compression

• zeigt, dass bereits ein Präfix von nur einem Byte Länge zu sinnvoller compression führt, sofern es pro Präfix in der Regel mindestens vier Datensätze gibt (was anhand eines Beispiels belegt und theoretisch erklärt wird: pro Satz werden zwei Byte eingespart(Inhalt + Length Byte), was gegen den Overhead von prefix (4 Byte) und prefix directory (4 Byte) zu rechnen ist).
• eine Reduzierung der Index-Größe kann allerdings höhere contention für einzelnen Index-Blocks hervorrufen.
• beim Select ist der Overhead größer, wenn es sehr viele Präfixe und nur wenige zugehörige rows gibt. Insgesamt ist er aber in der Regel nicht signifikant (diese Einschätzung deckt sich mit meinen Erfahrungen).
• durch die Compression ändert sich das Costing des Index (da darin die LEAF_BLOCKS eine zentrale Komponente sind): ein komprimierter Index kann dadurch interessanter werden als ein anderer nicht komprimierter Index, der bis dahin die erste Wahl des CBO gewesen ist.
• für DML-Operationen müssen im komprimierten Index zusätzliche Änderungen durchgeführt werden (Aktualisierung des prefix directory (usage counter) und der zugehörigen row). Für DML-Operationen ist der Overhead der Compression deutlich größer als im Fall von Select-Zugriffen.

Row Chaining und Session-Statistiken

Jonathan Lewis hat in seiner neuesten Quiz Night nach der Anzahl der Sätze in einer Tabelle gefragt, für die in den Session-Statistiken für table scan rows gotten ein Wert von 329922 erschien und außerdem 645 table fetch continued row aufgetreten sind. Tatsächlich enthielt die Tabelle nur 10000 rows, aber für den Zugriff auf das zweite row piece werden im Fall von row chaining - und unabhängig davon, ob es Block-Grenzen überschreitet - deutlich höhere Werte für table scan rows gotten angegeben, als man erwarten würde. Wenn man das Beispiel mit wenigen Datensätzen nachvollzieht, dann ergibt sich zunächst ein Muster für die Berechnung der table scan rows gotten, das folgendermaßen aussieht:

table scan rows gotten
rows       piece1          piece2
----       ------          ------
   1            2               4
   2            4              12
   3            6              24
   4            8              40
   5           10              60
...
  10           20             220

Bis dahin sieht's für die Angabe für piece 2 nach folgender Formel aus:

rows * ((rows * 2) +2)

Allerdings ändert sich das Muster relativ bald, wenn man höhere Satzanzahlen erreicht. Ich hatte vor kurzem mal ein paar Beobachtungen zum row chaining notiert, aber da mein Beispiel nur einen einzigen Satz enthielt, war mir das seltsame Verhalten der table scan rows gotten-Angabe nicht aufgefallen.

Nachtrag 16.01.2013: In einem ziemlich ausführlichen Kommentar falsifiziert der Herr Lewis meine Hypothese, dass die Statistik table fetch continued row etwas über die Anzahl der block-übergreifenden chainings aussagt. Ich habe dann auch noch einen übersichtlichen Test mit einer Tabelle mit 300 Spalten und 100 rows durchgeführt, bei dem ich im block dump 10 block-überschreitende Verkettungen beobachtete, aber für table fetch continued row beim Zugriff auf eins Spalte des zweiten pieces den Wert 24 erhalte (während die Statistik für Zugriffe auf Spalten des ersten pieces immer 0 liefert). Anscheinend hat die Angabe weder zur Anzahl der betroffenen Blocks (im Test 5) noch zum block-übergreifenden chaining einen direkten Bezug. Mein Test unterstützte immerhin eine weitere Annahme, nämlich die, dass die table scan rows gotten für den Zugriff auf das erste piece der Gesamtzahl der vorhandenen pieces entspricht, denn bei 100 rows und 10 block-überschreitenden Verkettungen kam ich für die Statistik auf den Wert 210.

Prozessorwahl für den SQL Server

Glenn Berry schreibt im SQLPerformance.com-Blog über die sinnvollste Wahl eines Prozessors für den SQL Server 2012, wobei er vor allem Microsofts neues Lizenzierungsmodell in Betracht zieht. Seine detaillierte Untersuchung mündet im Vorschlag der Nutzung eines Xeon E5-2690-Prozessors: "It will give you excellent single-threaded performance and relatively affordable SQL Server 2012 licensing costs." Kevin Closson schlägt in seinem Kommentar den Xeon E5-2643 vor "which is *exaclty* an E5-2690 chopped in half so it is 4c/8t."

Zu viele Indizes

Jonathan Lewis hat in seinem Blog einen älteren, aber immer noch relevanten Artikel zum Thema Over-indexing erneut veröffentlicht. Hier ein paar zentrale Punkte daraus:

• Indizierung von Foreign Key-Spalten: ein solcher Index dient der Vermeidung von lock (und deadlock) Problemen beim Update des zugehörigen Satzes in der Parent-Tabelle, da ohne den Index ein table lock erforderlich ist.
• Wenn es solche Fälle des Updates von Parent-Sätzen nicht gibt, dann benötigt man auch keinen Index (sofern er nicht zur Beschleunigung von Query-Zugriffen relevant ist)..
• Außerdem kann ein vorhandener Index zur Vermeidung des Lock-Problems verwendet werden, wenn er die Constraint-Spalte(n) als führende Spalte(n) enthält - wobei die Reihenfolge irrelevant ist.
• zusammengesetzte Indizes lassen sich durch eine geeignete Wahl der Spaltenreihenfolge gut komprimieren: Spalten mit vielen Wiederholungen sollten im Index vorne stehen (was natürlich nur gilt, wenn es keine anderen Gründe gibt, die eine andere Reihenfolge nahelegen: z.B. die Verwendung einer Spalte in sehr vielen unterschiedlichen Zugriffen, was dafür spräche, diese Spalte an den Anfang des Index zu stellen).
• ein Index auf trunc(update_date) kann mehrere Probleme mit sich bringen: einerseits kann er ein klassischer Fall für rebuilds sein (weil die blocks am linken Rand des Index im Fall massiver Update-Operationen allmählich fast, aber nicht ganz leer werden), andererseits wird er (wahrscheinlich) einen sehr schlechten CF entwickeln (sofern die Updates über die gesamte Tabelle verteilt sind) - und daher nicht unbedingt effektiv sein.

Oracle und R

Kyle Hailey hat in den letzten Tagen zwei interessante Artikel zur Visualisierung von (Oracle-) Daten mit R veröffentlicht. Das 1992 entwickelte R ist als Programmiersprache für statistisches Rechnen und statistische Grafiken sehr verbreitet und kann als Standard betrachtet werden. Die Artikel sind:

• No 3d charts in Excel? try R: zeigt, wie man mit R sinnvoll skalierte 3D-Grafiken erstellen kann, was Excel nicht schafft
• R: slicing and dicing data: liefert eine etwas umfangreichere Darstellung der Möglichkeiten, die R bietet

Skip Scan statt Range Scan

Jonathan Lewis zeigt in seinem Blog einen Fall, in dem der CBO einen SKIP SCAN wählt, obwohl die führende Index-Spalte in den Bedingungen der Query erscheint. Da die Bedingung für die führende Spalte aber einen Range enthält, ist die Entscheidung für den SKIP SCAN aber tatsächlich sinnvoll, da er offenbar die Leseoperationen im Index reduziert.

Costing ohne Statistiken

Im Zusammenhang eines OTN-Threads ist mir heute aufgefallen, dass ich keine klare Vorstellung davon hatte, wie der CBO die Kosten eines Zugriffs berechnet, wenn er keine Statistiken besitzt und kein dynamic sampling verwenden kann. Nun könnte man einwenden, dass das eine eher theoretische Kombination ist, aber ich finde den Fall doch recht interessant. Dazu hier eine leicht überarbeitete Version des Beispiels aus dem Thread:

-- 11.2.0.1 (aber mit 11.1.0.7 ergibt sich das gleiche Verhalten)
-- 8K blocksize
-- MSSM-Tablespace (für ASSM ergibt sich das gleiche Verhalten,
-- allerdings ist die Anzahl der Tabellenblocks erwartungsgemäß höher)

drop table test_tbl purge ;

create table test_tbl (id number ) tablespace test_ts;

begin
 for i in 1 .. 10000 loop
 insert into test_tbl values (i);
 end loop;
 commit;
 end;
/

alter session set optimizer_dynamic_sampling = 0 ;

explain plan for 
select * from test_tbl where id = 9999;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 602094504

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TBL |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("ID"=9999)

Ohne Statistiken und ohne dynamic sampling kommt der CBO hier zu einer (anscheinend) akkuraten Schätzung der Cardinality. Aber die Situation ändert sich, wenn man einen zusätzlichen Index anlegt - und wieder löscht. Im Thread hatte der Autor den Index als INVISIBLE definiert und vermutet, dass die Index-Statistiken die Grundlage der guten Cardinality-Schätzung seien, was zwar nicht unplausibel klingt, aber offenbar hier nicht der Fall ist (zumal ich den Index in meinem Test erst nach der ersten Query definiere).

create index test_tbl_ix on test_tbl(id);
-- create unique index test_tbl_ix on test_tbl(id);

explain plan for
select * from test_tbl where id = 9999;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 86670887

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_TBL_IX |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("ID"=9999)

explain plan for
select * from test_tbl where id = 9999;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 602094504

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    16 |   208 |    12   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TBL |    16 |   208 |    12   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("ID"=9999)

Demnach ändern sich die Kosten erst nach der Löschung des Index. Aber wie ergeben sich die Kosten in den betrachteten Fällen? Dazu ein paar Angaben aus den CBO-Traces der Varianten:

-- Fall 1: kein Index
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
    #Rows: 82  #Blks:  1  AvgRowLen:  100.00
Access path analysis for TEST_TBL
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
  Table: TEST_TBL  Alias: TEST_TBL
    Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 23521
      Resp_io: 2.00  Resp_cpu: 23521
  Best:: AccessPath: TableScan
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.82  Bytes: 0


-- Fall 2: non-uinque Index erzeugt
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
    #Rows: 82  #Blks:  1  AvgRowLen:  100.00
Index Stats::
  Index: TEST_TBL_IX  Col#: 1
    LVLS: 1  #LB: 21  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
Access path analysis for TEST_TBL
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
  Table: TEST_TBL  Alias: TEST_TBL
    Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 23521
      Resp_io: 2.00  Resp_cpu: 23521
  Access Path: index (index (FFS))
    Index: TEST_TBL_IX
    resc_io: 12.00  resc_cpu: 1849550
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  12.00  Resp: 12.00  Degree: 1
      Cost_io: 12.00  Cost_cpu: 1849550
      Resp_io: 12.00  Resp_cpu: 1849550
  Access Path: index (AllEqGuess)
    Index: TEST_TBL_IX
    resc_io: 1.00  resc_cpu: 15971
    ix_sel: 0.004000  ix_sel_with_filters: 0.004000 
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_TBL_IX
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 0.82  Bytes: 0


-- Fall 3: unique Index erzeugt
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
    #Rows: 82  #Blks:  1  AvgRowLen:  100.00
Index Stats::
  Index: TEST_TBL_IX  Col#: 1
    LVLS: 1  #LB: 20  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 16.00
***************************************
1-ROW TABLES:  TEST_TBL[TEST_TBL]#0
Access path analysis for TEST_TBL
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
  Table: TEST_TBL  Alias: TEST_TBL
    Card: Original: 82.000000  Rounded: 1  Computed: 0.82  Non Adjusted: 0.82
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 23521
      Resp_io: 2.00  Resp_cpu: 23521
  Access Path: index (index (FFS))
    Index: TEST_TBL_IX
    resc_io: 12.00  resc_cpu: 1842429
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  12.00  Resp: 12.00  Degree: 1
      Cost_io: 12.00  Cost_cpu: 1842429
      Resp_io: 12.00  Resp_cpu: 1842429
  Access Path: index (UniqueScan)
    Index: TEST_TBL_IX
    resc_io: 1.00  resc_cpu: 8171
    ix_sel: 0.012195  ix_sel_with_filters: 0.012195 
    Cost: 1.00  Resp: 1.00  Degree: 1
  Access Path: index (AllEqUnique)
    Index: TEST_TBL_IX
    resc_io: 1.00  resc_cpu: 8171
    ix_sel: 0.004000  ix_sel_with_filters: 0.004000 
    Cost: 1.00  Resp: 1.00  Degree: 1
 One row Card: 1.000000
  Best:: AccessPath: IndexUnique
  Index: TEST_TBL_IX
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 1.00  Bytes: 0


-- Fall 4: Index wieder gelöscht
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_TBL  Alias: TEST_TBL  (NOT ANALYZED)
    #Rows: 1634  #Blks:  20  AvgRowLen:  100.00
Access path analysis for TEST_TBL
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_TBL[TEST_TBL] 
  Table: TEST_TBL  Alias: TEST_TBL
    Card: Original: 1634.000000  Rounded: 16  Computed: 16.34  Non Adjusted: 16.34
  Access Path: TableScan
    Cost:  12.00  Resp: 12.00  Degree: 0
      Cost_io: 12.00  Cost_cpu: 469229
      Resp_io: 12.00  Resp_cpu: 469229
  Best:: AccessPath: TableScan
         Cost: 12.00  Degree: 1  Resp: 12.00  Card: 16.34  Bytes: 0

In den ersten beiden Fällen, also vor und nach der Anlage des non-unique Index, kommt der CBO jeweils zur gleichen Einschätzung: er nimmt an, dass die Tabelle nur einen Block umfasst (#Blks), dass die Satzlänge exakt 100 Byte beträgt (AvgRowLen: 100.00) und dass der eine Tabellenblock 82 rows umfasst (bei 8K Blockgröße und 100 Byte pro Satz ist das zwar etwas optimistisch, aber nicht völlig falsch). In Fall 2 werden die Index-Statistiken offenbar nicht zur Korrektur der Tabellen-Werte herangezogen, denn die Card-Angabe bleibt 0.82 - also 1% der Tabellen-Cardinality.

In Fall 3 weiß der CBO, dass der Index eindeutig ist, dass also zu einer ID (höchstens) ein Satz vorliegt - daher ergibt sich: Card: 1.00.

Nach der Löschung des Index ändert sich die Rechnung in Fall 4: jetzt geht der CBO von 20 Blocks aus und kommt auf 1634 rows (20 * 82 = 1640; vermutlich ist da noch Rundung im Spiel) für die Tabelle, woraus sich dann die Card-Angabe 16.34 für die Bedingung id = 9999 ergibt. Es bleiben zwei Fragen:

1. wie kommt der CBO darauf, dass die Tabelle 20 Blocks umfasst?
2. wieso ändert sich das Verhalten erst nach der Löschung des Index?

Frage 1 lässt sich anscheinend relativ leicht beantworten: die Anzahl der Blocks wird anscheinend aus der HWM im segment header ermittelt:

Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 7     
                  last map  0x00000000  #maps: 0      offset: 4128  
      Highwater::  0x01400205  ext#: 1      blk#: 5      ext size: 8     
  #blocks in seg. hdr's freelists: 5     
  #blocks below: 20    
  mapblk  0x00000000  offset: 1     
      Disk Lock:: Locked by xid:  0x0005.016.00003072
     Map Header:: next  0x00000000  #extents: 1    obj#: 101566 flag: 0x42000000
  Extent Map
  -----------------------------------------------------------------

Die #blocks below: 20 passen exakt zur Angabe des CBO Trace und der Zusammenhang kann auch bei veränderter Segmentgröße beobachtet werden. Schwieriger ist es mit Frage 2: ich sehe weder im CBO Trace noch im Block Dump eine Veränderung, die erklären könnte, warum der CBO erst nach der Löschung des Index die tatsächliche Segmentgröße berücksichtigt. Dabei kann man die Änderung des Costings auch auf anderem Weg erreichen, etwa durch Ergänzung eines NOT NULL Constraints, was dann unmittelbar zum realistischeren Costing unter Berücksichtigung der Objektgröße führt. Ein einfacher table comment genügt an dieser Stelle allerdings nicht, so dass es offenbar nicht um beliebige DDL-Operationen geht (in dem Fall müsste ja auch schon das CREATE INDEX die Änderung im Costing hervorrufen).

Wer schreibt mir jetzt einen Schluss? Ich hoffe mal: der Herr Lewis (oder auch der Herr Geist).

Nachtrag 04.01.2013: Jonathan Lewis hat im Thread noch die Beobachtung ergänzt, dass auch ein ALTER TABLE ... MOVE und ein ALTER INDEX ... VISIBLE/INVISIBLE die Änderung des Costings bewirken. Er schreibt weiter: "I think one of the key things is that some DDL will cause Oracle to flush and reload some of the dictionary cache information for the table - including the number of blocks, which can be derived from the table segment header. The problem is identifying which DDL. [,,,] I think there must be some detail about how Oracle thinks that DDL may cause important statistics to change, and therefore modifies the dictionary cache entries - but whether the apparent inconsistencies are deliberate or accidental I can't yet decide."

ODI Einführung

Mark Rittman hat mit einer kleinen Serie zum Oracle Data Integrator (ODI) begonnen:

Compared to Oracle Warehouse Builder, ODI 11g is a fairly easy-to-understand, extensible tool with a clear product roadmap, few hidden surprises and a solid set of features for manipulating relational sources and targets.

Da ich den ODI bisher nur aus sicherer Entfernung wahrgenommen habe, wäre die Serie vermutlich ein netter Einstieg. Möglicherweise ergänze ich hier noch ein paar Details zu den Artikeln der Serie, die alle im ersten Artikel verlinkt sind.