Interpretacja planów wykonania

Czym jest plan wykonania? Jest zwracanym przez optymalizator kosztowy algorytmem dostępu do danych. Zawiera informacje na temat obiektów które muszą zostać wykorzystane, sposobu dostępu do nich, wykorzystanych algorytmów łączenia tabel, szacowanych kosztów tych dostępów etc.

Przeglądanie planów wykonania

Z użyciem SQL Developera

Najprostszą metodą sprawdzenia planu wykonania dla zapytania jest wpisanie jego treści w polu edycji SQL Developera i naciśnięcie F10. Zobaczymy wtedy obraz jak powyżej.

Te najbardziej zagnieżdżone elementy są elementami składowymi elementów „piętro niżej” w zagnieżdżeniu. Odnosi się to również do szacunkowych kosztów.

Z użyciem tabeli plan_table

Jeśli chcemy, możemy plany wykonania gromadzić w tabeli plan_table. Wystarczy zapytanie poprzedzić frazą :

explain plan set statement_id='nasza nazwa' for [treść zapytania]

set statement_id jest po to, by rozróżnić elementy planów wykonania pomiędzy różnymi planami. Nie jest obligatoryjne , natomiast w przypadku więcej niż jednego zapytania testowanego w ten sposób pojawi się problem z rozróżnieniem elementów.

Po wykonaniu tego polecenia plan wykonania naszego zapytania znajdzie się w tabeli plan_table. Możemy do niej zajrzeć, jednak wynik będzie co najmniej nieczytelny. Wykonałem też dwa przykłady wykonania explain planów – jeden z ustawianiem statement_id, drugie bez. W sekcji statement_id mamy nulle. Co jeśli wykonamy tak kilka zapytań. Rozróżnimy ich elementy?

Poniżej przykład:

Odfiltrujmy tylko to co jest potrzebne:

Co tutaj jest co:

operation - rodzaj operacji

options – rodzaj dostępu do obiektu

object_type – rodzaj obiektu do którego się odwołujemy (index, tabela, tabela tymczasowa etc).

object_name – nazwa obiektu do którego się odwołujemy

cost – koszt (omawiany wcześniej)

cardinality – liczebność (omawiana wcześniej)

bytes – ilość bajtów które należy odczytać by pobrać dane

cpu_cost – ilość cykli procesora niezbędna do przeprowadzenia operacji

io_cost – ilość bloków bazodanowych jakie trzeba odczytaćw celu przetworzenia operacji.

Porównywanie planów wykonania z użyciem tabeli plan_table

Przy użyciu tabeli plan _table możemy również porównywać konkurencyjne zapytania pod kątem ilości io, kosztów etc. Wystarczy w pierwszej kolejności stworzyć explain plany dla zapytań:

Następnie przy użyciu funkcji agregujących i operatora zbiorowego UNION ALL porównujemy

dwa plany pod kątem obciążenia procesora, kosztów, ilości przetwarzanych wierszy, czytanych bloków:

Pakiet dbms_xplan

Wykorzystując ten pakiet możemy zastąpić ręczne selecty po tabeli_plan table. Wykonujemy polecenie explain plan for …. a następnie wywołujemy taką oto funkcję:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

dzięki czemu uzyskujemy bardzo czytelny wynik:

Alternatywnie możemy też wywołać:

SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY(NULL,NULL,'ALL'));

aby uzyskać jeszcze więcej informacji takich jak np. typ danych zwracanych kolumn.

Autotrace

W przypadku SQL Developera oraz SQL Plusa, możemy włączyć sobie jeszcze opcję autotrace. Umożliwia nam ona automatyczne wyświetlanie explain planów . W przypadku SQL Developera wykonujemy:

SET AUTOTRACE ON;

a następnie uruchamiamy zapytania przy użyciu F5 zamiast F9.

Jak widać poza danymi z zapytania widzimy również explain plan. W SQLPlusie wystarczy włączyć autotrace (set autotrace on) i wykonywać zapytania jak do tej pory.

Możesz też włączyć autotrace w ten sposób:

SET AUTOTRACE ON STATISTICS;

Aby to uczynić musisz jednak mieć świeże statystyki i nadaną rolę plustrace użytkownikowi z poziomu którego chcesz włączać taki autotrace. W przypadku takiego włączenia autotrace będziemy mogli zaobserwować statystyki związane z wykonaniem zapytania.

Co jest co:

recursive calls – ilość zapytań wewnętrznych wykonanych w związku z wykonywaniem naszego zapytania. Wliczane tutaj są np. zapytania po słownikach systemowych.

Consistent gets – Kiedy wykonywany jest update na danych, dane sprzed zmiany lądują w przestrzeni undo. Do czasu kiedy zmiany nie zostaną zatwierdzone (commit) przez użytkownika zmieniającego, wszyscy widzą dane oryginalne – pochodzące z przestrzeni UNDO. Consistent gets określa ilość bloków odczytanych w trybie consistent – co oznacza że dane mogą (ale nie muszą) pochodzić z przestrzeni UNDO.

Db block gets – dane aktualne odczytanie w sposób inny niż w trybie consistent. Takie dane są czytane tylko przy rekursywnych wewnętrznych odwołaniach – np. przy czytaniu nagłówków segmentów tabel.

Physical reads – ilość bloków odczytanych bezpośrednio lub z bufora db_cache.

redo_logs – ilość bajtów wykorzystanych w dziennikach powtórzeń na potrzeby tego zapytania.

Sorts – czasami w niektórych metodach dostępu do danych wykorzystywane jest sortowanie. Przykładowo przy sort merge join.

Bytes set via SQL*Net to client – ilość bajtów przesłana do klienta

Bytes received via SQL*Net from client – ilość bajtów przesłana przez klienta.