Podpowiedzi optymalizatora

Podpowiedzi  optymalizatora

Podpowiedzi optymalizatora stosuje się kiedy chcemy wpłynąć na sposób działania optymalizatora kosztowego. Możemy mu np. kazać skorzystać z indeksu, lub tego zabronić. Możemy nakazać mu wykorzystanie określonego algorytmu łączenia tabel, a także wpływać na wiele innych procesów.

Generalnie podpowiedzi (hintów) używa się w ostatniej kolejności w procesie optymalizacji i tylko wtedy gdy optymalizator kosztowy ewidentnie się myli, albo mamy do czynienia z sytuacjami o których optymalizator kosztowy nie wie (np. czas wykonania zapytania dla algorytmu o teoretycznie wyższym koszcie jest krótszy).

Podpowiedzi powinny następować zaraz po klauzuli SELECT i mają konstrukcję następującą:

SELECT /*+ podpowiedź */ LAST_NAME, FIRST_NAME FROM EMPLOYEES;

Podpowiedź musi się znaleźć pomiędzy /*+ a */ i następować zaraz po SELECT. Jeśli popełnimy błąd (np. literówka) podpowiedź zostanie zignorowana i potraktowana jako komentarz. Nie możemy wymusić hintem czynności niemożliwych, takich jak np. zastosowanie indeksu który założony jest na kolumnę innej tabeli niż ta do której sięgamy.

INDEX

SELECT /*+ index(e nazwaindeksu)*/ nazwakolumny from nazwatabeli e;

Wymusi zastosowanie indeksu do pobierania wartości z kolumny (np. zamiast Full scana po tabeli).

NO_INDEX

SELECT /*+ no_index(e nazwaindeksu)*/ nazwakolumny from nazwatabeli e;

Zabroni wykorzystania indeksu do pobrania wartości z kolumny.

ALL_ROWS

Select /*+ALL_ROWS*/ Employee_Id From Employees e;

Wymusi stosowanie takich algorytmów by optymalizacja była nastawiona na efektywny zwrot wszystkich wierszy.

FIRST_ROWS

Select /*+FIRST_ROWS(n)*/ Employee_Id From Employees e;

Wymusi stosowanie takich algorytmów by optymalizacja była nastawiona na efektywny zwrot pierwszych „n” wierszy.

FULL

Select /*+FULL(e)*/ employee_id from employees;

Wymusi wykorzystanie pełnego skanu po tabeli przy pobieraniu danych.

USE_NL

Select /*+use_nl(e d)*/ * From Employees e join departments d using(department_id);

Wymusi wykorzystanie algorytmu nested loops (pętle zagdzieżdżone) do łączenia tabel.

NO_USE_NL

Select /*+no_use_nl(e d)*/ * From Employees e join departments d using(department_id);

Zabroni wykorzystania algorytmu nested loops (pętle zagdzieżdżone) do łączenia tabel.

USE_MERGE

Select /*+use_merge(e d)*/ * From Employees e join departments d using(department_id);

Wymusi wykorzystanie algorytmu sort merge join podczas łączenia tabel.

NO_USE_MERGE

Select /*+no_use_merge(e d)*/ * From Employees e join departments d using(department_id);

Zabroni wykorzystania algorytmu sort merge join podczas łączenia tabel.

USE_HASH

Select /*+use_hash(e d)*/ * From Employees e join departments d using(department_id);

Wymusi wykorzystanie algorytmu hashującego do łączenia tabel.

NO_USE_HASH

Select /*+no_use_hash(e d)*/ * From Employees e join departments d using(department_id);

Zabroni wykorzystania algorytmu hashującego do łączenia tabel.

CURSOR_SHARING_EXACT

Select /*+cursor_sharing_exact*/ * from employees where employee_Id=:x

Wymusi potraktowanie zmiennych bindowanych w zapytaniu tak jakbyśmy mięli włączony parametr CURSOR_SHARING na EXACT w sytuacji kiedy mamy ustawiony inaczej niż domyślnie.

Podpowiedzi optymalizatora jest znacznie więcej. Tutaj przedstawiłem najczęściej używane.

Używanie zmiennych bindowanych

Zmienne bindowane

W Oracle funkcjonują zmienne bindowane, powiązane z sesją. Możemy wprowadzić do takiej zmiennej wartość, a następnie wykonać zapytanie z użyciem tej zmiennej. Jakie to ma znaczenie przy strojeniu SQL?

Jeżeli wykonujemy wiele podobnych zapytań:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=100;

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=104;

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=107;

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=109;

dla każdego z tych zapytań wymyślany jest osobny plan wykonania. To nie jest dobre z przynajmniej dwóch powodów:

• Oracle musi poświęcić czas i zasoby na analizę i opracowanie explain planu (planu wykonania).
• Zapełniana jest przestrzeń w shared_pool w sumie niemal identycznymi zapytaniami – a właściwie to związanymi z nimi planami wykonania.

Sytuacja taka jest dość powszechna. Wykorzystujemy słowniki, a te jako z reguły intensywnie eksploatowane powinny być wykorzystywane w sposób możliwie najbardziej optymalny.

