Helion, O'Reilly - Optymalizacja Oracle SQL - Leksykon kieszonkowy.pdf - Book - Infomatyka - rafulus

Optymalizacja Oracle SQL

Leksykon kieszonkowy

Wstęp

Książka niniejsza stanowi skrócony podręcznik strojenia Oracle SQL. Nie stanowi ona jednak

wyczerpującego kompendium wiedzy dotyczącego tego zagadnienia.

Zadaniem „Leksykonu" jest przedstawienie Czytelnikowi pewnych informacji dotyczących

praktycznych doświadczeń związanych ze strojeniem. Doświadczenia te Autor zdobył

pracując w swojej firmie Mark Gurrey & Associates. Firma ta jest odpowiedzialna za proces

dostrajania w wielu dużych ośrodkach. Ośrodki, takie jak banki, duże instytucje finansowe,

giełdy papierów wartościowych i elektrownie są szczególnie narażone na problemy związane

z niewydajnym działaniem systemu.

Przy wzrastającym zapotrzebowaniu na działanie systemów w trybie 24/7 konieczność

wydajnego działania poleceń języka SQL w systemie produkcyjnym staje się jeszcze

ważniejsza. Kiedy wprowadzane jest nowe polecenie języka SQL, należy upewnić się, że

będzie ono wykonywane wydajnie. Wprowadzenie nowego indeksu oznacza konieczność

upewnienia się, że istniejące już polecenia SQL będą prawidłowo go wykorzystywać. Książka

niniejsza omawia właśnie te kwestie.

Wiele ośrodków wykorzystuje obecnie pakiety oprogramowania pochodzące od różnych

dostawców (na przykład Peoplesoft, SAP, Oracle Applications, Siebel, Keystone i inne).

Strojenie języka SQL w przypadku tych aplikacji nie może polegać na umieszczaniu

wskazówek (hints) w poleceniach SQL, ponieważ użytkownik nie jest upoważniony do

modyfikowania kodu aplikacji. Oczywiście z tych samych względów nie można kodu SQL

przepisywać. Nie należy jednak zapominać, że mimo to istnieje wiele porad i podpowiedzi

pomocnych przy dostrajaniu gotowego oprogramowania.

Książka niniejsza ma w zamierzeniu Autora wyrazić jego głębokie przekonanie, że zawsze

istnieje sposób zwiększenia wydajności działania systemu tak, aby była ona możliwa do

zaakceptowania przez użytkowników.

Podziękowania

Autor składa podziękowania wydawcy Jonathanowi Gennick. Jego komentarze i sugestie w

ogromnym stopniu wpłynęły na jakość i czytelność tej książki. Autor dziękuje także

zespołowi redaktorów technicznych, wśród których byli: Sanjay Mishra, Stephen Andert oraz

Tim Gorman. Podziękowania za wsparcie techniczne należą się także konsultantom z firmy

Mark Gurry & Associates. Autor pragnąłby także złożyć szczególne podziękowania żonie -

Julianie - za wyrozumiałość w okresie pisania niniejszej książki.

Uwagi

W książce niniejszej nie omówiono wszystkich rodzajów środowiska pracy ani wszystkich

scenariuszy poprawiania wydajności za pomocą strojenia, jakie stosować może administrator

lub programista systemu Oracle.

Autor pragnie podkreślić w tym miejscu wagę regularnie przeprowadzanych samodzielnych

testów, które powinny służyć przygotowaniu do zastosowania własnych sposobów

zwiększenia wydajności działania.

Przyjęte konwencje

DUŻE LITERY

Wskazują słowo kluczowe języka SQL.

kursywa

Stosowana jest w celu uwydatnienia pewnych wyrażeń oraz terminów w języku angielskim.

małe litery

Wykorzystywane są dla elementów definiowanych przez użytkownika, takich jak nazwy przestrzeni tabel i

nazwy plików danych.

czcionka o stałej szerokości

Stosowana jest w przykładowych kodach programów.

czcionka pogrubiona o stałej szerokości

Stosowana jest w celu uwydatnienia fragmentów w przykładowych kodach

programów.

[]

Oznacza opis składni poleceń; nawias kwadratowy stosowany jest w celu wskazania

elementu opcjonalnego.

{}

Oznacza opis składni poleceń; nawias klamrowy stosowany jest w celu wskazania

konieczności wyboru elementu.

Oznacza opis składni poleceń; znak ten stosowany jest w celu oddzielenia od siebie

elementów wymaganego wyboru.

Nowe funkcje systemu Oracle9i

Rozpoczynanie pracy z nową wersją systemu Oracle zawsze jest ekscytujące. W podrozdziale

niniejszym wymieniono pokrótce nowe mechanizmy systemu Oracle w wersji 9i, które są

