Kategorisierung von Hints

Bei Dom Brooks findet man den Versuch einer Taxonomie der unterschiedlichen Typen von Hints, also z.B.:

• Optimizer-Mode
• Direct-Path
• Anpassungen von Cardinality-Schätzungen
• Bug-Handhabung
• Cursor-Sharing
• Parallelisierung
• Caching
• ...

Eine Liste verfügbarer Hints liefert V$SQL_HINT.

Ergänzungen zu display_cursor

Randolf Geist hat ein hübsches Weihnachtsgeschenk abgeliefert: eine um eine Reihe sehr nützlicher Zusatzinformationen erweiterte Version zur selbst schon extrem nützlichen dbms_xplan.display_cursor-Routine. Das Script kombiniert einige Ideen, die Adrian Billington und Kyle Hailey gelegentlich geäußert haben, mit zusätzlichen Ergänzungen, und macht es deutlich einfacher, den Ablauf der Operationen im execution plan korrekt zu deuten. Für besonders nützlich halte ich den TCF Graph zur Visualisierung von Fehleinschätzungen bei den Cardinalities und die klarere Verteilung der I/O-Operationen auf die Einzelschritte (in den %SELF-Spalten und in den Graphen). Eine schöne Alternative für alle, die keine Lizenz für das Real-Time SQL Monitoring besitzen - dafür aber einen breiten Monitor.

ix_sel_with_filters

In Cost-Based Oracle erläutert Jonathan Lewis im vierten Kapitel die Grundlagen des Index Costing und verweist dabei auf die von Wolfgang Breitling im Jahr 2002 veröffentlichte Formel:

cost = blevel
       + ceiling(leaf_blocks * effective index selectivity)
       + ceiling(clustering_factor * effective table selectivity)

Völlig unproblematisch sind in dieser Rechnung die Faktoren blevel, leaf_blocks und clustering_factor, die man z.B. in der View user_indexes findet. Die beiden anderen Elemente effective index selectivity und effective table selectivity werden im CBO-Buch ebenfalls erläutert, aber ich hatte mir bisher nie die Mühe gemacht, zu prüfen, ob ich diese Erklärungen tatsächlich verstanden hatte. Das soll hiermit geändert werden.

Dabei scheint die effective index selectivity auch noch recht harmlos zu sein: sie ergibt sich aus der Kombination der Selektivitäten der für den Zugriff verwendeten Index-Spalten. leaf_blocks * effective index selectivity repräsentiert dabei die Kosten des Zugriffs auf die Index-Struktur, von der ein durch die effective index selectivity bestimmter Anteil gelesen werden muss.

Zur effective table selectivity schreibt Jonathan Lewis, sie solle "be based only on those predicates that can be evaluated in the index, before you reach the table." (S. 67) Da mir dieser Punkt noch immer nicht völlig klar ist dazu ein kleiner Test (mit 11.2.0.1):

-- Anlage einer Test-Tabelle mit einem zusammengesetzten Index
drop table test_ind_selectivity;

create table test_ind_selectivity tablespace test_ts
as
select rownum id
     , mod(rownum, 10) col1 -- 10 unterschiedliche Werte -> Selektivität: 0,1
     , mod(rownum, 100) col2 -- 100 unterschiedliche Werte -> Selektivität: 0,01 
     , mod(rownum, 1000) col3 -- 1000 unterschiedliche Werte -> Selektivität: 0,001  
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'TEST_IND_SELECTIVITY', method_opt=>'FOR ALL COLUMNS SIZE 1')

create index test_ind_selectivity_ix1 on test_ind_selectivity(col1, col2, col3);

Für den Index ergeben sich folgende Statistiken:

select index_name
     , blevel
     , leaf_blocks
     , clustering_factor
  from user_indexes
 where table_name = upper('test_ind_selectivity');

INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
TEST_IND_SELECTIVITY_IX1                2        2894           1000000

Ich könnte jetzt die tatsächlichen Kosten von Queries ermitteln und versuchen, passende Werte für die Selektivitäten einzusetzen, aber ich mache mir die Sache ganz einfach und erzeuge ein 10053er Trace:

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

select /*+ index(test_ind_selectivity) test1 */ count(id) from test_ind_selectivity where col1 = 1;

select /*+ index(test_ind_selectivity) test2 */ count(id) from test_ind_selectivity where col1 = 1 and col2 = 1;

select /*+ index(test_ind_selectivity) test3 */ count(id) from test_ind_selectivity where col1 = 1 and col2 = 1 and col3 = 1;

select /*+ index(test_ind_selectivity) test4 */ count(id) from test_ind_selectivity where col1 = 1 and col3 = 1;

ALTER SESSION SET EVENTS '10053 trace name context OFF';

Für die Queries ergeben sich folgende Trace-Inhalte:

Fall 1: where col1 = 1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    #Rows: 1000000  #Blks:  16907  AvgRowLen:  116.00
Index Stats::
  Index: TEST_IND_SELECTIVITY_IX1  Col#: 2 3 4
    LVLS: 2  #LB: 2894  #DK: 1000  LB/K: 2.00  DB/K: 1000.00  CLUF: 1000000.00
    User hint to use this index
Access path analysis for TEST_IND_SELECTIVITY
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_IND_SELECTIVITY[TEST_IND_SELECTIVITY] 
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    Card: Original: 1000000.000000  Rounded: 100000  Computed: 100000.00  Non Adjusted: 100000.00

  Access Path: index (RangeScan)
    Index: TEST_IND_SELECTIVITY_IX1
    resc_io: 100292.00  resc_cpu: 751223460
    ix_sel: 0.100000  ix_sel_with_filters: 0.100000 
    Cost: 100292.15  Resp: 100292.15  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_IND_SELECTIVITY_IX1
         Cost: 100292.15  Degree: 1  Resp: 100292.15  Card: 100000.00  Bytes: 0

***************************************

Die ix_sel repräsentiert im 10053er Trace die effective index selectivity, während die ix_sel_with_filters für die effective table selectivity steht. Beide Werte sind für die erste Query 0,1 - und entsprechen damit der Selektivität der Einzel-Spalte. Setzt man die Werte in die Formel ein, ergibt sich:

• blevel + ceiling(leaf_blocks + effective index selectivity) + ceiling(clustering_factor * effective table selectivity)
• 2 + ceil(2894 * 0,1) + ceil(1000000 * 0,1)
• 2 + 290 + 100000 = 100292

Das passt dann schon mal zur resc_io, die offenbar den I/O-Anteil der Kosten darstellt. Die zusätzlichen 0,15 in den Kosten sind dann vermutlich der CPU-Anteil.

Fall 2: where col1 = 1 and col2 = 1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    #Rows: 1000000  #Blks:  16907  AvgRowLen:  116.00
Index Stats::
  Index: TEST_IND_SELECTIVITY_IX1  Col#: 2 3 4
    LVLS: 2  #LB: 2894  #DK: 1000  LB/K: 2.00  DB/K: 1000.00  CLUF: 1000000.00
    User hint to use this index
