Nie ufaj kosztowi zapytania cz.2

Dzisiejszy wpis będzie kontynuacją poprzedniego. Przypomnę, że chodziło o ustalenie przyczyny wyboru przez Oracle planu zapytania (z metoda łączenia HASH JOIN), który w ostateczności dawał gorsze rezultaty (niż wymuszony hintem NESTED LOOP). Przypadkowo rozwinęła się ciekawa dyskusja na ten temat na goldenline. Link do tematu: Dyskusja na GoldenLine .

Zgodnie ze wskazówkami wypowiadających się tam osób wykonałem kilka testów. Wyniki zaprezentuję poniżej.

Ułożyłem krótki scenariusz testowy, który polegał na wykonaniu takiej sekwencji zdarzeń:

• wyczyszczenie bufora
• truncate tabeli testowej
• ustawienie odpowiednich parametrów
• insert do tabeli testowej z selecta, który wywoływany jest z różnym planem wybieranym przez CBO

Wyczyszczenie bufora realizowałem za pomocą
alter system flush buffer_cache;

Truncate tabeli testowej to polecenie
truncate table test_2;

Dochodzimy teraz do kluczowego punktu – ustawienia parametrów. Zgodnie z sugestiami zainteresowałem się parametrami OPTIMIZER_INDEX_CACHING oraz OPTIMIZER_INDEX_COST_ADJ. Okazało się, że nie były one prawidłowo ustawione w chwili wykonywania pierwszego testu (poprzedni wpis).
OPTIMIZER_INDEX_CACHING ustawiony był na wartość 0, OPTIMIZER_INDEX_COST_ADJ na 100. Są to wartości domyślne, które w większości środowisk działających produkcyjnie powinny zostać stosownie dostrojone. Pokrótce tłumacząc – pierwszy parametr podpowiada CBO ile procent indeksu może znajdować się w buforze. Drugi natomiast ustala czy indeksy mają być bardziej przyjazne niż full scany tabeli.
Testy, które zostały wykonane zostały powtórzone dla dwóch przypadków:

1. Wartości domyślne parametrów. OPTIMIZER_INDEX_CACHING = 0 , OPTIMIZER_INDEX_COST_ADJ = 100
2. Wartości dostrojone. OPTIMIZER_INDEX_CACHING = 50, OPTIMIZER_INDEX_COST_ADJ = 15

Kolejnym etapem był insert do tabeli testowej, która znajdowała się w trybie LOGGING (cała baza w trybie ARCHIVELOG). Były więc generowane znaczne ilości redologów oraz archivelogów.  Insert ten korzystał z wyników zapytania, które analizowane było w poprzednim poście. W kontekście tego testu wybrałem jednak kilka możliwości wykonania zapytania bazowego. Były to:

1. Zapytanie bez hintów
2. Zapytanie używające USE_NL
3. Zapytanie używające PARALLEL 5
4. Zapytanie używające PARALLEL 5 , USE_NL

Poniższa tabela pokazuje uzyskane wyniki:

	Czas wykonania zapytania w sek. (średnia z 3 prób)
	brak hintów	parallel 5	use_nl	parallel 5 + use_nl
OPTIMIZER_INDEX_CACHING = 50	137	135	200	120
OPTIMIZER_INDEX_COST_ADJ = 15
OPTIMIZER_INDEX_CACHING = 0	145	140	200	120
OPTIMIZER_INDEX_COST_ADJ = 100

Pomimo modyfikacji parametrów w dalszym ciągu najlepiej prezentuje się opcja PARALLEL , USE_NL – z poprzedniego wpisu widać, że nie jest to dla Oracla najlepsza ścieżka wykonania zapytania. Warto zwrócić uwagę na kolumnę PARALLEL 5 i wiersz, w którym wspomniane wyżej parametry zostały zmodyfikowane – czas wykonania operacji zmniejszył się – zmienił się plan (dzięki modyfikacji parametrów, CBO wykorzystuje teraz INDEX RANGE SCAN przy HASH JOINie)  :

W dalszym ciągu jednak metoda NESTED_LOOPS okazuje się szybsza niż HASH JOIN.