Poszukaj w słowniku v$sql zapytań o podobnej konstrukcji np.

select * from v$sql where lower(sql_text) like '%from%employees%';

Dzięki temu sprawdzisz czy taka sytuacja ma również miejsce u Ciebie.

Aby się przed tym uchronić możemy nieco zmodyfikować zapytania. Przede wszystkim tworzymy zmienną bindowaną :

variable x number;

następnie przypisz do niej wartość:

begin

:x:=100;

end;

Pamiętaj że nazwy zmiennych bindowanych przy przypisaniu i odwołaniu poprzedzamy dwukropkiem. Teraz możesz odwołać się do tej zmiennej w zapytaniach:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:X;

Pamiętaj że wartość zmiennej możesz zmieniać dowolną ilość razy. Jednak niezależnie od tego, dla optymalizatora kosztowego będzie to to samo zapytanie. Będzie mógł więc wykorzystać wcześniejszy plan wykonania. Na tym polega „trick”. Po prostu zamiast produkować właściwie identyczne zapytania różniące się tylko jedną wartością, korzystamy ze zmiennej bindowanej i wykonujemy zapytanie z jej użyciem. Podmieniamy tylko wartość tej zmiennej.

Zmienne bindowane a CURSOR_SHARING

Możesz ustawić parametr cusor_sharing na force:

ALTER SESSION SET CURSOR_SHARING=FORCE;

ALTER SYSTEM SET CURSOR_SHARING=FORCE;

dzięki czemu w przypadku takich drobnych różnic literalnych (jak employee_id w poprzednich przykładach), Oracle sam sobie stworzy zmienną bindowaną na potrzeby tej wartości i będzie działał tak, jakbyśmy stworzyli zmienną bindowaną i podstawiali jej wartości.

W przypadku ustawienia CURSOR_SHARING na SIMILAR i jednoczesnym korzystaniu ze zmiennych bindowanych, Oracle będzie traktował tak samo dwa zapytania o takiej budowie:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:x;

ponieważ employee_id jest kolumną na którą jest założony klucz główny. Jakakolwiek wartość trafi do zmiennej bindowanej X, sens zapytania i sposób wykonania się nie zmienią. Zawsze uzyskamy jeden wiersz. W przypadku takiego zapytania:

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=:x;

sens zapytania w zależności od wartości X może ulec zmianie. Może być wiele wierszy które mają daną wartość w kolumnie department_id, a ich liczebność oraz rozłożenie może ulec zmianie w zależności od wartości x.

Metody dostępu do danych. Indeksy i dostęp po indeksach

Co to jest index?

Jest obiektem bazodanowym niezależnym logicznie i fizycznie od tabeli. Pozwala uzyskać szybszy dostęp do danych. Indeksy zakłada się na kolumnę w tabeli, kilka kolumn naraz (do 32) . Bez nich wszystko będzie działać, jednak indeksy pozwolą nam szybciej dostać się do danych. Indeksy przechowują wartości kolumn na które są nakładane oraz ROWID wiersza.

Rodzaje indeksów

B-tree : Najczęściej wykorzystywane indeksy. Wykorzystuje się je tam gdzie dane w kolumnach na które zakłada się taki indeks są dość mocno zróżnicowane. Przykładowo nr pesel w tabeli ze wszystkimi obywatelami Polski. Nie przechowuje wartości nullowych. Tworzenie:

CREATE INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY)

Bitmapowe: Wykorzystuje je się tam gdzie jest małe zróżnicowanie danych np. nazwa województwa w tabeli ze wszystkimi obywatelami Polski. Przechowuje wartości nullowe. Nie występują w wersji Express Edition Oracle.

CREATE BITMAP INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY)

Złożone: Składają się z większej niż jedna ilości kolumn. Stosuje się je kiedy w warunku where, podczas łączenia tabel lub w grupowaniu występuje kilka kolumn naraz. Powinno się stosować w indeksie taką kolejność kolumn jak występuje w zapytaniach.

CREATE INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY1, NAZWAKOLUMNY2)

Unikalne: Zakładane na te kolumny w których wartości są unikalne. Umożliwiają stosowanie unique scan.

CREATE UNIQUE INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY)

Funkcyjne: Oparte na funkcjach które przetwarzają zawartość kolumn na które są nakładane. Stosuje się je kiedy w tabeli mamy dane nieprzetworzone, a wykorzystujemy przetworzone w zapytaniach.

CREATE INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY*2)

Tabele zorganizowane indeksowo: To tabele o strukturze indeksu b-tree. Wykorzystuje się je często przy słownikach (w bazodanowym pojęciu). Taka tabela musi posiadać klucz główny.

CREATE TABLE NAZWATABELI(

KOLUMNA1 TYPDANYCH PRIMARY KEY,

KOLUMNA2 TYPDANYCH

) ORGANIZATION INDEX;

Skany po indeksach

Unique Scan

Wiemy już że przeszukiwanie indeksów będzie szybsze niż przeszukiwanie tabeli. Wiemy też że indeksy przechowują rowidy wierszy.