zdolne w jeszcze większym stopniu podnieść wydajność działania kodu SQL. Nowe funkcje

wymieniono poniżej.

• Nowy parametr w pliku konfiguracyjnym INIT.ORA, FIRST_ROWS _n, który pozwala,

aby optymalizator kosztowy podejmował jeszcze lepsze decyzje dotyczące optymalnego

scenariusza wykonania w przypadku aplikacji OLTP. Wartość n może być równa 1. 10, 100

lub 1000. Jeśli parametr zostanie określony jako FIRST_ ROWS_1. system Oracle określi

optymalny scenariusz wykonania w celu zwrócenia jednego wiersza; FIRST_ROWS_10

oznaczać będzie optymalny scenariusz dla zwrócenia 10 wierszy itd.

W przypadku stosowania parametru CURSOR_SHARING istnieje nowa opcja o nazwie

SIMILAR. Do zalet współużytkowania kursora zaliczyć należy mniejsze zużycie pamięci,

szybszą analizę składniową oraz zmniejszone konflikty blokad niskiego poziomu (latch

contention). Opcja SIMILAR odpowiada za zamianę literałów na zmienne wiązane i różni się

od opcji FORCE tym, że podobne instrukcje mogą współużytkować ten sam obszar pamięci

SQL bez powodowania rozbicia scenariuszy wykonania.

• Istnieje nowa wskazówka o nazwie CURSOR_SHARING_EXACT, co pozwala na

współużytkowanie kursorów przez wszystkie polecenia oprócz tych. które zawierają

powyższą wskazówkę. W istocie anuluje ona współużytkowanie kursora przez dane

polecenie.

• Znacznie ulepszono mechanizm zapobiegania problemowi asymetrii (skewness problem).

Pojawia się on wówczas, gdy wartość zmiennej wiązanej jest obliczana już po określeniu

scenariusza wykonania. Jeśli pewna tabela zawiera l 000 000 wierszy zawierających pole.

STATUS='C', co oznacza Closed (zamknięte), oraz 100 wierszy z polem STATUS='O' w

znaczeniu Open (otwarte), to system Oracle powinien użyć indeksu w kolumnie STATUS w

momencie pojawienia się zapytania dotyczącego STATUS= ' O ', a ponadto powinien

przeprowadzić przegląd całej tabeli (full table scan) w przypadku pojawienia się zapytania

dotyczącego STATUS='C'. Korzystanie ze zmiennych wiązanych przed wprowadzeniem

Oracle9i oznaczało, że system zakładał równomierny (50/50) rozkład obu wartości i w obu

przypadkach korzystał z przeglądu całej tabeli. System Oracle9i określa wartość zmiennej

wiązanej przed określeniem planu wykonania, co rozwiązuje cały problem.

• Istnieje możliwość zidentyfikowania nieużywanych indeksów przy użyciu polecenia

ALTER INDEX MONITOR USAGE.

• Można wykorzystać pakiet DBMS_STATS do zbierania systemowych danych

statystycznych, a w tym także tych, które dotyczą użycia procesora i operacji wejścia-wyjścia.

Jeśli okaże się, że „wąskie gardło" stanowią dyski twarde, system Oracle będzie posiadał

informacje potrzebne do odpowiedniego dostosowania planów wykonania.

Istnieją nowe wskazówki, takie jak NL_AJ, NL_SJ, FACT, NO_FACT oraz FIRST_ROW(n).

Wszystkie z nich zostały opisane szczegółowo w rozdziale zatytułowanym „Korzystanie ze

wskazówek SQL", który znajduje się w niniejszej książce.

• W systemie Oracle8i wprowadzono scenariusze (outlines) umożliwiające wymuszanie

