Statystyki optymalizatora Oracle: fundament decyzji podejmowanych przez CBO

Rola statystyk w Cost Based Optimizer

Cost Based Optimizer (CBO) to mózg Oracle Database odpowiedzialny za wybór najefektywniejszego planu wykonania zapytania. W przeciwieństwie do swojego poprzednika Rule Based Optimizer, CBO nie kieruje się sztywnymi regułami, lecz oblicza koszt różnych wariantów wykonania i wybiera ten najtańszy. Cały ten proces opiera się na statystykach.

Gdy wykonujesz zapytanie łączące trzy tabele z kilkoma warunkami filtrowania, CBO musi podjąć szereg decyzji. Którą tabelę odczytać pierwszą? Czy użyć indeksu, czy wykonać pełne skanowanie? Zastosować NESTED LOOPS czy HASH JOIN? Każda z tych decyzji wymaga oszacowania liczby wierszy na poszczególnych etapach wykonania.

Statystyki dostarczają CBO informacji niezbędnych do tych obliczeń. Jeżeli tabela ORDERS zawiera 10 milionów wierszy, a warunek WHERE STATUS = 'PENDING' zwraca 0.1% z nich, optymalizator może sensownie oszacować, że potrzebuje przetworzyć około 10 tysięcy rekordów. Bez statystyk musiałby zgadywać.

DBMS_STATS jako podstawowe narzędzie

Pakiet DBMS_STATS zastąpił polecenie ANALYZE jako rekomendowane narzędzie do zarządzania statystykami. Oracle automatycznie uruchamia zadanie zbierania statystyk w oknie serwisowym, jednak w środowiskach produkcyjnych często wymaga to dostosowania.

Podstawowa procedura GATHER_TABLE_STATS przyjmuje kilkanaście parametrów, z których najważniejsze to ESTIMATE_PERCENT określający procent próbkowania oraz METHOD_OPT kontrolujący zbieranie histogramów. Domyślna wartość ESTIMATE_PERCENT wynosi AUTO_SAMPLE_SIZE, co pozwala Oracle samodzielnie dobrać wielkość próbki.

Parametr METHOD_OPT z wartością 'FOR ALL COLUMNS SIZE AUTO' oznacza automatyczne tworzenie histogramów dla kolumn, które tego wymagają. Oracle analizuje wykorzystanie kolumn w predykatach i decyduje o utworzeniu histogramu na podstawie obserwowanego skośnego rozkładu danych.

W mojej praktyce widziałem dziesiątki przypadków, gdy domyślne ustawienia AUTO działały świetnie przez lata, a potem nagle przestawały. Zwykle przyczyną był wzrost wolumenu danych przekraczający pewien próg lub zmiana charakterystyki danych. Automatyka jest wygodna, ale nie zwalnia z monitorowania.

Jakie statystyki przechowuje Oracle

Oracle gromadzi statystyki na kilku poziomach: tabel, indeksów, kolumn oraz partycji. Każdy poziom dostarcza innych informacji niezbędnych do optymalizacji.

Dla tabel przechowywane są: NUM_ROWS (liczba wierszy), BLOCKS (liczba bloków poniżej znacznika wysokiej wody), AVG_ROW_LEN (średnia długość wiersza) oraz SAMPLE_SIZE (wielkość próbki użytej do zbierania statystyk). Te wartości znajdziesz w widoku DBA_TAB_STATISTICS.

Statystyki kolumn obejmują: NUM_DISTINCT (liczba unikalnych wartości), NUM_NULLS (liczba wartości NULL), DENSITY (gęstość wykorzystywana do obliczeń selektywności) oraz LOW_VALUE i HIGH_VALUE (minimalna i maksymalna wartość). W przypadku kolumn z histogramami dochodzą informacje o rozkładzie wartości.

Dla indeksów Oracle przechowuje BLEVEL (głębokość B-drzewa), LEAF_BLOCKS (liczba bloków liści), DISTINCT_KEYS (liczba unikalnych kluczy) oraz CLUSTERING_FACTOR. Ten ostatni parametr jest szczególnie istotny, ponieważ informuje optymalizator o stopniu uporządkowania danych tabeli względem indeksu.

Histogramy i ich dwuznaczna natura

Histogramy opisują rozkład wartości w kolumnie. Oracle obsługuje kilka typów histogramów: FREQUENCY dla kolumn z małą liczbą unikalnych wartości, HEIGHT-BALANCED dla większych zbiorów oraz wprowadzone w wersji 12c histogramy HYBRID i TOP-FREQUENCY.

Histogram FREQUENCY przechowuje dokładną liczbę wystąpień każdej wartości. Jeżeli kolumna STATUS przyjmuje pięć różnych wartości, histogram zawiera pięć wpisów z precyzyjnymi licznikami. To pozwala optymalizatorowi dokładnie oszacować selektywność predykatu WHERE STATUS = 'ACTIVE'.

Problem pojawia się przy zmiennych wiązanych. Gdy wykonujesz zapytanie z predykatem WHERE STATUS = :bind_var, optymalizator musi podjąć decyzję bez znajomości konkretnej wartości. W starszych wersjach Oracle stosował bind peeking, czyli podglądał pierwszą wartość i tworzył plan na jej podstawie. Prowadziło to do słynnych problemów z niestabilnością planów.

