Zajmowaliśmy się już kiedyś ciekawą kwestą korygowania nieoptymalnego planu wykonania z wykorzystaniem Cardinality Feedback (Jak optymalizator uczy się na błędach). Pokazaliśmy, że po pierwszym wykonaniu nieoptymalnego planu wykonania optymalizator potrafi go zmodyfikować tak, aby drugie wykonanie przebiegło już bardziej efektywnie. Niestety, ten mechanizm serwera potrafi być skuteczny dopiero przy drugim wywołaniu tego samego zapytania. Pierwsze wywołanie trzeba spisać na straty…? Niekoniecznie…
Interesujące rozwiązanie zostało wprowadzone w Oracle Database 12.1 pod nazwą Adaptive Plans. Polega ono na opracowywaniu przez optymalizator wielowariantowych planów wykonania, w których decyzja wyboru konkretnego wariantu zapada dopiero podczas wykonywania zapytania. Dzięki temu, optymalizator może “na gorąco” naprawić skutki swoich nieprecyzyjnych szacunków kardynalności/selektywności.
Przyjrzyjmy się działaniu adaptatywnych planów wykonania. Na początek posłużę się zapytaniem z podobnego przykładu Toma Kyte’a. Będzie to zapytanie do tabel przykładowych schematu OE. Przeanalizujmy jego plan wykonania, przyglądając się wariantom adaptatywnym (parametr format=>’+adaptive’ w DISPLAY_CURSOR).
SQL> alter session set statistics_level=all;
...
SQL> alter system flush shared_pool;
...
SQL> select product_name
2 from oe.order_items o, oe.product_information p
3 where o.unit_price = 15
4 and o.quantity > 1
5 and p.product_id= o.product_id;
...
SQL> select * from table(dbms_xplan.display_cursor(format=>'+adaptive, allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 13 |
| * 1 | HASH JOIN | | 4 | 13 |
|- 2 | NESTED LOOPS | | | 13 |
|- 3 | NESTED LOOPS | | 4 | 13 |
|- 4 | STATISTICS COLLECTOR | | | 13 |
| * 5 | TABLE ACCESS FULL | ORDER_ITEMS | 4 | 13 |
|- * 6 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | 0 |
|- 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 0 |
| 8 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 288 |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Sposób zapisu wielowariantowego planu adaptatywnego może budzić zdziwienie. W naszym zapytaniu występuje przecież tylko jedno łączenie tabel, a w planie widzimy dwa operatory połączenia – HASH JOIN i NESTED LOOPS! Zwróćmy jednak uwagę na znaki minus w pierwszej kolumnie planu – oznaczają one, że te kroki planu zostały pominięte w wykonaniu. A zatem nasze zapytanie rzeczywiście zostało wykonane według planu HASH JOIN-TABLE ACCESS FULL-TABLE ACCESS FULL. Rozszyfrujmy jednak zapis wielowariantowego planu adaptatywnego. Jego ważnym krokiem jest STATISTICS COLLECTOR – jest to działanie weryfikujące rzeczywistą kardynalność wyniku pośredniego. Jeśli okaże się, że odbiega ona znacząco od prognozowanej, następuje wybór innego wariantu planu wykonania. Optymalizator spodziewał się z tabeli ORDER_ITEMS odczytać cztery rekordy (E-Rows) – gdyby to było prawdą, to wykonałby wariant NESTED LOOPS-TABLE ACCESS FULL-INDEX UNIQUE SCAN-TABLE ACCESS BY INDEX ROWID. Jednak w naszym przypadku otrzymał 13 rekordów (A-Rows), co spowodowało przełączenie na wariant alternatywny HASH JOIN-TABLE ACCESS FULL-TABLE ACCESS FULL.
A jak wyglądałby wybór wariantu gdyby prognozowana kardynalnośc okazała się trafna? Proszę bardzo:
SQL> select * from table(dbms_xplan.display_cursor(format=>'+adaptive, allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 |
|- * 1 | HASH JOIN | | 1 | 1 |
| 2 | NESTED LOOPS | | | 1 |
| 3 | NESTED LOOPS | | 1 | 1 |
|- 4 | STATISTICS COLLECTOR | | | 1 |
| * 5 | TABLE ACCESS FULL | ORDER_ITEMS | 1 | 1 |
| * 6 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 1 |
|- 8 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 0 |
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">150))
6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Tym razem wyłączone zostały kroki HASH JOIN-TABLE ACCESS FULL, więc w rezultacie wykonany został wariant NESTED LOOPS-TABLE ACCESS FULL-INDEX UNIQUE SCAN-TABLE ACCESS BY INDEX ROWID.
Reasumując, nasz wygenerowany wielowariantowy plan adaptatywny można ująć w następujących punktach:
1. Wykonaj TABLE ACCESS FULL na ORDER_ITEMS
2. Sprawdź kardynalność wyniku pośredniego (STATISTICS COLLECTOR)
3. Jeżeli kardynalność jest zgodna z prognozowaną, to:
a. wykonaj połączenie metodą NESTED LOOPS (INDEX UNIQUE SCAN/TABLE ACCESS BY INDEX ROWID na PRODUCT_INFORMATION)
w przeciwnym razie:
a. wykonaj połączenie metodą HASH JOIN (TABLE ACCESS FULL na PRODUCT_INFORMATION)
Pamiętajmy tylko, że cały ten mechanizm służy wyłącznie optymalnemu pierwszemu wykonaniu zapytania. W kolejnych uruchomieniach nie ma już niespodzianek, stosowany jest znany wcześniej Cardinality Feedback.
Aktualizacja: zainteresowanym „matematycznym” kontekstem powyższych rozwiązań proponuję zerknięcie na publikację „Adaptive Statistics in Oracle 12c” z konferencji VLDB 2017: link.