planów wykonania (określanych w tym wypadku właśnie jako „scenariusze") dla określonych

poleceń SQL. Jednak czasem niebanalnym problemem było wymuszenie tego, aby dana

instrukcja SQL wykonywana była zgodnie z określonym scenariuszem. System Oracle9i

zapewnia rozwiązanie: obecnie istnieje możliwość edycji scenariusza przy użyciu pakietu

DBMS_OUTLN_EDIT.

Optymalizatory SQL

Kiedy użytkownik wykonuje polecenie SQL, jeden z komponentów systemu bazy danych

zwany optymalizatorem musi podjąć decyzję dotyczącą najlepszego sposobu dostępu do

danych, którymi polecenie to operuje. System Oracle zawiera dwa optymalizatory:

optymalizator regułowy (rule-based optimizer, był on wprowadzony jako pierwszy) oraz

optymalizator kosztowy (cost-based optimizer).

W celu określenia optymalnego scenariusza wykonania optymalizatory biorą pod uwagę

następujące kwestie:

• składnia wydanego polecenia;

• warunki, które muszą spełniać dane (wyrażenia WHERE);

• tabele bazy danych, do których dostępu wymaga polecenie;

• wszystkie możliwe indeksy, jakie mogą być wykorzystane do pobrania danych z tabeli;

• wersja systemu zarządzania bazą danych Oracle;

• bieżący tryb optymalizatora;

• wskazówki polecenia SQL;

• wszystkie dostępne statystyki dotyczące obiektów (utworzone za pomocą polecenia

ANALYZE);

• fizyczna lokalizacja tabel (rozproszony SQL);

ustawienia w pliku IN1T.ORA (zapytania równoległe, asynchroniczne operacje wejścia-

wyjścia itd.).

System Oracle oferuje możliwość wyboru jednej z możliwości optymalizacji: przewidywalny

optymalizator regułowy lub bardziej „inteligentny" optymalizator kosztowy.

Działanie optymalizatora regułowego

Optymalizator regułowy (rule based optimizer, RBO) w celu określenia ścieżki dostępu

(access patii) do danych bazy danych korzysta z reguł pierwszeństwa. Jądro systemu

zarządzania bazą danych korzysta z optymalizatora regułowego, gdy:

• w pliku INIT. ORA znajduje się wpis OPTIMIZER_MODE = RULE:

• w pliku INIT.ORA znajduje się wpis OPTIMIZER_MODE = CHOOSE oraz dla żadnej

tabeli związanej z wykonywanym poleceniem nie utworzono statystyk;

• wydano polecenie ALTER SESSION SET OPTIMIZER_MODE = RULE;

• wydano polecenie ALTER SESSION SET 0PTIMIZER_MODE = CHOOSE oraz dla

żadnej tabeli związanej z wykonywanym poleceniem nie utworzono statystyk;

• w wykonywanym poleceniu użyto odpowiedniej wskazówki (na przykład SELECT /*+

RULE */...).

Działanie optymalizatora regułowego opiera się w głównej mierze na 20 rangach warunków

(czyli „złotych regułach"). Reguły te pozwalają optymalizatorowi określić ścieżkę wykonania

danego polecenia, podpowiadają, kiedy wykorzystać jeden indeks zamiast drugiego, a kiedy

przeprowadzić przegląd całej tabeli. Reguły te przedstawiono w tabeli 1. Są one niezmienne,

ustalone odgórnie i - w przeciwieństwie do optymalizatora kosztowego - nie mają na nie

wpływu czynniki zewnętrzne (rozmiary tabel, rozkłady indeksów itd.)

Choć znajomość reguł jest pomocna, to nie mówią one zbyt wiele o tym, jak przeprowadzać

strojenie za pomocą optymalizatora regułowego. Poniższe podrozdziały prezentują

informacje, które pomogą Czytelnikowi uzupełnić te braki.

Reguły RBO o tym nie mówią #1

Jedynie indeksy pojedynczych kolumn podlegają scaleniu. Rozpatrzmy następujące polecenie

SQL oraz indeksy:

SELECT col1, ...

FROM emp

WHERE emp_name = 'GURRY'

AND emp_no = 127

AND dept_no = 12;

Indexl (dept_no)

Index2 (emp_no, emp_name)

Instrukcja SELECT przegląda wszystkie trzy kolumny posiadające indeks. Wiele osób sądzi,

że w celu zwrócenia wymaganych danych system Oracle scali oba indeksy, a zatem pośrednio

wszystkie trzy kolumny. W rzeczywistości jednak używany jest tylko indeks dwukolumnowy,

zaś indeks jednokolumnowy pozostaje niewykorzystany. System Oracle scaliłby dwa indeksy

jednokolumnowe, ale nie scali indeksu wielokolumnowego z innym.

Tabela 1. Warunki szeregowania optymalizatora regułowego

Ranga | Warunek

1 ROWID' = wartość stała

2 Złączenie klastrowe (cluster join) z kluczem unikatowym lub głównym =

wartość stała

3 Klucz klastra haszowanego (hash cluster) z kluczem unikatowym lub głównym

= wartość stała

4 Cały unikatowy indeks skonkatenowany = wartość stała

5 Kolumna z indeksem unikatowym = wartość stała

6 Cały klucz klastra = odpowiedni klucz klastra z innej tabeli w tym samym

klastrze

7 Klucz klastra haszowanego = wartość stała

8 Cały klucz klastra = wartość stała

9 Cały nieunikatowy indeks skonkatenowany = wartość stała

10 Scalenie indeksu nieunikatowego

11 Cały indeks skonkatenowany = ograniczenie dolne

12 Większość wiodących kolumn indeksu unikatowego = wartość stała

13 Zaindeksowana kolumna pomiędzy dolną a górną wartością lub

zaindeksowana kolumna LIKE " ABC % " (przedział ograniczony)

14 Zaindeksowana nieunikatowa kolumna pomiędzy dolną a górną wartością lub

zaindeksowana kolumna LIKE " ABC % " (przedział ograniczony)

15 Unikatowa kolumna zaindeksowana lub wartość stała (przedział

nieograniczony)

16 Nieunikatowa kolumna zaindeksowana lub wartość stała (przedział

nieograniczony)

17 Równość kolumn niezaindeksowanych = kolumna lub wartość stała (złączenie

przez sortowanie i scalenie)

18 MAX lub MIN pojedynczych kolumn w indeksach

19 ORDER BY całego indeksu

20 Przeglądy całej tabeli

ROWID - identyfikator wiersza; identyfikuje każdy wiersz poprzez jego lokalizację lub adres

- przyp. tłum.

Mając na względzie powyższe uwagi - należy pamiętać o jednej rzeczy. Jeśli indeks

jednokolumnowy jest indeksem klucza unikatowego lub głównego, sprawia to. że indeks

jednokolumnowy ma pierwszeństwo przed indeksem wielokolumnowym. Wystarczy

porównać rangę 4. z rangą 9. z tabeli 1.

U WAGA

W systemie Oracle8i wprowadzono nową wskazówkę, INDEX_ JOIN, która pozwala na

scalanie indeksów wielokolumnowych.

Reguły RBO o tym nie mówią #2

Jeśli wszystkie kolumny należące do pewnego indeksu zostały wymienione w wyrażeniu

WHERE, wówczas indeks taki będzie miał pierwszeństwo przed innymi (w przypadku

których odwołanie nastąpiło tylko do części ich kolumn). Na przykład:

SELECT col1, ...

FROM emp

WHERE emp_name = 'GURRY'

AND emp_no - 127

AND dept_no = 12;

Indexl (emp_name)

Index2 (emp_no, dept_no, cost_center)

W przykładzie powyższym wykorzystany zostanie Index1, ponieważ w wyrażeniu WHERE

zawarto wszystkie jego kolumny, a nie jest tak w przypadku Index2.

Reguły RBO o tym nie mówią #3

Jeśli wiele indeksów odpowiada specyfikacji wyrażenia WHERE i wszystkie posiadają taką

samą liczbę kolumn, wówczas użyty zostanie indeks utworzony jako ostatni. Na przykład:

SELECT col1, ...

FROM emp

WHERE emp_name = 'GURRY'

AND emp_no - 127

AND dept_no = 12

AND emp_category = 'CLERK';

Indexl (emp_name, emp_category) Utworzony 16.00 11 Lut 2002

Index2 (emp_no, dept_no) Utworzony 17.00 11 Lut 2002

W przykładzie powyższym wykorzystany zostanie tylko Index2, ponieważ został on

utworzony o godzinie 17.00, zaś drugi z indeksów o godzinie 16.00. Takie zachowanie

optymalizatora może spowodować pewne problemy, ponieważ po odbudowaniu indeksów w

zmienionej w stosunku do początkowej kolejności może nagle okazać się, że do wykonywania

zapytań używany jest inny indeks. W celu obejścia tego problemu w wielu ośrodkach

wprowadza się standard nazywania indeksów, który wymaga tego, aby w miarę ich tworzenia

nadawać im nazwy w kolejności alfabetycznej. Wówczas -jeśli tabela jest odbudowywana -

indeksy mogą zostać odbudowane w kolejności alfabetycznej, co pozwala zachować

poprawną kolejność tworzenia. Indeksy można także numerować. W takiej sytuacji każdy

nowo dodany do tabeli indeks opatrzony będzie kolejną liczbą.

Reguły RBO o tym nie mówią #4

Jeśli następuje dostęp do wielu kolumn indeksu przy użyciu operatora =, to ma on

pierwszeństwo przed innymi operatorami, takimi jak LIKE lub BETWEEN. Wykorzystanie

dwóch operatorów = daje pierwszeństwo przed wykorzystaniem dwóch operatorów = oraz

jednego LIKE. Na przykład:

SELECT col1, ...

FROM emp

WHERE emp_name LIKE 'GURRY'

AND emp_no = 127

AND dept_no - 12

AND emp_category = 'CLERK'

AND emp_class = 'Cl';

Indexl ( emp_category, emp_class, emp_name) Index2 (emp_no, dept_no)

W przykładzie powyższym wykorzystane zostanie tylko Index2, pomimo że następuje

Helion, O'Reilly - Optymalizacja Oracle SQL - Leksykon kieszonkowy.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: