Execution Plans: jak naprawdę czytać plan wykonania Oracle

Czym jest plan wykonania i skąd się bierze

Plan wykonania to sekwencja operacji, które Oracle zamierza wykonać, aby zwrócić wynik zapytania SQL. Kluczowe słowo to zamierza. Cost Based Optimizer (CBO) analizuje dostępne ścieżki dostępu do danych, metody łączenia tabel oraz kolejność operacji, a następnie wybiera plan o najniższym szacowanym koszcie.

Proces budowania planu opiera się na trzech filarach: statystykach obiektów (tabele, indeksy, kolumny), parametrach systemowych oraz regułach transformacji zapytań. Optymalizator nie wie, jak szybko wykona się zapytanie. Wie jedynie, ile operacji I/O i CPU szacunkowo będzie potrzebnych przy założeniach wynikających ze statystyk.

Statystyki to fundament wszystkiego. Gdy masz tabelę z milionem wierszy, ale statystyki pokazują tysiąc, optymalizator podejmie decyzje odpowiednie dla małej tabeli. Efekt? Nested Loops zamiast Hash Join, pełne skanowanie zamiast użycia indeksu, katastrofalna wydajność.

EXPLAIN PLAN i jego ograniczenia

Najprostszą metodą uzyskania planu jest polecenie EXPLAIN PLAN FOR. Generuje ono plan i zapisuje go w tabeli PLAN_TABLE. Problem polega na tym, że pokazuje plan przewidywany, nie rzeczywisty.

EXPLAIN PLAN nie uwzględnia bind peeking przy pierwszym wykonaniu ze zmiennymi wiązanymi. Nie pokazuje też adaptive cursor sharing ani innych mechanizmów, które mogą zmienić plan w czasie rzeczywistego wykonania. Traktuję go jako narzędzie rozpoznawcze, nie diagnostyczne.

Dodatkowo EXPLAIN PLAN wykonuje tzw. hard parse bez wykonania zapytania. Oznacza to, że nie widzi rzeczywistych wartości bind variables; używa ich typów, ale nie wartości. W środowiskach OLTP z intensywnym użyciem zmiennych wiązanych może to prowadzić do pokazania zupełnie innego planu niż ten faktycznie używany.

DBMS_XPLAN: właściwe narzędzie do analizy

Pakiet DBMS_XPLAN oferuje znacznie bogatsze możliwości. Funkcja DISPLAY pokazuje plan z PLAN_TABLE, ale prawdziwa moc tkwi w DISPLAY_CURSOR, która wyświetla plan faktycznie użyty przez Oracle.

Podstawowe wywołanie to SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')). Parametr ALLSTATS LAST pokazuje statystyki z ostatniego wykonania, co jest kluczowe dla diagnostyki.

Inne przydatne formaty to TYPICAL (domyślny, zrównoważony), ALL (wszystkie dostępne informacje), ADVANCED (włącznie z outline i transformacjami) oraz ADAPTIVE (pokazuje plan adaptacyjny z zaznaczeniem aktywnych i nieaktywnych operacji).

Dla planów historycznych używam DISPLAY_AWR, która sięga do Automatic Workload Repository. To nieocenione, gdy analizujesz problem, który wystąpił w nocy, a teraz zapytanie działa poprawnie.

GATHER_PLAN_STATISTICS: most między teorią a praktyką

Hint GATHER_PLAN_STATISTICS lub parametr STATISTICS_LEVEL ustawiony na ALL powoduje zbieranie rzeczywistych statystyk wykonania. To zmienia wszystko w diagnostyce.

Po wykonaniu zapytania z tym hintem, DBMS_XPLAN.DISPLAY_CURSOR pokazuje dodatkowe kolumny: A-Rows (rzeczywista liczba wierszy), A-Time (rzeczywisty czas), Buffers (odczyty logiczne), Reads (odczyty fizyczne). Porównanie E-Rows (estymacja) z A-Rows natychmiast ujawnia problemy z kardynalnością.

Gdy widzę E-Rows: 1 i A-Rows: 500000, wiem dokładnie gdzie szukać problemu. To nie jest kwestia wydajności operacji; to fundamentalny błąd w statystykach lub predykatach, który zmusza optymalizator do złych decyzji.

Jak czytać plan: od końca do początku

Plan wykonania ma strukturę drzewa. Operacje wcięte głębiej wykonują się jako pierwsze i przekazują wyniki operacjom nadrzędnym. Ale ta zasada ma wyjątki przy operacjach równoległych i niektórych typach joinów.

Praktyczna metoda czytania: zacznij od operacji z największym wcięciem, śledź przepływ danych w górę drzewa. Dla każdej operacji sprawdź kolumnę Rows (lub A-Rows jeśli masz rzeczywiste statystyki). Mnożenie błędów kardynalności przez kolejne poziomy planu daje efekt kuli śnieżnej.

Kolumna Id z gwiazdką oznacza operację z predykatami. Zawsze sprawdzaj sekcję Predicate Information na końcu planu. Access predicates używane są do nawigacji po indeksie. Filter predicates stosowane są po odczycie danych. Różnica jest fundamentalna dla wydajności.

Predykaty: gdzie naprawdę dzieje się filtrowanie

Sekcja Predicate Information to często pomijany fragment planu, a zawiera kluczowe informacje. Predykat access przy Index Range Scan oznacza, że warunek służy do określenia zakresu skanowania indeksu. Predykat filter oznacza, że Oracle najpierw odczytuje dane, a potem je filtruje.

Klasyczny przykład problemu: indeks na kolumnie STATUS, zapytanie WHERE UPPER(STATUS) = 'ACTIVE'. W planie zobaczysz Full Table Scan, bo funkcja na kolumnie uniemożliwia użycie indeksu. Predykat będzie pokazany jako filter, nie access.

Inny częsty przypadek: implicit conversion. Kolumna VARCHAR2, wartość przekazana jako NUMBER. Oracle dodaje TO_NUMBER() do kolumny, indeks staje się bezużyteczny. Plan pokaże to w predykatach, ale trzeba wiedzieć, czego szukać.

Kardynalność: serce optymalizatora

Kardynalność (Rows/E-Rows w planie) to szacowana liczba wierszy zwracanych przez operację. Optymalizator używa jej do wszystkich decyzji: wyboru metody łączenia, kolejności tabel w joinie, użycia indeksu versus pełnego skanowania.

Błędna kardynalność ma efekt kaskadowy. Jeśli Oracle szacuje, że podzapytanie zwróci 10 wierszy, wybierze Nested Loops. Jeśli rzeczywiście zwraca 100000 wierszy, wykonanie będzie katastrofalne, bo dla każdego wiersza wykona osobne odwołanie do drugiej tabeli.

Najczęstsze przyczyny błędnej kardynalności: nieaktualne statystyki, brak histogramów na kolumnach z nierównomiernym rozkładem, korelacje między kolumnami niewidoczne dla optymalizatora, użycie funkcji w predykatach.

Najważniejsze operacje w planach Oracle

TABLE ACCESS FULL nie zawsze oznacza problem. Dla małych tabel lub zapytań zwracających duży procent danych pełne skanowanie jest optymalne. Problem pojawia się, gdy widzisz Full Table Scan na dużej tabeli przy zapytaniu, które powinno zwrócić kilka wierszy.

INDEX RANGE SCAN to najczęstsza operacja indeksowa. Sprawdź kardynalność; jeśli Oracle szacuje tysiąc wierszy, a indeks zwraca milion, masz problem ze statystykami lub selektywnością predykatu.

NESTED LOOPS sprawdza się przy małej liczbie wierszy z driving table. Dla każdego wiersza wykonuje lookup w drugiej tabeli. Przy błędnej kardynalności staje się zabójcą wydajności.

HASH JOIN buduje tabelę haszującą z mniejszego zbioru i przeszukuje ją dla każdego wiersza większego zbioru. Wymaga pamięci; przy niewystarczającym PGA wykonuje operacje na dysku (widoczne jako HASH JOIN z operacjami TEMP).

SORT operations pojawiają się przy ORDER BY, GROUP BY, DISTINCT, niektórych typach joinów. Sprawdź, czy sortowanie nie jest wykonywane na dysku, co dramatycznie spowalnia zapytanie.

Plan wykonania a rzeczywista wydajność

Identyczny plan może wykonywać się sekundę lub godzinę w zależności od danych w cache, obciążenia systemu, równoległości. Plan pokazuje co Oracle robi, nie jak szybko.

SQL Monitor (dostępny przez DBMS_SQLTUNE.REPORT_SQL_MONITOR lub Enterprise Manager) pokazuje rzeczywiste wykonanie w czasie rzeczywistym, włącznie z wait events i rozkładem czasu między operacjami.

SQL Trace z TKPROF daje najdokładniejszy obraz: rzeczywiste czasy, waity, recursive SQL. To narzędzie ostateczne, gdy plan wygląda dobrze, a wydajność jest fatalna.

AWR i ASH pozwalają analizować historię. Gdy użytkownik zgłasza, że zapytanie było wolne wczoraj o 3 w nocy, ASH pokaże, na co czekało, a AWR zachowa plan i statystyki z tamtego okresu.

Najczęstsze błędy optymalizacyjne

Nieaktualne statystyki to klasyk. Tabela rośnie z tysięcy do milionów wierszy, statystyki wciąż pokazują starą wielkość. Rozwiązanie: regularne zbieranie statystyk, monitoring stale_stats w DBA_TAB_STATISTICS.

Brak histogramów na kolumnach z nierównomiernym rozkładem. Kolumna STATUS z 99% wartości COMPLETED i 1% PENDING. Bez histogramu optymalizator zakłada równomierny rozkład i może wybrać zły plan dla zapytań o STATUS = 'PENDING'.

Implicit conversion w predykatach. Kolumna DATE porównywana ze stringiem, VARCHAR2 z NUMBER. Oracle dodaje funkcje konwersji, indeksy stają się bezużyteczne.

Korelacje między kolumnami. WHERE region = 'EMEA' AND country = 'Germany'. Optymalizator mnoży selektywności, zakładając niezależność. W rzeczywistości Germany jest zawsze w EMEA, kardynalność jest błędna. Rozwiązanie: extended statistics lub column groups.

Praktyczny przykład analizy

Zapytanie raportowe działa 45 minut zamiast oczekiwanych 2. Pierwszy krok: sprawdzam plan z GATHER_PLAN_STATISTICS. Widzę Hash Join z E-Rows: 500 i A-Rows: 2500000. To jest źródło problemu.

Sprawdzam predykaty przy operacji dającej błędną kardynalność. Widzę warunek na kolumnie z funkcją: WHERE TRUNC(created_date) = TRUNC(SYSDATE). Optymalizator nie może oszacować selektywności funkcji, używa domyślnej wartości 1%.

Rozwiązanie: tworzę function-based index na TRUNC(created_date) i zbieram na nim statystyki. Alternatywnie zmieniam predykat na created_date >= TRUNC(SYSDATE) AND created_date < TRUNC(SYSDATE) + 1, co pozwala użyć standardowego indeksu.

Po zmianie: E-Rows i A-Rows są zbliżone, optymalizator wybiera Nested Loops zamiast Hash Join (bo teraz wie, że driving set jest mały), zapytanie wykonuje się w 30 sekund.