Adaptive Cursor Sharing
Tom Kyte, autor wielu książek o tematyce związanej z bazami danych Oracle, na swoim blogu opisał swego czasu historię wpływu opadów atmosferycznych na szybkość wykonywania zapytań SQL w bazie Oracle. Artykuł ten nie jest już niestety dostępny, ale postaram się samodzielnie opisać tę dziwną historię.
Ale o co chodzi? I co w tym wszystkim mają zmienne bindowane?
Baza danych o której mowa, to baza kopiowana przy użyciu tzw. „zimnych kopii zapasowych”. Każdej niedzieli w nocy baza wyłączana była w celu wykonania kopii plików, następnie w godzinach porannych, ponownie uruchamiana. Oznaczało to, że w każdy poniedziałek rano wszystkie bufory byłyby praktycznie puste. Był to więc okres dużych hard parsów.
Głównym użytkownikiem aplikacji korzystającej z bazy była osoba, która przyjeżdżała do biura zwykle o 6 rano. Podczas pracy używała selektywnych zapytań, które zwracały bardzo małą liczbę wierszy. W takim wypadku optymalizator miał tendencję do używania indeksów. Kiedy tak się działo – wszyscy późniejsi użytkownicy byli zadowoleni.
Ludzie, pracujący w aplikacji, którzy zadawali zapytania mniej selektywne – mieszkali po drugiej stronie ulicy, niedaleko od biura. Sytuacja pogodowa nie miała wpływu na porę przybycia do pracy, tak więc zawsze pojawiali się o 9 rano. Jeśli padał deszcz byli pierwszymi użytkownikami bazy i wykonując przekrojowe, mało selektywne zapytania, dokonywali pełnego skanowania tabel i tworzyli w ten sposób mało efektywny plan wykonania zapytań.
Gdy osoba, która z powodu deszczu jechała długo przez miasto, kiedy docierała do pracy musiała pogodzić się z powolną pracą aplikacji. W krytycznych przypadkach wykonywała telefon do administratora mówiąc „Jest poniedziałek, pada deszcz, wiesz, co robić…„. Administrator restartował bazę, po czym wszystko działo dużo szybciej i stan taki utrzymywał się przez resztę tygodnia.
Nieco historii
We wcześniejszych niż Oracle 11g wersjach bazy, przy każdorazowym uruchomieniu zapytania SQL, wykorzystywany był ten sam plan wykonania, niezależnie od wartości zmiennych bindowanych. W praktyce wyglądało to tak, że pierwsze zapytanie powodowało ustalenie planu przez optymalizator, który realizowany był konsekwentnie przy kolejnych uruchomieniach tego zapytania. Sytuacja taka miała miejsce nawet wtedy, gdy zapytanie to różniło się wartościami zmiennych bindowanych.
Ilustruje to przykład:
Utwórzmy tabelę z kluczem głównym, oraz przeliczmy jej statystyki:
CREATE TABLE t AS SELECT rownum AS id, rpad('*',100,'*') AS pad
FROM dual
CONNECT BY level <= 1000;
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
BEGIN
dbms_stats.gather_table_stats(
ownname => user,
tabname => 't',
estimate_percent => 100,
method_opt => 'for all columns size 1'
);
END;
/
Kilka pierwszych rekordów tabeli:
Wykonajmy teraz kilka instrukcji SELECT z różnymi wartościami zmiennej ID, oraz przyjrzyjmy się ich planom zapytań:
VARIABLE id NUMBER
EXECUTE :id := 10;
select count(pad) from t where id < :id;
COUNT(PAD)
----------
9
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID | T | 9 |
| 3 | INDEX RANGE SCAN | T_PK | 9 |
EXECUTE :id := 990;
select count(pad) from t where id < :id;
COUNT(PAD)
----------
989
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID | T | 9 |
| 3 | INDEX RANGE SCAN | T_PK | 9 |Najważniejszym aspektem jest fakt, że dopóki kursor pozostanie w pamięci podręcznej biblioteki i może być udostępniony, zostanie on ponownie użyty. Dzieje się tak niezależnie, czy ponowne użycie planu w kontekście użycia zupełnie innych zmiennych jest optymalny czy nie.
Począwszy od wersji Oracle 11g wprowadzono technologię „Adaptive Cursor Sharing”, powodującą wygenerowanie różnych planów wykonania dla tego samego zapytania w zależności od podanych wartości zmiennych.
Optymalizator odczytuje je w chwili pierwszego wywołania kursora. Określenia jest wtedy „selektywność” zapytania a dokładnie klauzuli WHERE.
Gdy rozkład danych w kolumnach wymienionych w klauzuli WHERE jest jednostajny, wszystkie zapytania mogą korzystać z tego samego planu, jednak jeżeli rozkład danych jest typu „skewed” (nie wiem jak to przetłumaczyć, wypaczone?, skośne?), wtedy plan wykonania wybrany przez optymalizator podczas odczytania wartości zmiennej bindowanej nie musi być odpowiedni dla wszystkich możliwych wartości tych zmiennych.
Może być tak, że plan wykonania będzie właściwy dla wartości zmiennej odczytanej przez optymalizator, a zupełnie nieefektywny dla wszystkich innych wartości.
Kiedy mówimy, że kolumny są „skośne„?
O kolumnach „skośnych” możemy mówić wtedy, gdy dane w nich zawarte są nierównomiernie rozmieszczone. Przykładowo:
Mamy tabelę orders zawierającą 100 mln wierszy. Tabela ta zawiera kolumnę o nazwie customer_id. Posiadamy milion różnych klientów, przy czym przeciętny klient składa zazwyczaj kilka zamówień, podczas gdy niektórzy (np. hurtownicy), mogą ich złożyć nawet kilkaset tysięcy.
W powyższym przykładzie dane w orders.customer_id są wypaczone. Można oczekiwać, że średnio każdy odrębny customer_id będzie posiadał 100 wierszy zamówienia (100 milionów wierszy podzielonych przez 1 milion różnych klientów). W praktyce okazuje się, że niektórzy klienci mają tylko kilka rekordów zamówień, podczas gdy inni dużo, dużo więcej.
Sytuacja taka jest nieoptymalna pod względem wydajności, ponieważ optymalizator opiera swój plan wykonania na statystykach. Baza uważa, że operacja SELECT powinna uzyskać dostęp do order_lines na podstawie nieunikalnego indeksu na customer_id i statystycznie rzecz biorąc odczytać 100 rekordów dla każdego z klientów. Rekordy te może następnie dołączyć do innej tabeli za pomocą operacji NESTED LOOP.
Ale, gdy faktycznie otrzyma milion wierszy zamówienia dla konkretnego klienta, dostęp do indeksu i łączenie zagnieżdżonej pętli działać będzie w sposób bardzo nieoptymalny. O wiele lepiej byłoby, gdyby optymalizator podjął decyzję o pełnym skanowaniu pierwszej tabeli i dołączył hash do tabeli drugiej.
Wynika z tego, że gdy dane są „skwed”, optymalny plan dostępu zależy od tego, którego konkretnego klienta wybieramy!
Baza danych Oracle pozwala uniknąć tego problemu, gromadząc „histogramy” w kolumnach, dzięki czemu wie, które wartości mają wiele wierszy, a które tylko kilka. W większości przypadków daje to optymalizatorowi informacje potrzebne do wygenerowania najlepszego planu.
Wracając jednak do tematu Adaptive Cursor Sharing
Począwszy od wersji Oracle 11.1, dostępne są następujące nowe kolumny w widoku v$sql:
Is_bind_sensitive wskazuje nie tylko, czy do generowania planu wykonania użyto zmiennych bindowanych, ale także tego, czy można użyć technologii adaptive cursor sharing.
Is_bind_aware wskazuje, czy kursor korzysta z adaptive cursor sharing.
Is_shareable wskazuje, czy kursor można udostępnić. Jeśli ustawisz wartość N, kursor nie będzie już ponownie używany.
Ilustruje to przykład:
EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
SELECT sql_id FROM v$sqlarea
WHERE sql_text = 'SELECT count(pad) FROM t WHERE id < :id';
SQL_ID
-------------
asth1mx10aygn
SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable,
plan_hash_value FROM v$sql
WHERE sql_id = 'asth1mx10aygn';
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE
------------ ----------------- ------------- ------------ ---------------
0 Y N N 4725643561
Ciekawie robi się w momencie, kiedy kursor jest wykonywany kilka razy z różnymi wartościami zmiennej bindowanej. Dwa nowe kursory podrzędne zastąpią go za pomocą technologii adaptive cursor sharing:
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable,
plan_hash_value
FROM v$sql WHERE sql_id = 'asth1mx10aygn'
ORDER BY child_number;
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE
------------ ----------------- ------------- ------------ ---------------
0 Y N N 4725643561
1 Y Y Y 2536785427
2 Y Y Y 4725643561
Patrząc teraz na plany wykonania, jak można było się spodziewać, jedno z nowych „dzieci” zapytania ma plan wykonania na podstawie pełnego skanowania tabeli, podczas gdy drugie jest oparte na skanowaniu indeksowym:
Plan hash value: 4725643561
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID | T |
| 3 | INDEX RANGE SCAN | T_PK |
Plan hash value: 2536785427
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL | T |Adaptive Cursor Sharing jest funkcjonalnością wprowadzoną w wersji bazy Oracle Database 11g. W poprzednich wersjach administratorzy zmuszeni byli do czyszczenia Buffer Cache’a w sytuacji, gdy wydawało im się, że baza zaczyna stosować niewłaściwy plan wykonania zapytań w wyniku odczytywania wartości zmiennych bindowanych. W wersji bazy Oracle 11g lub nowszej, optymalizator automatycznie zmieni plan wykonania, gdy wykryje dane „wypaczone”. Dzięki Adaptive Cursor Sharing baza stosuje wiele planów wykonania zapytania zawierającego zmienne bindowane i zapewnia w ten sposób, że w zależności od wartości zmiennych wybierany jest w miarę możliwości najlepszy plan.
