Strona główna > Oracle Database > Nie ufaj kosztowi zapytania cz.2

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…

  1. z
    2011/02/17 o 15:34

    pierwszy!

    • gdrzymala
      2011/02/17 o 15:34

      😛

  1. No trackbacks yet.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s

%d bloggers like this: