oracle - strojenie zapytañ sql(1).pdf - Oracle - P_i_C

IX Konferencja PLOUG

Koœcielisko

PaŸdziernik 2003

Strojenie zapytañ SQL – czyli, „mo¿na

jeszcze szybciej”

Krzysztof Kawa

Strojeniu zapytañ SQL poœwiêcono ju¿ bardzo wiele miejsca w literaturze. Jako osoba z doœæ du¿ym doœwiadczeniem w dziedzinie

strojenia zapytañ SQL baz danych Oracle 8i i 9i chcia³bym przedstawiæ kilka rad, których stosowanie z pewnoœci¹ korzystnie wp³ynie na

szybkoœæ realizacji zapytañ.

Informacja o autorze:

Krzysztof Kawa, absolwent informatyki na Politechnice Poznañskiej w Poznaniu oraz Wydzia³u Biznesu Miêdzynarodowego na Akade-

mii Ekonomicznej w Poznaniu. Od kilku lat pracuje przy projektowaniu i eksploatacji du¿ych systemów informatycznych w kraju i

zagranic¹. Specjalizuje siê w strojeniu aplikacji opartych na rozwi¹zaniach firmy Oracle oraz projektowaniu rozwi¹zañ J2EE.

Strojenie zapytań SQL – czyli, można jeszcze szybciej

361

1. Wstęp

Znana z inżynierii oprogramowania zasada mówi, iż tuning systemu powinien zostać prowa-

dzony już od wczesnych faz budowania/projektowania systemu. Tylko takie podejście pozwala na

uzyskanie znaczących efektów stosunkowo małym kosztem. Koszt tuningu aplikacji rośnie wraz z

kolejnymi fazami powstawania projektu, a co więcej, założenia [w przypadku systemów bazoda-

nowych chodzi tu choćby o schemat tabel] powstałe w pierwszych fazach będą wpływać negatyw-

nie [bądź korzystnie] na szybkość pracy całego systemu w przyszłości. Obrazowo przedstawia to

rysunek 1.

Koszty tuningu aplikacji w kolejnych fazach

powstawania projektu

Zysk

Koszt

Projekt

Dewelopowanie

Testy

Wdrożenie systemu

Rys. 1. Koszty tuningu aplikacji w kolejnych fazach realizacji projektu

2. Aspekty strojenia aplikacji

Strojenie SQL jest tylko jednym z wielu aspektów wpływających na szybkość pracy całego

systemu. Innymi, nie mniej ważnymi aspektami wpływającymi na wydajność całego systemu są:

•

architektura całej aplikacji uwzględniająca aspekty związane nie tylko z bazą danych, ale i

aspekty doboru technologii, języka programowania użytego przy tworzeniu aplikacji

•

fizyczny koncept bazy danych wyznaczający reguły przechowywania danych w odpowied-

nich strukturach, zastosowanie indeksów, replikacji, tabel agregacyjnych

•

aspekty związane z doborem platformy systemowo-sprzętowej oraz jej tuningowanie [za-

równo samego systemu operacyjnego jak i sprzętu]

3. Metody dostępu do danych, metody łączenia tabel, optymaliza-

tory, histogramy

Zanim przedstawię praktyczne aspekty tuning SQL i PLSQL zamierzam przypomnieć i w skró-

cie omówić podstawowe aspekty związane z wykonywaniem zapytań.

362

Krzysztof Kawa

Metody dostępu do tabel

•

full scan – to chyba najprostsza metoda dostępu do danych, polegająca na tym, iż Oracle

czyta wiersz po wierszu zapisanym w tabeli, aż do osiągnięcia tzw. high-water mark

•

rowid access – rowid jest pseudokolumną, zawierającą w treści fizyczną reprezentację da-

nych. Odczyt przy jej pomocy jest szybki

•

index lookup – Oracle przy dostępie do danych korzysta z indeksu pozwalającego na szyb-

sze dotarcie do danych [Oracle wspiera 2 typy indeksów – bitmapowe oraz B*]

•

hash key access – metoda dostępu oparta na funkcji haszującej tj. przekształceniu matema-

tycznemu wyznaczającemu lokalizację przechowywania danych na podstawie wartości w

kolumnie [hash cluster]

Metody łączenia tabel

•

sort merge – do wykonania połączenia nie jest wymagane użycie indeksów; Oracle sortuje

dwie łączone tabele względem kolumn, które maja być połączone, a następnie łączy posor-

towane tabele

•

nested loop – algorytm ten zwykle korzysta z indeksów przynajmniej dla jednej z tabel. Na

mniejszej z tabel wykonywany jest full-scan (często z pominięciem indeksu) i dla każdej

krotki z tego zbioru używany jest indeks, aby odnaleźć krotkę do połączenia w drugiej tabeli

•

hash join – dla większej z obu tabel tworzona jest tabela haszowa, następnie odczytywana

jest zawartość mniejszej tabeli, a tabela haszowa używana jest do odnalezienia wiersza w

większej tabeli [metoda sprawdza się dobrze, gdy tabela haszowa może w całości zostać

utworzona w pamięci ]

Optymalizatory a statystyki

Po sprawdzeniu składni zadanego polecenia w języku SQL (język SQL jest językiem deklara-

tywnym, tym samym polecenie mające na przykład wyświetlić określone krotki z bazy danych nie

zawiera algorytmu, według którego baza danych ma postępować, aby dotrzeć do danych) Oracle

musi w przeciągu ułamka sekundy wybrać najbardziej optymalną drogę dostępu do danych – do

tego celu zostały zaimplementowane 2 rodzaje optymalizatorów:

• regułowy (ang. rule)– optymalizator podejmuje decyzję, w jaki sposób najszybciej dotrzeć

do danych na podstawie zestawu reguł oraz rankingu różnych ścieżek dostępu do danych.

Optymalizator regułowy dla przykładu zawsze faworyzuje dostęp poprzez indeks, nie potra-

fi rozróżnić i dostosować się względem dostępu do małych tabel jak i do wielkich. Decyzja

o dostępie zapada na podstawie indeksów. Optymalizator nie jest rozwijany od wersji Orac-

le 6

• kosztowy (ang. cost-based) – został wprowadzony w wersji Oracle 7. Algorytm korzysta z

zgromadzonych wcześniej informacji informacjach o samych danych. Jest on obecnie do-

myślnym optymalizatorem. Jest on również zalecany przez Oracle, gdyż tylko nad nim

trwają dalsze prace badawcze.

Jak to zostało już wyżej zaznaczone optymalizator kosztowy przy wyborze ścieżki dostępu do

danych korzysta ze zgromadzonych wcześniej statystyk, stąd pojawia się troska o to, aby zgroma-

dzone statystyki były aktualne.

Do zbierania statystyk służ polecenie ANALYZE o składni:

ANALYZE {TABLE | INDEX | CLUSTER} name

[{COMPUTE STATISTICS |

ESTIMATE STATISTICS SAMPLE size [ROWS|PERCENT] }

Strojenie zapytań SQL – czyli, można jeszcze szybciej

363

[FOR {TABLE |

ALL [INDEXED] COLUMNS [SIZE histogram_size] |

Column_list [SIZE histogram_size] |

ALL [LOCAL] INDEXES ] } …]

Polecenie zbiera informacje statystyczne dla tabeli, indeksu lub klastra i zapisuje je w słowniku

danych w celu późniejszego użycia ich przy wyznaczaniu ścieżki dostępu. W słowniku zapisywa-

ne są:

• dla tabeli (liczba wierszy, liczba użytych i pustych bloków, średnia długość wiersza, średnia

zajętość bloków danych)

• dla indeksu (liczba bloków liści, głębokość indeksu B*, ilość jednakowych kluczy)

Chcąc przeliczyć statystyki dla wszystkich obiektów jednego schematu (tabel lub indeksów)

znacznie łatwiej posłużyć się procedurą ANALYZE_SCHEMA z pakietu DBMS_UTILITY. Po-

zwala ono wybrać pomiędzy estymowaniem statystyk a obliczaniem statystyk bazując na całych

obiektach.

Niestety statystyki nie wnoszą żadnych informacji o rozkładzie samych danych, co może w wielu

sytuacjach być zgubne przy budowanie optymalnej ścieżki dostępu do danych. Weźmy za przy-