Access path analysis for TEST_IND_SELECTIVITY
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_IND_SELECTIVITY[TEST_IND_SELECTIVITY] 
  ColGroup (#1, Index) TEST_IND_SELECTIVITY_IX1
    Col#: 2 3 4    CorStregth: -1.00
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    Card: Original: 1000000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: 
  Access Path: index (RangeScan)
    Index: TEST_IND_SELECTIVITY_IX1
    resc_io: 1005.00  resc_cpu: 7547047
    ix_sel: 0.001000  ix_sel_with_filters: 0.001000 
    Cost: 1005.00  Resp: 1005.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_IND_SELECTIVITY_IX1
         Cost: 1005.00  Degree: 1  Resp: 1005.00  Card: 1000.00  Bytes: 0

***************************************

In diesem Fall sind beide Selektivitäten auf 0,001 (also das Produkt der Selektivitäten der Einzelspalten 0,1 * 0,01) gesetzt, was auch wieder den Erwartungen entspricht. In der Formel ergibt sich für die Komponenten 2 + 3 + 1000.

Fall 3: where col1 = 1 and col2 = 1 and col3 = 1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    #Rows: 1000000  #Blks:  16907  AvgRowLen:  116.00
Index Stats::
  Index: TEST_IND_SELECTIVITY_IX1  Col#: 2 3 4
    LVLS: 2  #LB: 2894  #DK: 1000  LB/K: 2.00  DB/K: 1000.00  CLUF: 1000000.00
    User hint to use this index
Access path analysis for TEST_IND_SELECTIVITY
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_IND_SELECTIVITY[TEST_IND_SELECTIVITY] 
  ColGroup (#1, Index) TEST_IND_SELECTIVITY_IX1
    Col#: 2 3 4    CorStregth: 1000.00
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0010
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    Card: Original: 1000000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0010
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0010
  Access Path: index (AllEqRange)
    Index: TEST_IND_SELECTIVITY_IX1
    resc_io: 1005.00  resc_cpu: 7567047
    ix_sel: 0.001000  ix_sel_with_filters: 0.001000 
    Cost: 1005.00  Resp: 1005.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_IND_SELECTIVITY_IX1
         Cost: 1005.00  Degree: 1  Resp: 1005.00  Card: 1000.00  Bytes: 0

***************************************

Die Werte für ix_sel und ix_sel_with_filters sind mit denen in Fall 2 identisch. Ein offensichtlicher Unterschied sind die Angaben unter Partial und CorStregth (wo vielleicht ein n fehlt) - zu diesen Einträgen im CBO-Trace hat Randolf Geist im Rahmen einer Untersuchung von Korrelationseffekten und extended statistics (die wiederum auf einen Artikel von Riyaj Shamsudeen Bezug nimmt) gelegentlich ein paar Erläuterungen gegeben, die ich so interpretiere, dass die Angaben etwas über die Korrelation der Spaltenwerte aussagen und durch extended statistics und Histogramme beeinflusst werden können. Die Werte in Fall 3 scheinen anzudeuten, dass der CBO die Korrelation der Spalten erkennt, aber wie er das macht, ist mir unklar, da keine extended statistics erzeugt wurden (und auch keine Histogramme: method_opt=>'FOR ALL COLUMNS SIZE 1'). In jedem Fall entsprechen die Kosten exakt denen von Fall 2.

Nachtrag 24.12.2011: Die Antwort auf meine Frage liefert Randolf Geists Kommentar: im gegebenen Fall eines Zugriffs mit Einschränkung auf alle Index-Spalten kann der CBO die Index-Statistiken verwenden, statt sich mit der indirekten Bestimmung über die Tabellenspalten-Statistiken behelfen zu müssen.

Fall 4: where col1 = 1 and col3 = 1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    #Rows: 1000000  #Blks:  16907  AvgRowLen:  116.00
Index Stats::
  Index: TEST_IND_SELECTIVITY_IX1  Col#: 2 3 4
    LVLS: 2  #LB: 2894  #DK: 1000  LB/K: 2.00  DB/K: 1000.00  CLUF: 1000000.00
    User hint to use this index
Access path analysis for TEST_IND_SELECTIVITY
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST_IND_SELECTIVITY[TEST_IND_SELECTIVITY] 
  ColGroup (#1, Index) TEST_IND_SELECTIVITY_IX1
    Col#: 2 3 4    CorStregth: -1.00
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: #1 (2 4 )  Sel: 0.0010
  Table: TEST_IND_SELECTIVITY  Alias: TEST_IND_SELECTIVITY
    Card: Original: 1000000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
kkofmx: index filter:"TEST_IND_SELECTIVITY"."COL3"=1


  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: #1 (2 4 )  Sel: 0.0010
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial: #1 (2 4 )  Sel: 0.0010
  Access Path: index (skip-scan)
    SS sel: 0.001000  ANDV (#skips): 100.000000
    SS io: 300.000000 vs. index scan io: 8455.000000
    Skip Scan rejected
  Access Path: index (RangeScan)
    Index: TEST_IND_SELECTIVITY_IX1
    resc_io: 1292.00  resc_cpu: 29410900
    ix_sel: 0.100000  ix_sel_with_filters: 0.001000 
 ***** Logdef predicate Adjustment ****** 
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ****** 
    Cost: 1292.01  Resp: 1292.01  Degree: 1
  Best:: AccessPath: IndexRange
  Index: TEST_IND_SELECTIVITY_IX1
         Cost: 1292.01  Degree: 1  Resp: 1292.01  Card: 1000.00  Bytes: 0

***************************************

In diesem Fall erscheinen nur die führende und die letzte Spalte des Index in der WHERE-Bedingung. Einleuchtend ist deshalb die ix_sel, die nur die Selektivität der führenden Spalte berücksichtigt: aufgrund der Einschränkung müssen 10% der Index-Struktur durchsucht werden, um alle Sätze mit col1 = 1 zu ermitteln, ehe dann die Filterung auf col3 = 1 erfolgen kann. Aber die ix_sel_with_filters ist mir nicht ganz klar, denn sie ergibt sich nicht aus der Selektivität der beiden Spalten (0,1 * 0,001 wären 0,0001). Möglicherweise ist auch hier eine Korrelationsbestimmung im Spiel, denn die ix_sel_with_filters = 0.001000 entspricht der Angabe in:  Partial: #1 (2 4 ) Sel: 0.0010. Auch in diesem Fall ist mir unklar, wie der CBO die Korrelation erkennen kann.

Interessant ist darüber hinaus noch der Plan für die vierte Query, der sich im CBO-Trace ebenfalls findet:

============
Plan Table
============
----------------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                         |       |       |  1292 |           |
| 1   |  SORT AGGREGATE               |                         |     1 |    12 |       |           |
| 2   |   TABLE ACCESS BY INDEX ROWID | TEST_IND_SELECTIVITY    |  1000 |   12K |  1292 |  00:00:07 |
| 3   |    INDEX RANGE SCAN           | TEST_IND_SELECTIVITY_IX1|  1000 |       |   292 |  00:00:02 |
----------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("COL1"=1 AND "COL3"=1)
3 - filter("COL3"=1)

Hier erscheinen col1 und col3 als access-Prädikate und col3 dann noch einmal als Filter-Prädikat. Ich hatte erwartet, dass nur col1 als access-Prädikat verwendbar wäre und deshalb 100000 Sätze über den Index gelesen werden müssten, ehe die zusätzliche Filterung auf col3 = 1 erfolgen könnte, aber das ist offenbar nicht der Fall, wie die Ausführungsinformationen belegen:

select plan_table_output
  from table( dbms_xplan.display_cursor ( NULL, NULL, 'allstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  gj6fuawjzpr51, child number 0
-------------------------------------
select /*+ index(test_ind_selectivity) gather_plan_statistics */
count(id) from test_ind_selectivity where col1 = 1 and col3 = 1

Plan hash value: 779399104

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |      1 |        |      1 |00:00:00.03 |    1296 |
|   1 |  SORT AGGREGATE              |                          |      1 |      1 |      1 |00:00:00.03 |    1296 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_IND_SELECTIVITY     |      1 |   1000 |   1000 |00:00:00.01 |    1296 |
|*  3 |    INDEX RANGE SCAN          | TEST_IND_SELECTIVITY_IX1 |      1 |   1000 |   1000 |00:00:00.01 |     296 |
-------------------------------------------------------------------------------------------------------------------

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

   3 - access("COL1"=1 AND "COL3"=1)
       filter("COL3"=1)

Tatsächlich liefert der Index-Zugriff also nur die 1000 relevanten Sätze, kann also die fehlende Spalte col2 überspringen - was durchaus plausibel ist, aber nicht unbedingt das, was ich erwartet hatte.

Nachtrag 24.12.2011: auch dazu liefert Randolf Geist in seinem Kommentar die Erklärung: der ACCESS liefert 296 von 1296 Blocks (und 100.000 Sätze) und anschließend erfolgt die FILTERung auf 1.000 Sätze. Das Erscheinen von col3 als ACCESS-Prädikat dient dabei offenbar nur zur Verwirrung... Hilfreich wär's, wenn die Informationen zum ACCESS auch noch bei den A-Rows auftauchen würden, wie Randolf unten anmerkt. Eine schöne Erklärung zur Interpretation von FILTER-Prädikaten in Index-Scan-Steps (und überhaupt zur Rolle von ACCESS- und FILTER-Prädikaten) findet man hier und hier bei Markus Winand.

Einmal mehr bin ich nicht unbedingt dort angelangt, wo ich hin wollte - nämlich bei einem klaren Verständnis der ix_sel_with_filters, aber für heute genügt's mir.

RBO-Nutzung in Version 11

Maria Colgan erläutert im Blog der CBO-Entwickler, welche Objekt- bzw. Query-Eigenschaften die Verwendung des CBO erzwingen. Grundsätzlich gilt, dass alle Features neueren Datums dazu führen, dass der RBO aus dem Spiel ist.

Ehrenrettung für Trigger

Ich bin bei Tom Kyte (und anderswo) so oft vor dem Einsatz von Triggern gewarnt worden und habe selbst so viele Unerfreuliches mit ihnen erlebt, dass ich sehr gespannt darauf bin, was Toon Koppelaars in seinem neuen Blog Triggers Considered Harmful, Considered Harmful alles aufführen wird, um den schlechten Ruf dieses Features zu korrigieren.

Hier eine kurze Liste der bisher veröffentlichten Artikel (die ich vermutlich nicht bis zum Ende des Blogs ergänzen werde):

• Starting this blog: mit einem Blick zurück ins Jahr 1994 und einigen Tom-Kyte-Zitaten zum Thema Trigger.
• So what triggers are we talking about? mit einer kurzen Klassifizierung nach Trigger-Typen und einer Erläuterung zu row-level- und statement-level-triggers.
• Some preliminaries: erklärt, dass in einem Trigger keine DDL-Operation aufgerufen werden darf (wegen des impliziten Commit; wenn man den problematischen Code in eine autonome Transaktion packt, kann diese die Änderungen der laufenden DML-Operation dann - natürlich - nicht sehen). Dann wird erklärt, dass der Fehlschlag eines Triggers ein Statement-Rollback für die betroffene DML-Operation hervorruft.Außerdem wird das mutating table Problem erläutert: "row level triggers are not allowed to read what's called the mutating table. If your row level trigger code queries the table that's being affected by the triggering DML statement, then Oracle will throw an ORA-04091 at you and cause the above mentioned statement level rollback."
• "Workarounds" for ORA-04091: nennt zwei beliebte Workarounds für das Mutating-Table-Problem: die Verwendung einer autonomen Transaktion und die (weit weniger verbreitete) Verwendung eines loopback db-link. Ein wichtiger Unterschied der beiden Varianten ist, dass der db-link-Zugriff die Änderungen der noch nicht festgeschriebenen Transaktion sehen kann (da er zur gleichen dirstibuted transaction gehört), während die autonomous transaction das (natürlich) nicht kann.
• The mutating table error prevents non-deterministic behavior of your code: erklärt, warum die beiden oben angeführten Workarounds grundsätzlich problematisch sind - und wieso der Mutating-Table-Error eine sinnvolle Sicherheitseinrichtung ist: im Fall des Inserts mehrerer Sätze entscheidet die Reihenfolge der rows darüber, ob ein row Trigger mit loopback db_link, der die Beziehung von Tabelleninhalten überprüft, einen Fehler liefert oder nicht - das Verhalten ist also nicht deterministisch. Ein row Trigger mit dem Pragma autonomous transaction würde in einem solchen Fall immer einen Fehler liefern, wenn die neu eingefügten Sätze nur in ihrer Kombination die Logik des Triggers erfüllen.
  
• Look mom: a mutating table error without a trigger!: zeigt dass ORA-04091 nicht nur für Trigger relevant ist, sondern auch für Funktionen - und auch dort einen wichtigen Sicherheitsmechanismus darstellt.
• hier fehlen ein oder zwei Artikel ...
• Where TK agrees with TK (or: why are triggers harmful): liefert Beispiele für Trigger-Verwendungen, die auch der Herr  Koppelaars für problematisch hält, da sie das Standardverhalten von DML-Operationen "automagically" verändern (wie's der Herr Kyte nennt). Die fraglichen Bereiche sind unter anderem die Ergänzung von Spalten-Werten (z.B. durch automatisches Setzen eines INSERT_DATE), die konditionale Ausführung weiterer DML-Operationen (wenn ein Satz in Tabelle A eingefügt wird, erfolgt automatisch ein weiteres INSERT nach B) und die Ausführung nicht-transaktionaler Operationen (z.B. autonomer Transaktionen).
• The fourth use-case for triggers: beschäftigt sich mit Triggern, die Select-Statements ausführen - in erster Linie, um Datenintegritäts-Constraints zu unterstüzen (obwohl Tom Kyte davon explizit abrät). Dabei erfolgt ein Hinweis auf die ASSERTION, die Bestandteil des SQL-92-Standards ist und über die sich deklarative Datenintegritäts-Constraints definieren ließen - was daran scheitert, dass sie von keinem RDBMS-Vendor implementiert wurde, da das offenbar alles andere als trivial wäre.
• Continuing the story: liefert zunächst eine Zusammenfassung der zuvor veröffentlichten Artikel, die die Grundidee des jeweiligen Textes (aus naheliegenden Gründen) sehr viel treffender beschreibt, als ich das hier getan bzw. versucht habe. Die Liste ist außerdem auch lückenlos, was sie ebenfalls von meiner Sammlung unterscheidet. Darüber hinaus liefert der Artikel die Antwort auf die der gesamten Serie zugrunde liegende Frage: wie vermeidet man den mutating table error bei der Implementierung eines multi-row constraints sinnvoll (also nicht mit den problematischen Krücken loopback DB-Link oder autonomous transaction)? Diese Antwort lautet: man muss eine Kombination aus row-level und statement-level trigger verwenden. Die Lösung ist ein wenig komplexer, als dass ich sie hier nacherzählen wollte - aber an ihre Existenz sollte ich mich erinnern, wenn ich multi-row sonstraints definieren müsste.

RAC-Test-Installation mit Oracle VM

Sollte ich mal wieder auf die seltsame Idee kommen, zu Test-Zwecken ein RAC-System aufzubauen, würde ich am besten noch mal einen Blick auf Gavin Soormas detaillierte Anleitung werfen.

Nachtrag 04.01.2013: eine andere Anleitung mit dem vielversprechenden Titel Build an 11gR2 RAC cluster in VirtualBox in 1 Hour using OVM templates hat dieser Tage John Piwowar von den Pythians veröffentlicht.

Eine kurze Geschichte der Datenverarbeitung

Thomas Kejser hat in seinem Blog eine kleine Geschichte der Datenverarbeitung in den letzten 40 Jahren aufgeschrieben. Der Historiker in mir freut sich über solche Überblicke. Sein Fazit lautet:

What history has taught us is that such killer architectures do not exist, but that the optimal strategy (just like in nature) depends on which environment you find yourself in. Being an IT architect means rising above all these technologies, to see that big picture, and resisting the temptation to find a single solution/platform for all problems in the enterprise. They key is to understand the environment you live in, and design for it using a palette of technologies.

Und das ist sicher eine ziemlich zeitlose Schlussfolgerung.

Join-Cardinality

Randolf Geist hat eine interessante Erläuterung zum Thema Table Functions And Join Cardinality Estimates veröffentlicht. Zunächst erklärt er darin (unter Ausklammerung von Spezialeffekten mit NULL, Histogrammen etc.), wie der CBO die Cardinality eines Joins grundsätzlich einschätzt:

• Join Selectivity = 1 / greater(num_distinct(t1.c1), num_distinct(t2.c2))
• low and high values of the join columns: If the join columns do not overlap at all the join cardinality will be calculated as 1.
• Finally this join selectivity will be multiplied by the (filtered) cardinality of the two row sources to arrive at the join cardinality: Join Cardinality = Join Selectivity * cardinality t1 * cardinality t2

Dazu gibt's dann (natürlich) allerlei Beispiele. Im Hauptteil des Artikels erläutert der Herr Geist dann, warum der CBO im Fall von Table Functions auch bei Verwendung von dynamic sampling nicht zu den erwarteten Angaben kommt, aber diesen Teil des Artikels sollte man lieber im Original lesen, so dass ich mir hier das Exzerpieren spare.

Nachtrag 15.02.2012: im Sinne einer doppelt verketteten Liste hier noch der Verweis auf einen älteren Eintrag zum gleichen Thema (und mit nahezu gleichem Titel).

Satzgenerierung mit Pipelined Function

In einem Kommentar im Oracle Scratchpad zeigt Valentin Nikotin die Verwendung einer Piplined Function als performanter Alternative zum beliebten connect-by-level-Verfahren:

create function generator (n pls_integer) return sys.odcinumberlist pipelined 
is

begin

  for i in 1 .. n loop
    pipe row (i);
  end loop;

end;
/

select count(*) from table(generator(1e7));

Lesenswert ist der vorangehende Artikel des Herrn Lewis natürlich auch, aber dessen Grundgedanke war mir schon bekannt.

ORA-08176 mit Merge und Interval Partitioning

Interval Partitioning ist eine sehr praktische Vereinfachung für die Partitionierung in Version 11. Leider weist die Option aber noch einige recht unerfreuliche Bugs auf. Darüber, dass man den High_Value der initialen Partition mit Bedacht wählen sollte, habe ich gelegentlich geschrieben. Heute ist mir dann Bug 9638090 begegnet: "ORA-8176 from DML on an INTERVAL Partitioned Table OR table with deferred segment creation". Die Grundaussage des Fehlers ora-08176: consistent read failure; rollback data not available erläutert Mark Bobak in einem Kommentar im Oracle Scratchpad: "1555 means the rollback has been overwritten, 8176 means the required rollback never existed." In MOS werden die Voraussetzungen für das Auftreten des Bugs wie folgt beschrieben:

• (a) A session is performing DML on an interval partitioned table or a table with deferred segment creation.
• (b) The DML creates one or more interval / deferred partitions, and
• (c) The same table is involved as the 'source' for the DML, and this involves an index scan (hence causing a consistent read of the index). For eg., update, merge, insert-select statements.

Ein schönes Fallbeispiel zum Thema findet man auch in einem zugehörigen OTN-Thread. Als harmloser Workaround kommt eine prophylaktische Erzeugung der Partition vor dem Merge in Frage.

Nachtrag 09.12.2011: In der MOS-Beschreibung des Bugs wird nur ein Patch in 11.2.0.2 erwähnt; tatsächlich gibt es aber auch Patches für 11.1.0.7 und andere Versionen (wie schon Dom Brooks im OTN-Thread erwähnt; mir waren diese Patches zunächst entgangen)

Cardinality-Schätzungen für Remote-Zugriffe

Dieser Tage ist mir folgendes Phänomen begegnet, auf das ich mir noch keinen ganz klaren Reim machen kann: Ausgangspunkt war eine etwas unglücklich eingerichtete Reporting-Lösung, die grundsätzlich auf ein Star-Schema in einer Datenbank A (10.2.0.4) zugreift, aber zusätzlich auf die Daten einer Dimensionstabelle in einer Datenbank B (11.1.0.7) angewiesen ist, die über DB-Link (und Synonyme) angesprochen wird. Vor einiger Zeit ergaben sich für die Queries dieser Applikation Performance-Probleme, die zum Teil mit falschen Statistiken in Datenbank A zusammenhingen, aber z.T. auch auf fehlerhaften Einschätzungen der Cardinalities für die Dimensionstabelle in der Datenbank B beruhten. Meine erste Vermutung war, dass die falsche Cardinality durch die Korrelation von Spalteninhalten hervorgerufen wurde (in diesem Fall Produktgruppen und Lieferanten, die eben in vielen Fällen nicht unabhängig voneinander sind), weshalb ich extended statistics (und Histogramme) für die fragliche Spaltenkombination anlegte. Dadurch wurden die Cardinality-Schätzungen beim Zugriff in Datenbank B besser (wenn auch nicht völlig akkurat) und auch beim Remote-Zugriff von Datenbank A aus auf die einzelne Tabelle erhielt man die gleichen Angaben:

select /*+ full(t) */ count(*)
  from dim_table t
 where t.col1 = 421220
   AND t.col2 = 20

  COUNT(*)
----------
      2841

-------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|                 |     1 |     9 | 60397   (1)|
|   1 |  SORT AGGREGATE        |                 |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL    | DIM_TABLE       |   147 |  1323 | 60397   (1)|
-------------------------------------------------------------------------------

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

   2 - filter("A1"."COL1"=421220 AND "A1"."COL2"=20)

Note
-----
   - fully remote statement

Für das "fully remote statement" erwartet der CBO demnach 147 Sätze, was zwar von den tatsächlichen 2841 rows recht weit entfernt ist, aber immerhin mehr als die 1 (oder 2), die hier ohne die extended statistics erschien.

Für die komplexeren Queries der Applikation blieb es aber trotzdem bei einer deutlichen Unterschätzung der Cardinality für den Zugriff auf DIM_TABLE. Dazu hier ein minimales Beispiel:

select count(*)
  from fact_table f
     , dim_table d
 where f.col0 = d.col0
   and d.col1 = 421220
   AND d.col2 = 20
   and f.col3 = '01.12.2011'

  COUNT(*)
----------
     13562

----------------------------------------------------------------------------------------
| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |    54 |     8   (0)|
|   1 |  SORT AGGREGATE          |                        |     1 |    54 |            |
|   2 |   NESTED LOOPS           |                        |    24 |  1296 |     8   (0)|
|   3 |    REMOTE                | DIM_TABLE              |     2 |    78 |     4   (0)|
|   4 |    PARTITION RANGE SINGLE|                        |    12 |   180 |     2   (0)|
|*  5 |     INDEX RANGE SCAN     | IDX_FACT_TABLE         |    12 |   180 |     2   (0)|
----------------------------------------------------------------------------------------

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

   5 - access("F"."COL0"="A"."COL0" AND "F"."COL3"=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "COL0","COL1","COL2" FROM "DIM_TABLE" "A" WHERE "COL1"=421220 AND "COL2"=20
       (accessing 'DBL_XXX' )

Statt der Cardinality-Schätzung 147, die sich bei der einzelnen Ausführung des Remote SQL ergibt, erscheint hier eine 2, was im gegebenen Fall wahrscheinlich kein Problem ist, da der NL hier angemessen erscheint - aber manchmal einen geeigneteren HASH JOIN verhindern könnte. Meine Vermutung ist, dass die Extended Statistics nur beim "fully remote statement" herangezogen werden können, während das Join-Statement auf die Statistiken der Einzelspalten zurückgreift - möglicherweise, weil es in Datenbank A ausgeführt wird, also in 10.2.0.4, wo extended statistics noch gar nicht bekannt sind. Zur Prüfung dieser Annahme ein Versuch mit Ausführung in Datenbank B - erzwungen durch den DRIVING_SITE-Hint, zu dem Jonathan Lewis gelegentlich ein paar Hinweise gegeben hat.

select /*+ DRIVING_SITE(a) */ count(*)
  from fact_table f
     , dim_table d
 where f.col0 = d.col0
   and d.col1 = 421220
   AND d.col2 = 20
   and f.col3 = '01.12.2011'

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE       |                              |     1 |    38 |   395   (1)|
|   1 |  SORT AGGREGATE               |                              |     1 |    38 |            |
|   2 |   NESTED LOOPS                |                              |  1703 | 64714 |   395   (1)|
|   3 |    TABLE ACCESS BY INDEX ROWID| DIM_TABLE                    |   147 |  2352 |   100   (0)|
|*  4 |     INDEX RANGE SCAN          | IDX_DIM_TABLE                |   147 |       |     3   (0)|
|   5 |    REMOTE                     | FACT_TABLE                   |    12 |   264 |     2   (0)|
---------------------------------------------------------------------------------------------------

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

   4 - access("A1"."COL2"=20 AND "A1"."COL1"=421220)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   5 - SELECT "COL0","COL3" FROM "XXX"."FACT_TABLE" "A2" WHERE "COL3"=TO_DATE(' 2011-12-01
       00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "COL0"=:1 (accessing '!' )

Damit ist man dann wieder bei der 147, was meine Vermutung zu bestätigen scheint. Und damit ist das Problem in diesem Fall vermutlich nicht einmal durch einen Umzug der DIM_TABLE in Datenbank A lösbar, sondern erfordert eine grundsätzlichere Behandlung (oder einen kompletten Umzug der Applikation nach B).

Optimizer Trace für Queries im Cache

Obwohl ich nur recht selten das Event 10053 (aka Optimzer Trace) verwende, um die Entscheidungen des CBO genauer nachvollziehen zu können, finde ich Maria Colgans Hinweis darauf, dass man ein solches Tracing für ein im Cache vorliegendes Statement in 11.2 einfach mit Hilfe von DBMS_SQLDIAG.DUMP_TRACE erzeugen kann, sehr interessant. Vor einigen Monaten hatte Greg Rahn dazu schon mal etwas geschrieben, aber ich hatte die Möglichkeit schon wieder völlig vergessen, obwohl ich sie hier verlinkt hatte ...

Die einzige ausführlichere Erläuterung zu Event 10053, die mir bekannt ist, findet sich (natürlich) in Jonathan Lewis' CBO-Buch.

MERGE und Transitive Closure

Eigentlich hatte ich im folgenden Zusammenhang vermutet, einem Problem des CBO auf der Spur zu sein; es ist aber wohl doch eher ein Problem des vorliegenden ETL-Prozesses...

Dass der Optimizer dazu in der Lage ist, über transitive closure (also die Transitive Hülle) zusätzliche Prädikate zu ergänzen, kann man unter anderem in Jonathan Lewis' CBO-Buch nachlesen. Kurz zusammengefasst geht es darum, dass der CBO indirekte Abhängigkeiten erkennen kann:

select ...
  from a
     , b
 where a.id = b.id
   and a.id = 10

In einem solchen Fall erkennt der CBO, dass indirekt gilt:

and b.id = 10

Bei Jonathan Lewis erfährt man auch, dass die transitive closure in manchen Fällen unerwünschte Effekte hervorruft, da sie die Arithmetik des CBO durcheinander bringen kann, aber in den meisten Fällen ist sie recht nützlich. Man kann zu diesem Thema noch sehr viel mehr sagen, aber ich verweise in diesem Fall auf die üblichen Verdächtigen (neben dem Herrn Lewis wäre da noch an Randolf Geist zu denken), in deren Blogs, Büchern und Präsentationen man dazu allerlei Material finden kann.

Mein eigentliches Thema ist ein anderes: ich habe dieser Tage zum wiederholten Mal gesehen, dass die Prädikat-Weitergabe über transitive closure im Fall von MERGE-Statements nicht immer zu greifen scheint. Im fraglichen Fall (unter 10.2.0.4) wurde in der USING-clause des MERGEs auf einen Datums-Range eingeschränkt und diese Einschränkung wäre nützlich gewesen, um in der Zieltabelle eine Partition Elimination durchzuführen. Die zugehörige Query sah ungefähr folgendermaßen aus (hier anonymisiert und vereinfacht):

MERGE /*+ APPEND */ INTO FACT 
USING (SELECT A_DATE
            , ...
         FROM (SELECT DISTINCT 
                      A_DATE
                    , ...
                 FROM BASE
                WHERE A_DATE BETWEEN :B2 AND :B1 ) 
       ) SOURCE
    ON (     some_conditions
         AND FACT.A_DATE = SOURCE.A_DATE ) 
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED THEN INSERT ...

Die Query rief eine extreme Laufzeit hervor und der zugehörige Plan war:

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                |          |       |       |       | 57703 (100)|          |       |       |        |      |            |
|   1 |  MERGE                         | FACT     |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR               |          |       |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10001 | 41430 |  8981K|       | 57703   (7)| 00:13:04 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     VIEW                       |          |       |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|   5 |      HASH JOIN OUTER           |          | 41430 |  8981K|       | 57703   (7)| 00:13:04 |       |       |  Q1,01 | PCWP |            |
|   6 |       BUFFER SORT              |          |       |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|   7 |        PX RECEIVE              |          | 41430 |  5502K|       | 17408   (6)| 00:03:57 |       |       |  Q1,01 | PCWP |            |
|   8 |         PX SEND PARTITION (KEY)| :TQ10000 | 41430 |  5502K|       | 17408   (6)| 00:03:57 |       |       |        | S->P | PART (KEY) |
|   9 |          VIEW                  |          | 41430 |  5502K|       | 17408   (6)| 00:03:57 |       |       |        |      |            |
|  10 |           SORT UNIQUE          |          | 41430 |  2022K|  6520K| 17408   (6)| 00:03:57 |       |       |        |      |            |
|  11 |            FILTER              |          |       |       |       |            |          |       |       |        |      |            |
|  12 |             TABLE ACCESS FULL  | BASE     | 41430 |  2022K|       | 16966   (6)| 00:03:51 |       |       |        |      |            |
|  13 |       PX PARTITION RANGE ALL   |          |   895M|    71G|       | 39789   (7)| 00:09:01 |     1 |   362 |  Q1,01 | PCWC |            |
|  14 |        TABLE ACCESS FULL       | FACT     |   895M|    71G|       | 39789   (7)| 00:09:01 |     1 |   362 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------------

Demnach wurden also alle FACT-Partitionen von 1 bis 362 gelesen, obwohl der Partition-Key A_DATE als Join-Bedingung verwendet wurde und im USING auf einen Range eingeschränkt wurde. Die Einschränkung betraf übrigens genau einen Monat, was der Partitionierungs-Strategie der FACT-Tabelle entspricht. Eine Ersetzung der Bindewerte durch Literale änderte das Verhalten nicht.

An einem der folgenden Tage habe ich dann die semantisch redundante Sub-Query im Using herausgenommen und erhielt einen Plan, bei dem die transitive closure wieder zu funktionieren schien - und damit dann auch die Partition Elimination; so jedenfalls deutete ich die KEY-KEY-Einschränkungen (der zeitliche Abstand erklärt dann auch die veränderten Cardinalities):

MERGE /*+ APPEND */ INTO FACT 
USING (SELECT DISTINCT 
              A_DATE
            , ...
         FROM BASE
        WHERE A_DATE BETWEEN :B2 AND :B1 ) 
       ) SOURCE
    ON (     some_conditions
         AND FACT.A_DATE = SOURCE.A_DATE ) 
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED THEN INSERT ...

Liefert folgenden Plan:

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                         |           | 19294 |  5916K|       | 43473   (1)|       |       |        |      |            |
|   1 |  MERGE                                  | FACT      |       |       |       |            |       |       |        |      |            |
|   2 |   PX COORDINATOR                        |           |       |       |       |            |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                  | :TQ10001  | 19294 |  4182K|       | 43473   (1)|       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     VIEW                                |           |       |       |       |            |       |       |  Q1,01 | PCWP |            |
|   5 |      NESTED LOOPS OUTER                 |           | 19294 |  4182K|       | 43473   (1)|       |       |  Q1,01 | PCWP |            |
|   6 |       BUFFER SORT                       |           |       |       |       |            |       |       |  Q1,01 | PCWC |            |
|   7 |        PX RECEIVE                       |           |       |       |       |            |       |       |  Q1,01 | PCWP |            |
|   8 |         PX SEND ROUND-ROBIN             | :TQ10000  |       |       |       |            |       |       |        | S->P | RND-ROBIN  |
|   9 |          VIEW                           |           | 19294 |  2562K|       |  4881   (9)|       |       |        |      |            |
|  10 |           SORT UNIQUE                   |           | 19294 |   942K|  3048K|  4881   (9)|       |       |        |      |            |
|  11 |            FILTER                       |           |       |       |       |            |       |       |        |      |            |
|  12 |             TABLE ACCESS FULL           | BASE      | 19294 |   942K|       |  4674   (9)|       |       |        |      |            |
|  13 |       PARTITION RANGE ITERATOR          |           |     1 |    86 |       |     0   (0)|   KEY |   KEY |  Q1,01 | PCWP |            |
|  14 |        TABLE ACCESS BY LOCAL INDEX ROWID| FACT      |     1 |    86 |       |     0   (0)|   KEY |   KEY |  Q1,01 | PCWP |            |
|  15 |         INDEX RANGE SCAN                | PK_FACT   |     1 |       |       |     0   (0)|   KEY |   KEY |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------

Allerdings hätte ich einen FTS-Zugriff auf FACT vorgezogen. Mit einem FULL-Hint für FACT bekam ich dann aber wieder den ursprünglichen Plan mit dem Zugriff auf alle Partitionen. Die Planänderung hatte also nichts mit meiner Umformulierung zu tun, sondern ergab sich aus den veränderten Statistiken. Die Elimination funktionierte im FTS-Fall erst wieder, als ich das DISTINCT entfernte, das vermutlich eingebaut werden musste, um "ORA-01427: single-row subquery returns more than one row" zu vermeiden (da die BASE-Tabelle offenbar tatsächlich Duplikate enthalten kann). Ohne DISTINCT und mit FULL-Hint ergibt sich:

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT               |          | 19294 |  5916K| 33548   (9)|       |       |        |      |            |
|   1 |  MERGE                        | FACT     |       |       |            |       |       |        |      |            |
|   2 |   PX COORDINATOR              |          |       |       |            |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10002 | 19294 |  2562K| 33548   (9)|       |       |  Q1,02 | P->S | QC (RAND)  |
|   4 |     VIEW                      |          |       |       |            |       |       |  Q1,02 | PCWP |            |
|   5 |      FILTER                   |          |       |       |            |       |       |  Q1,02 | PCWC |            |
|   6 |       HASH JOIN OUTER BUFFERED|          | 19294 |  2562K| 33548   (9)|       |       |  Q1,02 | PCWP |            |
|   7 |        BUFFER SORT            |          |       |       |            |       |       |  Q1,02 | PCWC |            |
|   8 |         PX RECEIVE            |          | 19294 |   942K|  4674   (9)|       |       |  Q1,02 | PCWP |            |
|   9 |          PX SEND HASH         | :TQ10000 | 19294 |   942K|  4674   (9)|       |       |        | S->P | HASH       |
|  10 |           TABLE ACCESS FULL   | BASE     | 19294 |   942K|  4674   (9)|       |       |        |      |            |
|  11 |        PX RECEIVE             |          |  2237K|   183M| 28872   (9)|       |       |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH          | :TQ10001 |  2237K|   183M| 28872   (9)|       |       |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR    |          |  2237K|   183M| 28872   (9)|   KEY |   KEY |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL   | FACT     |  2237K|   183M| 28872   (9)|   KEY |   KEY |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Demnach verhindert das DISTINCT die Weitergabe der Datumseinschränkung über transitive closure und das Partition Pruning (jedenfalls im FTS-Fall). Offenbar kann der CBO nicht erkennen, dass das DISTINCT keinen Einfluß auf die angegebenen Datums-Ranges haben kann. Die KEY-KEY-Einschränkungen im Fall des Index-Zugriffs sind übrigens das Ergebnis des NL-Joins, der ein dynamisches Pruning ermöglicht, wie Randolf Geist in seinem Kommentar erklärt. Um das Lesen der überflüssigen SOURCE-Partitionen zu vermeiden, hätte man demnach folgende Möglichkeiten:

• Umbau der ETL-Logik zur Vermeidung von Duplikaten in der Tabelle BASE - dann wäre das DISTINCT verzichtbar.
• Ergänzung einer zusätzlichen Zeiteinschränkung in der ON-clause (WHERE FACT.A_DATE BETWEEN :B2 AND :B1), so dass man sich nicht mehr auf die transitive closure verlassen muss.

Nachtrag 05.12.2011: ich habe den Eintrag heute Morgen noch mal überarbeitet, nachdem mir klar wurde, dass meine Schlußfolgerungen nicht wirklich schlüssig waren - erst im Anschluß daran habe ich gesehen, dass Randolf Geist bereits einen Kommentar zum Thema abgegeben hatte, der ebenfalls auf diese Fehleinschätzungen hinwies (und der dadurch vielleicht nicht mehr ganz zum Eintrag zu passen scheint).

Locks und Latches im SQL Server

Bei Thomas Kejser gibt's eine sehr schöne Erläuterung zum Verhalten von Locks und Latches im SQL Server und zu den zugehörigen Analysemöglichkeiten, die die DMVs liefern.

Recyclebin

Über den Recyclebin haben zuletzt Charles Hooper und Timur Akhmadeev geschrieben:

• Hooper zeigt, wie es dazu kommen kann, dass in Zugriffsplänen Indizes mit BIN%-Namen erscheinen: diese Namen ergeben sich, wenn eine Tabelle über flashback wiederhergestellt wird - die Tabelle erhält dann wieder ihren urspünglichen Namen, aber die zugehörigen Indizes behalten ihren Recyclebin-Namen (den man natürlich auch wieder ändern kann). Wie üblich liefert der Autor alle erdenklichen Beispiele und Links zum Thema.
• Ahkmadeev erwähnt einen Fall, in dem ein INSERT INTO ... VALUES-Statement für einen einzelnen Satz über eine Stunde lang hing und CPU verbrannte, weil es mit einem rekursiven DELETE FROM RECYCLEBIN$ kollidierte, das (vermutlich in einem Loop) mehr als eine Million mal ausgeführt wurde.

Beschreibende Index-Hints

Jonathan Lewis erläutert in seinem Blog beschreibende Index-Hints, die seit Version 10 verfügbar sind. Dabei wird nicht der Name eines Index angegeben, sondern die Tabelle, der zugehörige Query-Block und die Reihenfolge der relevanten Spalten.

Details zu DBMS_METADATA

Zwei interessante Notizen zu DBMS_METADATA:

• Neil Johnson zeigt, dass für die Verwendung von DBMS_METADATA die SELECT_CATALOG_ROLE benötigt wird.
• Gary Myers führt vor, wie man mit Hilfe von DBMS_METADATA auf die Hash-Version von Benutzer-Paßwörtern zugreifen kann (was in 11g über DBA_USERS nicht mehr möglich ist)

DROP-Operationen verhindern

Carsten Czarski zeigt in seinem Blog, wie man das versehentliche Löschen von Datenbankobjekten mit Hilfe von Triggern erschweren kann.

LISTAGG

Da ich heute - wahrscheinlich zum wiederholten Mal - die LISTAGG-Funktion in Releases unterhalb von 11.2 verwenden wollte, ehe mir einfiel, dass sie dort noch nicht existiert, hier ein Hinweis auf Tim Halls Erläuterung diverser Methoden zur Aggregation von Strings, die unter anderem auch die handliche WM_CONCAT-Funktion enthält. Weitere Hinweise auch zur Performance verschiedener Verfahren findet man bei Adrian Billington (bei dem ich dieser Tage andauernd lande), der auch einen Link auf Tom Kytes klassische STRAGG-Funktion liefert.

CBO-Schätzungen für PL/SQL

Randolf Geist stellt in seinem Blog eine Liste von Artikeln zusammen, auf die er in seinen DOAG-Unconference-Sessions (die ich gerne gesehen hätte) Bezug genommen hat - vor allem auf Adrian Billingtons Beiträge zum Thema und auf Joze Senegacniks grundlegendes Kapitel im Band Expert Oracle Practices.

Index Organized Tables

Jonathan Lewis hat ein paar Links zu Index Organized Tables (IOT) aufgelistet: vor allem führt er Martin Widlakes Artikelserie zum Thema auf.

Nachtrag 13.12.2011: Noch ein Hinweis vom Herrn Lewis: die including clause stellt nicht notwendig sicher, dass eine Spalte im Index-Segment landet (und nicht im Overflow-Segment), da Oracle die Spalten unter Umständen intern umstellt.

Cardinality Probleme bei Star Transformation

Randolf Geist erläutert in seinem Blog, dass der CBO bei der Star Transformation zu falschen Cardinality-Schätzungen kommt, wenn mehrere Dimensionen in einer einzigen Tabelle zusammengefasst sind.

Instabile Pläne

Kerry Osborne hat vor einiger Zeit ein extrem nützliches Script zur Bestimmung von Queries veröffentlicht, deren Ressourcen-Nutzung nach einer Planänderung dramatisch verändert ist. Hintergrundinformationen zum Thema (und weitere Analysescripte) findet man in seinem Blog. Ich habe das Script behutsam erweitert (und weniger behutsam formatiert) und meiner eigenen Sammlung hinzugefügt:

• Kerry Osbornes Script
• Meine Version

Basierend auf dem Ergebnis kann man dann in DBA_HIST_SQLSTAT prüfen, ob die Änderung eine Verbesserung oder eine Verschlechterung darstellte. Und anschließend kann mann dann mit Hilfe von DBMS_XPLAN.DISPLAY_AWR die im Automatic Workload Repository vorliegenden Pläne ermitteln.

Statistik-Erfassung für große partitionierte Tabellen

Eine kurze Notiz zur Frage: wie sollten Statistiken für große partitionierte Tabellen aktualisiert werden? Darauf gibt es viele Anworten, zwei davon findet man im Blog der cbo-Entwickler. Darin erläutert Maria Colgan die Optionen für 10.2.0.4 und 11:

• In Version 10.2.0.4 kann man mit Hilfe von DBMS_STATS.COPY_TABLE_STATS die Statistiken einer Partition in eine andere Partition kopieren, wobei die Statistiken der partition key Spalte sinnvoll angepasst werden; so wie ich's verstehe, bleiben die übrigen Spalten-Statistiken identisch, was aber in der Regel kein Problem sein sollte, da die meisten Spalten der für das Feature in Frage kommenden großen historisch partitionierten Faktentabellen pro Partition ähnliche Eigenschaften haben sollten

• In Version 11 kann man die globale Statistik-Erhebung für große partitionierte Objekte auf INCREMENTAL umstellen, was bedeutet, dass nur noch die Deltas analysiert werden müssen und distinkte Anzahlen über eine Synopsis bestimmt werden. Damit sollte das Thema in Version 11 deutlich unproblematischer sein. Weitere Details zum Thema findet man bei Greg Rahn, der auch noch mal auf ein weiterführendes White-Paper verweist (das auch bei Frau Colgan erwähnt wird).

Doug Burns hat zum Thema der Statistiken für partitionierte Tabellen eine ganze Serie von Blog-Artikeln geschrieben, die neben weiteren relevanten Artikeln von Randolf Geist und Kerry Osborne im Oracle Sratchpad verlinkt sind. Dort stellt Jonathan Lewis fest: "It’s possible to spend ages talking about the best ways of collecting, or creating, statistics on partitioned tables." Und da hat er offenbar mal wieder Recht.

Nachtrag 18.01.2012: inzwischen hat Randolf Geist ein paar wichtige Hinweise zur inkrementellen Erhebung von Partitionsstatistiken veröffentlicht - und vor allem auch ein paar potentielle Probleme des Features aufgeführt.

Evil Events

Tanel Poder erwähnt in seinem Blog ein paar Events, die Oracle offenbar moralisch bewertet, nämlich Inject Evil Literals und Inject Evil Identifiers. Nahe liegend ist dann die von Oracle vorgeschlagene Action zu den Fehlermeldungen: "never set this event".

TKPROF mit External Table anzeigen

Adrian Billington erläutert, wie man TKPROF-Ausgabe-Dateien mit Hilfe des External Table Preprocessors in sqlplus verfügbar machen kann:

Using the simple techniques described in this article, we are now able to find a trace file, read it using the TRACEFILE_XT read-only external table, profile it with preprocessor external tables over TKProf or OraSRP and read the reports without leaving our IDE once. We can even read HTML reports in a browser window generated from within SQL*Plus without any additional keystrokes. For readers who work regularly with trace files, these are good productivity improvements!

Logging in Error-Tabelle

Dieser Tage wurde ich an die Möglichkeit erinnert, Fehler bei DML-Operationen in einer DML-Error-Logging-Tabelle zu protokollieren, was ich mal wieder als Option betrachte, an PL/SQL vorbei zu kommen...

Der Database Data Warehousing Guide erläutert diese Option grundsätzlich folgendermaßen:

DML error logging extends existing DML functionality by enabling you to specify the name of an error logging table into which Oracle Database should record errors encountered during DML operations. This enables you to complete the DML operation in spite of any errors, and to take corrective action on the erroneous rows at a later time.

This DML error logging table consists of several mandatory control columns and a set of user-defined columns that represent either all or a subset of the columns of the target table of the DML operation using a data type that is capable of storing potential errors for the target column.

Ein detailliertes Beispiel für das Vorgehen gibt's bei Tim Hall; für den eiligen Nutzer hier eine kleinere Demonstration:

-- Anlage einer Tabelle TEST
create table test
( id number
, name varchar2(10)
, description varchar2(32)
, some_data number(8));

-- Anlage einer passenden ERROR-Table zur Tabelle TEST
-- mit Hilfe des dbms_errorlog-Packages
exec dbms_errlog.create_error_log (dml_table_name => 'test');

-- Die erzeugte ERROR-Tabelle besitzt das Präfix ERR$_
-- und enthält alle Spalten der Zieltabelle als VARCHAR2(4000)
-- und zusätzlich diverse Spalten zur Speicherung von Fehler-
-- Informationen
desc ERR$_TEST

Name                                      Null?    Typ
----------------------------------------- -------- -------------------
ORA_ERR_NUMBER$                                    NUMBER
ORA_ERR_MESG$                                      VARCHAR2(2000)
ORA_ERR_ROWID$                                     ROWID
ORA_ERR_OPTYP$                                     VARCHAR2(2)
ORA_ERR_TAG$                                       VARCHAR2(2000)
ID                                                 VARCHAR2(4000)
NAME                                               VARCHAR2(4000)
DESCRIPTION                                        VARCHAR2(4000)
SOME_DATA                                          VARCHAR2(4000)

Details zum Format der ERROR-Tabelle liefert der Administrator's Guide:

• ORA_ERR_NUMBER$: Oracle error number
• ORA_ERR_MESG$: Oracle error message text
• ORA_ERR_ROWID$: Rowid of the row in error (for update and delete)
• ORA_ERR_OPTYP$: Type of operation: insert (I), update (U), delete (D) Note: Errors from the update clause and insert clause of a MERGE operation are distinguished by the U and I values.
• ORA_ERR_TAG$: Value of the tag supplied by the user in the error logging clause

Mit Hilfe der Klausel LOG ERRORS INTO ... kann man nun eine DML-Operation zum erfolgreichen Abschluss bringen, die sonst auf Fehler gelaufen wäre:

-- mein Insert klappt erst mal nicht
insert into test
select rownum
     , 'bla'
     , 'irgendwas'
     , rownum * 1000000
  from dual
connect by level < 10000;

     , rownum * 1000000
              *
FEHLER in Zeile 5:
ORA-01438: Wert größer als die angegebene Gesamststellenzahl, die für diese Spalte zulässig ist

-- aber mit der LOG ERRORS INTO Klausel geht's durch
insert into test
select rownum
     , 'bla'
     , 'irgendwas'
     , rownum * 1000000
  from dual
connect by level < 10000
log errors into err$_test ('insert') reject limit unlimited;

99 Zeilen wurden erstellt.
--> die übrigen 9900 Sätze landen dabei in ERR$_TEST

Offenbar wird dabei nur der erste auftretende Fehler protokolliert:

-- Löschung der Daten der ERROR-Tabelle:
truncate table err$_test;

-- dazu ein INSERT, das für mehrere Spalten unverdauliche Daten enthält:
insert into test 
values ( 1
       , 'blaaaaaaaaaaaaaaaaaaaaaa'
       , 'ssssssssssssssssssssssssssspppppppppppppppppppaaaaaaaaaaaaaaaaaaaaaaaaammmmmmmmmmmmmmmmm'
       , 123456789000000000000)
log errors into err$_test ('insert') reject limit unlimited;

0 Zeilen wurden erstellt.

--> was für die Spalten 2 - 4 nicht klappen kann

-- Die Metadaten zum Fehler sind dann:

select ora_err_number$
     , ora_err_mesg$
     , ora_err_rowid$
     , ora_err_optyp$
     , ora_err_tag$
  from err$_test;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                  ORA_ERR_ROWID$  ORA_ERR_OPTYP$  ORA_ERR_TAG$
--------------- ------------------------------ --------------- --------------- ---------------
          12899 ORA-12899: Wert zu groß für Sp                 I               insert
                alte "DBADMIN"."TEST"."NAME" (
                aktuell: 24, maximal: 10)

-- dann folgen in err$_test die Werte des gescheiterten INSERTs.

Die Dokumentation (in diesem Fall die SQL Language Reference) nennt noch folgende Fälle, in denen der Mechanismus nicht verwendbar ist - also ein Fehler auftritt und ein Rollback durchgeführt wird:

• Verletzung von deferred constraints.
• Direct-Path-Operationen, die eine unique constraint oder index violation hervorrufen.
• Update oder Merge Operationen, die eine unique constraint oder index violation hervorrufen.

Für LONG, LOB und Objekt-Typen gelten spezielle Bedingungen, deren Aufzählung ich mir hier aber mit Verweis auf die Doku spare.

Auto DOP in 11.2

Dieser Tage haben sich Uwe Hesse, Randolf Geist und Gwen Shapira mit dem neuen Auto Degree Of Parallelism (DOP) Feature in 11.2 beschäftigt.

Eine grundlegende Diskussion des Features liefert Frau Shapira in ihrem Blog:

at least in the white papers and presentations it sounds like a very attractive solution. There are two levels to Auto DOP:

• Limited – when accessing tables and indexes that have been declared with parallel clause, Oracle will decide on the degree of parallelism based on the query and system resources.
• Auto – Oracle will decide on degree of parallelism for every query. In addition two exciting new features are enabled: parallel statement queuing and in-memory parallel execution.

Der Herr Hesse erläutert die Wirkung des Parameters parallel_degree_policy und die daraus resultierende Wirksamkeit von Hints:

parallel_degree_policy=LIMITED will give you the DOP you request and compute an appropriate DOP only with a parallel degree of DEFAULT as an attribute of the table. Use this parameter if you trust that your applications/designers know why they use a certain DOP. parallel_degree_policy=AUTO will overrule any specific DOP you gave – except the new 11g parallel (n) Hint – and consider to do things in parallel for all tables even without a Hint or Degree.

Beim Herrn Geist geht's um die Beobachtung, dass ein parallel_degree_policy=auto die Verwendung von direct path inserts hervorrufen kann, was zwar in gewisser Weise folgerichtig ist, aber die üblichen Nebeneffekte des direct path hervorruft (INSERT oberhalb der HWM und "ORA-12838: cannot read/modify an object after modifying it in parallel"), wobei besonders ORA-12838 ungünstige Wirkungen haben kann:

An existing application logic might break because it attempts to re-access the object after the now direct-path insert within the the same transaction which will end up with an "ORA-12838: cannot read/modify an object after modifying it in parallel".

Bei den Herren Hesse und Geist gibt's die dort üblichen Testfällen, mit deren Hilfe die Effekte nachvollzogen und überprüft werden können.

Nachtrag 12.11.2011: In seinem Kommentar weist Randolf Geist darauf hin, dass Uwe Hesses Beobachtung der Ausschaltung von Parallel-Hints durch parallel_degree_policy=AUTO wahrscheinlich auf Bug 10628995 beruht. In seinem Kommentar zu Uwe Hesses Blog kommt Greg Rahn zur gleichen Einschätzung.

SQL Sentry Plan Explorer

Dieser Tage bin ich auf den kostenlosen SQL Sentry Plan Explorer gestossen, ein Tool, mit dessen Hilfe sich Execution Plans im SQL Server deutlich klarer visualisieren lassen als mit den Bordmitteln im SSMS (die selbst aber schon ganz brauchbar sind). Das Tool lässt sich als Add-In zum SSMS einrichten und erfordert eine Installation von .Net 4.0. Zu den Features des Tools gehören das Plan Diagram mit einer klaren Hervorhebung der kostspieligsten Abschnitte eines Plans, der Join Tree mit einer Visualisierung der Tabellenverknüpfungen und nicht zuletzt der Plan Tree, der den Zugriffsplan so darstellt, wie man es aus Oracle z.B. vom dbms_display-Package kennt. Nett und - wie gesagt - kostenlos.

Mutex Waits

Andrey Nikolaev hat mal wieder einen interessanten Artikel über das Verhalten von Mutexes veröffentlicht. Dieses Verhalten hat sich offenbar in 11.2 deutlich verändert, so dass Mutex Waits nun ziemlich gut analysierbar und damit auch optimierbar sind. Für das Verhalten vor 11.2 galt dabei Folgendes (die Aufzählungen sind dabei jeweils Zitate aus der Quelle):

• “Cursor: pin S” was pure wait for CPU. Long “cursor: pin S” waits indicated CPU starvation.
•  Mutex contention was almost invisible to Oracle Wait Interface.
•  Spin time to acquire mutex was accounted as CPU time. It was service time, not waiting time.

In 11.2 gilt:

• Oracle 11.2 Wait Interface computes “wait time” as a duration between the first spin and successful mutex acquisition. As a result we observe only one wait in v$system_event.
• Oracle 11.2 mutex wait includes both spinning and waiting.
• the 11.2.0.2 the “cursor: pin S” wait event no more starve CPU. This reduces probability of CPU thrashing due to mutex waits.

Es folgen noch allerlei Informationen zu den (underscore) Parametern, die das Verhalten der Mutexe steuern, aber die Details will ich hier nicht wiederholen 

OWB und ODI

Peter Scott von Rittman Mead Consulting verspricht eine Artikelserie, in der er die Unterschiede zwischen Oracle Warehouse Builder (OWB) und Oracle Data Integrator (ODI) erläutern will und liefert zunächst eine Zusammenfassung der konzeptionellen Gemeinsamkeiten und Unterschiede:

• "Both ODI and OWB have a similar (I am being very simplistic here) three-component design of: a metadata repository, a development environment where the developer defines the processes and data flows and a runtime component that executes the code and flows."
• Speicherung des Repositories:
• OWB: Repository ist vorinstalliert in einer Oracle-DB
• das ODI Repository wird mit Hilfe des Oracle Fusion Middleware’s Repository Creation Utility in einer unterstützten DB angelegt (nicht notwendig Oracle) 
• Standard-Operationen:
• "with OWB the key parts of the IDE are those for the development of MAPPINGS and (optionally) the design of process flows to orchestrate mappings."
• "In the ODI world think INTERFACES for mappings and PACKAGES for process flows. This is simplistic though as ODI also has PROCEDURES (code developed in one of the ODI supported languages) and LOAD PLANS (multiple packages orchestrated to execute in serial or parallel)"
• Umsetzung der Operationen:
• OWB mappings require the developer to include all of the components needed to facilitate the mapping – we connect source columns to target columns through a logic flow of joiners, filters, expressions, aggregates and a whole palette of other activities. Typically, this would generate a single, but large, SQL statement with much use of in-line views." (immer PL/SQL)
• "ODI interfaces are simply about connecting source columns to target columns in a logical relationship (we also create expressions, joins and filters here) and allowing the physical implementation to be supplied by a knowledge module." (kann auch einfach SQL sein)

Meine Erfahrungen mit dem OWB sind sehr beschränkt und mit dem ODI habe ich noch nie ernsthaft gearbeitet, aber aus meiner Sicht sind die gravierendsten Einschränkungen des OWB (abgesehen von seiner fehlenden Zukunft), dass er oft recht suboptimales SQL (in PL/SQL-Verpackung) erzeugt und viele neuere Datenbankfeatures kalt lächeln ignoriert - und das scheint im Fall des ODI dann mit Hilfe des knowledge modules stärker beeinflußbar zu sein. Dass grafische ETL-Tools bei der Definition relativ einfacher Transformationen oft sehr umständliche GUI-Darstellungen erzeugen, ist vermutlich eher ein allgemeines Problem solcher Werkzeuge. Und dass der PL/SQL-Code des OWB in nahezu jedem Fall ausufernd groß wird, ist vermutlich nur für Kommandozeilen-Fetischisten wie mich ein Problem.

DBMS_SCHEDULER Grundlagen

Dieser Tage habe ich mich zum ersten Mal ein wenig mit dem DBMS_SCHEDULER beschäftigt und nehme das zum Anlass, ein paar grundlegende Dinge aufzuschreiben und einige Links zu notieren. Grundsätzlich dient der Scheduler zur Definition Zeit- oder Event-gesteuerter Aktionen und bietet dabei deutlich umfangreichere Funktionen als DBMS_JOB - z.B. ist es möglich komplexe Zeitpläne und Ausführungs-Windows zu definieren, Abhängigkeiten anzugeben und ein detailliertes Monitoring der Ausführungen zu erzeugen. Hier nur ein ganz einfaches Beispiel:

-- Loeschung des Jobs (wenn vorhanden)
exec dbms_scheduler.drop_job (job_name => 'my_scheduler_test_job');

-- Job-Definition
begin
  dbms_scheduler.create_job (
    job_name        => 'my_scheduler_test_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN TEST_PROC; END;',
    start_date      => systimestamp,
    repeat_interval => 'FREQ=DAILY;BYHOUR=09;BYMINUTE=15',
    end_date        => null,
    enabled         => true,
    comments        => 'belangloser Test-Job zur Ausfuehrung einer belanglosen Test-Prozedur.');

end;
/

-- sofortiger Start des Jobs unabhaengig vom Zeitplan
exec dbms_scheduler.run_job('my_scheduler_test_job')

In der Job-Definition kann man die Aktion und den Zeitplan direkt angeben (wie im Beispiel), man kann aber auch zuvor definierte Schedules und Programme verwenden. Eine umfangreiche Darstellung der Möglichkeiten bietet Tim Halls unten verlinkte Artikelserie, während die beiden einführenden Artikel eher die Grundlagen der Verwendung des Schedulers beleuchten (allerdings deutlich heller, als ich das hier gemacht habe).

• Oracle-Scheduler, Eine Alternative zu "professionellen" Schedulern? Sven Vetter erläutert in diesem Trivadis-Dokument die Verbesserungen des Schedulers in 10.2 und führt aus, was ihm noch fehlt
• Jobsteuerung mit dbms_scheduler: einführender Artikel von Kathrin Hammerschmidt bei Ordix
• Job-Steuerung in Oracle Datenbanken: einführender Artikel auf der Website von Held Informatik
• Tim Halls Serie zu den Features des Schedulers in aktuellen Oracle-Versionen
• 10.1
• 10.2
• 11.1

Abhängigkeitsanalysen mit UTL_XML.PARSEQUERY

Philipp Salvisberg erläutert in seinem Blog, wie man UTL_XML.PARSEQUERY als Hilfsmittel zum Parsen verwenden kann, um damit alle View-Spalten zu finden, denen eine bestimmte Spalte einer Basistabelle zugrunde liegt.

SQL_EXEC_ID

Tanel Poder hat sich etwas intensiver mit den technischen Details der SQL_EXEC_ID beschäftigt und dabei - natürlich - ein paar recht interessante Beobachtungen gemacht; unter anderem liefert er eine Erklärung für den seltsamen Wert 16777216, über den ich mich auch gelegentlich schon gewundert hatte.

Planüberschreibung mit DBMS_SPM

Dom Brooks zeigt in seinem Blog ein sehr handliches Beispiel dafür, wie man SQL Plan Baselines einsetzen kann, um den Zugriffsplan einer Query zu ändern, ohne das Statement anpassen zu müssen. Alles was man dafür benötigt, sind der (exakte)  SQL_TEXT der ursprünglichen Query, sowie SQL_ID und PLAN_HASH_VALUE der optimierten Version. Interessant sind auch die ergänzenden Informationen des Folgeartikels Quick overview of loading plans into a baseline.

In diesem Zusammenhang interessant ist auch noch der Hinweis von Maria Colgan, dass für die Verwendung von SQL Plan Management (SPM) und den in DBMS_SPM enthaltenen Prozeduren keine zusätzliche Lizenzierung erforderlich ist. SPM ist Bestandteil der Enterprise Edition.

Florenz

Von Fiesole aus gesehen.

Technical Papers des cbo Teams

Das Team der cbo-Entwickler hat ein paar Links zu technischen Papers bereitgestellt, die sicher alle eine genauere Lektüre verdienen.

block_sample für gather_table_stats

Dass man die Sample-Größe für die dbms_stats-Prozeduren seit Release 11 in aller Regel nicht mehr anpassen muss, da die AUTO_SAMPLE_SIZE sehr gute Resultate liefert, hat Greg Rahn schon vor einigen Jahren festgestellt:

Overall the 11g DBMS_STATS has been enhanced to gather stats in less time, but in my opinion the significant enhancement is to AUTO_SAMPLE_SIZE which yields near 100% sample accuracy in 10% sample time.

Trotzdem hatte ich mir zuletzt Gedanken darüber gemacht, ob man mit der Option block_sample die Laufzeit der Statistik-Erstellung signifikant reduzieren kann. Die Dokumentation erklärt diese Option folgendermaßen:

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

Ein kleiner Test zeigt, dass die Option tatsächlich einen deutlichen Einfluss auf die Leseoperationen der Statistikerfassung hat:

-- Anlage einer relativ großen Testtabelle
drop table big_t;

create table big_t
as
with
base_data
as
(
select rownum id1
  from dual
connect by level <= 1000000
)
,
mult
as
(
select rownum id2
  from dual
connect by level <= 30
)
select rownum id
     , 'aaa' col2
  from mult
     , base_data;

-- Statistikerhebung
exec dbms_stats.gather_table_stats(user, 'BIG_T')
 
exec dbms_stats.gather_table_stats(user, 'BIG_T', estimate_percent=>1)

exec dbms_stats.gather_table_stats(user, 'BIG_T', estimate_percent=>1, block_sample=>TRUE)

Die Ergebnisse für die angesprochenen Versionen sehen folgendermaßen aus:

Version	Parameter	Laufzeit
10.2.0.4	ohne	33.86 sec.
10.2.0.4	estimate_percent: 1	12.31 sec.
10.2.0.4	estimate_percent: 1; block_sampling	1.01 sec.
11.1.0.7	ohne	13.07 sec.
11.1.0.7	estimate_percent: 1	2.40 sec.
11.1.0.7	estimate_percent: 1; block_sampling	1.78 sec.

Dazu noch ein paar Kommentare:

• die deutlichen Laufzeitunterschiede ergeben sich aus der unterschiedlichen I/O-Performance der beiden Testsysteme (sehr langsame Platten für 10.2.0.4)
• ein 10046er Trace zeigt, dass die zugehörigen Queries mit der Klausel SAMPLE bzw. SAMPLE BLOCK ausgeführt werden.
• mit SAMPLE BLOCK sinkt die Anzahl der erforderlichen LIOs und PIOs in beiden Versionen; Für die Queries mit SAMPLE werden alle Tabellenblocks gelesen, während SAMPLE BLOCK tatsächlich nur auf einen gewissen Prozentsatz liest, der ungefähr dem geforderten Wert entspricht (ermittelt mit AUTOTRACE in 11.1.0.7):
• FTS ohne Sampling: 30812  consistent gets
• FTS mit sample(1): 30812  consistent gets
• FTS mit sample block (1): 327  consistent gets
• FTS mit sample block (10): 3399  consistent gets

Für die Erstellung von Statistiken für sehr große Tabellen könnte die block_sample-Option demnach auch in Versionen >= 11 immer noch interessant sein, da sie die erforderlichen Leseoperationen dramatisch reduzieren kann. Das gilt aber natürlich nur dann, wenn sichergestellt ist, dass die Blocks ähnliche Verteilungsmuster besitzen wie die gesamte Tabelle. Die Qualität der mit Hilfe von Sampling erzeugten Statistiken wäre aber ohnehin noch mal ein anderes Thema.

Dynamic Sampling

Heute wollte ich einer Query einen dynamic_sampling Hint mitgeben und fand bei Jonathan Lewis günstigerweise den Hinweis, dass man diesen Hint nicht pro Tabelle setzen muss, sondern auf Query-Ebene definieren kann.

Eine Liste mit einer Beschreibung der Sampling-Levels (und weitere Informationen zum Thema) findet man bei Maria Colgan.

Optimizer Statistics Präsentation bei Open World 2011

Das Team der cbo Entwickler (bzw. Maria Colgan) hat bei der Oracle Open World 2011 offenbar eine sehr schöne Präsentation zur Erstellung und Rolle der Statistiken gehalten, und die zugehörigen Folien ins Netz gestellt. Hier eine kurze Liste mit Punkten, die mir erinnerungswürdig erscheinen:

• dbms_stats
• üblicherweise sollte man nur die ersten Parameter der GATHER_%_STATS-Prozeduren benötigen (Schema Name, Table Name, Partition Name)
• Weitere Parameter:
• Inkrementelle Statistiken: "Ability to accurate generate global statistics from partition level statistics"
• Concurrent Statistics Gathering: "Ability to gather statistics on multiple objects concurrently under a GATHER_SCHEMA_STATS command"
• Default-Werte der Prozeduren können auf verschiedenen Ebenen gesetzt werden
• Sample Size: in 11g sollte man immer die default AUTO_SAMPLE_SIZE verwenden, da sie die Performance eines 10% Samples mit der Genauigkeit eines 100% Samples verbindet (dazu hatte Greg Rahn vor längerer Zeit gebloggt)
•  Index-Statistiken
• "Index statistics are automatically gathered during creation and maintained by GATHER_TABLE_STATS"
• Histogramme
• "Default create histogram on any column that has been used in the WHERE clause or GROUP BY of a statement AND has a data skew" ("Relies on column usage information gathered at compilation time and stored in SYS.COL_USAGE$")
• Cardinality-Berechnung:
• für popular values ("Popular value means values that are the endpoint for two or more buckets") -> (Anzahl Endpoint Buckets/Gesamtanzahl Buckets)/Anzahl rows in der Tabelle
• für non popular values ("Non-popular value means values that are the endpoint for only one bucket or are not an endpoint at all") -> DENSITY X * Anzahl rows in der Tabelle ("Density from 10.2.0.4 is calculated on the fly based on histogram information and is not the value show in USER_HISTOGRAMS")
• Probleme:
• Bind Peeking (in 11.2 durch Adaptive Cursor Sharing kein Problem mehr)
• Nearly popular values ("Nearly popular value means the value is classified as
  non-popular but the density calculation is not accurate for them"): werden unterschätzt; behebbar durch dynamic sampling hint im Statement)
• Extended Statistics
• für column groups: zur Darstellung von Korrelationen
• für expressions (virtual columns)
• automatische Erzeugung mit Hilfe von dbms_stats.seed_col_usage + Monitoring von Workload + dbms_stats.create_extended_stats
• Performance der Statistik-Erstellung durch Parallelisierung
• Parallelisierung für ein Objekt über Degree-Parameter ("Default is to use parallel degree specified on object"
• paralleler Zugriff auf mehrere Objekte: gesteuert über Parameter CONCURRENT
• Kombination beider Optionen möglich
• Inkrementelle Erstellung globaler Statistken
• "Typically gathering statistics after a bulk loading data into one partition would causes a full scan of all partitions to gather global table statistics" -> "Extremely time consuming"
• Inkremental sorgt dafür, dass nur Partitionen gelsen werden, deren Daten geändert wurden
• keine Statistiken für
• Volatile Tables: Tabellen mit stark veränderlichen Daten (Queue-Tabellen)
• GTT
• Intermediate work tables: ETL-Hilfstabellen

Tabellentransfer mit dbms_datapump und DB-Link

Laurent Schneider hat vor kurzem eine kleine Demonstration dazu geliefert, wie man einzelne Tabellen mit Hilfe von dbms_datapump transferieren kann, und auf diese Weise auch die abhängigen Elemente (Indizes, Constraints) befördert, die ein CTAS über DB-Link verlieren würde.

Beim Durchspielen des Beispiels gab's bei mir noch folgende Effekte:

• von 10.2.0.4 nach 11.1.0.7 funktionierte der Transfer problemlos: da der Transfer-Job asynchron läuft, wird die Tabelle zwar sofort angelegt, aber der Datentransfer benötigt anschließend noch einige Zeit, so dass ein SELECT COUNT(*) erst einmal 0 Sätze liefert.
• der umgekehrte Weg (von 11.1.0.7 nach 10.2.0.4) lieferte keinen Fehler, aber die Tabelle kam nicht an. Möglicherweise ist da noch ein Kompatiblitätsproblem im Spiel.

Auf jeden Fall ein interessantes Feature, das Transfer-Tasks deutlich handhabbarer machen könnte.

Dynamic Sampling für stark veränderliche Daten

Randolf Geist erläutert in seinem Blog, in welchen Fällen dynamic sampling für "highly volatile data" (z.B. in einer global temporary table) geeigneter ist als eine grundsätzliche Erhebung der Statistiken, und vor allem unter welchen Umständen dynamic sampling nicht ohne Weiteres weiterhilft:

However, there is still a potential problem even when resorting to Dynamic Sampling. If the cursors based on Dynamic Sampling get shared between sessions then they won't be re-optimized even if a GTT in one session is completely different from the one of the session that created the shared cursor previously.

Eine mögliche Lösung solcher Probleme liefert die Verwendung von Row Level Security (RLS) durch Ergänzung redundanter Prädikate, die dafür sorgen, dass Queries sich textuell unterscheinden (ergänzt wird 1=1; 2=2 oder dergleichen).

Darüber hinaus werden auch noch die Effekte von Adaptive Cursor Sharing und Cardinality Feedback diskutiert und - wie beim Herrn Geist üblich - anhand zahlreicher ausführlicher Testfälle überprüft.

SSAS 2008 R2 Performance-Guide

Der SSAS-Performance Guide für Version 2008 R2 ist seit einigen Tagen verfügbar. Chris Webb fasst die Bedeutung des Guides prägnant zusammen: "if you are serious about SSAS you need to read it".

Hier noch eine Sammlung von Punkten, die mir erinnerungswürdig erscheinen (ohne jeden Anspruch auf Vollständigkeit):

• Performance-Vorteile geeigneter Attribute Relationships (S. 11)
• "Cross products between levels in the hierarchy do not need to go through the key attribute. This saves CPU time during queries."
• "Aggregations built on attributes can be reused for queries on related attributes. This saves resources during processing and for queries."
• "Auto-Exist can more efficiently eliminate attribute combinations that do not exist in the data."
• Flexible vs. Rigid Relationships (S.12)
• "When a change is detected during process in a flexible relationship, all indexes for partitions referencing the affected dimension (including the indexes for attribute that are not affected) must be invalidated. This is an expensive operation and may cause Process Update operations to take a very long time. Indexes invalidated by changes in flexible relationships must be rebuilt after a Process Update operation with a Process Index on the affected partitions; this adds even more time to cube processing."
• Reference Dimensions (S. 14)
• können materialisiert werden
• Probleme beim ProcessUpdate: "if you run a process update on the intermediate dimension, any changes in the relationships between the outer dimension and the reference will not be reflected in the cube. Instead, the original relationship between the outer dimension and the measure group is retained"
• Large Dimensions (S. 18)
• "If a dimension contains attributes where the total size of the string values (this includes translations) exceeds 4 GB, you will get an error during processing." Für Denali soll das Limit beseitigt werden.
• Partition Slicing (S. 20)
• Auto Slice: "when Analysis Services reads the data during processing, it keeps track of the minimum and maximum attribute DataID reads. These values are used to set the slice when the indexes are built on the partition." + "auto slice typically works best if the data contained in the partition maps to a single attribute value. When that is the case, the maximum and minimum DataID contained in the slice will be equal and the slice will work efficiently."
• Partition Sizing (S. 22)
• "partition size alone does not have a substantial impact on query speeds"
• sollte sich an administrativen Gesichtspunkten orientieren
• die BIDS-Warnung vor Partitionen mit mehr als 20M rows ist bedeutungslos
• Relational Data Source Design (S.26ff.)
• "Use a Star Schema for Best Performance"
• "Consider Moving Calculations to the Relational Engine"
• "Use Views"
•  Query Processor Cache (S. 35)
• Query Context, Session, Context, Global Context: "Even though a query references all three contexts, it will typically use the cache of a single context. This means that on a per-query basis, the query processor must select which cache to use. The query processor always attempts to use the broadly applicable cache depending on whether or not it detects the presence of calculations at a narrower context. If the query processor encounters calculations created at query time, it always uses the query context, even if a query also references calculations from the global context (there is an exception to this –
  queries with query calculated members of the form Aggregate() do share the session cache)."
• offenbar gibt es sehr viele Fälle, in denen der Global Cache nicht verwendet werden kann (MDX functions that are locale-dependent; "use of cell security; functions such as UserName, StrToSet, StrToMember, and StrToTuple; or LookupCube functions in the MDX script or in the dimension or cell security definition disable the global cache. That is, just one expression that uses any of these functions or features disables global caching for the entire cube."; visual totals; "Queries that use the subselect syntax (SELECT FROM SELECT) or are based on a session subcube (CREATE SUBCUBE)"; Arbitrary shapes); es bietet sich an, mit dem SQL Profiler zu prüfen, ob Standard-Excel-Zugriffe den Global Cache verwenden können, oder ob einer der Problemfälle dagegen spricht.
• Data Retrieval (S.38)
• Storage Engine (SE) sucht nach Daten in folgender Reihenfolge:
• SE Cache
• Aggregationen (I/O from disk oder OS-Cache)
• Partitionsdaten (I/O from disk oder OS-Cache)
• "storage engine cache is also known as the data cache registry because it is composed of the dimension and measure group caches that are the same structurally"
• vorhandene Inhalte können aggregiert oder gesplittet werden
• bei Memory-Engpässen wird der Cache geleert
• Aggressive Data Scanning (S. 39): SSAS führt manchmal recht massive Prefetch-Operationen durch (in der Annahme, dass der Anwender die Daten schon noch brauchen wird). Deaktivieren kann man das Prefetching mit "Disable Prefetch = 1" im Connection-String.
• Subspace Computation (S.40ff.): erläutert die Unterschiede zwischen Cell-by-cell evaluation und subspace computation. 
•  Cell-by-cell evaluation: "The 10 cells for [2005, All Products] are each evaluated in turn. For each, the previous year is located, and then the sales value is obtained and then added to the sales for the current year." (bei sparse data wird viel unnötige Arbeit geleistet; außerdem werden Zugriffe unnötig wiederholt)
• "In subspace computation, the engine works its way down an execution tree determining what spaces need to be filled."
• Expensive vs. Inexpensive Query Plans (S. 42): ein Plan ist expensive, wenn er Cell-by-cell evaluation enthält: IIf, CASE, and IF functions können expensive query plans hervorrufen. Eine Liste mit Gründen für eine Cell-by-cell evaluation folgt aus Seite 49ff.
• Optimizing MDX (S. 45ff.): liefert diverse Hinweise zur Query-Optimierung (Cache-Effekte ausschalten; SQL Profiler nutzen; Unterscheidung zwischen SE und FE Events; binäre Vereinfachung von Queries etc.) und auch einige wichtige Links. Hier nur die Punkte, diemir halbwegs neu waren:
• Avoid Assigning Nonnull Values to Otherwise Empty Cells (S. 50): leuchtet ein, denn SSAS "is very efficient at using sparsity of the data to improve performance. Adding calculations with nonempty values replacing empty values does not allow Analysis Services to eliminate these rows."
• Sparse/Dense Considerations with “expr1 * expr2” Expressions (S. 51): die Expression mit der höheren Anzahl von NULLs sollte vorne stehen: entspricht dem Vorgehen bei Nested Loops Joins, wo ebenfalls die kleinere Menge die Schleife treiben sollte.
• IIF-Optimierung mit den Hints EAGER und STRICT (S. 53).
• NON_EMPTY_BEHAVIOR: in 2008 in vielen Fällen nicht mehr nützlich (= wird oft ignoriert), und manchmal sogar ein Problem ("Eliminate it from the MDX script and add it back after performance testing demonstrates improvement")
•  Aggregations (S. 60ff.)
•  unnötige Aggregationen schädigen den Cache: "adding unnecessary aggregations can worsen query performance because the rare hits move the aggregation into the file cache at the cost of moving something else out"; außerdem: "there is a direct correlation between the number of aggregations and the duration for the Analysis Services storage engine to parse them"
•  Aggregations-Zugriffe werden im Profiler durch das "Get Data From Aggregation event" ausgewiesen
• Scale-Out (S. 69): Verteilung auf mehrere Server; z.B. zur Vermeidung des Blockierens durch langlaufende Queries.
• Tuning Processing Performance (S. 70ff.)
• Dimension Processing
• ProcessUpdate (S. 76) 
• erfordert Neuaufbau von flexible aggregations: "Note that ProcessUpdate drops invalid aggregations and indexes, requiring you to take action to rebuild the aggregations in order to maintain query performance. However, flexible aggregations are dropped only if a change is detected."
• kann sehr teuer sein: "When ProcessUpdate runs, it must walk through the partitions that depend on the dimension. For each partition, all indexes and aggregation must be checked to see whether they require updating. On a cube with many partitions, indexes, and aggregates, this can take a very long time. Because this dependency walk is expensive, ProcessUpdate is often the most expensive of all processing operations on a well-tuned system, dwarfing even large partition processing commands."
• ProcessAdd (S. 77): 
• "typically runs much faster than ProcessUpdate."
• mit Default Konfiguration gibt's Fehlermeldungen duplicate key beim Processing ("caused by the addition of non-key properties that already exist in the dimension": Anpassung der Fehlerkonfiguration erforderlich)
• Tuning
• Reduzierung der Dimensionsattribute
• Verzicht auf Bitmap Indexes
• ByTable Processing: liefert notwendigerweise duplicate key Fehler
• Splitting Processing Index and Process Data (S.84): wird immer noch empfohlen