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).