Gestern habe ich im OTN Forum ein paar Fragen zum grundsätzlichen Verhalten der in 12c eingeführten Adaptive Plans gestellt, aber inzwischen denke ich, dass die Überlegungen hier besser aufgehoben wären. Falls der OTN Thread noch interessante Ergänzungen liefern sollte, werde ich sie auch hier hinzufügen. Aber nun zum Thema. Maria Colgan ("The Optimizer Lady" - wenn ich mich richtig erinnere, verdankt sie den Titel Doug Burns) nennt die Adaptive Plans in ihrem White Paper Optimizer with Oracle Database 12c "by far the biggest change to the optimizer in Oracle Database 12c". Zur Arbeitsweise des Features erklärt sie weiterhin:
Adaptive plans enable the optimizer to defer the final plan decision for a statement, until execution time. The optimizer instruments its chosen plan (the default plan), with statistics collectors so that at runtime, it can detect if its cardinality estimates, differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it can be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.
Der CBO entwickelt also nicht wie bisher einen vorab vollständig festgelegten Plan, den er dann zum Zeitpunkt der Ausführung durchführt, sondern er erzeugt einen Gesamtplan mit alternativen Sub-Plänen und behält sich vor, die Wahl des Sub-Plans erst zu treffen, wenn er die tatsächlichen Kardinalität der zugehörigen Input-Menge kennt. Wenn man bedenkt, wie viele suboptimale Pläne sich aus fehlerhaften Entscheidungen zwischen Nested Loops und Hash Joins ergeben, weil die Schätzung des CBO deutlich von den tatsächlichen Größen der Mengen abweichen, dann erscheint diese Strategie ausgesprochen plausibel. In meiner OTN-Frage hatte ich Frau Colgans Erläuterung zitiert, aber offenbar nicht bis zu Ende gelesen, denn der letzte Teil war mir offenbar entgangen: "on the first execution of a SQL statement". Die endgültige Entscheidung für einen Plan wird bei der ersten Ausführung der Query getroffen, wenn der statistics collector seine Zahlen liefert. Dazu ein Beispiel mit 12c auf meinem heimischen Windows 7-Rechner:
-- creation of test tables with indexes and statistics
drop table t1;
drop table t2;
create table t1
as
select rownum id
, mod(rownum, 10) col1
, lpad('*', 20, '*') col2
from dual
connect by level <= 100000;
exec dbms_stats.gather_table_stats(user, 't1')
create index t1_id_idx on t1(col1, id);
create table t2
as
select mod(rownum, 100) id_t1
, lpad('*', 20, '*') col2
, rownum col3
from dual
connect by level <= 100000;
exec dbms_stats.gather_table_stats(user, 't2')
create index t2_idx on t2(id_t1);
-- a significant change of the data distribution
-- without regathering of statistics
update t1 set col1 = 1000 where id > 1;
--> Update for 99999 rows
commit;
Für meine folgende Test-Query hätte die initiale Datenverteilung einen HASH JOIN nahe gelegt, aber durch das Update sollte ein NESTED LOOPS JOIN die bessere Wahl sein. Dazu zunächst die Einschätzung von Explain Plan:
explain plan for
select sum(t1.col1) sum_col3
from t1
, t2
where t1.id = t2.id_t1
and t1.col1 = 1;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 1632433607
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 84 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | HASH JOIN | | 100K| 1074K| 84 (2)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_ID_IDX | 10000 | 80000 | 28 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| T2_IDX | 100K| 292K| 55 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID_T1")
3 - access("T1"."COL1"=1)
Note
-----
- this is an adaptive plan
-- plan with adaptive parts
select * from table(dbms_xplan.display(format=>'+adaptive'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1632433607
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 84 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| * 2 | HASH JOIN | | 100K| 1074K| 84 (2)| 00:00:01 |
|- 3 | NESTED LOOPS | | 100K| 1074K| 84 (2)| 00:00:01 |
|- 4 | STATISTICS COLLECTOR | | | | | |
| * 5 | INDEX RANGE SCAN | T1_ID_IDX | 10000 | 80000 | 28 (0)| 00:00:01 |
|- * 6 | INDEX RANGE SCAN | T2_IDX | 10 | 30 | 55 (0)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | T2_IDX | 100K| 292K| 55 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID_T1")
5 - access("T1"."COL1"=1)
6 - access("T1"."ID"="T2"."ID_T1")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Der Plan ist explizit als "adaptive plan" gekennzeichnet und enthält den HASH JOIN, mit dem bei der von den Statistiken beschriebenen Datenverteilung zu rechnen ist. Dass diese Datenverteilung nach dem Update nicht mehr vorliegt, kann der Optimizer zu diesem Zeitpunkt nicht wissen, weil er im Rahmen von Explain Plan keinen Blick auf die Daten wirft. Die mit dem Format '+adaptive' erzeugte Version des Plans zeigt die Subplan-Varianten: in diesem Fall die Möglichkeit, den Hash Join durch einen NL Join zu ersetzen, wobei die inaktiven Teilen durch das führende '-' gekennzeichnet sind. Besonders lesbar ist dieser gemischte Plan nicht, aber die parallele Darstellung solcher Varianten ist auch keine dankbare Aufgabe, wie diverse Diff-Tools bestätigen können.
Nun zur tatsächlichen Ausführung der Query:
select sum(t1.col1) sum_col3
from t1
, t2
where t1.id = t2.id_t1
and t1.col1 = 1;
SUM_COL3
----------
1000
select * from table( dbms_xplan.display_cursor('0g3mdd8b1pstt', 0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0g3mdd8b1pstt, child number 0
-------------------------------------
select sum(t1.col1) sum_col3 from t1 , t2 where t1.id =
t2.id_t1 and t1.col1 = 1
Plan hash value: 1261696607
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 84 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | NESTED LOOPS | | 100K| 1074K| 84 (2)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| T1_ID_IDX | 10000 | 80000 | 28 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| T2_IDX | 10 | 30 | 55 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."COL1"=1)
4 - access("T1"."ID"="T2"."ID_T1")
Note
-----
- this is an adaptive plan
Die tatsächliche Ausführung verwendet also den NL Join, wobei der Plan weiterhin die Cardinalities anzeigt, die aus den Tabellen- und Spalten-Statistiken des Dictionary ermittelt wurden. Dabei sind die Cost-Angaben die des HASH JOIN Planes: wenn man den ausgeführten Plan mit einem USE_NL Hint erzwingt, ergeben sich sehr viel höhere Cost-Werte, was wiederum keine Überraschung, sondern ganz folgerichtig ist.
explain plan for
select /*+ use_nl(t1 t2) */
sum(t1.col1) sum_col3
from t1
, t2
where t1.id = t2.id_t1
and t1.col1 = 1;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1261696607
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 10081 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | NESTED LOOPS | | 100K| 1074K| 10081 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| T1_ID_IDX | 10000 | 80000 | 28 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| T2_IDX | 10 | 30 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."COL1"=1)
4 - access("T1"."ID"="T2"."ID_T1")
Letztlich steht alles, was ich hier ausführe, auch schon in Maria Colgans White Paper, und es zeigt sich wieder einmal, dass es Texte gibt, die eine gründliche Lektüre verdienen statt eines kursorischen Überfliegens. Hier noch einmal eine Zusammenfassung der Schritte:
- im Rahmen der Optimization entwickelt der CBO einen Plan mit adaptiven Komponenten, über deren Verwendung er erst zum Zeitpunkt der ersten Ausführung eine Entscheidung treffen will.
- während der ersten Ausführung prüft der statistics collector die tatsächliche Größe der Input-Mengen und trifft dann die endgültige Entscheidung für eine der im ursprünglichen Plan vorgesehenen potentiellen Varianten. Nachdem die Entscheidung getroffen ist, wird das Attribut IS_RESOLVED_ADAPTIVE_PLAN in v$sql auf 'Y' gesetzt (so dass ein Wert 'N' nur während der ersten Ausführung auftreten kann, wie man im White Paper erfährt)
Nützlich sind Adaptive Plans somit in Fällen, in denen die initialen Cardinality Schätzungen des CBO (aus welchen Gründen auch immer) nicht viel taugen. Für Fälle, in denen sich die Daten darüber hinaus rasch ändern, ist weiterhin dynamic sampling relevant (das in 12c das neue Label "dynamic statistics" bekommen hat).
Nachtrag 26.08.2013: ziemlich zeitgleich mit meiner Untersuchung hat Frau Colgan noch einen Artikel What's new in 12c: Adaptive joins veröffentlicht, der offenbar den Auftakt zu einer Serie zum Thema darstellt - und weitere wichtige Punkte anspricht (etwa den - im CBO Trace sichtbaren - 'inflection point', der definiert, wann eine Änderung der Join-Strategie aufgrund der Größe der ersten Input-Menge sinnvoll wird; Iordan Iotzow hat allerdings schon vor einigen Wochen darauf hingewiesen, dass die Statistiken hier immer noch relevant sind, nämlich bei der Größenbestimmung der zweiten Menge).
Nachtrag 25.04.2014: noch ein Artikel von Iordan Iotzow zum Thema: diesmal stellt er die Frage, wann adaptive Pläne verwendet werden und kommt zum Ergebnis:
Nachtrag 25.04.2014: noch ein Artikel von Iordan Iotzow zum Thema: diesmal stellt er die Frage, wann adaptive Pläne verwendet werden und kommt zum Ergebnis:
- wenn eine Menge (laut Statistiken) erheblich kleiner ist als die andere und passende Indizes vorliegen, wählt der Optimizer einen NL Join und spart sich den Aufwand, einen adaptive plan zu erzeugen.
- wenn beide Mengen eine ähnliche Größe haben (für geeignete Werte von ähnlich), dann wählt der Optimizer den HJ.
- wenn die eine Menge "slightly smaller" als die andere, dann wird ein adaptive plan erzeugt - wobei "slightly smaller" in der illustrierenden Grafik deutlich kleiner ist.
Die Aussage ist also recht allgemein, dafür aber vermutlich zutreffend.
Nachtrag 29.07.2014: mit Release 12.1.0.2 ist auch der SQL Monitor dazu in der Lage, adaptive plans korrekt anzuzeigen, wie man bei Franck Pachot nachlesen kann.
Nachtrag 30.07.2014: noch mal Iordan Iotzow mit Überlegungen zum Inflection-Point. Im Titel erscheint ein geklammertes "part 1", so dass von Fortsetzungen auszugehen ist.
Nachtrag 29.07.2014: mit Release 12.1.0.2 ist auch der SQL Monitor dazu in der Lage, adaptive plans korrekt anzuzeigen, wie man bei Franck Pachot nachlesen kann.
Nachtrag 30.07.2014: noch mal Iordan Iotzow mit Überlegungen zum Inflection-Point. Im Titel erscheint ein geklammertes "part 1", so dass von Fortsetzungen auszugehen ist.
Keine Kommentare:
Kommentar veröffentlichen