Die Anfänge dieses Features gehen auf Oracle 11gR2 zurück, wo die Funktionalität Cardinality Feedback genannt wurde. Bei der ersten Ausführung eines SQL Statements mit einem Execution Plan wird am Ende der Ausführung überprüft, ob der CBO bei seinen Schätzungen recht gehabt hat. Wenn dies signifikant nicht der Fall war, werden die während der Ausführung festgestellten Cardinalitäten (Statistiken) beim Statement hinterlegt. Bei der nächsten Ausführung des Statements wird der CBO basierend auf den neuen Statistik Informationen den Ausführungsplan anpassen.
Diese Information kann man in der Spalte USE_FEEDBACK_STATS in der View V$SQL_SHARED_CURSOR feststellen.
Basierend auf dieser Funktionalität wurde mit Oracle 12cR1 der nächste Schritt, die Automatische Reoptimization – allerdings nur für Oracle Enterprise Edition – als Teil der Adaptiven Optimizer Funktionalitäten eingeführt.
Ob ein Statement reoptimierbar ist, kann man in der Spalte IS_REOPTIMIZABLE in V$SQL feststellen. In V$SQL_MONITOR – hier benötigt man neben der Enterprise Edition auch noch das Diagnostic Pack – findet man mit IS_ADAPTIVE_PLAN ob der Ausführungsplan ein Adaptiver Plan ist.
Wie viele Oracle Datenbank Nutzer festgestellt haben, sind die Adaptiven Optimizer Funktionen in Oracle 12c R1 oft problematisch, beginnen mit Oracle 12c R2 und 18c wurde hier einiges verbessert, so dass diese Funktionalität ab Oracle 19c in den meisten Fällen gut funktioniert. Leider sind einige der Funktionalitäten in der Standard Edition leider eingeschränkt (zb: SQL Plan Management unterstützt bei SE2 nur einen Execution Plan pro SQL_ID, SQL Plan Directives werden von SE2 nicht unterstützt).
Oracle Statistics Feedback in der Praxis
Vielen Beispiele zu dem Thema werden mit künstlich optimierten Tabellen/Indizes gezeigt. Dieses SQL Statement ist von einer Real-Live Applikation. Mit Hilfe von selbstgeschriebenen SQL Scripts bereiten wir Informationen aus V$SQL auf und nutzen DBMS_XPLAN.DISPLAY_CURSOR um die Ausführungspläne anzuzeigen.
Wenn man ein Statement mehrfach ausführt, kann es aus verschiedenen Gründen mehrere CHILD_NUMBERn haben. Einer der Gründe ist Laufzeit Statistik Feedback, der dann meist zu einem anderen Ausführungsplan (PLAN_HASH_VALUE) führt.
SYS> @sqlPerfInfo c5p6qqqt566z7
SQL_TEXT
---------------------------------------------------------------------
select * from ( select METERID, SERIALID, METERINGPOINTID,
TECHNOLOGY, BSL, FIRMWARE, STATUS, TRAFOID, GATEWAYID,
LASTSTATUSCHANGE, SEQFAILURE, WOID, WOCREATIONTIME, WOSTATUS
from ( select M.METERID, M.SERIALID, MA.METERINGPOINTID,
M.TECHNOLOGY, NVL(M.BSL, 'N/A') as BSL, ( select
NVL(listagg(FWTYPE || ' ' || FWVERSION, ' - ') WITHIN GROUP (order by
FWTYPE), 'N/A') as firmware from METERFK
where METERID = M.METERID and upper(fwstate) =
upper('Active') ) as FIRMWARE, M.STATUS, M.TRAFOID,
M.GATEWAYID, M.LASTSTATUSCHANGE, S.SEQFAILURE, WOID,
WOCREATIONTIME, WOSTATUS from METERS M JOIN
SEQFAILURE S on M.METERID = S.METERID and S.KPI = 'IME' and
S.SEQFAILURE > 0 LEFT JOIN ( SELECT METERID,
SMTYPE, LISTAGG(METERINGPOINTID, ', ') WITHIN GROUP (ORDER BY
METERINGPOINTID) "METERINGPOINTID" FROM METERANLAGE
...
Laufzeitinformation
CHILD# PLAN_HASH_VALUE EXECUTIONS ELAP_S CPU_S DSK_E BGETS_E ROWS_E
------ --------------- ---------- ------- ------- ------ -------- -------
0 2232001671 1 2.3074 1.1478 36710 41750 607
1 1703873321 1 .5175 .4276 3903 44520 607
Statement Waits (es gibt nur I/O Waits)
CHILD# PLAN_HASH_VALUE EXECUTIONS IOW_S APPW_S CONCW_S CLUW_S PLSQLX_S
------ --------------- ---------- ------- ------- -------- ------- ---------
0 2232001671 1 1.3148 .0000 .0000 .0000 .0000
1 1703873321 1 .0980 .0000 .0000 .0000 .0000Hinweis: sqlPerfInfo.sql ist ein Script, dass zuerst den SQL_TEXT und dann weitere Informationen aus V$SQL anzeigt.
- Bei der Laufzeitinformation sieht man, dass beim Ergebnis 607 Zeilen zurück kommen.
- Beim Ausführungsplan 2232001671 werden 41.750 Buffer Gets (= logische Blockzugriffe benötigt) und 1.15 CPU Sekunden benötigt. Die Laufzeit ist auf Grund von mehr phyischen I/Os deutlich höher, weil die benötigten Daten nicht im Buffer Cache vorhanden waren – das ist aber für die Analyse nicht relevant.
- Beim Ausführungsplan 1703873321 werden mit 44.520 Buffer Gets deutlich mehr logische Blockzugriffe durchgeführt, allerdings mit deutlich geringerem CPU Aufwand von 0.43 Sekunden.
- Bei den Statement Waits sehen wir, dass lediglich I/O Waits gibt. Das ist aber für das Statistik Feedback nicht relevant.
Ausführungspläne verifizieren
Als nächsten schauen wir uns die beiden verschiedenen Ausführungspläne mittels DBMS_XPLAN.DISPLAY_CURSOR an.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('c5p6qqqt566z7',null));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID c5p6qqqt566z7, child number 0
-------------------------------------
select * from ( select METERID, SERIALID, METERINGPOINTID,
...
Plan hash value: 2232001671
----------------------------------------------------------------------------
| Id |Operation |Name |Rows |Bytes |
----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | |
| 1| SORT GROUP BY | | 1| 44 |
| 2| TABLE ACCESS BY INDEX ROWID BATCHED |METERFK | 1| 44 |
|* 3| INDEX RANGE SCAN |METERFK_IDX| 1| |
|* 4| COUNT STOPKEY | | | |
| 5| VIEW | | 11| 116K|
|* 6| SORT GROUP BY STOPKEY | | 11| 109K|
| 7| VIEW | | 11| 109K|
| 8| NESTED LOOPS OUTER | | 11|89529 |
| 9| NESTED LOOPS OUTER | | 11|23441 |
| 10| NESTED LOOPS | | 11| 1397 |
|* 11| TABLE ACCESS FULL |SEQFAILURE | 12| 324 |
|* 12| TABLE ACCESS BY INDEX ROWID |METERS | 1| 100 |
|* 13| INDEX UNIQUE SCAN |METERS_PK | 1| |
| 14| VIEW PUSHED PREDICATE | | 1| 2004 |
| 15| SORT GROUP BY | | 1| 49 |
| 16| TABLE ACCESS BY INDEX ROWID BATCHED|METERANLAGE| 1| 49 |
|* 17| INDEX RANGE SCAN |DBM_IDX2 | 1| |
| 18| VIEW PUSHED PREDICATE | | 1| 6008 |
| 19| SORT GROUP BY | | 1| 37 |
| 20| TABLE ACCESS BY INDEX ROWID BATCHED |WORKORDERS | 1| 37 |
|* 21| INDEX RANGE SCAN |WORKO_IDX3 | 1| |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("METERID"=:B1)
filter(UPPER("FWSTATE")='ACTIVE')
4 - filter(ROWNUM<=:1)
6 - filter(ROWNUM<=:1)
11 - filter(("S"."KPI"='IME' AND "S"."SEQFAILURE">0 AND
TO_CHAR(INTERNAL_FUNCTION("S"."EXECUTEDAT"),'DD-MM-YYYY')=
CASE WHEN ('16-05-2023' IS NULL) THEN
TO_CHAR(SYSDATE@!,'DD-MM-YYYY') ELSE '16-05-2023' END
AND "S"."BATCH"=CASE WHEN ('1' IS NULL) THEN CASE WHEN
(EXTRACT(HOUR FROM CURRENT_TIMESTAMP(6))>=12) THEN '2'
ELSE '1' END ELSE '1' END AND CASE WHEN
"S"."SEQFAILURE">=60 THEN '60+' WHEN ("S"."SEQFAILURE">=51
AND "S"."SEQFAILURE"<=59) THEN '51-59' WHEN
("S"."SEQFAILURE">=29 AND "S"."SEQFAILURE"<=50) THEN '29-50'
WHEN ("S"."SEQFAILURE">=22 AND
"S"."SEQFAILURE"<=28) THEN '22-28' WHEN ("S"."SEQFAILURE">=15
AND "S"."SEQFAILURE"<=21) THEN '15-21' WHEN
("S"."SEQFAILURE">=7 AND "S"."SEQFAILURE"<=14) THEN '7-14'
ELSE TO_CHAR("S"."SEQFAILURE") END ='60+'))
12 - filter("M"."TECHNOLOGY"='PLC')
13 - access("M"."METERID"="S"."METERID")
17 - access("METERID"="M"."METERID")
21 - access("DEVICEID"="M"."METERID")
filter("STATUS"<>'4')
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID c5p6qqqt566z7, child number 1
-------------------------------------
select * from ( select METERID, SERIALID, METERINGPOINTID,
...
Plan hash value: 1703873321
-----------------------------------------------------------------------------
| Id |Operation |Name |Rows |Bytes |
-----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | |
| 1| SORT GROUP BY | | 1| 44 |
| 2| TABLE ACCESS BY INDEX ROWID BATCHED |METERFK | 1| 44 |
|* 3| INDEX RANGE SCAN |METERFK_IDX| 1| |
|* 4| COUNT STOPKEY | | | |
| 5| VIEW | | 625| 6605K|
|* 6| SORT GROUP BY STOPKEY | | 625| 6231K|
| 7| VIEW | | 625| 6231K|
|* 8| HASH JOIN OUTER | | 625| 5045K|
| 9| JOIN FILTER CREATE |:BF0000 | 625| 1300K|
| 10| NESTED LOOPS OUTER | | 625| 1300K|
| 11| NESTED LOOPS | | 625|79375 |
|* 12| TABLE ACCESS FULL |SEQFAILURE | 627|16929 |
|* 13| TABLE ACCESS BY INDEX ROWID |METERS | 1| 100 |
|* 14| INDEX UNIQUE SCAN |METERS_PK | 1| |
| 15| VIEW PUSHED PREDICATE | | 1| 2004 |
| 16| SORT GROUP BY | | 1| 49 |
| 17| TABLE ACCESS BY INDEX ROWID BATCHED|METERANLAGE| 1| 49 |
|* 18| INDEX RANGE SCAN |DBM_IDX2 | 1| |
| 19| VIEW | |98156| 574M|
| 20| SORT GROUP BY | |98156| 3546K|
| 21| JOIN FILTER USE |:BF0000 |99636| 3600K|
|* 22| TABLE ACCESS FULL |WORKORDERS |99636| 3600K|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("METERID"=:B1)
filter(UPPER("FWSTATE")='ACTIVE')
4 - filter(ROWNUM<=:1)
6 - filter(ROWNUM<=:1)
8 - access("M"."METERID"="WO"."DEVICEID")
12 - filter(("S"."KPI"='IME' AND "S"."SEQFAILURE">0 AND
TO_CHAR(INTERNAL_FUNCTION("S"."EXECUTEDAT"),'DD-MM-YYYY')=CASE
WHEN ('16-05-2023' IS NULL) THEN TO_CHAR(SYSDATE@!,'DD-MM-YYYY')
ELSE '16-05-2023' END AND "S"."BATCH"=CASE WHEN ('1'
IS NULL) THEN CASE WHEN
(EXTRACT(HOUR FROM CURRENT_TIMESTAMP(6))>=12)
THEN '2' ELSE '1' END ELSE '1' END AND CASE
WHEN "S"."SEQFAILURE">=60 THEN '60+' WHEN ("S"."SEQFAILURE">=51
AND "S"."SEQFAILURE"<=59) THEN '51-59' WHEN
("S"."SEQFAILURE">=29 AND "S"."SEQFAILURE"<=50) THEN '29-50'
WHEN ("S"."SEQFAILURE">=22 AND "S"."SEQFAILURE"<=28) THEN
'22-28' WHEN ("S"."SEQFAILURE">=15 AND "S"."SEQFAILURE"<=21)
THEN '15-21' WHEN ("S"."SEQFAILURE">=7 AND
"S"."SEQFAILURE"<=14) THEN '7-14' ELSE TO_CHAR("S"."SEQFAILURE")
END ='60+'))
13 - filter("M"."TECHNOLOGY"='PLC')
14 - access("M"."METERID"="S"."METERID")
18 - access("METERID"="M"."METERID")
22 - filter(("STATUS"<>'4' AND SYS_OP_BLOOM_FILTER(:BF0000,"DEVICEID")))
Note
-----
- statistics feedback used for this statementWenn man sich beide Ausführungspläne genau anschaut, sind man folgende Unterschiede:
- In den Notes des zweiten Ausführungplans findet man den Hinweis: „statistics feedback used for this statement“
- Im ersten Ausführungsplan geht Oracle von 11 Datensätzen aus, die im Ergebnis zurückkommen sollen. Wir wissen aber, dass es 607 sind. Beim zweiten Ausführungsplan schätzt Oracle mit 625 deutlich korrekter – ein Ergebnis des Statistik Feedbacks.
- Ab der Zeile 8 beginnt die Unterschiede, die sich letztendlich auf folgende wichtige Aspekte drehen
- Im Zugriff auf die Datenstrukturen wird auf die WORKORDERS zuerst mittels Index zugegriffen und danach mittels Full Table Scan.
- Der wichtigste Unterschied ist aber, dass beim zweite Execution Plan ein Bloom Filter zum Einsatz kommt.
Zusammenfassung
Spätestens mit Oracle 19c funktioniert das Statistik Feedback im Oracle CBO in den meisten Fällen sehr gut, allerdings müssen die Statements auch mehrfach ausgeführt werden, damit das Laufzeit Feedback zu einem verbesserten Ausführungsplan führen kann.
In diesem Beispiel hat sich die CPU Zeit des Statement von 1.14 Sekunden auf 0.43 Sekunden (Faktur 2,65) verbessert. Da das Statement in der Praxis oft zum Einsatz kommt, ist das eine merkliche Entlastung der CPU.
Referenzen
- Oracle Blog zu Statistics Feedback (Formerly Cardinality Feedback)
- Automatic Reoptimization in Oracle Database 12c Release 1 (12.1)