Wykonałem zatem sprawdzenie tych dwóch opcji wykonania zapytania (za pomocą sqlplusa i autotrace).

Dla zmodyfikowanych parametrów OPTIMIZER_INDEX oraz z hintem PARALLEL (średni czas wykonania operacji – 135 sek.) otrzymałem:
Statystyki
-----------------------------
11574 recursive calls
585824 db block gets
586891 consistent gets
631910 physical reads

Dla opcji z domyślnymi wartościami parametrów OPTIMIZER_INDEX, hintami PARALLEL oraz USE_NL (średni czas wykonania operacji – 120 sek.):
Statystyki
------------------------------
11089 recursive calls
585807 db block gets
28610968 consistent gets
580144 physical reads

Logiczna ilość operacji I/O (db block gets + consistent gets) jest więc dużo gorsza dla zapytania, które wykonuje się szybciej!

Mimo wykonania serii testów, modyfikowania odpowiednich parametrów oraz sprawdzenia statystyk wykonywanych zapytań dalej nie mam jednoznacznej odpowiedzi czemu CBO zachował się w tym przypadku w ten sposób.

W dalszym ciągu jest to tylko wierzchołek góry lodowej…

Nie ufaj kosztowi zapytania

Witajcie. Dawno nie pisałem, głównie z braku weny… Dzisiaj jednak postanowiłem nadrobić zaległości. Padły pewne zarzuty, że blog jest mało konkretny/techniczny, więc dzisiejszy post niech będzie małą wprawką 🙂

Prosty przykład. Na razie bez szczegółów.

• Tabele oraz indeksy mają odpowiednio zebrane statystyki.
• Tabela A zawiera 14 184 446 wierszy.
• Tabela B zawiera 2 322 917 wierszy.
• Obszary SGA i PGA zarządzane są przez (nowy w Oracle 11g) parametr MEMORY_TARGET – ustawiony na 8GB
• Tabele nie są spartycjonowane
• W systemie nie działają inne znaczące procesy – jednym słowem cały procesor i cała przepustowość dysku jest do wykorzystania
• Używana jest macierz dyskowa, o bliżej nieznanych mi parametrach

Zapytanie bazowe:

SELECT A.lista_kolumn, B.lista_kolumn
FROM tabela_a A LEFT OUTER JOIN tabela_b B ON warunek_zlaczenia;

Warunek złączenia jest zwykłym warunkiem równościowym. Tabela A będzie zawsze czytana cała, więc brany będzie FULL SCAN tabeli. Można więc rozbić ten spory koszt poprzez dodanie hinta PARALLEL.

Zapytanie z hintem PARALLEL:

SELECT /*+ PARALLEL(A,5) */ A.lista_kolumn, B.lista_kolumn
FROM tabela_a A LEFT OUTER JOIN tabela_b B ON warunek_zlaczenia;

Mimo, że na tabela_b istnieje odpowiedni indeks, Oracle generuje plan zapytania, w którym go nie uwzględnia:

Jak widać – Oracle chce użyć metody HASH JOIN, i uważa, że ta metoda będzie szybsza. Można jednak wymusić użycie indeksu.

Zapytanie z hintami PARALLEL i USE_NL:

SELECT /*+ PARALLEL(A,5) USE_NL(B)*/ A.lista_kolumn, B.lista_kolumn
FROM tabela_a A LEFT OUTER JOIN tabela_b B ON warunek_zlaczenia;

USE_NL- podpowiada optymalizatorowi, że chcemy skorzystać ze złączenia typu NESTED LOOPS

Plan zapytania:

Mimo, że koszt drugiego zapytania jest dużo wyższy, zapytanie wykonywane jest szybciej. Uśrednione wyniki to ok 0.6 sekundy dla wersji z indeksem (i metodą złączenia NESTED LOOPS) oraz ok 10 sekund dla wersji bez indeksu (metoda złączenia HASH JOIN) i jednocześnie niższym kosztem. Wstępna konkluzja powinna być oczywista – optymalizator kosztowy czasem się myli – niższy koszt wcale nie musi oznaczać niższego czasu wykonania danej instrukcji SQL.

 

Ale to tylko wierzchołek góry lodowej….