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).
Hallo Martin,
AntwortenLöschenes wäre sehr hilfreich gewesen in diesem Falle, die "Predicate Information" noch mitzuposten.
Ich bin mir recht sicher, dass auch der Plan mit NESTED LOOPs keine Transitive Closure beinhaltet hat - und der Grund für die Plan-Änderung liegt wahrscheinlich mehr an der unterschiedlichen Kardinalitätsabschätzung für BASE von vorher 43000 nach 19000.
Warum dort das Pruning anders funktioniert, liegt schlicht an der Tatsache, dass eben ein NL-Join zum Einsatz kam, der grundsätzlich die Informationen aus der Driving Row Source für den Zugriff in der "inner" Row Source verwenden kann und somit das Pruning dynamisch für jede Iteration der Loop durchführen kann.
Randolf
Hallo Randolf,
AntwortenLöschenja, das stimmt natürlich alles. Ich hatte den Eintrag heute Morgen noch mal überarbeitet, ehe ich den Kommentar sah, da mir auch aufgefallen war, dass da einiges nicht so recht zusammen passte.
Der Hinweis auf das dynamische Pruning beim NL-Join beantwortet dann auch die letzte größere Frage, die für mich noch offen geblieben war (nämlich wie sich die KEY-KEY-Einschränkung im Index-Fall erklärt.
Danke.
Martin
die "Predicate Information" fehlen übrigens, weil die Pläne im Kundensystem mit Explain Plan und einer uralten plan table erzeugt wurden, für die dbms_xplan.display die Sektion nicht ausgibt. Da sollte ich gelegentlich mal um eine Aktualisierung bitten.
AntwortenLöschen