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:
AND emp_no - 127
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:
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:
WHERE emp_name LIKE 'GURRY'
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 odwołanie do trzech kolumn Indexl w porównaniu z dwoma, jakie posiada Index2.
Reguły RBO o tym nie mówią #5
Wyższy odsetek kolumn, do jakich następuje odwołanie, daje pierwszeństwo przed niższym odsetkiem. Ogólnie rzecz biorąc - optymalizator wybierze ten indeks, w przypadku którego określi się największy odsetek posiadanych przez niego kolumn. Jednak - jak wcześniej wiedziano - wszelkie kolumny określone w indeksie klucza unikatowego lub głównego dają pierwszeństwo przed użyciem jakichkolwiek innych indeksów. Na przykład:
WHEFE emp_name = 'GURRY'
AND emp_class - 'C1';
Indexl (emp_name, emp_class. emp_category)
Index2 (emp_no, dept_no)
W przykładzie powyższym wykorzystany zostanie tylko Index1, ponieważ następuje odwołanie do 67% jego kolumn. Index2 nie będzie wykorzystywany, gdyż w jego przypadku odwołanie dotyczy 50% zaindeksowanych kolumn.
...
homiczeski