Podczas estymacji kosztu planu wykonania zapytania, kosztowy optymalizator zapytań musi m.in. oszacować liczby rekordów (tzw. kardynalność), które będą przetwarzane przez zapytanie w różnych fazach jego realizacji. Od precyzji tych szacunków zależą decyzje dotyczące np. kolejności łączenia tabel czy też wyboru algorytmu łączenia tabel. Szacunki kardynalności są dokonywane przede wszystkim na podstawie dostępnych statystyk dla tabel i ich kolumn. Nie zawsze jednak optymalizator zachowuje sie nieomylnie, zwłaszcza gdy w zapytaniu występują złożone predykaty lub gdy dostępne statystyki są niedokładne lub nieaktualne.
W celu zapobiegania problemom wynikającym z błędów oszacowań kardynalności, Oracle Database 11g Release 2 wprowadza mechanizm Cardinality Feedback – sprzężenia zwrotnego dostarczającego „po fakcie” optymalizatorowi zapytań informacje o faktycznej kardynalności na różnych etapach realizacji zapytania. Dzięki temu optymalizator jest w stanie poprawić wygenerowany wcześniej plan wykonania zapytania, jeśli okaże się, że oczekiwane kardynalności nie pokrywały się z faktycznymi zaobserwowanymi w czasie realizacji zapytania. Jest to rozwiązanie podobne do Automatic SQL Tuning, tyle że stosowane na bieżąco.
Uwaga: Cardinality Feedback wymaga ustawienia parametru „statistics_level” na „all”!
Przyjrzyjmy się następującemu przykładowi funkcjonowania Cardinality Feedback. Treść zapytania zapożyczyłem z artykułu [https://blogs.oracle.com/optimizer/entry/cardinality_feedback]. W ramach eksperymentu pokażemy, jak plan wykonania zapytania automatycznie zmienia się po pierwszym wykonaniu zapytania, gdy tylko optymalizator dowie się, jakie błędy oszacowań popełnił.
Poniżej przedstawiam faktyczny plan wykonania przykładowego zapytania SQL _za pierwszym razem_:
SQL> connect oe/oe
SQL> alter system flush shared_pool;
SQL> alter session set statistics_level=all;
SQL> select product_name
2 from order_items o, product_information p
3 where o.unit_price = 15 and quantity > 1
4 and p.product_id = o.product_id;
...
SQL> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
...
------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 13 |
| 1 | NESTED LOOPS | | | 13 |
| 2 | NESTED LOOPS | | 4 | 13 |
|* 3 | TABLE ACCESS FULL | ORDER_ITEMS | 4 | 13 |
|* 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | 13 |
| 5 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 13 |
------------------------------------------------------------------------------
Zauważmy, że znaleziony przez optymalizator plan wykonania obejmuje połączenie tabeli ORDER_ITEMS z indeksem PRODUCT_INFORMATION_PK z użyciem algorytmu NESTED LOOPS, a następnie połączenie wyniku z tabelą PRODUCT_INFORMATION za pomocą ponownego wykonania algorytmu NESTED LOOPS. Do takich wyborów optymalizator skłonił się w wyniku m.in. oszacowań kardynalności, widocznych w powyższej tabelce w kolumnie E-Rows (Estimated Number of Rows). Z kolei kolumna A-Rows (Actual Number of Rows) obrazuje rzeczywiste kardynalności, zebrane już w trakcie wykonywania zapytania (po optymalizacji). Jak widać, wartości te znacząco się różnią – np. optymalizator spodziewał się tylko czterech rekordów z tabeli ORDER_ITEMS spełniających warunek UNIT_PRICE=15, podczas gdy okazało się później, że takich rekordów było jednak 13.
Ciekawie będzie wyglądać drugie uruchomienie tego samego zapytania i ponowna obserwacja planu jego wykonania:
SQL> select product_name
2 from order_items o, product_information p
3 where o.unit_price = 15 and quantity > 1
4 and p.product_id = o.product_id;
...
SQL> select * from table( dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
...
-----------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 13 |
|* 1 | HASH JOIN | | 13 | 13 |
|* 2 | TABLE ACCESS FULL| ORDER_ITEMS | 13 | 13 |
| 3 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 288 | 288 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
2 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
Note
-----
- cardinality feedback used for this statement
Plan wykonania się zmienił! Tym razem optymalizator postanowił połączyć tabele ORDER_ITEMS i PRODUCT_INFORMATION algorytmem HASH_JOIN. A co ważniejsze, tym razem trafnie oszacował kardynalności (poznał je podczas poprzedniego wykonania tego samego zapytania). O zastosowaniu techniki Cardinality Feedback świadczy też notka w opisie planu wykonania („cardinality feedback used for this statement”). Obserwacja użycia Cardinality Feedback jest też możliwa za pomocą kolumny USE_FEEDBACK_STATS perspektywy V$SQL_SHARED_CURSOR (występuje od wersji 11.2.0.2!).
„Odkryte” kardynalności nie są trwale odnotowywane w bazie danych, w związku z czym po wyczyszczeniu bufora Library Cache cała zabawa rozpoczyna się od początku. Jeśli komuś bardzo, bardzo zależy na utrwaleniu faktycznych kardynalności, można poeksperymentować z niejawnym hintem CARDINALITY…