Jeżeli na kolumnie założymy indeks, Oracle będzie mógł go przeszukać pod kątem wartości przez nas podanych by odnaleźć rowid wiersza. Kolejność w tym przypadku jest taka:

1. Oracle przeszukuje indeks w poszukiwaniu wartości 100
2. Znajduje wartość w indeksie i odczytuje przypisany do tej wartości rowid.
3. Poprzez rowid (adres fizyczny wiersza na dysku) Oracle dostaje się do wiersza w tabeli.

Ten rodzaj skanu wykorzystywany jest kiedy poszukujemy dane poprzez kolumnę z kluczem głównym, lub jeśli na kolumnie której dotyczy warunek nałożony jest indeks unikalny.

Range Scan

Skan zakresowy, wykorzystywany jest kiedy stosujemy warunki typu < lub > na kolumnach na które założony jest index.

Full Scan

 

Jest to liniowe przeszukiwanie całego indeksu. Po co jest to not null? Założony mamy index b-tree, a jak wspomniałem wcześniej taki indeks nie przechowuje wartości null! Co za tym idzie , jeśli nie zagwarantujemy optymalizatorowi że albo w kolumnie nie ma wartości null (kolumna z warunkiem not null) albo nie powiemy mu że ewentualne wartości null nawet jeśli występują to nas nie interesują (warunkiem użytym w tym przykładzie), to optymalizator nie mając pewności czy zwróci wszystkie dane będzie wykonywał full scan ale po tabeli. Odnosi się to również do innnych rodzajów skanów!

Fast Full Scan

Wykorzystywany w przypadkach kiedy wszystkie informacje niezbędne do pobrania znajdują się

w indeksie. W takim przypadku w ogóle nie ma potrzeby sięgania do tabeli.

Skip Scan

Wykorzystywane wtedy gdy podczas przeszukiwania indeksu można pominąć bloki, w których z całą pewnością nie znajdziemy poszukiwanych przez nas danych.

Skany po tabelach indeksowych

Zauważ że mimo że wykonuję pełny skan po tabeli, mam rodzaj skanu charakterystyczny tylko dla indeksów. Taka tabela zbudowana jest jak indeks (struktura drzewiasta). Dodatkową korzyścią

z tworzenia tabeli zorganizowanych indeksowo, jest możliwość stosowania na nich skanów typowych dla indeksów – np. unique scan. To sprawia że takie tabele użyteczne są np. przy

słownikach.

Metody dostępu do danych - dostęp do tabeli

Oracle w celu pobrania danych może zastosować różne metody. Może wykorzystywać przeszukiwanie tabel w całości, lub korzystać z indeksów w celu odnalezienia danych w tabelach. Poniżej zamieszczam listing różnych metod dostępu dostępnych dla tabel i dla indeksów.

Tabele	Full Table Scan Rowid Scan Sample Table Scan
Indeksy	Unique Scan Range Scan Full Scan Fast Full Scan Skip Scan Index Join

Dane mogą zostać pobrane przy użyciu wymienionych wyżej metod, w zależności oczywiście od tego jakie możliwości mu damy (przypominamy sobie historię z trasą z Gdyni do Krakowa). W pierwszej kolejności omówię każdą z tych metod dostępu do danych. W żadnym wypadku nie pomijaj tego rozdziału! Znajomość tych metod i ich dobre zrozumienie jest bardzo ważne!



Full Table Scan

To przeglądanie całej tabeli wiersz po wierszu w celu znalezienia informacji które nas interesują. Tego typu przeglądanie tabeli jest naprawdę mało wydajne. Wyobraź sobie że szukasz jednego wiersza. Pracownika o numerze 100, który znajduje się „gdzieś” w tabeli zawierającej ogólne milion wierszy. Aby go znaleźć tą metodą musisz przejrzeć całe te milion wierszy jeden po drugim aż nie znajdziesz właściwego. Czy to nie jest marnotrawstwo czasu i zasobów? Optymalizator kosztowy wybiera tą metodę dostępu do danych kiedy nie ma indeksów z których mógłby skorzystać, kiedy żądamy od bazy całej zawartości tabeli , lub gdy zapytanie jest skonstruowane tak że uniemożliwia skorzystanie z indeksu (na razie tym się nie przejmuj, wyjaśni się nieco później).

Rowid Scan

Przede wszystkim muszę Ci przybliżyć pojęcie rowid. Każdy wiersz w tabeli zawiera rowid, który reprezentuje fizyczną lokalizację danego wiersza na dysku. Możesz wyświetlić te wartości:

Jeśli taki adres jest znany, Oracle może pominąć przeszukiwanie tabeli wiersz po wierszu i od razu dostać się do poszukiwanego wiersza korzystając z ROWID:

Oczywiście rzadko kiedy stosuje się takie wyszukiwanie jak na powyższym obrazku. Taki adres (rowid) pobierany jest z indeksu najczęściej z indeksu i dopiero w kolejnym etapie następuje dostęp do tabeli z wykorzystaniem rowid.

Sample Table Scan

Ten rodzaj dostępu do tabeli wykorzystywany jest do pobierania próbek danych. Zwraca losowe wiersze z tabeli.