Adaptive Cursor Sharing wprowadzone w Oracle 11g częściowo rozwiązuje ten problem, pozwalając na tworzenie wielu planów dla tego samego kursora w zależności od wartości zmiennych wiązanych. Jednak mechanizm ten wymaga kilku wykonań zapytania, zanim zadziała poprawnie.

Dynamic Sampling jako mechanizm awaryjny

Gdy statystyki są nieaktualne lub całkowicie brakuje, Oracle może zastosować Dynamic Sampling. Mechanizm ten wykonuje podczas parsowania zapytania próbkowanie danych, aby oszacować kardynalność.

Poziom Dynamic Sampling kontrolowany jest parametrem OPTIMIZER_DYNAMIC_SAMPLING z wartościami od 0 do 11. Poziom 2 (domyślny w wielu wersjach) oznacza próbkowanie tabel bez statystyk przy użyciu 64 bloków. Wyższe poziomy zwiększają agresywność próbkowania.

W Oracle 12c wprowadzono Adaptive Dynamic Sampling, który automatycznie decyduje o zastosowaniu próbkowania na podstawie złożoności zapytania i dostępności statystyk. Jest to część szerszej funkcjonalności Adaptive Query Optimization.

Kardynalność: serce optymalizacji

Oszacowanie kardynalności, czyli przewidywanej liczby wierszy na każdym etapie planu wykonania, to fundamentalne zadanie optymalizatora. Błędne oszacowania propagują się przez cały plan, prowadząc do kaskadowych problemów wydajnościowych.

Prosty przykład: jeżeli optymalizator szacuje, że warunek WHERE zwróci 100 wierszy, wybierze NESTED LOOPS jako metodę łączenia. Gdy w rzeczywistości zwraca 100 tysięcy wierszy, NESTED LOOPS wykona 100 tysięcy odczytów indeksu zamiast efektywnego HASH JOIN.

Najczęstsze przyczyny błędnych oszacowań to: nieaktualne statystyki, predykaty na wielu kolumnach z założeniem niezależności (podczas gdy kolumny są skorelowane), funkcje użyte na kolumnach oraz predykaty z wyrażeniami nieobsługiwanymi przez standardowe statystyki.

Jak rozpoznać problemy ze statystykami

Pierwszym sygnałem ostrzegawczym jest nagła zmiana czasu wykonania zapytania bez zmian w kodzie. Jeżeli zapytanie działało sekundę, a nagle wykonuje się dziesięć minut, prawdopodobnie zmienił się plan wykonania.

Analiza planu wykonania za pomocą DBMS_XPLAN.DISPLAY_CURSOR z parametrem FORMAT => 'ALLSTATS LAST' pokazuje rzeczywistą liczbę przetworzonych wierszy obok oszacowań optymalizatora. Rozbieżności rzędu kilku procent są akceptowalne; różnice stukrotne wskazują na poważny problem.

Warto regularnie sprawdzać widoki DBA_TAB_STATISTICS i DBA_IND_STATISTICS pod kątem tabel z parametrem STALE_STATS = 'YES'. Oracle oznacza statystyki jako nieaktualne, gdy tabela zmieniła się o więcej niż 10% od ostatniego zbierania.

Praktyczne scenariusze z produkcji

Przypadek pierwszy: system rozliczeniowy z partycjonowaną tabelą transakcji. Nowe partycje były tworzone automatycznie, ale statystyki dla nich nie były zbierane. Optymalizator stosował Dynamic Sampling, co dodawało kilka sekund do każdego zapytania i generowało dodatkowe obciążenie I/O.

Rozwiązaniem było dodanie do skryptu tworzącego partycje wywołania DBMS_STATS.COPY_TABLE_STATS, które kopiuje statystyki z poprzedniej partycji jako punkt wyjścia, oraz zaplanowanie regularnego zbierania statystyk dla najnowszych partycji.

Przypadek drugi: tabela z kolumną STATUS posiadającą silnie skośny rozkład; 99.5% wierszy miało status PROCESSED, pozostałe statusy występowały rzadko. Histogram poprawnie opisywał ten rozkład, ale zapytania raportowe używające zmiennych wiązanych otrzymywały nieoptymalne plany dla rzadkich statusów.

Zastosowaliśmy SQL Plan Baselines do zamrożenia optymalnych planów dla kluczowych zapytań, jednocześnie zachowując histogramy dla innych zastosowań tej tabeli.

Kiedy aktualizacja statystyk szkodzi

Paradoksalnie świeże statystyki mogą pogorszyć wydajność. Dzieje się tak najczęściej w trzech scenariuszach: gdy nowe statystyki powodują zmianę stabilnego planu na gorszy, gdy histogram utworzony automatycznie destabilizuje plany dla zapytań ze zmiennymi wiązanymi oraz gdy zbieranie statystyk podczas szczytu obciążenia konkuruje o zasoby z aplikacją.

Dlatego w krytycznych systemach produkcyjnych warto rozważyć strategię pending statistics. Statystyki są zbierane, ale nie publikowane od razu. Administrator może je przetestować na kopii środowiska przed aktywacją w produkcji.