kład tabele zawierającą informacje o wieku osób. Tylko bardzo niewiele osób będzie w wieku

powyżej 100 lat. Informacja o rozkładzie statystycznym danych mogłaby być bardzo pomocna

podczas realizacji zapytań mających wyszukać osoby starsze niż 100 lat. Jeśli tabela z danymi o

osobach jest duża, wykonanie na niej full-scana będzie bardzo kosztowne, a przecież Oracle mógł-

by się posłużyć się w tym przypadku istniejącym indeksem i dotrzeć do danych błyskawicznie.

Histogramy tworzymy następującym poleceniem :

ANALYZE TABLE table_name [ESTIMATE…|CALCULATE…]

{FOR COLUMNS column_list |

FOR ALL COLUMNS |

FOR ALL INDEXED COLUMNS }

SIZE n

4. Dostęp do danych w tabeli – tuning

W rozdziale tym przedstawię kilka sposobów dostępu do danych w tabeli. Przedstawię ich po-

równanie oraz sposoby zwiększenia wydajności dostępu do danych.

Optymalizator nie zawsze ma dyspozycji wszystkie potrzebne informacje, aby wybrać najbardziej

optymalny sposób dostępu do danych, co więcej ma na to wielokrotnie mniej czasu niż osoba pi-

sząca zapytanie w języku SQL.

Powszechnie, choć niesłusznie przyjęła się zasada unikania metody full-scan w dostępie do da-

nych. Często full-scan może okazać się metodą mniej kosztowną od dostępu poprzez indeks. Dzie-

je się tak, choćby i z tej przyczyny, iż dostęp przez indeks wymaga odczytu zarówno bloków in-

deksu oraz zwykle bloków danych. Co więcej może wiązać się to z naprzemiennym czytaniem

bloków danych i indeksu, co jeszcze bardziej zwiększa ruch. W literaturze znajduje się wiele

szkół mówiących, przy odczycie ilu procent danych zaczyna się opłacać stosowanie metody full-

scan.

Doznania empiryczne przedstawia rysunek 2. Zostały na nim zobrazowane czasy wykonywania

zapytań wykonanych przy użyciu indeksów jak i przy użyciu metody full-scan [dla wartości para-

364

Krzysztof Kawa

metru DB_FILE_MULTIBBLOCK_READ_COUNT 1 8 oraz 32] względem procentu odczytywa-

nych danych

Porównanie szybkości w dostępie do tabeli przy

pomocy indeksu oraz metody full-scan

Full DFMR=8

Full DFMR=32

Index nieposortowane

dane

Rys. 2. Porównanie szybkości w dostępie do tabeli przy pomocy indeksu oraz metody full-scan

Jak można łatwo odczytać z wykresu już przy odczycie około 40% wszystkich wierszy tabeli

zaczyna opłacać się korzystanie z metody full-scan.

Optymalizator kosztowy w porównaniu do optymalizatora regułowego, ma dostęp do informa-

cji statystycznych o tabelach. Tym samym finalna decyzja, jaka metoda dostępu zostanie użyta

przy odczycie danych z tabeli zostanie podjęta m. in. na podstawie:

•

ilości bloków zaalokowanych przez tabele [poniżej high-water mark]

•

rozmiaru bloku danych

•

selektywności indeksów

•

głębokości indeksu – ilości IO operacji potrzebnych do odczytania bloku danych

Kiedy Oracle użyje FULL SCAN ?

Czasem przygotowane zapytanie wymusza na optymalizatorze wybranie metody full-scan, mi-

mo iż nie musi być ona optymalną metodą do uzyskania pożądanego rezultatu. Poniżej przedsta-

wiam kilka przykładów.

Użycie operatora <>

W przypadku zastosowania operatora <> w zapytaniu powodujemy, iż Oracle zastosuje metodę

dostępu full-scan. Wydaje się to nawet sensowne; szybciej jest, bowiem przejrzeć wszystkie krotki

(full scan )pominąwszy te, które nie spełniają warunków zapytania

Rozpatrzmy plany wykonań 2 zapytań :

1 DB_FILE_MULTIBBLOCK_READ_COUNT określa liczbę bloków, która może być odczytana podczas pojedynczej operacji IO

oracle - strojenie zapytañ sql(1).pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: