helion%2c+o%27reilly+ +optymalizacja+oracle+sql+ +leksykon+kieszonkowy 5VYAOTMKBEMJVIKMQWPLPR26MA5UX57S22S74XY

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

ROWID' = wartość stała

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

Kolumna z indeksem unikatowym = wartość stała

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

klastrze
7

Klucz klastra haszowanego = wartość stała

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

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

Scalenie indeksu nieunikatowego

Cały indeks skonkatenowany = ograniczenie dolne

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

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

zaindeksowana kolumna

ABC

% " (przedział ograniczony)

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

zaindeksowana kolumna

ABC

% " (przedział ograniczony)

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

ORDER

całego indeksu

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

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:

SELECT col1, ...
FROM emp

WHEFE emp_name = 'GURRY'
AND emp_no = 127

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.

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

Jeśli dwie tabele podlegają złączeniu, optymalizator regułowy musi wybrać tabelę sterującą
(driving table). Wybrana tabela może mieć ogromny wpływ na wydajność. Dzieje się tak
szczególnie wtedy, gdy optymalizator zdecyduje o użyciu pętli zagnieżdżonych (nested
loops). Najpierw zostanie zwrócony wiersz pochodzący z tabeli sterującej,
a później odpowiadające wiersze zostaną wybrane z drugiej tabeli. Ważną kwestia jest
zapewnienie tego, aby z tabeli sterującej została wybrana jak najmniejsza liczba wierszy.
W celu wybrania tabeli sterującej optymalizator regułowy korzysta z przedstawionych poniżej
reguł.
• Posiadanie przez tabelę indeksu klucza unikatowego lub głównego zawsze daje jej
pierwszeństwo w wyborze jako tabeli sterującej przed tabelą nieposiadającą takiego indeksu.
• Indeks, w przypadku którego dostęp do jego wszystkich kolumn odbywa się przy użyciu
operatora =, ma pierwszeństwo przed indeksem, z którego wybierana jest tylko część kolumn,
co ma wpływ na wybór dla zapytania stosownej tabeli jako tabeli sterującej.
• Tabela, która posiada wyższy odsetek swoich kolumn w indeksie ma pierwszeństwo przed
tabelą o mniejszym odsetku kolumn w indeksie.
• Tabela zawierająca indeks dwukolumnowy, do którego następuje odwołanie w wyrażeniu
WHERE zapytania, ma pierwszeństwo przed tabelą zawierającą dwa indeksy
jednokolumnowe.
• Jeśli dwie tabele posiadają taką samą liczbę kolumn w indeksie, wtedy tabelą sterującą
staje się ta, którą w wyrażeniu FROM wymieniono jako ostatnią. W poniższym poleceniu
SQL tabelą sterującą stanie się tabela emp, ponieważ w wyrażeniu FROM wymieniono ją jako
ostatnią.
SELECT ....
FROM dept d, emp e

WHERE e.emp_name = 'GURRY'
AND d.dept_name - 'FINANCE'

AND d.dept_no - e.dept_no;

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

Jeśli w wyrażeniu WHERE pojawia się kolumna, która stanowi kolumnę wiodącą pewnego
indeksu, to optymalizator regułowy użyje właśnie
tego indeksu. Wyjątkiem jest sytuacja, gdy w wyrażeniu WHERE na wiodącą kolumnę

indeksu nałoży się określoną funkcję. Na przykład:
SELECT col1, . . .
FROM emp
WHERE emp_name - 'GURRY';
Indexl (emp_name, emp_class, emp_category)
Index2 (emp_class, emp_name, emp_category)
Użyty zostanie Indexl, ponieważ emp_name (występująca w wyrażeniu WHERE) stanowi
jego kolumnę wiodącą. Index2 nie zostanie wykorzystany, gdyż emp_name nie jest jego
kolumną wiodącą.
Poniższy przykład prezentuje sytuację, gdy wobec zaindeksowanej kolumny użyje się funkcji:

SELECT col1, ...
FROM emp

WHERE LTRIM(emp_name) = 'GURRY';
W tym przypadku - z uwagi na wykorzystanie funkcji LTRIM - nie zostanie użyty żaden
indeks.

Działanie optymalizatora kosztowego

Optymalizator kosztowy stanowi bardziej wyrafinowane narzędzie od optymalizatora
regułowego. W celu określenia najlepszej ścieżki wykonania polecenia zamiast ze sztywnych
reguł korzysta on z informacji o bazie danych, takich jak rozmiar tabel, liczba wierszy,
szerokość klucza itd.
Informacje wymagane przez optymalizator kosztowy stają się dostępne po wykonaniu w
odniesieniu do danej tabeli polecenia ANALYZE lub za pomocą pakietu DBMS_STATS.
Jeśli tabela nie została zanalizowana, w celu wyboru najlepszej ścieżki dostępu do danych
optymalizator kosztowy może korzystać tylko z logiki reguł. Możliwe jest wykonanie
scenariusza łączącego zachowanie kosztowe i regułowe poprzez zanalizowanie tylko części
tabel

UWAGA
Polecenie ANALYZE oraz funkcje pakietu DBMS_STATS zbierają statystyki na temat tabel,
klastrów oraz indeksów i zachowują je w słowniku danych (data dictionary).
Polecenie SQL jest domyślnie poddawane optymalizacji kosztowej, jeśli choć jedna tabela w
nim używana została zanalizowana. Optymalizator kosztowy podejmuje wówczas na
podstawie informacji ze słownika danych odpowiednie decyzje w celu określenia najlepszej
ścieżki dostępu w przypadku pozostałych tabel.
Jądro systemu zarządzania bazą danych korzysta z optymalizatora kosztowego, gdy:
• w pliku INIT.ORA znajduje się wpis OPTIMIZER_MODE = CHOOSE oraz istnieją
statystyki dla przynajmniej jednej z tabel występujących w poleceniu;
• wydano polecenie ALTER SESSION SET OPTIMIZER_MODE = CHOOSE oraz
utworzono statystyki dla przynajmniej jednej tabeli związanej z wykonywanym poleceniem;
• wydano polecenie ALTER SESSION SET OPTIMIZER_ MODS = FIRST_ROWS (lub
ALL_ROWS) oraz utworzono statystyki dla przynajmniej jednej tabeli związanej z
wykonywanym poleceniem:
• w wykonywanym poleceniu użyto wskazówki FIRST_ROWS lub ALL_ROWS (na
przykład SELECT /*+ FIRST_ROWS */...).
Polecenie ANALYZE

Sposób, w jaki analizowane są tabele, może mieć ogromny wpływ na wydajność poleceń
SQL. Jeśli administrator bazy danych zapomni o zanalizowaniu tabel lub indeksów po
odbudowaniu tabeli, wpływ takiego zaniedbania na wydajność może być bardzo negatywny.
Jeśli administrator wykonuje analizy co weekend, osiągnięta być może nowa
wartość progowa i system Oracle zmieni plan wykonania. Nowy plan będzie zazwyczaj

stanowił ulepszenie, choć czasem może okazać się gorszy od poprzedniego.
Trudno w wystarczająco silny sposób podkreślić, że jeśli wszystkie polecenia SQL zostały
dostrojone, to nie należy przeprowadzać analizy tylko dla samej analizy. W jednym z
ośrodków, w którym Autor przeprowadzał dostrajanie, polecenie SQL o krytycznym
znaczeniu zwracało dane w czasie krótszym niż sekunda. Jednak administrator co weekend
przeprowadzał analizy wierząc, że ścieżka wykonania będzie ulegać dalszej poprawie. Nic
dziwnego, że pewnego dnia okazało się, że czas odpowiedzi wydłużył się do 310 sekund.
Jeśli chcemy analizy przeprowadzać często, to przed wykonaniem ponownej analizy należy
skorzystać z procedury DBMS_STATS. EXPORT_SCHEMA_STATS w celu utworzenia
kopii zapasowej istniejących statystyk. Daje to możliwość przywrócenia poprzednich
statystyk w sytuacji, gdy coś pójdzie nie tak.
Podczas przeprowadzania analizy można nakazać systemowi Oracle, aby przeglądał wszystkie
wiersze tabeli (ANALYZE_COMPUTE) lub tylko część wierszy (ANALYZE_ESTIMATE).
W typowej sytuacji Autor korzysta z opcji ANALYZE_ESTIMATE dla bardzo dużych tabel
(l 000 000 lub więcej wierszy) oraz ANALYZE_COMPUTE dla tabel małych i średnich.
Bardzo wskazane jest użycie podczas analizy opcji FOR ALL INDEXED COLUMNS dla
każdej tabeli, którą może charakteryzować silna asymetria danych. Jeśli na przykład duży
odsetek wierszy w tabeli ma taką samą wartość w danej kolumnie, to jest to właśnie przykład
asymetrii. Użycie opcji FOR ALL INDEXED COLUMNS sprawia, że optymalizator
kosztowy poza licznością (liczba różnych wartości) będzie także posiadał informację o
asymetrii danych w kolumnie.
Podczas analizowania tabeli za pomocą polecenia ANALYZE analizowane są także wszystkie
związane z nią indeksy. Jeśli później indeks zostanie usunięty i odtworzony, musi zostać
ponownie zanalizowany. Warto pamiętać, że procedury DBMS_STATS .
GATHER_SCHEMA_STATS oraz DBMS_TABLE_STATS domyślnie analizują jedynie
tabele (nie ich indeksy). Wykorzystując te procedury należy w przypadku indeksów użyć opcji
CASCADE=>TRUE tak, aby także one zostały zanalizowane.
Poniżej przedstawiono pewne przykłady użycia polecenia ANALYZE:

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL
INDEXED COLUMNS;

ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5
PERCENT FOR ALL INDEXED_COLUMNS;

ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED
COLUMNS ;
Jeśli zanalizuje się tabelę omyłkowo, można statystyki te usunąć. Na przykład:

ANALYZE TABLE EMP DELETE STATISTICS;
Proces analizy może zająć bardzo dużo czasu, jeśli zastosuje się opcję COMPUTE dla dużych
obiektów. Okazuje się, że niemal przy każdej okazji wykonanie polecenia ANALYZE
ESTIMATE 5 PERCENT dla dużej tabeli sprawia, że optymalizator podejmuje taką samą
decyzję jak przy użyciu ANALYZE COMPUTE.

Strojenie bazy danych

przed wejściem w fazę produkcyjną

Głównym dylematem związanym z wykorzystaniem optymalizatora kosztowego (cost-bascd
optimizer, CBO) jest sposób strojenia poleceń SQL jeszcze przed wejściem bazy danych w
fazę produkcyjną. Większość baz rozwojowych i testowych zawiera znacznie mniej danych
niż produkcyjna baza danych. Jest zatem bardzo prawdopodobne, że CBO będzie podejmował
różne decyzje dotyczące planu wykonania. Wiele ośrodków nie stać na koszta i niedogodności
związane z kopiowaniem produkcyjnej bazy danych do bazy przedprodukcyjnej.
Systemy Oracle8i i nowsze oferują różne mechanizmy mające na celu rozwiązanie tego
problemu, a w tym pakiet DBMS_STATS oraz mechanizm scenariuszy (outlines). Opisano je
bardziej szczegółowo w dalszej części książki.

Wewnętrzne działanie optymalizatora kosztowego
W przeciwieństwie do optymalizatora regułowego optymalizator kosztowy nie posiada
sztywnych reguł umożliwiających szybkie określenie ścieżki wykonania. CBO jest
narzędziem elastycznym i może dostosować się do danego środowiska pracy. Dostosowanie
takie jest możliwe wtedy, gdy zostaną odświeżone (ponownie zanalizowane) statystyki
dotyczące wymaganych obiektów. Stała jest tylko metoda, dzięki której optymalizator
kosztowy oblicza każdy możliwy plan wykonania i ocenia jego koszt (efektywność).
Działanie optymalizatora kosztowego można (nieformalnie) rozbić na następujące etapy:
1. Analiza składniowa polecenia SQL (sprawdzenie składni, uprawnień posiadanych przez
obiekty itd.).
2. Utworzenie listy wszystkich potencjalnych planów wykonania.
3. Obliczenie (oszacowanie) kosztu każdego z planów wykonania przy wykorzystaniu
dostępnych statystyk obiektów.
4. Wybranie planu wykonania o najniższym koszcie.
Optymalizator kosztowy jest używany wtedy, gdy choć jedna tabela z polecenia SQL posiada
statystyki (statystyki dla tabel niezanalizowanych są szacowane). Jeśli nie ma dostępnych
statystyk dla żadnej z tabel występujących w poleceniu, system bazy danych stosuje
optymalizator regułowy, chyba że użycie optymalizatora kosztowego wymuszono wcześniej
wskazówkami poziomu polecenia (hints) lub opcją optymalizatora ALL_ROWS lub
FIRST_ROWS.
Zrozumienie działania optymalizatora kosztowego - a w konsekwencji umiejętność jego
wykorzystania - wymaga zrozumienia sposobu jego „myślenia".

Równość klucza głównego i (lub) indeksu unikatowego
Selektywność indeksu unikatowego (zawierającego w swojej definicji słowo kluczowe
UNIQUE) wynosi 100%. Żadna inna metoda dostępu do danych z użyciem indeksu nie może
być lepsza. Z tego względu indeksu unikatowego używa się zawsze, gdy jest dostępny.

Równość indeksu nieunikatowego
W przypadku indeksów nieunikatowych obliczana jest ich selektywność. Optymalizator
kosztowy przyjmuje założenie, że tabela (oraz odpowiednie indeksy) posiada dane rozłożone
równomiernie, chyba że w poleceniu ANALYZE użyto opcji FOR ALL INDEXED
COLUMNS. Opcja ta powoduje, że optymalizator otrzymuje informacje o ewentualnej
asymetrii danych w zaindeksowanych kolumnach.

Obliczanie zakresu
W przypadku planów wykonania dotyczących zakresów wartości indeksów obliczana jest ich
selektywność. Obliczenia te oparte są na wartościach górnych i dolnych ostatnio utworzonych
statystyk. Optymalizator kosztowy ponownie przyjmuje założenie dotyczące równomiernego
rozkładu danych w tabeli (oraz odpowiednich indeksach), chyba że podczas procesu
analizowania tabeli określono opcję FOR ALL INDEXED COLUMNS.

Obliczanie zakresu na podstawie zmiennych dowiązanych
W tym przypadku dla planów wykonania dotyczących zakresów indeksów selektywność jest
odgadywana. Przed wprowadzeniem systemu Oracle9i - z uwagi na fakt, że wartości
zmiennych dowiązanych (bind variables) nie były dostępne w czasie dokonywania analizy
składniowej (wartości były przekazywane do kursora już po określeniu planu wykonania) -
optymalizator nie mógł podjąć decyzji na podstawie tych wartości. Zgodnie z praktyczną
regułą zakładał więc 25% selektywność dla nieograniczonych zakresów zmiennych
dowiązanych (na przykład WHERE dept_no = :b1) oraz 50% selektywność dla zakresów
ograniczonych (na przykład WHERE dept_no > :b1 AND dept_no < :b2). Począwszy od

systemu Oracle9i optymalizator otrzymuje wartości zmiennych dowiązanych przed
określeniem planu wykonania.

Histogramy
Przed wprowadzeniem w systemie Oracle 7.3 histogramów, optymalizator kosztowy nie był w
stanie wyróżnić silnie nierównomiernego rozkładu danych kluczowych.

Wykorzystanie zasobów systemowych
Domyślnie optymalizator kosztowy zakłada, że dany użytkownik jest jedynym, który pracuje z
bazą danych. W systemie Oracle9i możliwe jest przechowywanie informacji o wykorzystaniu
zasobów systemowych, co pozwala podjąć znacznie lepsze decyzje na podstawie obciążenia
(procedura DBMS_STATS. GATHER_SYSTEM_STATS).

Istotne są statystyki bieżące
Optymalizator kosztowy może niepoprawnie wybrać plan wykonania, jeśli tabela została
zanalizowana, ale nie zostały zanalizowane jej indeksy, a także wtedy, gdy indeksy zostaną
zanalizowane, a tabela nie.
Nie powinno się nakazywać systemowi wykorzystania optymalizatora kosztowego poprzez
dołączone wskazówki, kiedy dla żadnej tabeli z polecenia SQL nie są dostępne statystyki.
Korzystanie ze starych (nieaktualnych) statystyk może nieść ze sobą większe
niebezpieczeństwo niż szacowanie statystyk w czasie wykonania. Pamiętać jednak należy, że
częste zmienianie statystyk także może być niewskazane (szczególnie w przypadku systemów
o znaczeniu strategicznym, z których korzysta wielu użytkowników). Zawsze należy tworzyć
kopie zapasowe statystyk za pomocą procedury DBMS_STATS .
EXPORT_SCHEMA_STATS przed ponownym analizowaniem.
Analiza dużych tabel oraz związanych z nimi indeksów przy użyciu opcji COMPUTE zajmuje
dużo czasu, wymaga sporych zasobów procesora, systemu wejścia-wyjścia oraz tymczasowej
przestrzeni tabel. Jest to często niepotrzebne. Analiza ze spójną wartością (na przykład
ESTIMATE 3%) zazwyczaj pozwala na to, by optymalizator podejmował optymalne decyzje.
Połączenie informacji otrzymanych dzięki zasadom selektywności z innymi informacjami o
systemie wejścia-wyjścia bazy danych pozwalają optymalizatorowi obliczyć koszt planu
wykonania.

Opcja EXPLAIN PLAN

optymalizatora kosztowego
System Oracle może przedstawić informacje dotyczące kosztu wykonania zapytania, kiedy
użyjemy funkcji EXPLAIN PLAN. Funkcję EXPLAIN PLAN można wykorzystać do
wyświetlenia obliczonych kosztów wykonania po wprowadzeniu pewnych rozszerzeń. W
szczególności kolumna COST tabeli planu zawiera wartość, której zmniejszanie i zwiększanie
odzwierciedla względny koszt wykonania zapytania. Na przykład:

EXPLAIN PLAN FOR SELECT COUNT(*)
FROM winners, horses WHERE winners.owner = horses.owner AND

winners.horse_name LIKE 'Mr %';
COLUMN "SQL" FORMAT a56

SELECT lpad(' ',2*level)| operation||''
|[options ' ' jobject_namej | decode(OBJECT„TYPE, •', •',

(' | |obj ect_type| | ') ') "SQL", cost "Cost", cardinality

"Num Rows" FROM plan_table

CONNECT BY PRIOR id = parent_id START WITH id = 0;
SQL

Cost

Num Rows

SELECT STATEMENT

44 l

SORT AGGREGATE

HASH JOIN

100469

INDEX RANGĘ SCAN MG1(NON-UNIQUE)

1471

INDEX FAST FULL SCAN OWNER_PK(UNIQUE)

6830

Manipulując optymalizatorem kosztowym (na przykład poprzez dołączone wskazówki,
poprzez tworzenie lub usuwanie indeksów, poprzez dostosowywanie sposobu, w jaki
analizowane są indeksy lub tabele)
można dostrzec różnice w koszcie wykonania obliczonym przez optymalizator. Warto używać
EXPLAIN PLAN w celu porównania różnych wersji zapytania i wybrać wersję o najniższym
koszcie względnym.
W celu osiągnięcia absolutnie optymalnej wydajności w wielu ośrodkach analizuje się
większość tabel i indeksów, natomiast mała liczba tabel, które używane są oddzielnie, nie
podlega analizie. Ma to zazwyczaj zapewnić zachowanie oparte na regułach dotyczących
tabel, które nie są analizowane. Jednakże istotną kwestią jest zapewnienie tego, aby tabele
nieanalizowane nie były złączane z tabelami analizowanymi.

Częste nieporozumienia

związane z optymalizatorami

Warto spróbować w tym miejscu wyjaśnić pewne nieporozumienia dotyczące
optymalizatorów.
Systemy Oracle8i oraz Oracle9i nie korzystają z optymalizatora regułowego
Absolutnie nie jest to prawdą. Część wydawnictw podawała takie informacje jakiś czas temu,
ale firma Oracle zapewnia, że z pewnością tak nie jest.
Wskazówek nie można używać w przypadku optymalizatora regułowego
Większość wskazówek może być z powodzeniem stosowana w poleceniach SQL
korzystających z optymalizatora regułowego.
Polecenie SQL dostrojone według reguł będzie wykonywane wydajnie według kosztów
Przy dużej dozie szczęścia może się tak zdarzyć, jednak przechodząc do optymalizatora
kosztowego trzeba być przygotowanym na konieczność dostrojenia wielu poleceń SQL. Autor
nie spotkał jednak systemu, w którym nie byłoby możliwe dostrojenie po takiej zmianie.
Polecenie SQL dostrojone według kosztów będzie wykonywane
wydajnie według reguł
Jest to mało prawdopodobne, chyba że kod został napisany z uwzględnianiem cech
optymalizatora regułowego.
Nie można równolegle używać optymalizatora regułowego i kosztowego Można korzystać z
obu optymalizatorów, jeśli w pliku INIT.ORA parametrowi OPTIMIZER_MODE nada się
wartość CHOOSE, a ponadto część tabel w systemie zostanie zanalizowana, a część nie.
Należy uważać na to, aby nie złączać tabel, które zostały zanalizowane z tabelami, które nie
zostały poddane analizie.

Wybór optymalizatora

Jeśli Czytelnik korzysta obecnie z optymalizatora regułowego, ze wszech miar wskazane jest
przejście do optymalizatora kosztowego. Powodów takiego postępowania jest wiele.
• Czas poświęcony na kodowanie ulega skróceniu.
• Programista nie musi znać reguł.
• Optymalizator ten posiada więcej funkcji i o wiele więcej narzędzi służących do strojenia.
• Znacznie zwiększyły się szansę na znalezienie pakietów pochodzących od różnych
dostawców, które jednak działają wydajnie. Wiele takich pakietów opracowuje się pod kątem
uruchamiania na platformach DB2, Informix, SQL*Server, a także Oracle. Ich kod nie jest
zatem pisany tak, by odpowiadał potrzebom optymalizatora regułowego, lecz ma postać

ogólną.
• Użytkownicy końcowi mogą rozwijać dostrojony kod bez konieczności uczenia się sporego
zasobu reguł optymalizatora.
• Optymalizator kosztowy jest sukcesywnie ulepszany w kolejnych wersjach systemu Oracle.
Rozwój optymalizatora regułowego został zaniechany.
• Istnieje mniejsze ryzyko związane z dodawaniem nowych indeksów.
• Istnieje wiele funkcji, które są dostępne tylko w przypadku optymalizatora kosztowego.
Wśród nich wymienić należy rozpoznawanie perspektyw zmaterializowanych (materialized
views), transformacji typu gwiazda (star transformation), użycia indeksów funkcyjnych itd.
Liczba podobnych mechanizmów jest ogromna i w miarę upływu czasu różnice pomiędzy
optymalizatorami będą się powiększać.
• Firma Oracle wprowadziła pewne mechanizmy (takie jak pakiet DBMS_STATS oraz
scenariusze) w celu rozwiązania znanych problemów dotyczących niespójności działania
optymalizatora kosztowego w różnych środowiskach.

Problemy i ich rozwiązania w przypadku optymalizatora
regułowego

Optymalizator regułowy oferuje wiele możliwości strojenia. Z uwagi na to, że jego
zachowanie jest przewidywalne, uwarunkowane 20 rangami warunków przedstawionych już
w tabeli l, z łatwością można korzystać z owych możliwości.
Autor spróbował określić rodzaje problemów, które pojawiają się w czasie pracy z obydwoma
rodzajami optymalizatorów oraz określić najlepsze sposoby ich rozwiązania. Główne
przyczyny słabej wydajności pracy optymalizatora regułowego przedstawia tabela 2.
Tabela 2. Często powtarzające się problemy w przypadku optymalizatora regułowego
Problem

Przypadków

1. Nieodpowiednia tabela sterująca

40%

2. Nieodpowiedni indeks

40%

3. Nieodpowiedni indeks sterujący

10%

4. Użycie indeksu

ORDER

zamiast indeksu

WHERE

10%

Każdy problem (wraz z jego rozwiązaniem) opisują kolejne podrozdziały.

Problem pierwszy: nieodpowiednia tabela sterująca

Jeśli tabela sterująca w złączeniu nie zostanie wybrana optymalnie, to może okazać się, że
znacznie wzrasta czas potrzebny na wykonanie zapytania. Wcześniej (w podrozdziale: ,,
Reguły RBO o tym nie mówią #6") omówiono już to, co decyduje o wyborze tabeli sterującej.
Prezentowany poniżej przykład ilustruje potencjalne różnice w czasie wykonania:

SELECT COUNT(*)
FROM acct a, trans b WHERE b.cost_center = 'MASS’

AND a.acct_name = 'MGA'
AND a.acct_name = b.acct_name;
W przykładzie tym - jeśli założymy, że acct_name reprezentuje indeks klucza unikatowego,
zaś cost_center reprezentuje indeks pojedynczej kolumny o nieunikatowych wartościach -
klucz unikatowy sprawi to, że tabelą sterującą stanie się tabela acct.
Gdyby zarówno cost_center, jak i acct_name stanowiły indeksy pojedynczych kolumn o
nieunikatowych wartościach, wówczas optymalizator regułowy za tabelę sterującą przyjąłby
tabelę trans, ponieważ została ona wymieniona jako ostatnia w wyrażeniu FROM. Taka
sytuacja z dużą dozą prawdopodobieństwa oznaczać może dłuższy czas oczekiwania na
wykonanie zapytania, gdyż zazwyczaj istnieje tylko jeden wiersz w tabeli acct dla pewnej
nazwy rachunku (accouni) i wiele transakcji dla danego miejsca powstawania kosztów (cost
center).

W przypadku optymalizatora regułowego - jeśli rangi obu indeksów są identyczne - system
Oracle wykonuje po prostu polecenie w kolejności, w jakiej w czasie analizy składniowej
określane są tabele. Z uwagi na fakt, że analizator składniowy przetwarza nazwy tabel od
strony prawej do lewej, tabela, której nazwa została podana jako ostatnia (w przykładzie
powyższym - tabela trans) jest faktycznie przetwarzana jako pierwsza (tabela sterująca).

SELECT COUNT{*)
FROM acct a, trans b

WHERE b.cost_center = 'MASS'
AND a.acct_name = 'MGA'

AND a.acct_name = b.acct_name;
Odpowiedź = 19,722 sekund
Czas odpowiedzi po przestawieniu kolejności tabel w wyrażeniu FROM jest następujący:

SELECT COUNT(*)
FROM trans b, acct a

WHERE b.cost_center = 'MASS'
AND a.acct_name = 'MGA'

AND a.acct_name = b.acct_name;
Odpowiedź = 1,904 sekund
Istotną kwestią jest to, aby tabela, którą w wyrażeniu FROM wymieniono jako ostatnią,
zawierała jak najmniejszą liczbę zwracanych wierszy. Istnieje także możliwość dostosowania
indeksowania tak, aby wymuszało to użycie określonej tabeli jako tabeli sterującej. Na
przykład indeks cost_center można uczynić indeksem skonkatenowanym, złączonym z
kolumną, której się często używa w zapytaniach SQL wraz z obecną kolumną. Uniknie się
dzięki temu nadawania mu tak wysokiej rangi, kiedy ma miejsce złączanie tabel.

Problem drugi: nieodpowiedni indeks

Wyrażenie WHERE często zawiera wiele indeksów, z których może skorzystać optymalizator
regułowy. Optymalizator ten nie ma jednak żadnych informacji o liczbie wierszy, jakie będzie
musiał przejrzeć dany indeks ani o potencjalnym wpływie źle dokonanego wyboru na czas
odpowiedzi. Nieodpowiedni wybór indeksu znacznie zwiększa ten czas.
Optymalizator regułowy kieruje się prostymi zasadami wyboru indeksu. Reguły te (a także
pewne scenariusze) opisano wcześniej (w kolejnych podrozdziałach części zatytułowanej
„Reguły RBO o tym nie mówią").
Warto przeanalizować w tym miejscu przykładową sytuację.
Pakiet ERP opracowano w ogólnej formie w celu umożliwienia korzystania z kolumn dla
celów raportowania w dowolny, wybrany przez użytkownika sposób. Załóżmy, że istnieje
kolumna o nazwie business_unit, która posiada jednokolumnowy indeks. W większości
ośrodków istnieją setki jednostek biznesowych (business unit), ale w pozostałych - tylko
jedna.
Tabela Journal posiada jeden indeks w kolumnie (business_ unit) oraz drugi w kolumnach
(business_unit, account, journal_date). Wyrażenie WHERE użyte w zapytaniu ma następującą
postać:

WHERE business_unit - 'A203’

AND account = 801919

AND journal_date BETWEEN
‘01-DEC-2001’ AND ‘3l-DEC-2001’
Indeks jednokolumnowy ma pierwszeństwo przed indeksem trzykolumnowym, mimo że ten
ostatni zwraca rezultat w czasie stanowiącym ułamek czasu odpowiedzi przy użyciu indeksu
jednokolumnowego. Dzieje się tak, ponieważ zapytanie wykorzystuje wszystkie kolumny
indeksu jednokolumnowego. W takiej sytuacji jedyną możliwość stanowi usunięcie indeksu
lub użycie optymalizatora kosztowego. Jeśli nie wykorzystuje się gotowego oprogramowania,

to można również użyć wskazówek.

Problem trzeci: nieodpowiedni indeks sterujący

Sposób, w jaki podaje się warunki w wyrażeniu (wyrażeniach) WHERE instrukcji SELECT,
ma największy wpływ na wydajność polecenia SQL, ponieważ kolejność, w jakiej podaje się
warunki, wpływa na wybór indeksu dokonywany przez optymalizator.
Jeśli rangi dwóch indeksów są równe (kiedy na przykład w wyrażeniu WHERE występują
kolumny dwóch indeksów jednokolumnowych) system Oracle scala indeksy. Kolejność
scalania może mieć ogromny wpływ na wykonanie polecenia. Jeśli indeks, który steruje
zapytaniem zwraca więcej wierszy niż drugi z indeksów, wydajność wykonania zapytania nie
będzie optymalna. Efekt jest bardzo podobny do tego, który występował w przypadku
nieodpowiedniej kolejności tabel w wyrażeniu FROM. Warto rozpatrzyć następujący
przykład:

SELECT COUNT(*)
FROM trans

WHERE cost_center - ‘MASS’
AND bmark_id = 9;

Czas odpowiedzi: 4,255 sekund
Indeks, który posiada kolumnę wymienioną w wyrażeniu WHERE jako pierwszą, będzie
indeksem sterującym. W przypadku powyższego polecenia zaindeksowane wartości dla
cost_center = ‘MASS’ zwrócą zdecydowanie więcej wierszy niż dla bmark_id = 9, w którym
to przypadku zwrócony zostanie jeden, a najwyżej dwa wiersze.
W poniższym zapytaniu kolejność warunków została w wyrażeniu WHERE zamieniona, co
przekłada się na znacznie krótszy czas wykonania.
SELECT COUNTf*)

FROM trans
WHERE bmark_id = 9;

AND cost_center = ‘MASS’
Czas odpowiedzi: 1,044 sekund
W przypadku optymalizatora regułowego należy na początku wyrażenia WHERE umieszczać
te warunki, które zwrócą jak najmniejszą liczbę wierszy.

Problem czwarty: użycie indeksu ORDER BY zamiast indeksu WHERE

Nieco rzadziej występujący problemem związany z wyborem indeksu, który Autor
zaobserwował w systemach korzystających z optymalizatora regułowego, ilustruje następujące
zapytanie oraz indeksy:

SELECT fod_flag, account_no...
FROM account_master

WHERE (account_code LIKE ‘1%’)
ORDER BY account_no;

Index_l UNIQUE (ACCOUNT_NO)
Index_2 (ACCOUNT_CODE)
Przy podanych indeksach czas wykonania powyższego zapytania wyniósł 20 minut. Zapytanie
było wykorzystywane do tworzenia raportu i wykonywane codziennie przez wielu maklerów.
W przykładzie tym optymalizator próbuje uniknąć sortowania i wybiera indeks, który zawiera
kolumnę z wyrażenia ORDER BY zamiast indeksu posiadającego kolumnę w wyrażeniu
WHERE.
Ośrodek, w którym występował opisywany problemy, to firma maklerska. Polecenie SQL
było wykonywane często w celu utworzenia finansowych podsumowań rachunku.
Problem udało się rozwiązać tworząc indeks skonkatenowany w obu kolumnach:

# Dodatkowy Indeks (ACCOUNT_CODE, ACCOUNT_NO)

Zdecydowano się na usunięcie indeksu Index_2 (ACCOUNT_CODE), który nie był już
potrzebny, ponieważ kolumna account_code stanowiła kolumnę wiodącą nowego indeksu.
Kolumna account_no została dodana do nowego indeksu w celu wykorzystania rosnącego
porządku przechowywania danych w indeksie. Dzięki temu uniknięto konieczności
sortowania, a w konsekwencji czas wykonania zmniejszył się do 10 sekund.

Problemy i ich rozwiązania w przypadku optymalizatora
kosztowego

Optymalizator kosztowy uległ znaczącemu ulepszeniu w porównaniu ze swoją pierwotną
wersją. Autor sugeruje, aby w każdym ośrodku, w którym od niedawna używa się systemu
Oracle, korzystano właśnie z optymalizatora kosztowego. Ponadto warto pomyśleć także o
tym, aby w ośrodkach, w których korzysta się obecnie z optymalizatora re-gułowego,
przygotowano stosowny plan migracji do optymalizatora kosztowego. Istnieją jednak pewne
kwestie związane z tym rodzajem optymalizatora, o których trzeba pamiętać. W tabeli 3
wymieniono najczęściej powtarzające się problemy (wraz z częstotliwością ich
występowania), jakie Autorowi udało się zaobserwować.

Tabela 3. Często powtarzające się problemy w przypadku optymalizatora kosztowego
Problem

Przypadków

1. Problem asymetrii

30%

2. Analizowanie nieodpowiednich danych

25%

3. Wspólne używanie optymalizatorów przy złączeniach

20%

4. Wybieranie nieodpowiedniego indeksu

20%

5. Złączanie zbyt wielu tabel

< 5%

6. Nieodpowiednie ustawienia parametrów w pliku INIT.ORA

< 5%

Problem pierwszy: problem asymetrii

Załóżmy, że problem dotyczy systemu, w którym istnieje tabela trans o jednej z kolumn
noszącej nazwę status. Dopuszczalne są dwie wartości kolumny: O dla oznaczenia transakcji
otwartych (open transactions), które nie zostały jeszcze zaksięgowane, oraz C dla oznaczenia
transakcji zamkniętych (closed transaclions), które zostały już
zaksięgowane i nie wymagają dalszej obsługi. Istnieje ponad milion rekordów, które posiadają
status C i zawsze tylko 100 wierszy, które mają status O.
Utworzono następujące polecenie SQL, które jest wykonywane codziennie kilkaset razy,
jednak czas odpowiedzi nie jest zadowalający:
SELECT acct_no, customer, product, trans_date, amt
FROM trans
WHERE status = ‘O’;
Czas odpowiedzi: 16,308 sekund
W przykładzie tym - wziętym z życia - optymalizator kosztowy zdecydował, że system Oracle
powinien przeprowadzić przegląd całej tabeli (full table scan). Stało się tak dlatego, że
optymalizator posiadał informację o liczbie różnych wartości, jakie przyjmować mogły pola w
kolumnie STATUS, ale nie posiadał informacji o liczbie rekordów posiadających każdą z tych
wartości. W konsekwencji optymalizator założył równomierny rozkład danych (50/50) dla
każdej z dwóch wartości O oraz C. Przy takim założeniu system Oracle przeprowadza
przegląd całej tabeli w celu pobrania danych o otwartych transakcjach.
System Oracle będzie posiadał informację o asymetrii rozkładu danych, czyli liczbie wierszy
posiadających określoną wartość w zaindeksowanych kolumnach, jeśli podczas wykonywania
polecenia ANALYZE lub w momencie wywoływania pakietu DBMS_STATS poda się opcję
FOR ALL INDEXED COLUMNS. Załóżmy teraz, że kolumna status posiada indeks. W celu
zanalizowania tabeli użyć należy następującego polecenia:

ANALYZE TABLE TRANS COMPUTE STATISTICS FOR ALL INDEXED COLUMNS
Po przeprowadzeniu analizy tabeli i obliczeniu statystyk dla wszystkich zaindeksowanych
kolumn, optymalizator kosztowy będzie posiadał informację o tym, że tylko w około 100
wierszach występuje wartość O, co sprawi, że w przypadku tej kolumny użyje indeksu. W
rezultacie otrzymany zostanie dużo krótszy czas odpowiedzi:
Czas odpowiedzi: 0,259 sekund
Zazwyczaj, optymalizator kosztowy przeprowadza przegląd całej tabeli, jeśli dana wartość
kolumny występuje w ponad 12% wierszy tabeli, a korzysta z indeksu, gdy wartość występuje
w mniej niż 12% wierszy. Wybór dokonywany przez optymalizator kosztowy nie opiera się na
tak prostej regule, jednak praktyka wskazuje, że jest to typowe jego zachowanie.
Przed wprowadzeniem systemu Oracle9i - jeśli polecenie wykorzystywało zmienne
dowiązane - problem asymetrii wciąż mógł występować nawet wtedy, gdy użyto opcji FOR
ALL INDEXED COLUMNS. Warto przyjrzeć się następującemu poleceniu:

local_status := ‘O’;
SELECT acct_no, customer, product, trans_date, amt

FROM trans WHERE status = local_status;
Czas odpowiedzi: 16,608 sekund
Czas odpowiedzi jest zbliżony do tego, który występował w przypadku nieużywania opcji
FOR ALL INDEXED COLUMNS. Problem występuje dlatego, że optymalizator kosztowy
nie zna wartości zmiennej dowiązanej w momencie określania planu wykonania. Ogólnie
rzecz biorąc - w celu uniknięcia problemu asymetrii należy:
• wartości literałów zapisywać w kodzie bezpośrednio (na przykład można użyć WHERE
STATUS = ‘ O’ zamiast WHERE
STATUS = local_status);
• zawsze wykonywać analizę z opcją FOR ALL INDEXED COLUMNS.
Jeśli mimo to wciąż występują problemy wydajnościowe związane z nieużywaniem przez
optymalizator kosztowy indeksu z powodu zmiennych dowiązanych, a nie ma możliwości
zmiany kodu źródłowego, pozostaje próba usunięcia statystyk indeksu za pomocą polecenia:

ANALYZE INDEX
TRANS_STATUS_NDX

DELETE STATISTICS;
Usunięcie statystyk indeksu poprawia sytuację, gdyż wymusza zachowanie stosowane przez
optymalizator regułowy, który zawsze korzysta z istniejących indeksów (zamiast przeglądu
całej tabeli).
UWAGA
W systemie Oracle9i wartość zmiennych dowiązanych jest określana przed podjęciem decyzji
o planie wykonania, co eliminuje konieczność bezpośredniego zapisywania w kodzie wartości
literałów.

Problem drugi:
analizowanie nieodpowiednich danych

Autor miał styczność z wieloma systemami, w których problemy z wydajnością wynikały z
tego, że tabele i indeksy nie były analizowane w czasie, gdy zawierały typowe ilości danych.
Optymalizator kosztowy musi posiadać dokładne informacje (a w tym informacje o objętości
danych), aby mógł określić efektywny plan wykonania.
Sytuacje, w których statystyki mogą zostać utracone lub stać się nieaktualne, stanowić może
ponowne tworzenie tabeli lub jej przenoszenie, dodawanie indeksu lub tworzenie nowego
środowiska. Na przykład administrator może zapomnieć o ponownym utworzeniu statystyk po
przeniesieniu schematu bazy danych do środowiska produkcyjnego. Problemy pojawiają się
także wtedy, gdy administrator nie posiada wystarczających informacji o bazie danych, którą
zarządza i analizuje tabelę w momencie, gdy jest pusta, a nie wtedy, gdy po krótkim okresie

czasu ma ona setki lub tysiące wierszy.

Sposób sprawdzenia daty ostatniej analizy
W celu sprawdzenia tego, które tabele, indeksy i partycje zostały przeanalizowane i kiedy
zostało to zrobione po raz ostatni, można wykonać zapytanie pobierające wartość kolumny
LAST_ANALYZED z różnych perspektyw USER_XXX. Na przykład w celu określenia daty
ostatniej analizy wszystkich tabel należy wykonać:

SELECT table_name, num_rows, last_analyzed FROM user_tables;
Oprócz USER_TABLES istnieje wiele innych perspektyw, dzięki którym można sprawdzić
datę analizy różnych obiektów. W celu otrzymania pełnej listy perspektyw zawierających
kolumnę LAST_ANALYZED należy wykonać następujące zapytanie:
SELECT table_name

FROM all_tab_columns
WHERE column_name = ‘LAST_ANALYZED’;
Oczywiście nie chodzi o to, aby analizy z opcją COMPUTE przeprowadzać jak najczęściej.
Takie postępowanie może spowodować, że dostrojone polecenie SQL ulegnie rozstrojeniu.

Decyzja o czasie analizy
ponowne analizowanie tabel i indeksów może być równie niebezpieczne, jak dostosowywanie
indeksów i w idealnej sytuacji powinno być przeprowadzane na kopii bazy produkcyjnej
przed ostatecznym wprowadzeniem zmian w faktycznej bazie produkcyjnej.
Oprogramowanie firmy Peoplesoft jest przykładem aplikacji, która korzysta z tymczasowych
tabel do przechowywania danych, których nazwy kończą się wyrażeniem _TMP. Kiedy
rozpoczyna się wykonywa-nie procesu wsadowego, każda z tych tabel jest zazwyczaj pusta.
W czasie wykonaniu każdego etapu procesu wsadowego na tabelach wykonywane są operacje
wstawiania i uaktualniania danych.
Ostatnia faza procesu polega na wstawieniu danych do głównych tabel obsługi transakcji
aplikacji Poplesoft poprzez ekstrakcję danych z tabel tymczasowych. Po zakończeniu procesu
wsadowego zwykle wszystkie wiersze są z tabel tymczasowych usuwane. Transakcje
związane z tymi tabelami nie są zatwierdzone aż do zakończenia procesu, kiedy nie ma już w
nich żadnych danych.
Kiedy wydaje się polecenie ANALYZE względem tabel tymczasowych, zazwyczaj są one
puste. Kiedy optymalizator kosztowy otrzymuje informację o zerowej liczbie wierszy,
automatycznie podejmuje decyzję
o przeglądzie całej tabeli oraz zastosowaniu złączenia kartezjańskiego. W celu obejścia tego
problemu Autor sugeruje zapełnienie tabel tymczasowych danymi w celu przeprowadzenia
analizy. Potem można tabele opróżnić z danych i rozpocząć normalne przetwarzanie.
Opróżnienie tabel (polecenie TRUNCATE) nie powoduje usunięcia statystyk.
Polecenia INSERT i UPDATE języka SQL używane przez aplikację w celu wstawienia
danych do tabel tymczasowych można sprawdzić stosując procedurę śledzenia (tracing)
względem procesu wsadowego, który wstawia i uaktualnia dane. Tych samych poleceń SQL
można użyć do własnoręcznego zapełnienia tabel danymi.
Przy zastosowaniu takiego ujęcia problemu w jednym z dużych ośrodków w Australii, który
korzystał z oprogramowania Peoplesoft, czas wykonania procesu wsadowego spadł z 36
godzin do mniej niż 30 minut.
Jeśli analizowanie tabel przechowujących dane tymczasowe zawierające produkcyjne ilości
danych nie rozwiązuje problemów wydajnościowych, warto rozważyć usunięcie statystyk
odpowiednich dla tych tabel. Wymusza to zastosowanie względem poleceń SQL, które
odwołują się do tych tabel, zasad działania optymalizatora regułowego. Statystyki można
usunąć korzystając z polecenia ANALYZE nazwatab DELETE STATISTICS. Po ich
usunięciu ważną sprawą jest zapewnienie tego, aby tabele te nie były używane w złączeniach
z tabelami, które posiadają statystyki. Należy także zapewnić to, aby względem

niezanalizowanych tabel nie były używane indeksy posiadające statystyki. Jeśli tabele
tymczasowe są wykorzystywane oddzielnie i złączenia występują tylko pomiędzy nimi
samymi, wtedy preferowanym podejściem jest często wykorzystanie zasad działania
optymalizatora regułowego.

Problem trzeci: wspólne używanie optymalizatorów przy złączeniach

Jak wspomniano wcześniej, w sytuacji, gdy tabele podlegają złączeniu i jedna z nich zostanie
zanalizowana, zaś pozostałe tabele nie, optymalizator kosztowy działa najmniej korzystnie.
Analizując tabele oraz indeksy przy użyciu procedury DBMS_STATS.
GATHER_SCHEMA_STATS oraz procedury GATHER_TABLE_STATS należy pamiętać o
podaniu opcji CASCADE=>TRUE. Domyślnie pakiet DBMS_STATS zbiera statystyki
jedynie dla tabel. Posiadanie statystyk dla tabel, ale nie dla ich indeksów, także może
spowodować obieranie przez optymalizator kosztowy niewydajnych planów wykonania.
Jeden z przypadków wystąpienia takiego problemu, z jakim zetknął się Autor, miał miejsce w
systemie posiadającym niezanalizowaną tabelę trans oraz zanalizowaną tabelę acct.
Administrator w celu usunięcia danych ponownie utworzył tabelę trans, ale zapomniał
wykonać analizę. Poniższy przykład ilustruje wydajność wykonania złączenia obu tabel:

SELECT a.account_name, SUM(b.amount)
FROM trans b, acct a

WHERE b.trans_date > sysdate - 7
AND a.act_id = b.acct_id

AND a.acct_status = ‘A’
GROUP BY account_name;

SORT GROUP BY
NESTED LOOPS

TABLE ACCESS BY ROWID ACCT
INDEX UNIQUE SCAN ACCT_PK TABLE ACCESS FULL TRANS

Czas odpowiedzi: 410 sekund
Czas odpowiedzi uległ znacznemu skróceniu po zanalizowaniu tabeli trans za pomocą
poniższego polecenia:
ANALYZE TABLE trans ESTIMATE STATISTICS

SAMPLE 3 PERCENT
FOR ALL INDEXED COLUMNS
Nowy plan wykonania oraz czas odpowiedzi przedstawiały się następująco:

SORT GROUP BY
NESTED LOOPS

TABLE ACCESS BY ROWID ACCT
INDEX UNIQUE SCAN ACCT_PK

TABLE ACCESS BY ROWID TRANS
INDEX RANGĘ SCAN TRANS_NDXl

Czas odpowiedzi: 3,1 sekund
W innym systemie, który Autor także dostrajał, twórca oprogramowania zarządzającego
informacjami kadrowymi zalecił analizowanie tylko indeksów, a tabel - nie. Dostawca
oprogramowania opracował aplikację dla systemu baz danych firmy Microsoft SQL Server i
przeniósł ją do systemu Oracle. Rezultat analizowania samych indeksów miał daleko
sięgający - negatywny - wpływ na wydajność. Na przykład:
SELECT COUNT(*)

FROM trans WHERE acct_id = 9
AND cost_center = ‘VIC’;

TRANS_IDX2 jest na ACCT_ID
TRANS_NDX3 jest na COST_CENTER

Czas odpowiedzi: 77,3 sekund
Ironią losu było to, że twórca oprogramowania obarczał winą system Oracle. Twierdził
bowiem, że jego wydajność jest niższa od systemu SQL Server. Po zanalizowaniu tabel oraz
indeksów czas odpowiedzi polecenia SQL został drastycznie zmniejszony do 0,415 sekundy.
Czas odpowiedzi wielu innych poleceń SQL także znacznie się zmniejszył.
Morał płynący z tej historii mógłby brzmieć: strojenie systemu Oracle powinno być domeną
ekspertów systemu Oracle, zaś eksperci systemu SQL Server powinni przy tym systemie
pozostać. Jednakże specjaliści z sektora IT - coraz bardziej mobilni i pracujący z wieloma
systemami baz danych - powinni być może po prostu z większą uwagą czytać podręczniki,
kiedy przyjmują na siebie obowiązek strojenia nowej bazy danych.

Problem czwarty:
wybieranie nieodpowiedniego indeksu

Optymalizator kosztowy wybiera czasem indeks podrzędny, nawet jeśli wydaje się sprawą
oczywistą, że użyty być powinien inny indeks. Warto przyjrzeć się następującemu wyrażeniu
WHERE występującemu w oprogramowaniu Peoplesoft:
where business_unit = :5

and ledger = 6
and fiscal_year = 7

and accounting_period = 8
and affiliate = 9

and statisctics_code = 10
and project_id = 11

and account = 12
and currency_cd = 13

and deptid = 14
and product = 15
System Peoplesoft, z którego pochodzi powyższy przykład, posiadał indeks zawierający
wszystkie kolumny wyszczególnione w wyrażeniu WHERE. Wydawać by się mogło, że
system Oracle do wykonania zapytania użyje właśnie tego indeksu. Jednak optymalizator
kosztowy zdecydował o użyciu indeksu w kolumnach (business_unit, ledger, fiscal_year,
account). Po odtworzeniu polecenia SQL i porównaniu czasu wykonania z przypadkiem
użycia wskazówki nakazującej wykorzystanie większego indeksu okazało się, że jest on
ponad czterokrotnie krótszy od czasu wykonania przy użyciu indeksu wybranego przez
optymalizator.
Dalsze badania wykazały, że indeks ten powinien być utworzony jako unikatowy (UNIQUE),
lecz w procesie usuwania danych i odtwarzania tabeli omyłkowo utworzono go jako
nieunikatowy. Oczywiście czterokrotny zysk czasu bardzo ucieszył użytkownika systemu.
Jednak pojawiły się inne problemy. Ten sam indeks był idealnym kandydatem do
wykorzystania w znajdującym się poniżej poleceniu, które było jednym z częściej
wykonywanych w przypadku przetwarzania danych na końcu miesiąca lub końcu roku:

where business_unit = 5
and ledger = 6

and fiscal_year = 7
and accounting_period between l and 12

and affiliate = 9
and statisctics_code = 10

and project_id = 11
and account = 12

and currency_cd = 13
and deptid = 14

and product = 15
Pomimo poprawnego utworzenia indeksu jako unikatowego, optymalizator kosztowy
ponownie go nie wziął pod uwagę. Jedyna różnica pomiędzy poleceniem bieżącym a
poprzednim polegała na tym, że dotyczyło ono raczej zakresu okresów obrachunkowych
(accounting period) dla roku fiskalnego (fiscal year), a nie po prostu jednego okresu
obrachunkowego.
Dla powyższego wyrażenia WHERE używany był ten sam, co poprzednio, nieodpowiedni
indeks z kolumnami (business_unit, ledger, fiscal_year, account). I ponownie - po zmierzeniu
czasu wykonania polecenia przy użyciu indeksu wybranego przez optymalizator kosztowy
oraz indeksu zawierającego wszystkie kolumny - okazało się, że ten drugi zapewniał co
najmniej trzykrotnie szybsze wykonanie.
Problem rozwiązano dzięki przestawieniu kolumny accounting_ period na ostatnią pozycję w
indeksie (oryginalnie znajdowała się na trzeciej). Nowy indeks miał następującą postać:
business_unit

ledger
fiscal_year

affiliate
statisctics_code

project_id
account

currency_cd
deptid

product
accounting_period
Innym sposobem zmuszenia optymalizatora kosztowego do użycia danego indeksu jest
wykorzystanie jednej ze wskazówek, które pozwalają na jego określenie. Jest to dobre
rozwiązanie, jednak wiele ośrodków korzysta z pakietów dostarczanych przez twórców
oprogramowania, których nie można modyfikować (a w konsekwencji nie można wykorzystać
wskazówek). Jednak możliwe jest utworzenie perspektywy zawierającej wskazówkę oraz
nadanie użytkownikom uprawnień dostępu do tej perspektywy. Będzie ona przydatna, jeśli
polecenie SQL, którego wydajność wykonania pozostawia wiele do życzenia, stanowi część
raportu lub zapytania bezpośredniego, które mogą odczytywać perspektywę.
W ostateczności okazuje się czasem, że można wymusić użycie indeksu, jeśli usunie się jego
statystyki. Czasem można także użyć polecenia ANALYZE ESTIMATE z jedynie
podstawową wartością 1064 analizowanych wierszy. Często zdarza się, że plan wykonania
zmieniony zostanie na pożądany, jednak ten rodzaj postępowania ma w sobie coś z ,,
czarowania". Niezmiernie istotną sprawą jest to, aby stosując takie „magiczne" działania
dokładnie udokumentować wykonane czynności. Jeszcze inna metoda polega na próbie
zmniejszenia parametru OPTIMIZER_INDEX_COST_ADJ* (Wartość tego parametru
ustawia się w pliku INIT.ORA - przyp. tłum) do wartości z przedziału 10 do 50.
Podsumowując trzeba odpowiedzieć na pytanie o to, dlaczego optymalizator kosztowy
podejmuje takie nieodpowiednie decyzje. Po pierwsze - należy podkreślić, że zła decyzja
dotycząca planu wykonania to raczej wyjątek niż reguła. Przykłady z niniejszego podrozdziału
pokazują, że kolumny są rozpatrywane raczej indywidualnie niż grupowo. Gdyby tak było, w
pierwszym z prezentowanych przykładów optymalizator kosztowy stwierdziłby - bez
konieczności odtworzenia indeksu przez administratora jako unikatowego - że każdy wiersz
posiada unikatowe wartości. Przykład drugi pokazuje, że jeśli kilka kolumn indeksu posiada
małą liczbę różnych dopuszczalnych wartości, a polecenie SQL żąda dostępu do większości z
nich, to optymalizator kosztowy często pomija taki indeks. Dzieje się tak, mimo że
rozpatrywane razem kolumny są ściśle określone i zapytanie zwróci niewiele wierszy.
Nieco usprawiedliwiając działanie optymalizatora należy stwierdzić, że użycie indeksów o
mniejszej ilości kolumn często daje znaczny wzrost wydajności wykonywania w porównaniu

z użyciem indeksów o wielu kolumnach.

Problem piąty: złączanie zbyt wielu tabel

Pierwsze wersje optymalizatora kosztowego często wykorzystywały metodę ,,dziel i rządź" w
sytuacji, gdy złączaniu podlegało więcej niż pięć tabel. Rozpatrzmy przykład przedstawiony
na rysunku 1. Zapytanie wybiera wszystkie dane związane z przedsiębiorstwem o
identyfikatorze rachunku (kolumna acct_id) równym 777818. Przedsiębiorstwo posiada kilka
oddziałów, a zapytanie dotyczy oddziału znajdującego się w stanie Waszyngton (WA). Tabela
A to tabela acct, tabela F to acct_address, zaś tabela G to address.
WHERE acct id=77818

rysunek

Rysunek 1. Złączenie siedmiu tabel

Użytkownik oczekuje, że zapytanie zwróci stosunkowo niedużą liczbę wierszy z różnych
tabel, a czas odpowiedzi nie będzie przekraczał l sekundy. Najlepiej jest, jeśli system Oracle
otrzymuje wiersze z tabeli acct_address odpowiadające danemu rachunkowi, a następnie
złącza ją z tabelą address w celu określenia tego, czy adresy odpowiadają stanowi
Waszyngton.
Jednakże ze względu na to, że złączeniu podlega tak wiele tabel, optymalizator kosztowy
często decydował będzie o tym, że przetwarzane będą tabele F i G niezależnie od pozostałych
i dopiero na końcu dane zostaną scalone. Rezultatem złączenia tabel F i G będzie to, że będą
musiały zostać wybrane wszystkie adresy, które dotyczą stanu Waszyngton. Proces ten może
zająć nawet kilka minut, co zapewne spowoduje, że ogólny czas wykonania będzie dużo
dłuższy od tego, który miałby miejsce, gdyby system Oracle sterował dostępem do wszystkich
tabel od tabeli A.
Zakładając, że tabela acct_address (F) posiada indeks w kolumnie acct_id, można problem ten
rozwiązać wykorzystując odpowiednią wskazówkę instruującą optymalizator kosztowy, że
użyty powinien zostać ten właśnie indeks. Znacznie zwiększy to wydajność.
Co interesujące - optymalizator regułowy ma często dużo większe problemy z poprawnym
określeniem planu wykonania w przypadku złączania wielu tabel niż optymalizator kosztowy.
Optymalizator regułowy często w ogóle nie używa tabeli acct jako tabeli sterującej. Aby to
wymusić, należy w wyrażeniu FROM nazwę tabeli A umieścić jako ostatnią.
Jeśli wykorzystywane jest gotowe oprogramowanie, najlepszym sposobem może być
utworzenie perspektywy zawierającej wskazówkę (o ile jest to dopuszczalne i możliwe w
przypadku używanego pakietu).

Problem szósty: nieodpowiednie ustawienia parametrów w pliku

INIT.ORA

W wielu ośrodkach korzysta się z przedprodukcyjnej bazy danych w celu przetestowania
wydajności wykonywania poleceń SQL jeszcze przed przeniesieniem indeksów i kodu do
bazy produkcyjnej. W idealnej sytuacji baza przedprodukcyjna zawiera ilość danych bliską
bazie produkcyjnej, a ponadto jej tabele są analizowane dokładnie w ten sam sposób, co w
przypadku bazy produkcyjnej. Taka przedprodukcyjna baza stanowić często będzie kopię
faktycznych plików danych fazy produkcyjnej.
Kiedy administratorzy testują zmiany w bazie przedprodukcyjnej, zwykle wszystko działa
prawidłowo, jednak po przejściu do bazy produkcyjnej czasem okazuje się, że obierane są
inne plany wykonania. Powodem takich problemów mogą być różne ustawienia parametrów
w plikach INIT.ORA bazy przedprodukcyjnej i produkcyjnej.
Autor spotkał kiedyś znajdujące się poniżej polecenie uaktualnienia danych, którego czas
wykonania sięgał czterech minut, pomimo że warunek umieszczony w wyrażeniu WHERE

odwoływał się do klucza głównego tabeli. Co dziwniejsze - kiedy z tabeli acct pobierano dane
(SELECT), nie zaś uaktualniano (UPDATE), przy użyciu dokładnie takiego samego
wyrażenia WHERE okazywało się, że indeks był używany.

UPDATE acct SET proc_flag = ‘Y’
WHERE pkey = 100;

# Czas odpowiedzi wynosił 4 minuty
# i klucz główny nie był używany
Wypróbowano wszelkie sposoby ponownego analizowania tabeli, a w końcu usunięto jej
statystyki. Okazało się wtedy, że kiedy używany był optymalizator regułowy, polecenie było
wykonywane wydajnie.
Po wielu analizach zdecydowano się na sprawdzenie parametrów pliku INIT.ORA. Okazało
się, że parametr COMPATIBLE był ustawiony na system w wersji 8.0.0, choć faktycznie
praca odbywała się w systemie Oracle 8.1.7. Po zmianie wartości parametru na 8.1.7 i
ponownym wykonaniu polecenia UPDATE okazało się, że indeks jest tym razem używany
poprawnie i czas wykonania wynosił około O, l sekundy.
Parametr COMPATIBLE nie jest jedynym, który wymaga takiego samego ustawienia w bazie
przedprodukcyjnej i produkcyjnej w celu zapewnienia identycznego działania optymalizatora
kosztowego. Są to:
SORT_AREA_SIZE
Liczba bajtów przydzielanych użytkownikowi na sesję w celu sortowania danych w pamięci.
Jeśli parametr jest ustawiony na swoją wartość domyślną 64K, NESTED LOOPS (pętle
zagnieżdżone) będą preferowane względem SORT MERGES (złączenia przez sortowanie i
scalenie) lub HASH JOINS (złączenia haszujące).
HASH_AREA_SIZE
Liczba bajtów przydzielanych użytkownikowi na sesję w celu przeprowadzania w pamięci
złączeń haszujących (hash joins). Wartość domyślna wynosi dwukrotność
SORT_AREA_SIZE. Złączenia haszujące często nie działają poprawnie, jeśli parametr ten
jest ustawiony na wartość niższą od l megabajta.
HASH_JOIN_ENABLED
Udostępnia lub blokuje używanie złączeń haszujących. Wartość domyślna to TRUE i
parametr zazwyczaj nie wymaga ustawienia.
OPTIMIZER_MODE
Możliwe wartości to CHOOSE, FIRST_ROWS lub ALL_ROWS. Wartość CHOOSE
powoduje, że optymalizator kosztowy jest używany, jeśli dostępne są statystyki. Wartość
FIRST_ROWS działa w ten sam sposób, ale powoduje zwiększenie preferencji wobec
NESTED LOOPS zamiast SORT MERGE lub HASH JOINS. Wartość ALL_ROWS
powoduje preferowanie SORT MERGE oraz HASH JOINS przed NESTED LOOPS.
DB_FILE_MULTIBLOCK_READ_COUNT
Liczba bloków, które system Oracle pobiera przy każdym odczycie z tabeli. Jeśli określi się
dużą wartość (na przykład 16 lub 32), system Oracle będzie w wielu sytuacjach wybierał
FULL TABLE SCANS (przegląd całej tabeli) zamiast NESTED LOOPS.
OPTIMIZER_MODE_ENABLE
Udostępnia nowe funkcje optymalizatora. Na przykład ustawienie parametru na wartość 8.1.7
pozwala na wykorzystanie wszystkich mechanizmów udostępnionych w wersji systemu
Oracle 8.1.7 (i wcześniejszych). Parametr ten może także automatycznie ustawiać inne
parametry, takie jak FAST_FULL_SCAN_ENABLED.
Główne udoskonalenia, jakie wprowadzono w różnych wersjach systemu to: 8.0.4 -
uszeregowane pętle zagnieżdżone (ordered nested loops), szybkie pełne przeglądy (fast fuli
seans); 8.0.5 - wiele poprawek błędów optymalizatora; 8.1.6 - udoskonalona obsługa
histogramów, partycji i pętli zagnieżdżonych; 8.1.7
- udoskonalona obsługa partycji i optymalizacja podwyrażeń; 9.0.1 - znacznie udoskonalone
złączenia indeksowe, scalanie złożonych perspektyw, usprawnienia w obsłudze indeksów

bitmapowych, podwyrażeń oraz złączeń.
OPTIMIZER_INDEX_CACHING
Parametr ten „mówi" systemowi Oracle o odsetku danych zaindeksowanych, jakich można się
będzie spodziewać w pamięci. Domyślna wartość tego parametru wynosi 0, a zakres wartości
- od O do 100. Im wyższa wartość, tym bardziej prawdopodobne jest wykorzystanie NESTED
LOOPS zamiast SORT MERGE i HASH JOIN. W niektórych systemach zauważono poprawę
wydajności przy ustawieniu go na wartość 90.
OPTIMIZER_INDEX_ COST_ADJ
Ustawienia tego parametru mogą zwiększyć częstotliwość używania indeksów. Wartość
domyślna to 100. Zmniejszenie jej do 10 oznacza, że optymalizator kosztowy ma obniżyć
koszt wykorzystania indeksu do 10% wartości domyślnej. Można także określić wartość jako
dużo większą od 100 w celu wymuszenia SORT MERGE lub HASH JOIN. W niektórych
ośrodkach zanotowano wzrost wydajności przy ustawieniu tego parametru na wartość
pochodzącą z przedziału 10 do 50 w przypadku systemów OLTP* oraz 50 w przypadku
systemów wspomagających podejmowanie decyzji . Zmniejszenie wartości może
przyspieszyć wykonywanie niektórych zapytań OLTP, ale sprawia jednocześnie, że czas
wykonywania zadań wynoszący kilka godzin ulec może znaczącemu wydłużeniu.
Zwiększenie tej wartości może spowodować sytuację odwrotną.
OLTP (On Line Transaction Processing) - systemy przetwarzania transakcyjnego-przyp. tlum
OLAP (On Linę Analytical Processing) - systemy przetwarzania analitycznego-przyp. tlum
STAR_TRANSFORMATION_ENABLED
Sprawia, że wykorzystywana jest transformacja typu gwiazda (star transformation) w celu
scalenia indeksów bitmapowych w tabelach faktów*. Jest to metoda odmienna od złączenia
kartezjańskiego, które zazwyczaj ma miejsce w przypadku zapytań stosowanych względem
danych w tabelach o logicznej strukturze gwiazdy (star schema).
Tabela faktów (fact table) to główna, znormalizowana tabela w przypadku schematu gwiazdy
- najczęściej stosowanej logicznej struktury bazy danych w systemach OLAP (hurtownie
danych) -przyp. tlum
QUERY_REWRITE_ENABLED
Pozwala na używanie indeksów funkcyjnych (function-based in-dexes), a także na
przepisywanie (rewrite) zapytań w przypadku perspektyw zmaterializowanych. Wartość
domyślna to FALSE, co może wyjaśniać to, dlaczego indeksy funkcyjne nie są używane. Po
prostu - należy nadać parametrowi wartość TRUE.
PARTITION_VIEW_ENABLED
Parametr ten pozwala na korzystanie z perspektyw partycji. Wartością domyślną jest FALSE,
więc używając perspektyw partycji należy nadać mu wartość TRUE. Najogólniej rzecz biorąc
- perspektywa partycji to perspektywa, która stanowi złączenie tabel typu UNION ALL.
Wprowadzono je jeszcze przed partycjami systemu Oracle i są z powodzeniem używane w
wielu systemach w celach archiwizacji i zwiększenia wydajności pracy.
PARALLEL_BROADCAST_ENABLED
Parametr ten jest wykorzystywany przez zapytanie równoległe (pa-rallel ąuery), kiedy
używane są małe tabele przeglądowe (lookup tables). Jego wartość domyślna to FALSE. Po
ustawieniu na TRUE wiersze małych tabel są przesyłane do każdego procesu potomnego w
celu przyspieszenia wykonania MERGE JOIN i HASH JOIN w momencie złączania małej
tabeli z dużą.
OPTIMIZER_MAX_PERMUTATIONS
Może być wykorzystany w celu skrócenia czasu analizy składniowej. Jednakże zmniejszenie
liczby permutacji może spowodować określanie nieefektywnych planów wykonania, tak więc
w przypadku tego parametru wartość domyślna nie powinna być zmieniana.
CURSOR_SHAR1NG
Ustawienie na wartość FORCE lub SIMILAR może przyspieszyć analizę składniową,
zmniejszyć wykorzystanie pamięci w obszarze pamięci wspólnej oraz zmniejszyć ilość blokad

niskiego poziomu (latch contention). Osiągane jest to dzięki modyfikacji podobnych poleceń,
które w wyrażeniu WHERE zawierają literały, na polecenia używające zmiennych
dowiązanych.
Wartością domyślną jest EXACT. Autor sugeruje ustawienie wartości tego parametru na
SIMILAR w systemie Oracle9i tylko wtedy, jeśli ma się pewność, że w systemie istnieje wiele
podobnych poleceń, które różnią się jedynie wartościami literatów. Dużo lepiej jest kod
swojej aplikacji zapisać tak, aby wykorzystywane były zmienne dowiązane.
Ustawienie parametru na wartość FORCE powoduje, że polecenia podobne wykorzystują ten
sam obszar pamięci SQL, co może obniżyć wydajność. Wartości tej nie powinno się
stosować.
Warto zauważyć, że opcja STAR TRANSFORMATION nie będzie działać, jeśli parametrowi
temu nada się wartość SIMILAR lub FORCE.
ALWAYS_SEMI_JOIN
Parametr ten może w ogromnym stopniu zwiększyć wydajność w przypadku aplikacji, które
często wykorzystują opcję WHERE EXISTS. Ustawienie go na wartość MERGE lub HASH
może spowodować, że polecenia wykonywane wcześniej przez kilka godzin będą kończyć
pracę po kilku minutach. Wartość domyślna to STANDARD, co oznacza, że zapytanie
główne (ale nie zapytanie podrzędne) steruje planem wykonania. Jeśli się ten parametr
wybierze, zapytanie podrzędne stanie się zapytaniem sterującym.
ALWAYS_ANTI_JOIN
Parametr ten zmienia zachowanie poleceń typu NOT IN i można znacznie przyspieszyć cały
proces, jeśli nada mu się wartość HASH lub MERGE. Takie ustawienie spowoduje, że
wykonywane będą scalenia lub złączenia haszujące zamiast czasochłonnego złączenia
kartezjańskiego, które ma miejsce w przypadku standardowego wykonania poleceń typu NOT
IN.
Należy pamiętać, że jeśli któryś z powyższych parametrów ma różną wartość w bazie
przedprodukcyjnej i produkcyjnej, to możliwe jest, że plany wykonania dla poleceń SQL
także będą się różnić. Identyczne wartości zapewniają jednakowe działanie.

Problemy wspólne dla optymalizatora regułowego i kosztowego

W rozdziale niniejszym wymieniono problemy wspólne dla obu typów optymalizatorów -
regułowego i kosztowego. Istotną sprawą jest zdanie sobie sprawy z istnienia owych
problemów i staranie się o to, by ich unikać, kiedy tylko jest to możliwe. W tabeli 4
wymieniono te problemy i procentową częstotliwość ich występowania.

Tabela 4. Często powtarzające się problemy w przypadku obu typów optymalizatorów

Problem

Przypadków

1. Polecenia zapisane w postaci uniemożliwiającej wykorzystanie

25%

indeksów
2. Brak indeksów lub nieodpowiednie indeksy

16%

3. Korzystanie ze scalania indeksu jednokolumnowego

15%

4. Błędne użycie pętli zagnieżdżonych, sortowania i łączenia lub złączeń haszujących
5. Błędne użycie

EXISTS

NOT

EXISTS

lub

złączeń tabel
6. Niepotrzebne sortowanie

7. Zbyt wiele indeksów w przypadku tabeli

8. Użycie

zamiast

UNION

9. Tabele i indeksy z wieloma wierszami usuniętymi

10. Inne

10%

Problem pierwszy: polecenia zapisane w postaci uniemożliwiającej

wykorzystanie indeksów

W przypadku niektórych instrukcji SELECT w wyrażeniu WHERE nie wykorzystuje się
indeksów w ogóle. Większość problemów tego typu wynika z powodu stosowania funkcji w
przypadku zaindeksowanej kolumny. W systemie Oracle8i (oraz nowszych) udostępniono
indeksy funkcyjne (function indexes), które mogą okazać się alternatywą umożliwiającą
używanie wydajnych indeksów.
W przykładach przedstawionych w niniejszym podrozdziale dla każdego wyrażenia, w
przypadku którego niemożliwe jest wykorzystanie indeksu, Autor sugeruje podejście
alternatywne, które pozwala zwiększyć wydajność polecenia SQL.
W przykładzie zaprezentowanym poniżej funkcja SUBSTR uniemożliwia wykorzystanie
indeksu, kiedy używana jest z kolumną zaindeksowaną.

Źle:
SELECT account_name, trans_date, amount

FROM transaction
WHERE SUBSTR(account_name,l,7) = ‘CAPITAL’;

Dobrze:
SELECT account_name, trans_date, amount

FROM transaction
WHERE account_name LIKE ‘CAPITAL%;
W kolejnym przykładzie funcja ! = (różne od) nie pozwala na skorzystanie z indeksu. Należy
pamiętać, że indeksy mogą odpowiedzieć na pytanie o to, jakie dane znajdują się w tabeli, ale
nie na pytanie o to, jakich danych w tabeli nie ma. Wszelkie odwołania do NOT, ! = oraz <>
uniemożliwiają stosowanie indeksów.

Źle:

SELECT account_name, trans_date, amount
FROM transaction

WHERE amount != O;

Dobrze:

SELECT account_name, trans_date, amount

FROM transaction
WHERE amount > O;
W kolejnym przykładzie wykorzystanie indeksu uniemożliwia funkcja
TRUNC.

Źle:
SELECT account_name, trans_date, amount

FROM transaction
WHERE TRUNC(trans_date) = TRUNC(SYSDATE);

Dobrze:
SELECT account_name, trans_date, amount

FROM transaction
WHERE trans_date BETWEEN TRUNC(SYSDATE)

AND TRUNC(SYSDATE) + .99999;
W przykładzie kolejnym || jest funkcją konkatenacji; łączy ona wartości ciągów znaków z
dwóch kolumn. Podobnie jak inne funkcje wyklucza użycie indeksu.
Źle:
SELECT account_name, trans_date, amount

FROM transaction
WHERE account_name | | account_type = ‘AMEXA’;

Dobrze:

SELECT account_name, trans_date, amount
FROM transaction

WHERE account_name = ‘AMEX’
AND account_type = ‘A’ ;

W przykładzie kolejnym operator dodawania także stanowi funkcję, przez co uniemożliwia
wykorzystanie indeksu. Wszystkie inne operatory arytmetyczne (-, * oraz /) przynoszą
podobne konsekwencje.
Źle:

SELECT account_name, trans_date, amount
FROM transaction

WHERE amount •+• 3000 < 5000;

Dobrze:

SELECT account_name, trans_date, amount

FROM transaction
WHERE amount < 2000;
W następnym przykładzie indeksy nie są używane, ponieważ jest tak wówczas, gdy dana
kolumna lub kolumny pojawiają się po obu stronach operatora. W rezultacie stosowany jest
przegląd całej tabeli.
Źle:

SELECT account_name, trans_date, amount
FROM transaction

WHERE account_name - NVL(:acc_name, account_name);

Dobrze:

SELECT account._name, trans_date, amount

FROM transaction
WHERE account_name LIKE NVL(:acc_name, ‘%’);
Jak wcześniej wspomniano, można także użyć indeksów funkcyjnych w przypadku, gdy
funkcja w wyrażeniu WHERE reprezentuje tę samą funkcję i kolumnę, w której utworzono
indeks funkcyjny. Aby indeksy funkcyjne mogły poprawnie działać, należy w pliku INIT.ORA
parametr QUERY_REWRITE_ENABLED ustawić na wartość TRUE. Trzeba także korzystać
z optymalizatora kosztowego. Poniższe polecenie wykorzystuje indeks funkcyjny:
CREATE INDEX results_fn_ndxl ON results(UPPER(owner))

SELECT COUNT(*) FROM results WHERE UPPER(owner) - ‘MR M A
GURRY’;

Execution plan
O SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=l

Bytes=32)
1 O SORT (AGGREGATE)

2 l INDEX (RANGĘ SCAN) OF
‘RESULTS FN_NDX1’ (NON-UNIQUE)

(Cost=l Card=l Bytes=32)
Inny problem, który powoduje to, że indeksy nie są wykorzystywane, często występuje wtedy,
gdy typ danych zaindeksowanej kolumny różni się od typu wartości występującej w
wyrażeniu WHERE. System Oracle automatycznie wykonuje prostą koń wersję typu
kolumny, czyli rzutowanie (casting), kiedy porównuje dwie kolumny o różnych typach. Na
potrzeby tego przykładu załóżmy, że emp_type to zaindeksowana kolumna o typie
VARCHAR2:
SELECT ...
FROM emp
WHERE emp_type = 123
Execution plan
SELECT STATEMENT OPTIMIZER HINT: CHOOSE
TABLE ACCESS (FULL) OF 'EMP’
Z uwagi na fakt, że emp_type posiada wartość typu VARCHAR2, zaś stała 123 jest
traktowana jako wartość numeryczna, system Oracle zrzutuje wartość typu YARCHAR2 na
wartość typu NUMBER. Faktycznie polecenie zostanie wykonane jako:

SELECT ...
FROM emp

WHERE TO_NUMBER(emp_type) = 123
Narzędzie EXPLAIN PLAN nie jest w stanie wykryć ani zidentyfikować problemów z
rzutowaniem; zakłada po prostu, że wszystkie zmienne
dowiązane modułu mają poprawny typ danych. W programach, które wykazują problemy
wydajnościowe, mogą mieć one związek właśnie z rzutowaniem. Informacje wyjściowe z
EXPLAIN PLAN mówić będą o poprawnym użyciu indeksu, co jednak nie zawsze musi być
zgodne z prawdą.

Problem drugi: brak indeksów lub nieodpowiednie indeksy

Choć używanie indeksów w celu skrócenia czasu odpowiedzi stanowi istotną korzyść, to
często procedura taka może znacznie wydłużyć ten czas. Problem ten występuje wtedy, gdy
dostęp do ponad 10% wierszy tabeli odbywa się z użyciem indeksu.
Zaskakujące jest to, jak wielu użytkowników - aczkolwiek początkujących - sądzi, że jeśli
polecenie SQL korzysta z indeksu, musi być dostrojone. Zawsze należy się zastanowić nad
tym, czy dany indeks jest najlepszy, czy dodanie dodatkowego indeksu może zwiększyć
wydajność, czy przegląd całej tabeli nie dałby lepszych rezultatów.
Innym ważnym aspektem wykorzystania indeksów jest to, że w zależności od ich typu i
budowy, mogą wpływać na obierany plan wykonania polecenia SQL. Należy wziąć to pod
uwagę w momencie dodawania lub modyfikowania indeksów.
Indeksowanie a przegląd całej tabeli

Rysunek 2 wyjaśnia, dlaczego indeks może spowodować zwiększenie liczby odczytów danych
w porównaniu z wykonaniem FULL TABLE SCAN. Tabela znajdująca się po lewej stronie
rysunku 2 przedstawia wpisy indeksu wraz z odpowiednimi fizycznymi adresami na dysku.
Strzałki oznaczają fizyczne odczyty danych z dysku. Należy podkreślić, że dostęp do każdego
z wierszy łączy się z oddzielnym odczytem.

rysunek

Rysunek 2. Fizyczne odczyty danych spowodowane przez indeks

Przegląd całej tabeli w typowej sytuacji pozwala na pobranie ponad 100 wierszy tabeli w
jednym bloku danych. Ponadto parametr DB_FILE_MULTIBLOCK_READ_COUNT
pozwala na poinstruowanie systemu Oracle i skłonienie do odczytywania wielu bloków w
jednym fizycznym procesie dostępu do dysku. Można na przykład w jednym odczycie pobrać
800 wierszy. Natomiast w przypadku indeksu zwykle jeden fizyczny odczyt danych
odpowiada pobraniu informacji o jednym wierszu zwróconym w zapytaniu z tabeli.
Jeśli proces przeglądania indeksu pobiera ponad 10% wierszy z tabeli, wówczas przegląd
całej tabeli prawdopodobnie stanowi o wiele szybszą metodę niż przeglądanie indeksu wraz z
dodatkowymi fizycznymi odwołaniami do tabeli w celu pobrania wymaganych danych.
Wyjątkiem od tej zasady jest sytuacja, gdy całość zapytania może zostać obsłużona przez
indeks bez konieczności odwoływania się do tabeli. W takim przypadku przeglądanie indeksu
może być niesłychanie efektywne. A jeśli zapytanie zawiera wyrażenie ORDER BY i
zawartość indeksu jest ustawiona w takiej samej kolejności jak kolumny w wyrażeniu
ORDER BY, unika się konieczności sortowania, co jeszcze bardziej zwiększa wydajność.

Dodawanie kolumn do indeksów

W prezentowanym poniżej przykładowym fragmencie kodu czas wykonania polecenia
używanego w dużej firmie maklerskiej zmniejszono z 40 do 3 sekund w przypadku rachunku
o identyfikatorze account_ id=100101, który okazał się najobszerniejszym rachunkiem w
tabeli. Czas odpowiedzi miał kluczowe znaczenie dla udzielania bieżących odpowiedzi na

zapytania klientów. Problem rozwiązano dzięki dodaniu do indeksu wszystkich kolumn z
wyrażenia WHERE oraz wymienionych po słowie kluczowym SELECT. Niewielkim
mankamentem takiego rozwiązania jest to, że należy od tej pory obsługiwać dodatkowy
indeks. Jednak korzyści w tym konkretnym przypadku znacznie przewyższyły koszty.

SELECT SUM(val)
FROM account_tran

WHERE account_id = 100101
AND fin_yr ••= '1996';
Na początku indeks istniał tylko dla account_id, zaś nowy indeks istniał dla account_id, finyr,
val. W rezultacie indeks całkowicie zapewniał obsługę zapytania i dostęp do tabeli nie był
konieczny.
Inny często występujący problem pojawia się w przypadku złączania tabel, kiedy kolumna
wiodąca indeksu nie jest kolumną złączenia obu tabel. Na przykład:

WHERE a.surname = 'GURRY'
AND a.acct_no = t.acct_no

AND t.tran_date > 'Ol-JUL-97

AND t.tran_type = 'SALARY'
W takiej sytuacji w wielu systemach istnieć będzie indeks w kolumnie surname tabeli acct
oraz indeks w tran_date oraz tran_type dla tabeli trans. W celu znacznego przyspieszenia
wykonywania zapytania najlepiej jest dodać kolumnę złączeniową acct_no jako kolumnę
wiodącą indeksu dla tabeli trans. Potrzebne są następujące indeksy:

Indeks ACCT na (SURNAME)
Indeks TRANS na (ACCT_NO, TRANJDATE, TRAN_TYPE)

Indeksowanie małych tabel
Innym często powtarzającym się problemem jest brak indeksów dla małych tabel. Często
słyszy się argumenty, że indeks w takim przypadku nie jest potrzebny, gdyż tabela jest mała i
dane tak czy inaczej będą przechowywane w pamięci. Często też padnie uwaga, że tabela
może zostać utworzona z atrybutem CACHE.
Z doświadczeń Autora wynika, że każda mała tabela powinna posiadać indeks. Istnieją dwa
powody przemawiające za takim podejściem: unikatowość wierszy w tabeli można wymusić
poprzez klucz unikatowy lub główny oraz - co ważniejsze - optymalizator ma możliwość
określenia optymalnego planu wykonania zapytań względem tabeli. Znajdujące się w
poniższej tabeli przykładowe dane pokazują, że czas odpowiedzi w przypadku pewnego
zapytania spadł z 347 do 39,72 sekund, kiedy dla tabeli utworzono indeks. Największą wadą
wynikającą z braku indeksu jest to, że bez niego optymalizator często będzie określał
nieoptymalne plany wykonania.

Bez indeksu

Z indeksem

CPU

Czas

CPU

Czas

ANALIZA
SKŁADNIOWA

0,00

0,03

0,01

0,11

WYKONANIE 146,14 347,36 18,09 39,60

POBRANIE

0,00

RAZEM

146,14 347,39 18,10 39,72

Problem trzeci: korzystanie ze scalania indeksu jednokolumnowego

Na początku książki Autor wspominał, że system Oracle złącza jedynie indeksy
jednokolumnowe. Jest tak, jeśli nie użyje się wskazówki INDEX_JOIN. Scalanie indeksów
jednokolumnowych jest niewygodne we wszystkich relacyjnych bazach danych, nie tylko
Oracle. Powoduje to, że pozycja każdego indeksu powoduje odczyt wartości pochodzących z
obu indeksów.
Warto zapoznać się z poniższym przykładem, który opiera się na schemacie
wykorzystywanym przez znaną firmę maklerską:

INDEX1(approve_flag) oraz
INDEK2(adviser_code)

SELECT COUNT(*)
FROM account_master

WHERE approve_flag = 'Y'
AND adviser_code = 'IAM' ,

3,509 sekund
Liczba wierszy tabeli account_master, w których pole approve_ flag ma wartość Y, wynosi
około miliona. System Oracle odczytuje wszystkie te wiersze (gdzie występuje wartość Y), a
następnie znacznie mniejszą liczbę wierszy, w których pole adviser_code ma wartość IAM, a
w końcu łączy oba rezultaty. Oczywiście istnieje dużo mniej wierszy z wartością N pola
approve_flag.
Istnieje jednak sposób, w jaki można obejść ten problem w systemie Oracle: należy po prostu
utworzyć indeks zawierający obie kolumny. W przykładzie poniższym po utworzeniu nowego
indeksu powinno się usunąć indeks jednokolumnowy dla adviser_code.
CREATE INDEX mg1

ON account_master (adviser_code, approve_flag)
SELECT COUNT(*)

FROM account_master
WHERE approve_flag = 'Y'

AND adviser_code = 'IAM';
0,041 sekund
Należy podkreślić tu, że zmniejszył się czas wykonania z 3,509 sekund do 0,041 sekundy.

Problem czwarty: błędne użycie pętli zagnieżdżonych, sortowania i

łączenia lub złączeń haszujących

Jeśli użytkownik pozostawi wszystkie parametry pliku INIT.ORA przy ich wartościach
domyślnych, wówczas istnieć będzie w systemie zdecydowana preferencja względem
stosowania pętli zagnieżdżonych (nested loops) dla złączeń tabel. Pętle zagnieżdżone są
bardzo dobrym rozwiązaniem w przypadku systemów przetwarzania transakcyjnego, jednak
mogą okazać się nieodpowiednie w systemach raportowania lub w przypadku tych, które
korzystają z procesów wsadowych. Optymalizator regułowy zawsze używa pętli
zagnieżdżonych, chyba że za pomocą wskazówek lub innych środków (na przykład poprzez
usunięcie wszystkich indeksów tabeli) nakaże mu się inne postępowanie.
Aplikacje, w których wyniki zapytań prezentowane są na ekranie, z pewnością powinny
korzystać z pętli zagnieżdżonych, ponieważ w tym przypadku dane będą zwracane
natychmiast. W typowej sytuacji jednorazowo na ekranie prezentowanych będzie 20 wierszy,
a kontynuowanie pobierania danych uzależnione będzie od użytkownika, który zechce pobrać
następny zbiór danych. Zastosowanie odpowiednich indeksów zapewni czas odpowiedzi
(konieczny do pobrania zbioru danych) w granicach sekundy lub dwóch.
Zgodnie z praktyczną regułą - jeśli zapytanie zwraca mniej niż 10% wierszy tabeli - powinno
korzystać się z pętli zagnieżdżonych. Z kolei złączenia haszujące (hash joins) oraz sortowanie
i łączenie (sort merge) powinny być używane, gdy zwracane jest 10% lub więcej wierszy.

W celu przeprowadzenia złączenia haszującego w pamięci najmniejszej tabeli tworzona jest
tabela haszowania (hash labie), a następnie przeglądana jest druga z tabel. Wiersze drugiej z
tabel porównywane są z tabelą haszowania.
Złączenie haszujące zazwyczaj działa szybciej niż złączenie przez scalanie (merge join), które
jest związane z sortowaniem, a następnie scalaniem, jeśli wielkość pamięci jest wystarczająca
do przechowania całej tabeli haszowania. Cały zbiór wynikowy musi zostać określony
przed zwróceniem użytkownikowi pierwszego wiersza. Stąd złączenia haszujące są używane
zazwyczaj w przypadku przetwarzania wsadowego lub raportowania.
Wielu administratorów i programistów baz danych ślepo wierzy w to, że złączenie haszujące
jest szybsze od złączenia przez scalenie. Jednak nie zawsze tak jest. Złączenie haszujące może
być wykorzystane tylko w przypadku złączeń opartych na warunku równości (=), ale nie w
przypadku złączeń opartych na zakresach wartości (<, < = , >, >=). Jeśli w wyrażeniu
WHERE znajdują się kolumny złączenia w postaci WHERE o . owner<=w . owner, wówczas
złączenie haszujące nie zadziała.
Złączenia przez scalenie działają wydajnie w przypadku złączeń opartych tak na warunku
równości, jak i tych, które opierają się na zakresach. Często będą one działać szybciej, jeśli
wszystkie kolumny wymienione w wyrażeniu WHERE będą z góry posortowane należąc do
indeksu. W takim przypadku wiersze są po prostu pobierane z tabeli przy użyciu pola ROWID
w indeksie.
W przypadku złączeń przez scalenie wszystkie tabele podlegają posortowaniu, chyba że
wszystkie kolumny wyrażenia WHERE znajdują się w indeksie. Sortowanie może być
kosztowne i wyjaśnia to, dlaczego złączenie haszujące często działa szybciej od złączenia
przez scalenie. Podobnie jak w przypadku złączeń haszujących, cały zbiór wynikowy musi
zostać określony, zanim możliwe będzie zwrócenie użytkownikowi pierwszego wiersza. Stąd
złączenia przez scalenie oraz haszujące są zazwyczaj używane przy raportowaniu i procesach
wsadowych.
Odkrycie tego, że sytuacja, w której stosowane są pętle zagnieżdżone, wymaga innego
sposobu postępowania, zmusza do podjęcia odpowiednich działań. Po pierwsze - można
skorzystać ze wskazówek, takich jak USE_NL, USE_HASH lub USE_MERGE. Jednak w
przypadku korzystania z gotowego oprogramowania pojawia się problem, kiedy okazuje się,
że wskazówek zastosować nie można. Rozwiązaniem problemu może być wówczas
utworzenie perspektywy dla celów raportowania i umieszczenie w niej wskazówki. Jeśli
korzysta się z optymalizatora regułowego, można przejść do używania optymalizatora
kosztowego, który potrafi rozstrzygnąć, która metoda złączania jest w danej sytuacji
najodpowiedniejsza.
Ponadto kilka parametrów pliku ORA.INIT może wpłynąć na dokonywany wybór. Są to
między innymi: SORT_AREA_SIZE, HASH_ AREA_SIZE, HASH_JOIN_ENABLED,
OPTIMIZER_MODE, DB_FILE_MULTIBLOCK_READ_COUNT,
OPTIMIZER_MODE_ENABLE, OPTIMIZER_INDEX_CACHING oraz
OPTIMIZER_INDEX_COST_ ADJ. Wszystkie te parametry już opisano w podrozdziale:
„Problem szósty: nieodpowiednie ustawienia parametrów w pliku INIT.ORA".
Przykładem wpływu owych parametrów na system jest doświadczenie Autora, który odkrył, iż
pozostawienie wartości domyślnej parametrów SORT_AREA_SIZE oraz
HASH_AREA_SIZE (rozmiar dwa razy większy niż w przypadku poprzednim) sprawia, że
wynoszą one - odpowiednio - 64K oraz 128K. Autor spotkał systemy, w których
optymalizator kosztowy korzystał ze złączeń haszujących tylko, jeśli wartość
HASH_AREA_SIZE wynosiła ponad l megabajt.
Należy dokładnie poznać znaczenie wszystkich wymienionych parametrów pliku INIT.ORA
oraz ich wpływ na decyzje podejmowane przez optymalizator.

Problem piąty: błędne użycie IN, EXISTS, NOT IN, NOTEXISTS lub

złączeń tabel

Czytelnik prawdopodobnie zastanawia się, co zapewnia lepszą wydajność: użycie NOT IN
czy NOT EXIST, użycie IN, EXISTS czy złączenia tabel. W rzeczywistości odpowiedź zależy
od okoliczności. Nawet budzący obawy operator NOT IN może działać wydajnie przy
zastosowaniu odpowiedniej wskazówki. W podrozdziale niniejszym wymieniono realne
przykłady, które mogą pomóc w określeniu najlepszej w danej sytuacji konstrukcji.

Złączenie wydajniejsze od zapytania podrzędnego

Ogólnie rzecz biorąc - złączenia tabel są wydajniejsze od stosowania podzapytań.
Doświadczenia Autora sugerują także, że jeśli użycie podzapytania jest konieczne,
skorzystanie z operatora EXISTS daje w większości przypadków lepsze rezultaty niż
zastosowanie operatora IN. Jednakże zawsze istnieją wyjątki. Powodem, dla którego złączenia
działają zazwyczaj szybciej niż zapytania podrzędne, jest fakt, że te pierwsze zazwyczaj
korzystają z indeksów, zaś te ostatnie z przeglądu całej tabeli.
Oto przykład:
SELECT ..... FROM emp e

WHERE EXISTS (SELECT 'x'
FROM dept d

WHERE d.dept_no = e.dept_no
AND d.dept_cat = 'FIN');

Czas: 0,47578 sekund
O SELECT STATEMENT Optimizer=CHOOSE

1 O SORT (AGGREGATE)
2 l FILTER

3 2 TABLE ACCESS (FULL) OF 'EMP

4 2 AND-EQUAL

5 4 INDEX (RANGĘ SCAN) OF 'DEPT_NDX1' (NON-UNIQUE)
6 4 INDEX (RANGĘ SCAN) OF 'DEPT_NDX2' (NON-UNIQUE)
Należy zwrócić uwagę na przegląd całej tabeli emp, a także czas (tu: 47,578 milisekund)
potrzebny na wykonanie polecenia. Złączenia korzystają raczej z indeksów. Poniższe
zapytanie, które zwraca ten sam wynik, wykonywane jest znacznie szybciej:

SELECT ..... FROM emp e, dept d
WHERE e.deptano = dept d

AND d.dept_cat = 'FIN

;

Czas: 0,2153 sekund

O SELECT STATEMENT Optimizer=CHOOSE
1 O SORT (AGGREGATE)

2 l NESTED LOOPS
3 2 TABLE ACCESS (BY ROWID) OF 'DEPT

4 3 INDEX (RANGE SCAN) OF 'DEPT_NDX2' (NON-UNIQUE)
5 2 INDEX (RANGE SCAN) OF 'DEPT_NDXl' (NON-UNIQUE)
Jak widać, zapytanie wykorzystujące złączenie wykonane zostało w czasie 2,153 milisekund,
co wydaje się imponujące w porównaniu z poprzednią wartością47,578 milisekund.

Porównanie IN z EXISTS
Wydajność zastosowania obu powyższych operatorów zależy od sytuacji. Przy wykorzystaniu
EXISTS plan wykonania jest sterowany przez tabele pochodzące z zewnętrznej instrukcji
SELECT; jeśli użyte zostanie IN, najpierw przetwarzane będzie zapytanie podrzędne, a
dopiero potem złączanie z każdym wierszem zwróconym przez zapytanie zewnętrzne.
W przykładzie poniższym warto zwrócić uwagę na fakt, że tabela horses w zewnętrznej
instrukcji SELECT jest przetwarzana w pierwszej kolejności i to ona steruje zapytaniem:

SELECT h.horse_name
FROM horses h

WHERE horse_name LIKE 'C%'
AND EXISTS (SELECT 'x

FROM winners w
WHERE w.position = 1

AND w.location = 'MONEE VALLEY'
AND h.horse_name = w.horse_name);

Execution plan
O SELECT STATEMENT Optimizer=CHOOSE

l O FILTER
1 l INDEX (RANGĘ SCAN) OF

2 'HORSES_PK' (UNIQUE)
3 l TABLE ACCESS (BY INDEX

4 ROWID) OF 'WINNERS

5 3 INDEX (RANGĘ SCAN) OF

6 'WINNERS_NDX1' (NON-UNIQUE)
Sytuacja ulegnie odwróceniu, kiedy użyje się IN. Poniższe zapytanie daje ten sam wynik,
jednak wykorzystano w nim operator IN zamiast EXISTS. Należy zwrócić uwagę na to, że
najpierw następuje odczyt z tabeli zapytania podrzędnego i to ona steruje zapytaniem:
SELECT h.horse name

FROM horses h
WHERE horse name LIKE 'CV

AND horse name IN (SELECT horse_name
FROM winners w

WHERE w.position = l
AND w.location = 'MONEE YALLEY

;

Execution plan
O SELECT STATEMENT Optimizer=CHOOSE

1 O NESTED LOOPS
2 l VIEW OF 'VW_NSO_1'

3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'WINNERS

5 4 INDEX (RANGE SCAN) OF
‘WINNERS_NDX4' (NON-UNIQUE)

6 l INDEX (UNIQUE SCAN) OF
'HORSES_PK' (UNIQUE)
Wystarczy stwierdzić, że w większości sytuacji lepszym rozwiązaniem jest użycie EXISTS
zamiast IN. Wyjątek stanowi przypadek, gdy w tabeli podzapytania istnieje bardzo mała
liczba wierszy, a tabela zapytania głównego posiada dużą liczbę wierszy, które muszą zostać
odczytane w celu wykonania zapytania.
W kolejnym przykładzie wykorzystana została tabela tymczasowa, która ma zwykle około
2000 wierszy. Tabeli tej używa się w zapytaniu podrzędnym. Tabela zapytania zewnętrznego
liczy ponad 16 000 000 wierszy. W przykładzie tym tabela podzapytania podlega złączeniu z
tabelą główną przy użyciu wszystkich kolumn jej klucza głównego. Jest to przykład sytuacji,
w której wykorzystanie IN oznacza znaczny wzrost wydajności w porównaniu z operatorem
EXISTS.
Najpierw zaprezentowane zostanie rozwiązanie z użyciem EXISTS:

DELETE
FROM ps_pf_ledger_fOO

WHERE EXISTS (SELECT 'x

FROM ps_pf_led_pst2_tl b
WHERE b.business_unit = ps_pf_ledger_f00.

business_unit
AND b.fiscal_year = ps_pf_ledger_f00.

fiscal_year
AND b.accounting_period = ps_pf_ledger_f00.

accounting_period
AND b.pf_scenario_id = ps_pf_ledger_f00.

pf_scenario_id
AND b.source = ps_pf_ledger_f00.source

AND b.account = ps_pf_ledger_f00.account
AND b.deptid = ps_pf_ledger_fOO.deptid

AND b.cust_id = ps_pf_ledger_fOO.cust_id
AND b.product_id - ps_pf_ledger_fOO.product_id

AND b.channel_id - ps_pf_ledger_f00.channel_id
AND b.obj_id = ps_pf_ledger_f00.obj_id

AND b.currency_cd = ps_pf_ledger_f00.currency_cd);
Czas: 00:08:160,51
Warto zwrócić uwagę na czas wykonania. Poniżej zaprezentowana zostanie wersja tego
samego zapytania z użyciem IN. Czas wykonania ulega znacznemu skróceniu.

DELETE FROM ps_pf_ledger_f00
WHERE (business_unit,fiscal_year,accounting_period,

pf_scenario_id,source,account,deptid, cust_id,
product_id,channel_id,obj_id,currency_cd

IN
(SELECT business_unit,fiscal_year,accounting_period,

pf_scenario_id,source,account,deptid,cust_id,
product_id,channel_id,obj_id,currency_cd FROM

ps_pf_led_pst2_tl);
Czas: 00:00:00,30
W celu przyspieszenia wykonywania zapytań z EXISTS często można skorzystać ze
wskazówek HASH_SJ oraz MERGE_SJ (obie opisano szczegółowo w rozdziale
zatytułowanym „Korzystanie ze wskazówek SQL" w dalszej części książki). Wskazówki te
pozwalają systemowi Oracle na jednorazowe zwrócenie wierszy z zapytania podrzędnego.
Wyjaśnia to poniższy przykład:

UPDATE ps_jrn1 IN
SET jrnl_line_status = 'D’

WHERE business_unit = 'A023'
AND process_instance = 0001070341

AND j ourna l_dat e
IN (TO_DATE('2001-08-01','YYYY-MM-DD’),

TO_DATE('2001-08-14','YYYY-MM-DD

))

AND ledger IN ('ACTUALS')

AND jrnl_line_status = 'O'
AND EXISTS

(SELECT /* + HASH_SJ */ 'X'
FROM ps_combo_data_tbl

WHERE setid = 'AMP'
AND process group = 'SERYICE01'

AND combination IN ('SERYICE01', ‘SERYICE02' , 'STAT_SERV1')
AND valid_code = 'V’

AND ps_jrnl_ln.journal_date BETWEEN
effdt_from AND effdt_to

AND ps_jrnl_ln.account = account
AND ps_jrnl_ln.deptid = deptid);

UPDATE STATEMENT Optimizer=Choose (Cost=9 Card=l

Bytes = 80)
UPDATE OF PS_JRNL_LN

HASH JOIN (SEMI) (Cost=9 Card=l Bytes=80)
TABLE ACCESS (BY INDEX ROWID) OF

PS_JRNL_LN (Cost=4 Card=l Bytes=33)
INDEX (RANGE SCAN) OF PS_JRNL_LN

(NON-UNIQUE) (Cost=3 Card=l)
INLIST ITERATOR

TABLE ACCESS (BY INDEX ROWID) OF
PS_COMBO_DATA_TBL (Cost=4 Card=12 Bytes=564)

INDEX (RANGE SCAN) OF PS_COMBO_DATA_TBL
(NON-UNIQUE) (Cost=3 Card=12)
Przedstawiony przykład pochodzi z aplikacji Peoplesoft i był wykonywany dwie godziny w
przypadku nieużywania HASH_SJ, zaś po zastosowaniu wskazówki uległ on skróceniu do
czterech minut. Wskazówka wymusza, aby wiersze wybrane przez instrukcję SELECT
zapytania podrzędnego zostały tylko raz odczytane, a potem zostały dołączone do tabeli
(ps_jrn1_ln) już poza podzapytaniem. Ten sam efekt dałoby ustawienie w pliku INIT.ORA
parametru ALWAYS_SEMI_ JOIN=HASH.

Problem szósty: niepotrzebne sortowanie

Pomimo wielu udoskonaleń wprowadzonych w systemie Oracle dotyczących mechanizmu
sortowania (w tym pomijanie bufora pamięci podręcznej, używanie przestrzeni tabel celowo
ustawionych jako tymczasowe oraz wydajniejsze wykorzystywanie pamięci) operacje, w
których skład wchodzi sortowanie, mogą być bardzo kosztowne i w praktyce powinno się ich
unikać.
Operacje wymagające sortowania to między innymi:
CREATE INDEX
DISTINCT
GROUP BY
ORDER BY
INTERSECT
MINUS
UNION
ZŁĄCZENIA NIEINDEKSOWANYCH TABEL
Administrator ma wiele możliwości zwiększenia wydajności sortowania, takich jak na
przykład upewnienie się, że przestrzeń tabel służąca do sortowania jest tymczasowa
(TEMPORARY), zapewnienie dużej wartości parametru SORT_AREA_SIZE, co pozwala na
wykonanie większej liczby sortowań w pamięci, upewnienie się, że domyślne obszary
INITIAL oraz NEXT przestrzeni tabel TEMP są wielokrotnościami wartości parametru
SORT_AREA_SIZE, a także upewnienie się, że wszystkim użytkownikom została
prawidłowo przydzielona w celach sortowania przestrzeń tabel TEMP.
Można także stosować się do pewnych zaleceń w czasie pisania kodu SQL w celu unikania
sortowania, co omawiają poniższe podrozdziały.

Wykorzystanie UNION ALL zamiast UNION

Programiści tworzący skomplikowane zapytania z wykorzystaniem wyrażenia UNION zawsze
powinni zastanowić się, czy nie wystarczy UNION ALL. Wyrażenie UNION wymusza
sortowanie i scalenie wszystkich wierszy zwróconych przez różne zapytania w nim
umieszczone tak, aby możliwe było odrzucenie wszelkich duplikatów, zanim do modułu
wywołującego nie zostanie zwrócony pierwszy wiersz. Z kolei UNION ALL zwraca wszystkie
wiersze (w tym duplikaty) i w tym przypadku nie są konieczne operacje sortowania, scalania i
filtracji.
Oto przykładowy kod:

SELECT acct_num, balance_amt
FROM debit_transactions

WHERE tran_date = '31-DEC-95'
UNION

SELECT acct_num, balance_amt
FROM credit_transactions

WHERE tran_date = '31-DEC-95

;

W celu zwiększenia wydajności działania należy kod powyższy zastąpić następującym:
SELECT acct_num, balance_amt

FROM debit_transactions
WHERE tran_date = '31-DEC-95'

UNION ALL
SELECT acct_num, balance_amt

FROM credit_transactions
WHERE tran_date = ' 31-DEC-95 ' ;
Jeśli dla programu istotną kwestią jest pozbycie się duplikatów wierszy, wówczas nie ma się
innej możliwości i trzeba skorzystać z wyrażenia UNION.

Wykorzystanie indeksu w celu uniknięcia konieczności sortowania
Indeksów można używać w celu uniknięcia konieczności wykonywania sortowania. Indeksy
są domyślnie przechowywane w kolejności rosnącej. Jeśli kolumny wyrażenia ORDER BY
mają tę samą kolejność, co kolumny w indeksie, wówczas wymuszenie wykorzystania tego
indeksu przy obsłudze polecenia sprawia, że dane zwracane są w żądanej kolejności.
Wymuszenie użycia indeksu możliwe jest poprzez zastosowanie wskazówki lub fikcyjnego
wyrażenia WHERE. Warto rozpatrzyć znajdujący się poniżej kod polecenia, które
wykonywane jest w przypadku tabeli posiadającej indeks w kolumnach (acc_name,
acc_surname):

SELECT acc_name, acc_surname
FROM account acct

ORDER BY acc_name;
Poniższa wersja tego samego polecenia wykorzystuje wskazówkę w celu wymuszenia użycia
indeksu:
SELECT /*+ INDEX_ASC(acct acc_ndxl) */ acc_name,

acc_surname
FROM account acct

ORDER BY acc_name;
Z drugiej strony - fikcyjne wyrażenie WHERE jest często wykorzystywane w przypadku kodu
służącego do wyświetlania wyników na ekranie na bieżąco. Przedstawiony poniżej przykład
wykorzystuje wyrażenie WHERE acc_name > chr (l) zamiast wyrażenia ORDER BY.
Wyrażenie WHERE wymusza użycie indeksu, co powoduje, że wiersze są zwracane w
posortowanej kolejności. Jedną z korzyści płynących z wyeliminowania sortowania w
aplikacji działającej na bieżąco jest fakt, że pierwsza porcja danych do wyświetlenia może być
zwrócona bardzo szybko.
SELECT acc_name, acc_surname

FROM account acct

WHERE acc_name > chr(l);
W przypadku takiego polecenia nie ma potrzeby używania wyrażenia ORDER BY. Gdyby
jednak zostało umieszczone w poleceniu, a użytkownik nie określiłby żadnych kryteriów
sortowania, to po wciśnięciu przycisku wyszukiwania każdy wiersz tabeli musiałby zostać
posortowany, zanim jakiekolwiek dane pojawiłyby się na ekranie. Może to zająć dość dużo
czasu, a taka sytuacja jest niepożądana w przypadku aplikacji OLTP.

Problem siódmy:

zbyt wiele indeksów dla tabeli

Autor spotkał się z ośrodkami, w których jako praktycznej reguły używano tej, która
dotyczyła założenia, że żadna tabela nie powinna posiadać więcej niż sześć indeksów. Często
będzie to sprawiać, że polecenia SQL będą bardzo dobrze wykonywane, jednak w kilku
przypadkach sytuacja okaże się znacznie gorsza, a dodatkowych indeksów nie będzie można
dodać, gdy tabela będzie posiadała wspomnianą ilość sześciu indeksów.
Czasem istnieć mogą indeksy nadmiarowe (redundantne), takie jak na przykład w
następującej sytuacji: INDEX_1 na kolumnach (A,B), INDEX_2 na (A,B,C) oraz INDEX_3
na (A,B,C,D). W takim przypadkach administrator często sugeruje usunięcie pierwszych
dwóch indeksów, ponieważ są redundantne, to znaczy mają tę samą kolumnę wiodącą i
kolumny ustawione w tej samej kolejności, co indeks ostatni. Jednakże usunięcie indeksów
redundantnych może spowodować problemy z wyborem nowej tabeli sterującej w przypadku
złączenia obsługiwanego przez optymalizator regułowy (patrz podrozdział: „Reguły RBO o
tym nie mówią"). Ryzyko to znacznie się zmniejsza, jeśli wykorzystywany zostanie
optymalizator kosztowy.
Posiadanie wielu indeksów dla jednej tabeli ma zazwyczaj niewielki wpływ na systemy
OLTP, ponieważ w pojedynczej transakcji przetwarzana jest niewielka liczba wierszy, zaś
narzut związany z uaktualnianiem indeksów liczony jest w milisekundach.
Z kolei w przypadku przetwarzania wsadowego posiadanie wielu indeksów może być bardzo
kosztowne, gdyż w typowej sytuacji występuje wiele operacji wstawienia, uaktualnienia i
usunięcia danych. Przedstawia to tabela 5.

Tabela 5. Wpływ wielu indeksów na wydajność procesu wstawiania danych

Liczba wstawianych wierszy i indeksów

Czas

Wstawienie 256 wierszy przy braku indeksów

1,101 sekund

Wstawienie 512 wierszy przy braku indeksów

1,161 sekund

Wstawienie 256 wierszy przy 5 indeksach

3,936 sekund

Wstawienie 512 wierszy przy 5 indeksach

12,788 sekund

Wstawienie 256 wierszy przy 10 indeksach

12,558 sekund

Wstawienie 512 wierszy przy 10 indeksach

22,132 sekund

W przypadku niektórych systemów problem ten rozwiązuje się przez usunięcie indeksów
przed wykonaniem operacji wsadowych oraz ponownym ich utworzeniem po zakończeniu. W
systemie Oracle wprowadzono wiele mechanizmów pomagających w szybkim odtworzeniu
indeksów. Można na przykład odtwarzać indeksy przy użyciu opcji NOLOGGING oraz
UNRECOVERABLE, a ponadto można je odtwarzać w procesie przetwarzania równoległego.
Pomimo tych ułatwień w przypadku tabel o bardzo dużym rozmiarze proces odtwarzania
indeksów może trwać dłużej niż czas wykonania programu wsadowego razem z narzutem
związanym z istnieniem indeksów.
Autor sugeruje, aby unikać zasad głoszących, że w systemie powinna istnieć określona
maksymalna liczba indeksów.
System Oracle9i daje pewną nową, doskonałą możliwość pozwalającą na identyfikowanie

indeksów, które nie są wykorzystywane. Odpowiednie polecenie to ALTER INDEX
MONITORING USAGE. Warto z niego korzystać w celu określania i usuwania
nieużywanych indeksów.
Ostatecznym celem wszelkich działań jest zapewnienie wydajnego działania poleceń SQL, a
zawsze istnieje na to sposób. Jeśli okaże się, że potrzeba 10 indeksów do tabeli, należy owe 10
indeksów utworzyć.

Problem ósmy: użycie OR zamiast UNION

Przy każdorazowym strojeniu różnych systemów, niemal zawsze Autor spotyka kilka
niezwykłych problemów, które z łatwością można rozwiązać. Jednym z nich jest podzapytanie
OR EXISTS. W kilku systemach Autor spotkał się z poleceniami podobnymi do
następującego:

SELECT .....
FROM ps_jrnl_header a

WHERE jrnl_hdr_status = 'E'
OR EXISTS

(SELECT 'x’
FROM ps_jrnl_header

WHERE business_unit_iu = a.business_unit_iu
AND journal_id = a.journal.id

AND journal_date = a.journal.date
AND unpost_seq = a.unpost_seq

AND jrnl_hdr_status = 'E');
Czas wykonania: 4 minuty 16 sekund
Warto zwrócić uwagę na długi czas wykonania polecenia. Rozwiązanie tego problemu jest
proste. Jeśli istnieje możliwość modyfikacji kodu (czyli nie jest to gotowe oprogramowanie),
należy użyć instrukcji UNION tak, aby OR zaimplementować w wyrażeniu WHERE w formie
dwóch oddzielnych zapytań:

SELECT .........
FROM p_jrnl_header a

WHERE jrnl_hdr_status = 'E’

UNION

SELECT .........
FROM ps_jrnl_header a, ps_jrnl_header b

WHERE b.business_unit_iu = a.business_unit_iu
AND b.journal_id = a.journal.id

AND b.journal_date = a.Journal.date
AND b.unpost_seq = a.unpost_seq

AND a.jrnl_hdr_status = 'E'
AND b.jrnl_hdr_status != 'E';

Czas wykonania: 0,02 sekund
Powodem, dla którego wydajność wykonania ulega takiej poprawie, jest to, że UNION
pozwala optymalizatorowi na wykonanie dwóch prostych operacji w celu zwrócenia wierszy,
zaś bardziej złożona konstrukcja OR sprawiła, że optymalizator określił nieoptymalny plan
wykonania.

Problem dziewiąty: tabele i indeksy z wieloma wierszami usuniętymi

System Oracle jest podobny do wielu innych systemów baz danych w tym, że istnieją pewne
kwestie dotyczące wydajności, które są związane z usuwaniem danych. System Oracle
charakteryzuje się wysokim wskaźnikiem watermark, który oznacza największą możliwą
liczbę wierszy wstawianych do tabeli. Ten wysoki wskaźnik może mieć wpływ na wydajność.

Warto zastanowić się nad przedstawionym poniżej kodem, którego wykonanie, czyli dostęp
do danych tabeli liczącej 151 070 wierszy, zajmuje 5,378 sekund:

SELECT COUNT(*)
FROM yyyy;

151070
Czas: 5,378 sekund
Przypadkiem we wszystkich ponad 150 000 wierszy wartość kolumny state wynosi VIC, a
tabela posiada indeks w kolumnie state. Jeśli skorzysta się z wyrażenia WHERE w celu
wymuszenia użycia tego indeksu do zliczania, czas wyniesie 16,884 sekund:
SELECT COUNT(*) FROM yyyy WHERE state = 'VIC';
151070
Czas: 16,884 sekund
Należy zwrócić uwagę, że przeglądanie z użyciem indeksu trwało około trzy razy dłużej niż
przegląd całej tabeli. Nawiasem mówiąc - powyższe polecenie SELECT wykonano przy
użyciu optymalizatora regułowego. Optymalizator kosztowy przeprowadziłby przegląd całej
tabeli.
Załóżmy, że w tym momencie ulegają usunięciu wszystkie wiersze i w rezultacie otrzymuje
się pustą tabelę:

DELETE FROM yyyy;
Czas: 55,277 sekund
Przy pustej tabeli ponownie następuje zliczenie wierszy:

SELECT COUNT(*) FROM yyyy;
0

Czas: 5,117 sekund
A zatem mniej więcej tyle samo czasu zajmuje zliczenie zera wierszy, co zliczenie
istniejących wierszy, kiedy tabela jest zapełniona danymi. Dzieje się tak dlatego, że
wykonując przegląd całej tabeli, system Oracle odczytuje dane aż do osiągnięcia pozycji
watermark, a jego wysoka wartość nie uległa w tym przypadku zmianie.
Przy ponownym wykonaniu zliczania przy użyciu indeksu...
SELECT COUNT(*) FROM yyyy
WHERE state = 'VIC';
0
Czas: 16,029 sekund
... dzieje się tak jak poprzednio - czas potrzebny na wykonanie zliczenia zera wierszy oraz 150
000 wierszy jest niemal ten sam. Jest tak dlatego, że pozycje w indeksie zostały usunięte tylko
logicznie, ale fizycznie wciąż istnieją.
W tabeli nigdy nie było wierszy z kolumną state o wartości NSW (New South Wales, Nowa
Południowa Walia), ale wykonanie zapytania z użyciem tej wartości daje następujący rezultat:
SELECT COUNT{*) FROM yyyy WHERE state = 'NSW;

Czas: 16,940 sekund
Zliczanie wciąż zajmuje tyle samo czasu. Jest tak dlatego, że przeglądanie indeksu wymaga,
aby system Oracle przeglądał logicznie usunięte wpisy z wartością VIC (Victoria).
W celu uniknięcia tego typu problemów wydajnościowych, Autor sugeruje ponowne
utworzenie tabeli i jej indeksów (zawsze po usunięciu z niej wielu danych). Jeśli kolumny
zaindeksowane są często uaktualniane, to także wtedy należy utworzyć indeksy ponownie,
ponieważ operacja uaktualnienia danych powoduje logiczne usunięcie danych w indeksie, a
następnie wstawienie nowego, uaktualnionego wpisu. W niektórych ośrodkach posuwa się to
nawet do tego, że indeksy są odbudowywane co noc, kiedy występuje duże nasilenie operacji
logicznego kasowania.
W celu zbadania tego, których tabel dotyczą częste operacje usuwania (DELETE) i
uaktualniania (UPDATE) danych, można wykonać następujące polecenie SELECT:
SELECT sql_text, executions

FROM v$sqlarea
WHERE UPPER(sql_text) LIKE 'DELETE'

OR
UPPER(sql_text) LIKE 'UPDATE%';
Informacja wyjściowa przekazywana przez to polecenie zawierać będzie polecenia SQL
wykonywane w przypadku tabel, których dotyczą częste operacje usuwania i uaktualniania
danych. Należy rozważyć regularne odbudowywanie indeksów dla tych tabel.

Inne problemy:

intensywne używanie perspektyw

Innym często występującym problemem jest nadmierne używanie perspektyw (views of views)
co może całkowicie zmylić oba typy optymalizatorów (podobnie jak osoby próbujące dostroić
takie polecenia). Należy pamiętać, że korzystanie ze wskazówek w perspektywach perspektyw
często nie daje stałej i zadowalającej wydajności. Używanie wskazówek w perspektywie
zewnętrznej jest lepsze od korzystania z nich z poziomu perspektywy wewnętrznej.

Inne problemy: złączanie zbyt wielu tabel

Złączanie ponad pięciu tabel niemal zawsze powoduje nieprawidłowe działanie obu typów
optymalizatorów i daje w wyniku nieoptymalny plan wykonania. Warto zapoznać się w tym
miejscu raz jeszcze z uwagami podrozdziału: „Problem piąty: złączanie zbyt wielu tabel",
który znajduje się w rozdziale zatytułowanym „Problemy i ich rozwiązania w przypadku
optymalizatora kosztowego". Jeśli użytkownik ma możliwość modyfikowania kodu poleceń
SQL, to może użyć wskazówek w celu obejścia tego problemu.
Częste wykonywanie w aplikacji złączania ponad pięciu tabel wskazuje zwykle na to, że w
procesie projektowania poświęcono zbyt mało uwagi kwestii wydajności, gdy logiczny model
danych podlegał tłumaczeniu na model fizyczny.

Drobne porady
dotyczące strojenia poleceń SQL

W poniższych podrozdziałach wymieniono pewne porady dotyczące strojenia poleceń SQL.
Mogą one okazać się przydatne zarówno w trakcie tworzenia kodu SQL, jak i rozwiązywania
problemów z wydajnością.

Identyfikowanie złego kodu SQL

Polecenia SQL umieszczone w niniejszym podrozdziale mają na celu zademonstrowanie
sposobu identyfikowania poleceń, które charakteryzują się oczekiwanym czasem odpowiedzi
większym niż 10 sekund. Założono przy tym, że wykonywane jest 300 dyskowych operacji
wejścia i wyjścia na sekundę, a także to, że możliwe jest przeprowadzenie 4000 operacji na
buforze w ciągu sekundy. Parametry takie są typowe dla maszyn średniego i wysokiego
standardu.
W celu zidentyfikowania poleceń, których wykonanie wymaga średnio ponad 3000 operacji
odczytu z dysku (co przekłada się na czas 10 sekund), należy wydać następujące polecenia
programu SQL*Plus:

column "Response" format 999,999,999.99;
column n1 newline;

ttitle 'SQL With Disk Reads > 10 Seconds'
SELECT sql_text n1, 'Executions='||

executions n1,
'Expected Response Time in Seconds= ', disk_reads / decode

(executions, O, l,
executions) / 300 "Response" FROM v$sql

WHERE disk_reads / decode(executicns, O, l,
executions)

/ 300 > 10
AND executions > O

ORDER BY hash_value, child_number;
Podobnie - następujące polecenia programu SQL*Plus identyfikują polecenia, których
wykonanie powoduje ponad 40000 operacji dostępu do bufora:
column "Response" format 999,999,999.99;

ttitle 'SQL Buffer Scan > 10 Seconds'
SELECT sql_text ni, 'Executions='||

executions n1, 'Expected Response Time in Seconds= ',
buffer_gets /

decode(executions, O, l, executions)
/ 4000 "Response"

FROM v$sql
WHERE buffer_gets / decode(executions, O, l,

executions)
/ 4000 > 10

AND executions > O
ORDER BY hash_value, child_number;
Po zidentyfikowaniu poleceń SQL o małej wydajności działania można spróbować je dostroić.

Identyfikowanie

długo wykonujących się poleceń SQL

W systemie Oracle8i (i nowszych) istnieje doskonały mechanizm, który zachowuje informacje
o bieżących, długo wykonujących się zapytaniach w perspektywie V$SESSION_LONGOPS.
Poniżej przedstawiony przykładowy kod pokazuje rezultat zapytania wydanego względem
perspektywy V$SESSION_LONGOPS:
SELECT username, sql_text, sofar, totalwork, units
FROM v$sql, v$session_longops
WHERE sql_address=address
AND sql_hash_value=hash_value
ORDER BY address, hash_value, child_number;

HROA
select count(*} from winners w1, winners_backup w2

where wl.owner=w2.owner||''
1061 7098 Blocks
W przykładzie tym użytkownik HROA uruchamia polecenie SELECT COUNT, którego
wykonanie dobiega około 15%. Innymi słowy - przetworzonych zostało 1061 z 7098 bloków
(1061/7098 = 15%). Polecenie zapisano tak, że nie wykorzystuje ono indeksu w kolumnie
owner tabeli winners_backup, ponieważ użyto operatora konkatenacji względem
tej kolumny. Być może administrator powinien zadzwonić do użytkownika HROA i zapytać
go o powyższe zapytanie, a nawet je anulować, jeśli okaże się, że będzie się ono wykonywało
jeszcze przez jakiś czas.

Użycie polecenia DECODE dla instrukcji wyboru IF/ELSE

Programistom często potrzebny jest odpowiedni sposób na zliczenie i (lub) dodanie różnych

zmiennych warunków dla grupy wierszy/. Polecenie DECODE jest dość skomplikowane i
niewielu programistów poświęca czas na poznanie jego składni na tyle, aby móc wykorzystać
cały jego potencjał. Polecenie poniższe korzysta z DECODE w celu zliczenia liczby
pierwszych, drugich oraz trzecich miejsc, jakie zajął koń wyścigowy:

SELECT horse_name, TO_CHAR(SUM(DECODE(position, 1, l, 0) ) )
, TO_CHAR(SUM(DECODE(position,2,1,0)))

, TO_CHAR(SUM{DECODE(position,3,1,0)))
FROM winners

GROUP BY horse_name;
Konstrukcja SUM(DECODE (position, 2 , l, 0) „mówi", że jeśli końcowa pozycja konia była
druga (2), należy dodać jeden do licznika drugich miejsc. Wynik tego polecenia ma więc
postać:
Horse Firsts Seconds Thirds
WildCharm 1 2 2

Alternatywna forma polecenia (bez użycia DECODE) wymaga trzykrotnego przeglądu tabeli
zamiast jednorazowego, co miało miejsce w poprzednim poleceniu:
SELECT horse_name,

COUNT(wl.position)
COUNT(w2.position)

COUNT(w3.position)
FROM winners wl, winners w2, winners w3

WHERE w1.horse_name = w2.horse_name
AND w2.horse.name = w3.horse_name
AND wl.position = l

AND w2.position = 2
AND w3.position = 3

GROUP BY horse_name;

Zmienne dowiązane

Wartości zmiennych dowiązanych (bind variables) nie muszą być takie same dla obu poleceń,
by można było traktować je jako identyczne. Jeśli odwołania do zmiennej dowiązanej są
identyczne, wówczas zanalizowane składniowo polecenia też traktowane są jako identyczne.
W konsekwencji polecenia współużytkują jedną formę zanalizowaną składniowo w obszarze
pamięci wspólnej.
Poniżej znajdują się dwa polecenia SQL możliwe do współużytkowania:
SELECT * FROM emp WHERE emp_no = B1; Wartość dowiązana: 123
SELECT * FROM emp WHERE emp_no = :B1; Wartość dowiązana: 987
Polecenia te są współużytkowane, ponieważ użyto zmiennych dowiązanych, co uczyniło oba
polecenia identycznymi. Faktyczna różnica pomiędzy wartościami dowiązanymi nie powoduje
tego, że polecenia traktowane są jako różne.
Znajdujące się poniżej dwa polecenia SQL nie mogą być współużytkowane. Dzieje się tak
dlatego, że nie skorzystano ze zmiennych dowiązanych, w wyniku czego bezpośrednio
umieszczone w kodzie wartości czynią polecenia różnymi.
SELECT * FROM emp WHERE emp_no = 123;

SELECT * FROM emp WHERE emp_no = 987;
Uogólniając - należy zachęcać programistów do używania zmiennych dowiązanych zamiast
bezpośredniego zapisywania wartości w kodzie. Pozwoli to na przechowywanie poleceń w
pamięci tylko raz (zamiast kilkukrotnie dla każdej kombinacji wartości zmiennych).
Używanie zmiennych dowiązanych może oznaczać dodatkowy wysiłek programistyczny, więc
powinno się tak postępować tylko wówczas, gdy jest
rzeczą pewną, że dane polecenie SQL będzie używane wielokrotnie, a jedynie wartości

zmiennych będą ulegać zmianie.
Istnieje jedna sytuacja, w której użycie zmiennych dowiązanych jest niewskazane. Jeśli dane
w kolumnie pewnej tabeli mają dużą liczbę pól o pewnej wartości i bardzo małą liczbę pól o
innych wartościach, należy skorzystać z histogramów. Zmienne dowiązane nie dają
możliwości użycia informacji udostępnianych przez histogramy.
Załóżmy, że istnieje tabela account licząca 3 miliony wierszy, a w jej ramach rachunek
SUSPENSE liczący milion wierszy. Wszystkie inne rachunki w tabeli liczą średnio po
kilkaset wierszy. Kiedy użytkownik użyje wyrażenia WHERE account = ' SUSPENSE',
najbardziej efektywnym sposobem wykonania okaże się przegląd całej tabeli. Z drugiej strony
- dużo bardziej efektywnym sposobem jest użycie indeksu, jeśli użytkownik poda nazwę
jakiegokolwiek innego rachunku.
Optymalizator kosztowy ma możliwość używania histogramów do inteligentnego korzystania
zarówno z przeglądu pełnej tabeli, jak i indeksów na podstawie wartości podanej w wyrażeniu
WHERE. Użycie zmiennych dowiązanych uniemożliwia to, ponieważ optymalizator w
momencie określania planu wykonania nie zna wartości, która zostanie dowiązana do
zmiennej w czasie wykonania.
W systemie Oracle9i wprowadzono parametr o nazwie CURSOR_ SHARING. Pozwala on na
bezpośrednie zapisywanie w kodzie wartości literałów, liczb lub dat, a mimo to nadal można
współużytkować je i przechowywać w pamięci tylko jedną wersję polecenia. Jest tak, gdy
wartość parametru CURSOR_SHARING ustawi się na FORCE lub SIMILAR. Korzystanie z
tego parametru i bezpośrednie zapisywanie wartości w wyrażeniach WHERE pomaga
optymalizatorowi kosztowemu w podejmowaniu znacznie lepszych decyzji.
W systemie Oracle9i optymalizator ma dostęp do wartości zmiennych dowiązanych w
momencie określania planu wykonania. Dzięki temu można uniknąć konieczności
bezpośredniego zapisu wartości w kodzie i polegania na parametrze CURSOR_SHARING =
FORCE lub SIMILAR, aby móc wykorzystać zalety zmiennych dowiązanych.

Korzystanie ze wskazówek SQL

Wskazówki (hints) mogą być umieszczane w kodzie SQL w celu wymuszenia na
optymalizatorze użycia konkretnego planu wykonania dla osiągnięcia najlepszej wydajności.
Poniższe podrozdziały opisują wskazówki dostępne w systemach do wersji Oracle9i.
Umieszczając w kodzie poleceń SQL własne wskazówki jako „komentarze" wymusza się, aby
wykonanie polecenia odbywało się zgodnie z określoną ścieżką (w miejsce obliczonej przez
optymalizator). W przykładzie poniższym dodanie /*+ RULE */ wewnątrz instrukcji SELECT
nakazuje wykorzystanie optymalizatora regułowego zamiast kosztowego:
SELECT /*+ RULE */....
FROM emp, dept
WHERE . . .
Wskazówki optymalizatora można umieszczać jedynie za początkowym słowem kluczowym
polecenia SQL, a i tak większość z nich jest ignorowana w przypadku poleceń typu INSERT:

SELECT /*+ tekst wskazówki */....
DELETE /*+ tekst wskazówki */....

UPDATE /*+ tekst wskazówki */....
Każda wskazówka ma zastosowanie tylko dla bloku polecenia, w którym występuje.
Blok polecenia to:
• proste polecenie SELECT, DELETE lub UPDATE;
• główna część polecenia złożonego;
• podzapytanie polecenia złożonego;
• część złożonego zapytania.
Warto rozpatrzyć następujący przykład:
SELECT /*+ RULE */....

FROM emp
WHERE emp_status = 'PART-TIME'

AND EXISTS
(SELECT /*+ FIRST_ROWS */ 'x’

FROM emp_history
WHERE emp_no = e.emp_no

AND emp_status != 'PART-TIME')
SELECT /*+ RULE*/ ....

FROM emp
WHERE emp_status = 'PART-TIME'

UNION
SELECT /*+ ALL_ROWS */....

FROM emp_history
WHERE emp_status ! = 'PART-TIME';

Ignorowanie wskazówek

Optymalizator nie potrafi odróżnić źle zapisanej wskazówki od komentarza. Niepoprawnie
sformułowana lub błędnie zapisana wskazówka jest ignorowana i nie jest wyświetlany żaden
komunikat o błędzie.
Poniższa lista wymienia niektóre z częściej popełnianych błędów wiążących się z używaniem
wskazówek. Nie zawarto w niej błędów oczywistych, takich jak pomyłki w zapisie
wskazówki lub nazwie obiektu.
Aliasy tabel
Wszystkie wskazówki wymagają tego, aby parametr tabeli poprawnie określał nazwę tabeli.
Oznacza to, że jeśli w poleceniu SQL odwołanie do tabeli następuje poprzez jej alias, to
wskazówka także musi odwoływać się poprzez alias. Na przykład:

SELECT /*+ INDEX(emp emp_PK) */ ...
FROM emp e

WHERE e.emp_no = 12345;
W przykładzie tym wskazówka zostanie zignorowana, ponieważ odwołuje się do tabeli
poprzez jej nazwę emp. Wskazówka powinna bowiem użyć aliasu e, ponieważ nadano go
tabeli emp.

Nieprawidłowe umieszczenie wskazówki
Wskazówki mogą pojawić się tylko tuż po pierwszym słowie kluczowym bloku polecenia.
Jeśli wskazówka zostanie umieszczona
w innym miejscu, zostanie zignorowana. Poniższe przykłady ilustrują poprawne
umiejscowienie wskazówek:
SELECT /*+ tekst wskazówki */....

DELETE /*+ tekst wskazówki */ . . . .
UPDATE /* + tekst wskazówki */....

SELECT /*+ RULE */....
FROM emp

WHERE emp_status = 'PART-TIME'
UNION

SELECT /*+ ALL_ROWS */ . . . .
FROM emp_history

WHERE emp_status ! = 'PART-TIME’;

Korzystanie ze wskazówek w perspektywach

Firma Oracle zniechęca do korzystania ze wskazówek w perspektywach, ponieważ

perspektywy mogą być używane w różnych kontekstach od jednego do kolejnego użycia.
Autor przyznaje racje temu, że perspektywy mogą być używane w różnych kontekstach,
jednak trzeba przyznać, że wskazówki w perspektywach stanowią jeden z najbardziej
przydatnych mechanizmów strojenia. Są one szczególnie przydatne w przypadku operacji
raportowania użytkownika końcowego. Niektóre programy służące do raportowania (takie jak
PeopJesoft nVision) nie będą działać w okolicach osiągalnego optimum bez wykorzystania
wskazówek.

Dostępne wskazówki

Poniższa lista opisuje wszystkie wskazówki dostępne w systemie Oracle9i. Wiele z nich było
dostępnych także we wcześniejszych wersjach systemu. Celem poniższej listy nie jest
wyczerpujące opisanie składni każdej ze wskazówek, lecz przedstawienie sposobu, w jaki
każda z nich jest zazwyczaj stosowana.

ALL_ROWS
Optymalizuje zwrócenie wszystkich wierszy wybranych przez zapytanie przy jak
najmniejszym wykorzystaniu zasobów. Wskazówka ta może spowodować czasem zastąpienie
NESTED LOOPS przez SORT MERGE lub HASH JOIN, jeśli zostanie użyta w poleceniu
SELECT, UPDATE lub DELETE, a parametr OPTIMIZER_MODE będzie miał wartość
CHOOSE.
SELECT /*+ ALL_ROWS */ ...

AND_EQUAL(tabela indeks1 indeks2 [...indeks5])
Jawnie określa scalenie indeksów jednokolumnowych. Podane muszą być co najmniej dwa
indeksy, ale nie więcej niż pięć. Scalenie indeksów jednokolumnowych może być bardzo
niewydajne, jeśli pierwszy indeks wyrażenia WHERE zwraca dużą liczbę wierszy.
SELECT /*+ AND_EQUAL(horse_owners ho_ndxl Ho_ndx2, ho_ndx3) */
COUNT(*)
FROM horse_owners
WHERE horse_name = 'WILD CHARM'
AND owner = 'Mr M A Gurry'
AND identifier = 14;

APPEND
Pozwala na bezpośrednie wstawienie danych do tabeli. Dane te pomijają bufor pamięci
podręcznej (buffer cache) i są dopisywane na końcu tabeli. W czasie procesu ładowania
danych ignorowane są ograniczniki spójności danych (integrity constraints), choć Autor
zaobserwował, że po zakończeniu tego procesu wykonywane jest sprawdzenie spójności
danych i polecenie może się zakończyć niepowodzeniem ze zwróconym błędem ogranicznika
spójności danych.

INSERT /*+ APPEND */ * INTO y
SELECT FROM winners;

CACHE( tabela)
Nakazuje optymalizatorowi ustawienie wszystkich bloków pobranych przez przegląd całej
tabeli na ostatnio używanym końcu listy LRU (Least Recently Used, najdawniej używane)
bufora pamięci podręcznej. Wskazówki tej używa się zazwyczaj w przypadku
małych tabel, ale Autor spotkał systemy o bardzo dużej pojemności pamięci podręcznej
bardzo dużych tabel, których zawartość była rzadko zmieniana.

SELECT /*+ FULL(winners) CACHE(winners) */
COUNT(*) FROM winners;

CHOOSE
Powoduje wykorzystanie optymalizatora kosztowego, gdy dostępne są statystyki dla
przynajmniej jednej tabeli; w przeciwnym przypadku używany jest optymalizator regułowy.
SELECT /*+ CHOOSE */

CLUSTER(tabela)
Wymusza użycie przeglądu klastra (cluster scan) dla danej tabeli. Wskazówka ta może być
użyta tylko w przypadku obiektów, które zostały umieszczone w klastrze.. Klaster stanowi
dwie lub większą ilość tabel powiązanych z rekordami przodków (parents) i związanych z
nimi potomków (childs) przechowywanych fizycznie obok siebie. Na przykład accountl
będzie mieć swoje transakcje przechowywane w tym samym fizycznym bloku co rekord
account.
SELECT /*+ CLUSTER(a) */ acct_name
FROM acct a;

CURSOR_SHARING_EXACT
Zapobiega tłumaczeniu przez system Oracle literałów na zmienne dowiązane, nawet jeśli
parametrowi CURSOR_SHARING nadano wartość FORCE lub SIMILAR. Na przykład:
SELECT /* + CURSOR_SHARING_EXACT */ name, suburb
FROM emp
WHERE surname = 'Gurry';
Gdyby w tym poleceniu SQL nie użyto powyższej wskazówki, zaś parametr
CURSOR_SHARING miałby wartość FORCE lub SIMILAR, wówczas wartość Gurry
zostałaby przetłumaczona na zmienną dowiązaną.

FACT( tabela)
Informuje optymalizator kosztowy, że podana tabela jest tabelą faktów i powinna być w
odpowiedni sposób obsługiwana. Wskazówka ta jest używana w przypadku operacji
STAR_TRANSFORMATION.

SELECT /*+ FACT(results) */

FIRST_ROWS lub FIRST_ROWS(n)
Optymalizuje zwrócenie pierwszych n wierszy wybranych przez zapytanie przy jak
najkrótszym czasie odpowiedzi. Dla tabel wymienionych w poleceniu SQL statystyki nie
muszą być obecne, gdyż zostaną one oszacowane przez optymalizator. Korzystając z tej
wskazówki można zawrzeć inne „wskazówki dotyczące ścieżki dostępu" i wówczas
FIRST_ROWS może zostać pominięta. Jeśli skorzysta się z opcji (n) w celu określenia
dokładnej liczby zwracanych wierszy, system Oracle będzie mógł podjąć bardziej precyzyjną
decyzję dotyczącą planu wykonania. Opcja ta jest dostępna jedynie w systemie Oracle9i (i
nowszych).
Przykład:
SELECT /*+ FIRST_ROWS(100) */
Wskazówka powyższa spowoduje:
• zawsze - wybranie indeksu zamiast przeglądu całej tabeli;
• o ile będzie to możliwe - użycie złączenia z pętlą zagnieżdżoną zamiast złączenia
sortowania i scalania;
• o ile będzie to możliwe - użycie indeksu w celu spełnienia wymagań wyrażenia ORDER
BY.
Optymalizator ignoruje powyższą wskazówkę użytą w blokach poleceń DELETE i UPDATE,
a także w każdym bloku polecenia SELECT, które zawiera operację „grupowania" (UNION,
INTER-SECT, MINUS, GROUP BY, DISTINCT, MAX, MIN, SUM itd.) lub wyrażenie FOR
UPDATE. Polecenia takie nie mogą zostać zoptymalizowane do jak najkrótszego czasu

odpowiedzi, ponieważ zanim pierwszy z wierszy zostanie zwrócony, musi nastąpić dostęp do
wszystkich wierszy.

FULL(tabela)
Wymusza użycie przeglądu całej tabeli dla tabeli podanej jako parametr.

SELECT /*+ FULL(emp) */ ename
FROM emp
WHERE commencement_ciate > SYSDATE -7 ;
Jeśli tabela posiada alias, właśnie jego należy użyć we wskazówce:
SELECT /*+ FULL(a) */ ename
FROM emp a WHERE a.commencement_date > SYSDATE -7;
HASH(tabela)
Wymusza użycie przeglądu tabeli haszowanej w przypadku tabeli podanej jako parametr.
Wskazówka ta ma zastosowanie jedynie w przypadku tabel przechowywanych w klastrze.
SELECT /*+ HASH(a) */ acct_name
FROM acct a
Wiele osób myli powyższą wskazówkę z USE_HASH, która wymusza użycie złączenia
haszująccgo. Nie są to wskazówki identyczne!
HASH_AJ
Zapewnia znaczny wzrost wydajności poprzez zmianę z operacji pętli zagnieżdżonej dla NOT
IN na operację złączenia haszującego. Wskazówka ta musi zostać umieszczona w poleceniu
SELECT podzapytania, a nie zapytania głównego.

SELECT COUNT(*)
FROM horses

WHERE horse_name LIKE 'M%'
AND horse_name NOT IN

(SELECT /*+ HASH_AJ */ horse_name
FROM horse_owners

WHERE owner LIKE ' %Lombardo% ' ) ;

HASH_SJ
Często pozwala na skrócenie czasu odpowiedzi w przypadku podzapytania EXISTS dzięki
jednorazowemu tylko zwróceniu wierszy z podzapytania.
SELECT COUNT(*)

FROM horses
WHERE horse_name LIKE 'M%'

AND EXISTS
(SELECT /*+ HASH_SJ */ horse_name

FROM horse_owners
WHERE owner LIKE '%Lombardo%'

AND horses.horse_name = horse_owners.horse_name);
Istnieją pewne ograniczenia dotyczące wykorzystania tej wskazówki:
• w podzapytaniu może być wymieniona tylko jedna tabela;
• wskazówki nie można używać w zapytaniu podrzędnym podzapytania;
• podzapytanie musi być związane z predykatem równości, co jest wymagane w przypadku
wszystkich złączeń haszujących;
• podzapytanie nie może zawierać wyrażenia GROUP BY lub CONNECT BY ani
odwołania ROWNUM.

INDEX( tabela [indeks [indeks...]])
Wymusza użycie indeksowanego przeglądu tabeli w przypadku tabeli podanej jako parametr.
Opcjonalnie można we wskazówce podać jeden lub większą liczbę indeksów. Jeśli nie poda

się żadnych indeksów, optymalizator obliczy dla tabeli koszt każdego z indeksów i wybierze
najbardziej efektywny (możliwe jest wykorzystanie kilku indeksów). Jeśli poda się kilka
indeksów, optymalizator obliczy koszt tych tylko indeksów i wybierze najbardziej efektywny
(możliwe jest wykorzystanie kilku z podanych indeksów, jeśli będą one jednokolumnowe).
Jeśli poda się indeks jednokolumnowy, optymalizator wykona przegląd za pomocą tego
indeksu.

SELECT /*+ INDEX(emp emp_ndxl) */
SELECT /*+ INDEX(emp) */

INDEX_ASC(tabela [indeks])
Wymusza użycie dla podanej tabeli przeglądu z wykorzystaniem indeksów w kolejności
rosnącej. Jako że system Oracle domyślnie przegląda indeksy w kolejności rosnącej, może
pojawić się pytanie
o zasadność korzystania z powyższej wskazówki. Zapewnia ona, że indeksy będą przeglądane
w kolejności rosnącej, nawet jeśli system Oracle zdecydowałby o odmiennym zachowaniu.
Wyjątkiem od reguły jest sytuacja, gdy indeks utworzono jako indeks klucza odwróconego (na
przykład CREATE INDEX POST ON OWNERS (ZIPCODE) REVERSE).
SELECT /*+ INDEX_ASC(emp emp_ndx1) */...

INDEX_COMBINE(tabela [indeks [indeks...]])
Jawnie nakazuje wykorzystanie indeksów bitmapowych w celu dostępu do danych w tabeli.
SELECT /* + INDEX_COMBINE(acct_tran at_state_bmi at_type_bmi) */

INDEX_DESC(tabela [indeks])
Wymusza użycie dla podanej tabeli przeglądu z wykorzystaniem indeksów w kolejności
malejącej. Domyślnie system Oracle przegląda indeksy w kolejności rosnącej. Wskazówka
powyższa gwarantuje, że indeks będzie przeglądany w kolejności malejącej. Typowym jej
użyciem jest pobieranie danych na temat ostatnio wykonanych transakcji na rachunku w
banku w kolejności malejącej według daty. Wskazówka ta może oddać spore usługi w
przypadku zapytań rozproszonych (distributed ąueries).
SELECT /* + INDEX_DESC(acct_trans acct_trans_ date_ndx) */

INDEX_FFS(tabela [indeks])
Nakazuje optymalizatorowi wykonanie przeglądu całego indeksu zamiast przeglądu całej
tabeli. Przegląd indeksu może czasem działać szybciej, ale tylko wtedy, gdy każda kolumna
wymieniona w wyrażeniu WHERE dla pewnej tabeli istnieje także w indeksie.
SELECT /*+ INDEX„FFS(acct_trans at_state_ndxl) */

INDEX_JOIN(tabela [indeks] tabela [indeks2])
Wskazówka ta nakazuje optymalizatorowi złączenie dwóch indeksów. Typowo plan
wykonania zawierał będzie złączenie haszujące
owych indeksów, co dać może pewne korzyści wydajnościowe. W przykładzie
zaprezentowanym poniżej dwie z trzech kolumn klucza głównego tabeli zostały użyte w
wyrażeniu WHERE (horse_ name oraz owner), a ponadto - kolumna wiodąca (identif ier)
indeksu niezwiązanego z kluczem głównym:

SELECT /*+ INDEX_JOIN(horse_owners ho_ndx2 ho_pk) */
horse_name, owner

FROM horse_owners
WHERE horse_name = 'WILD CHARM'

AND owner = 'Mr M A Gurry'
AND identifier = 10;
Gdyby nie użyto wskazówki INDEX_JOIN, optymalizator dokonałby złączenia tylko

indeksów jednokolumnowych.

MERGE(tabela)
Wymusza scalenie zagnieżdżonej (dołączonej) perspektywy z głównym zapytaniem
sterującym. W podanym niżej przykładzie dołączona perspektywa (inline view) wyrażenia
GROUP BY jest scalana z danymi wybranymi z tabeli owners.
Wskazówka ta może być również użyta w podzapytaniach, jeśli instrukcja IN jest
nieskorelowana (uncorrelated), czyli nie odwołuje się do kolumn złączenia z zapytania
głównego.

SELECT /*+ MERGE(w) */ o.owner,
w.num_wins, o.suburb

FROM owners o,
(SELECT owner, COUNT(*) num_wins

FROM winners
WHERE position = l

GROUP BY owner) w
WHERE o.owner = w.owner

AND w.num_wins > 15
ORDER BY w.num_wins DESC;

MERGE_AJ
Zapewnia znaczny wzrost wydajności poprzez zmianę z operacji pętli zagnieżdżonej dla NOT
IN na operację złączenia przez scalenie (podobnie do HASH_AJ). Wskazówka ta musi zostać
umieszczona w poleceniu SELECT znajdującym się w podzapytaniu. a nie w zapytaniu
głównym.
Czasem zapewnia pewien wzrost wydajności poprzez wymuszenie operacji pętli
zagnieżdżonej dla NOT IN. Jednakże wydajność pętli
zagnieżdżonych jest często mniejsza od złączeń haszujących oraz złączeń przez sortowanie i
scalanie. Wskazówka musi odnosić się do polecenia SELECT podzapytania, a nie do
zapytania głównego.

SELECT COUNT(*)
FROM horses

WHERE horse_name LIKE 'M%'
AND horse_name NOT IN

(SELECT /*+ NL_AJ */ horse_name
FROM horse_owners

WHERE owner LIKE '%Lombardo%');

NL_SJ
Wskazówka powyższa jest podobna do wskazówek HASH_S J oraz MERGE_SJ, lecz
nakazuje wykorzystanie operacji pętli zagnieżdżonej dla złączenia.
SELECT COUNT(*)

FROM horses
WHERE horse_name LIKE 'M%'

AND EXISTS
(SELECT /*+ NL_SJ */ horse_name

FROM horse_owners
WHERE owner LIKE '%Lombardo%'

• podzapytanie musi być związane z predykatem równości;
• podzapytanie nie może zawierać wyrażenia GROUP BY lub CONNECT BY ani
odwołania ROWNUM.

NO_EXPAND
Zapobiega rozbiciu zapytania na mniejsze części, co stanowi niemal odwrotność wskazówki
USE_CONCAT.
SELECT /*+ NO_EXPAND */ COUNT(*)
FROM horse_owners
WHERE identifier < 10 OR identifier > 20;

NO_FACT(tabela)
Informuje optymalizator kosztowy o tym, że podana tabela nie jest tabelą faktów i nie
powinna być jako taka traktowana. Wskazówka ta jest używana przy przetwarzaniu
STAR_TRANSFORMATION.
SELECT /*+ NO_FACT(reults) */

NO_MERGE(tabela)
Zapobiega scaleniu zagnieżdżonej (dołączonej) perspektywy.

SELECT /*+ NO_MERGE(w) */ o.owner,
w.num_wins, o.suburb

FROM owners o,
(SELECT owner, COUNT(*) num_wins

FROM winners
WHERE position = l GROUP BY owner) w

WHERE o.owner = w.owner
AND w.num_wins > 15

ORDER BY w.num_wins DESC;

NO_PUSH_PRED(tabela)
Zapobiega użyciu predykatu złączenia względem perspektywy dołączonej .
SELECT /*+ NO_PUSH_PRED(v) */ COUNT(*)

FROM horses h,
SELECT w.horse_name, o.owner, w.position

FROM winners w, owners o
WHERE w,owner = o.owner) v

WHERE h.horse_name = v.horse_name
AND v.position = 1;

NO_UNNEST
Zapobiega scaleniu podzapytania z poleceniem głównym. Może być używana tylko wtedy,
gdy UNNEST_SUBQUERY=TRUE.

SELECT /*+ NO_UNNEST */ COUNT(*)
FROM horses

WHERE horse_name LIKE 'M%'
AND horse_name NOT IN (SELECT horse_name

FROM horse_owners
WHERE owner LIKE '%Lombardo%');

NOAPPEND
Przeciwieństwo APPEND; powoduje użycie konwencjonalnej metody wstawienia danych do
tabeli. Nie ma gwarancji, że dane zostaną dopisane na końcu tabeli. Wstawiane wiersze nie

omijają bufora pamięci podręcznej, a ponadto brane są pod uwagę ograniczniki integralności
danych.

INSERT /*+ NOAPPEND */ * INTO y
SELECT FROM winners;

NOCACHE(tabela)
Nakazuje optymalizatorowi ustawienie wszystkich bloków pobranych z danej tabeli na
ostatnio używanym końcu listy LRU (Least Recently Used, najdawniej używane) bufora
pamięci podręcznej przy korzystaniu z przeglądu całej tabeli. Jest to normalne zachowanie w
przypadku przeglądu całej tabeli.

SELECT /*+ FULL(winners) NOCHACHE(winners) */
COUNT(*}

FROM winners;

NOINDEX(tabela [indeks [index...]])
Wyklucza użycie podanych indeksów w planie wykonania danego zapytania.
SELECT /*+ NOINDEX(emp emp_ndx1) */
Jeśli podana zostanie jedynie nazwa tabeli, nie będzie mógł być wykorzystany żaden
istniejący dla niej indeks.

SELECT /*+ NOINDEX(emp) */

NOPARALLEL(tabela)
Zapobiega wykorzystaniu przez system Oracle mechanizmu równoległości (wielu procesów)
w celu przeglądu danej tabeli. Na przykład załóżmy, że mechanizm równoległości będzie
udostępniany za pomocą polecenia:
ALTER TABLE x PARALLEL 2;
System Oracle będzie starał się wykorzystać dwa równoległe procesy, gdy wymagane będzie
dokonanie przeglądu tabeli. Poniższe polecenie korzysta ze wskazówki NOPARALLEL w
celu zapobieżenia użyciu mechanizmu równoległości:
SELECT /*+ NOPARALLEL(x) */ COUNT(*)
FROM x;

NOPARALLEL_INDEX(tabela. indeks)
Zapewnia, że nie będzie miało miejsca równoległe przetwarzanie indeksu w przypadku
indeksu podzielonego (partitioned index).
SELECT /*+ NOPARALLEL_INDEX(emp, emp_ndx) */

NOREWRITE
Zapobiega wykorzystaniu przez system Oracle perspektyw zmaterializowanych na podstawie
wybranej tabeli. Jest to odwrotność wskazówki REWRITE.

SELECT /*+ NOREWRITE */ horse_name, owner, position,
COUNT(*)

FROM results
GROUP BY horse_name, owner, position;

ORDERED
Wymusza na optymalizatorze złączenie tabel w tej samej kolejności, w jakiej zostały
wymienione w wyrażeniu FROM (od strony lewej do prawej). Wskazówka ta może dać
ogromne korzyści w kwestii wydajności w środowisku raportowania. Zwykle jest także zasadą
to, że im więcej tabel podaje się w wyrażeniu FROM, tym większe korzyści daje
zastosowanie tej wskazówki. Oto przykład użycia:

SELECT /*+ ORDERED */

acct_name, trans._date, amount, dept, address
FROM trans t, account a, category c,

branch b, zip z
WHERE t.trans_date > SYSDATE - 30

AND a.zip = z . zip
AND z.state = 'WA'

AND t.account BETWEEN 700000 AND 799999
AND t.account = a.account

AND a.account = 'ACTIVE'
AND a.category = c.category

AND c.category = 'RETAIL'
AND t.branch_id = b.branch_id

AND b.branch = 'BELLEVUE';
Zazwyczaj indeks sterujący - a więc także tabela sterująca zapytaniem -jest określany poprzez
typ indeksu, liczbę kolumn w indeksie, liczbę wierszy w indeksie itd. Na przykład tabela,
która posiada indeks unikatowy, względem którego w wyrażeniu WHERE występuje warunek
równości, ma pierwszeństwo w zostaniu tabelą sterującą przed tabelą, która posiada indeks
nieunikatowy dla kolumny wymienionej w wyrażeniu WHERE.
Co interesujące -jeśli wszystkie tabele otrzymują jednakową ocenę, optymalizator kosztowy
korzysta z tabel wymienionych w wyrażeniu FROM w kolejności od lewej do prawej, co jest
dokładnie odwrotnym zachowaniem względem optymalizatora regułowego. Jednakże w
przypadku skomplikowanego zapytania, rzadko zdarza się, aby wszystkie warunki w
wyrażeniu WHERE były równe. Wykorzystanie tej wskazówki zapewnia daną kolejność
złączania.

ORDERED_PREDICATES
Powoduje, że predykaty wyrażenia WHERE są obliczane w kolejności, w jakiej w nim
występują. Jeśli nie użyje się ORDERED_ PREDICATES, system Oracle obliczy najpierw
podzapytania oraz funkcje użytkownika.
SELECT ...

WHERE /*+ ORDERED_PREDICATES */
Jest to jedyna wskazówka, którą umieszcza się w wyrażeniu WHERE, nie zaś tuż po
pierwszym słowie kluczowym rozpoczynającym polecenie.
PARALLEL(tabela [, integer] [, integer])
Jawnie określa faktyczną liczbę współbieżnych serwerów zapytania (query servers), które
obsłużą jego wykonanie. Pierwsza opcjonalna wartość określa poziom równoległości (liczbę
serwerów zapytania) dla danej tabeli. Jest to liczba procesów przydzielonych do
przeprowadzenia równoległego przeglądu tabeli. Druga wartość opcjonalna określa liczbę
równoległych serwerów systemu Oracle, które mają zostać rozdzielone na całe zapytanie. Jeśli
określone zostanie PARALLEL (emp, 4 2 ), wówczas istnieć będą cztery równoległe procesy
działające na dwóch odrębnych serwerach równoległych. Jeśli nie zostanie podany żaden
parametr, domyślny (obliczony) poziom równoległości oraz liczba serwerów równoległych są
określane na podstawie parametrów podanych w pliku INIT.ORA.
Wskazówka ta może być używana do operacji pobierania, uaktualniania, usuwania i
wstawiania danych. W celu osiągnięcia korzyści wydajnościowych pliki danych muszą być
rozdzielone (przeplatane, striped) na kilka dysków. Nie należy ustalać poziomu równoległości
na wartość wyższą niż liczba dysków, na których mogłaby być rozdzielona tabela. W
środowisku wieloprocesorowym operacja może być wykonana jeszcze szybciej, ale tylko
wtedy, gdy tabela jest rozdzielona.
SELECT /*+ PARALLEL(x 4) */ COUNT(*) FROM x;

SELECT /*+ PARALLEL(x 42) */ COUNT(*) FROM x;
UPDATE /*+ PARALLEL(x 4) */ x SET position = position + l;

DELETE /*+ PARALLEL(x 4) */ FROM x;
INSERT INTO x

SELECT /*+ PARALLEL(winners 4) */ * FROM winners;

PARALLEL_INDEX( tabela, indeks, poziom równoległości, podział klastra)
Pozwala na równoległe przeglądanie zakresów indeksów w przypadku indeksów
podzielonych. Pozwala także na to, aby praca została wykonana na kilku instancjach
architektury serwera równoległego. Przykład poniższy nakazuje optymalizatorowi
wykorzystanie mechanizmu równoległego przetwarzania indeksów dla tabeli emp, która jest
spartycjonowana, użycie indeksu emp_ndx oraz wykonanie przy poziomie równoległości
wynoszącym cztery na dwóch instancjach serwera równoległego.
SELECT /*+ PARALLEL_INDEX(emp, emp_ndx, 4, 2) */

PQ_DISTRIBUTE(tabela, [Rozkład Zewnętrzny] [Rozkład Wewnętrzny])
Używana w celu zwiększenia wydajności złączania równoległego. Istnieje sześć możliwości
w przypadku wskazówek rozkładu (distribution), które prezentuje tabela 6.
SELECT /*+ USE_HASH(o) PQ_DISTRIBUTE (o HASH, HASH) */

COUNT(*)
FROM winners w, owners o

WHERE w.owner = o.owner;

PUSH_PRED(tabela)
Wstawia do perspektywy dołączonej predykat złączenia dla tabeli. Takie działanie może
czasem pomóc optymalizatorowi kosztowemu w podejmowaniu lepszych decyzji.
SELECT /*+ PUSH_PRED(v) */ COUNT(*)
FROM horses h,
(SELECT w.horse_name, o.owner, w.position
FROM winners w, owners o
WHERE w.owner = o.owner) v
WHERE h.horse_name = v.horse_name
AND v.position = 1;
Różnica w planie wykonania dla powyższego przykładu polegać będzie na tym, że kolumna
horse_name z wyrażenia WHERE zostanie złączona z perspektywą dołączoną jako część
danych wybranych w perspektywie dołączonej.
Tabela 6. Kombinacje wskazówki PQ_DISTRIBUTE
Kombinacja rozkładu Znaczenie
HASH, HASH

Używa funkcji haszującej na złączeniu kluczy dla każdego
procesu serwera obsługi zapytania. Można używać dla
złączenia haszującego lub złączenia przez scalenie. Działa
najlepiej, kiedy tabele są mniej więcej tego samego rozmiaru.

BROADCAST, NONE Przesyła wszystkie wiersze tabeli zewnętrznej do każdego z

równoległych serwerów zapytania. Należy stosować w
przypadku, gdy tabela zewnętrzna jest dużo mniejsza od
tabeli wewnętrznej.

NONE, BROADCAST Przesyła wszystkie wiersze tabeli wewnętrznej do każdego z

równoległych serwerów zapytania. Należy stosować w
przypadku, gdy tabela wewnętrzna jest dużo mniejsza od
tabeli zewnętrznej.

PARTITION, NONE

Odwzorowuje wiersze tabeli zewnętrznej poprzez
partycjonowanie tabeli wewnętrznej. Tabela wewnętrzna musi
być spartycjonowana oraz złączona tożsamościowo (equi-join)
za pomocą kluczy złączenia. Opcja ta działa najefektywniej,

kiedy liczba partycji w tabeli zewnętrznej jest równa liczbie
używanych równoległych procesów obsługi zapytania.

NONE, PARTITION

Odwzorowuje wiersze tabeli wewnętrznej poprzez
partycjonowanie tabeli zewnętrznej. Tabela zewnętrzna musi
być spartycjonowana według kluczy złączenia. Należy użyć tej
opcji, kiedy liczba partycji w tabeli zewnętrznej jest równa
liczbie używanych równoległych procesów obsługi zapytania.

NONE, NONE

Powoduje, że każdy serwer zapytania przeprowadza operację
złączenia pomiędzy parą odpowiednich partycji (jedną z
każdej tabeli). Obie tabele muszą być spartycjonowane
tożsamościowo (equi-partitioned), aby opcja ta działała
wydajnie.

PUSH_SUBQ
Wymusza, aby podzapytania, które nie podlegają scaleniu, były według planu wykonania
przetwarzane jak najwcześniej. Podzapytanaia, które nie podlegają scaleniu, są w normalnej
sytuacji wykonywane jako ostatni etap planu wykonania. Wskazówka ta nie ma wpływu na
podzapytanie, jeśli dotyczy ono tabeli odległej (polecenie SQL przetwarzania rozproszonego)
lub jeśli w podzapyta-niu używane jest złączenie przez scalenie.

SELECT COUNT(*)
FROM horses

WHERE EXISTS
(SELECT /*+ PUSH_SUBQ */ 'x'

FROM horse_owners
WHERE owner LIKE '%Lombardo%'

AND horses.horse_name = horse_owners.horse_name) ;

REWRITE
Pozwala systemowi Oracle na wykorzystanie perspektyw zmaterializowanych opartych na
wybranej tabeli. W zaprezentowanym poniżej przykładzie istnieje tabela zawierająca rezultaty
wyścigów konnych. Utworzona została perspektywa zmaterializowana, która zawiera wartości
owner, horse_name, position oraz COUNT ( * ) dla każdej z tych kombinacji.

CREATE MATERIALIZED VIEW LOG ON results
WITH ROWID,

PRIMARY KEY (horse_name, owner, race_date)
INCLUDING NEW VALUES;

CREATE MATERIALIED VIEW winning_horse_owners_vw
USING INDEX

REFRESH ON COMMIT
ENABLE QUERY REWRITE

AS SELECT horse_name, owner, position, COUNT(*)
FROM results

GROUP BY horse_name, owner, position,-
Aby powyższa perspektywa zmaterializowana była przydatna, należy w pliku INIT.ORA
zawrzeć parametr QUERY_REWRITE_ ENABLED=TRUE, a ponadto schemat użytkownika
musi posiadać uprawnienie QUERY REWRITE. Na przykład:

GRANT

QUERY

REWRITE

hroa;

Przedstawione poniżej polecenie SQL ma możliwość pobrania wszystkich potrzebnych
danych z perspektywy, wiec dzięki temu optymalizator użyje raczej perspektywy niż tabeli,
pomimo że polecenie SELECT dotyczy właśnie tabeli.
SELECT /*+ REWRITE */ horse_name, owner, position,
COUNT(*)

FROM results
GROUP BY horse_name, owner, position;
ROWID(tabela)
Wymusza przegląd danej tabeli poprzez ROWID. Jest to fizyczny adres wiersza.

SELECT /*+ ROWID(a) */ ename
FROM emp a

WHERE rowed > 'AAAGJ2AAIAAABn4AA'
AND surname LIKE ' GURR% ';

RULE
Nakazuje wykorzystanie optymalizatora regułowego dla bieżącego bloku polecenia. Ten sam
rezultat można otrzymać poprzez nadanie parametrowi OPTIMIZER_MODE w pliku
INIT.ORA wartości CHOOSE i nieanalizowanie tabel oraz indeksów używanych w poleceniu
SELECT.

STAR
Wymusza umieszczenie największej tabeli jako ostatniej w kolejności złączania. W typowej
sytuacji pozostałe tabele powinny stanowić tabele przeglądowe (lookup tables) lub tabele
odniesienia (reference tables). Wskazówka ta jest często używana w zastosowaniach
związanych z hurtowniami danych. Wskazówka STAR ma zastosowanie tylko wówczas, gdy
złączane są co najmniej trzy tabele.
SELECT /*+ STAR */ h.horse_name, o.owner
r.position, r.location, r.race_date
FROM results r, horses h, owners o
WHERE h.horse_name LIKE 'WI%'
AND h.horse_name = r.horse_name
AND r.owner = o.owner;

STAR_TRANSFORMATION
Wykorzystywana jest w przypadku tabel faktów i wymiarów. Wskazówka jest podobna do
wskazówki STAR. Główna różnica polega na tym, że pozwala ona optymalizatorowi
kosztowemu na
określenie tego, czy ma sens transformowanie polecenia na nowe polecenie przed określeniem
planu wykonania. Poprzez „transformację" rozumie się tu rozbicie polecenia na wiele
podzapytań, które mogą wykorzystać zalety indeksów bitmapowych.
Aby móc użyć tej wskazówki, należy bezwzględnie zawrzeć w pliku INIT.ORA wpis
STAR_TRANSFORMATION_ENABLED=TRUE.
Najbardziej widoczna różnica pomiędzy tą wskazówką a wskazówką STAR to fakt, że
STAR_TRANSFORMATION często powoduje łączenie indeksów bitmapowych w różnych
kolumnach tabeli faktów zamiast wykorzystywać złączenie kartezjańskie. Jest to osiągane
dzięki rozbiciu polecenia na podzapytania.
SELECT /*+ STAR_TRANSFORMATION */

UNNEST
Scala podzapytanie z zapytaniem głównym, co często może poprawić jakość decyzji
podejmowanych przez optymalizator. UNNEST może być używana tylko przy ustawieniu
parametru sesji

UNNEST_SUBQUERY=TRUE.
SELECT /*+ UNNEST */ COUNT(*)

FROM horses
WHERE horse_name LIKE 'M%'
AND horse_name NOT IN

(SELECT horse_name
FROM horse_owners
WHERE owner LIKE '%Lombardo%';

USE_CONCAT
Wymusza użycie przez optymalizator warunków OR w wyrażeniu WHERE i ich konwersję
na operację zapytania UNION ALL. W przedstawionym poniżej przykładzie indeks podlega
dwukrotnemu przeglądowi (raz dla każdego warunku po obu stronach OR). Dane są następnie
złączane w jeden zbiór wynikowy z użyciem operatora konkatenacji.

SELECT /*+ USE_CONCAT */ COUNT(*)
FROM horse_owners

WHERE identifier < 10 OR identifier > 20;

USE_HASH(tabela)
Złączenie haszujące jest alternatywą względem pętli zagnieżdżonej. Tabela haszowania jest
tworzona w pamięci najmniejszej z tabel, a następnie przeglądane są pozostałe tabele, a ich
wiersze porównywane z tabelą haszowania. Złączenie haszujące działa szybciej od złączenia
przez scalenie (sort merge), jeśli wielkość pamięci jest wystarczająca do przechowania całej
tabeli, która podlega haszowaniu. Cała operacja złączenia musi zostać ukończona, nim do
użytkownika zostanie zwrócony pierwszy wiersz. Stąd złączenia haszujące są
wykorzystywane zazwyczaj przy raportowaniu i przetwarzaniu wsadowym.
SELECT /*+ USE_HASH(w o) */ COUNT(*)

FROM winners w, owners o
WHERE w.owner LIKE 'Mr M A Gurry'

AND w.owner = o.owner
AND o.suburb = 'RICHMOND';
Złączenie haszujące może być wykorzystane tylko w przypadku złączeń opartych na warunku
równości (=), nie zaś dla złączeń opartych na zakresach (<, < = , > = ). Złączenie przez
scalenie może być często wykorzystywane wtedy, gdy nie można użyć złączenia haszującego.

UWAGA
Nie należy mylić wskazówek HASH oraz, USE_HASH.

USE_MERGE(tabela)
Złączenie przez scalenie jest alternatywą względem złączenia za pomocą pętli zagnieżdżonej
oraz złączenia haszującego. Wszystkie tabele są sortowane, chyba że wszystkie kolumny
wyrażenia WHERE są zawarte w indeksie. Proces sortowania może być bardzo kosztowny, co
wyjaśnia dlaczego złączenie haszujące często działa szybciej od złączenia przez scalenie.

SELECT /*+ USE_MERGE(w o) */ COUNT(*)
FROM winners w, owners o

WHERE w.owner LIKE 'Mr M A Gurry'
AND w.owner < o.owner

AND o.suburb = 'RICHMOND';
Musi być zwrócony cały zbiór wynikowy, zanim możliwe będzie zwrócenie do użytkownika
pierwszego wiersza. Stąd złączenia przez scalenie są wykorzystywane zazwyczaj przy
raportowaniu i przetwarzaniu wsadowym.

UWAGA
Nie należy mylić wskazówek MERGE oraz USE_MERGE.
Złączenia przez scalenie działają wydajnie zarówno w przypadku złączeń opartych na
warunku równości, jak i zakresach wartości. Złączenia przez scalenie często także działają
dużo szybciej od złączeń haszujących, kiedy wszystkie kolumny podane w wyrażeniu

WHERE są odgórnie posortowane w indeksie.

USE_NL(tabela)
Wymusza zastosowanie przez optymalizator złączenia podanej tabeli z drugą (lub z wynikami
podzapytania) za pomocą pętli zagnieżdżonej. Podana tabela podlega złączeniu jako
wewnętrzna tabela pętli zagnieżdżonej. Złączenia tego typu szybciej niż sortowanie i scalanie
czy złączenia haszujące zwracają pierwszy wiersz wyniku zapytania.
Aplikacje wyświetlające na bieżąco wyniki zapytań z pewnością powinny wykorzystywać
pętle zagnieżdżone, ponieważ dane zostaną zwrócone natychmiast. Praktyczna reguła mówi o
tym, że jeśli zwracane jest mniej niż 10% wierszy tabeli, należy rozważyć użycie pętli
zagnieżdżonych. W przeciwnym przypadku należy użyć złączeń haszujących lub złączeń
przez sortowanie i scalanie.

SELECT /*+ USE_NL(w o) */ COUNT(*)
FROM winners w, owners o

WHERE w.owner LIKE 'Mr M A Gurry'
AND w.owner < o.owner

AND o.suburb = 'RICHMOND';

Wykorzystanie pakietu DBMS_STATS do zarządzania danymi
statystycznymi

Pakiet DBMS_STATS wprowadzono w systemie Oracle8i; zapewnia on dostęp do funkcji o
podstawowym znaczeniu dla działania optymalizatora kosztowego (a w tym przyspieszenie
procesu analizy, modyfikowanie statystyk, przywracanie poprzedniej wersji statystyk,
kopiowanie statystyk z jednego schematu lub bazy danych do innego schematu.

Użycie pakietu DBMS_STATS do przyspieszenia procesu analizy

Pakiet DBMS_STATS oferuje dwa wydajne sposoby przyspieszenia procesu analizy. Po
pierwsze - można analizować tabele (nie indeksy) równolegle. Po drugie - można analizować
tylko te tabele i związane z nimi indeksy, w przypadku których zawartość ponad 10%
wszystkich wierszy została zmodyfikowana za pomocą poleceń INSERT, UPDATE lub
DELETE.
W celu przeprowadzenia równoległej analizy tabel danego schematu użytkownika, należy
wydać następujące polecenie:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS
(OWNNAME=> 'HROA', ESTIMATE_PERCENT=>10,

DEGREE=>4, CASCADE=> TRUE
Polecenie to powoduje oszacowanie statystyk dla schematu użytkownika HROA. Wartość
DEGREE określa używany poziom równoległości. Wartość CASCADE=>TRUE powoduje,
że analizie podlegają także indeksy każdej z tabel. Wartość DBMS_STATS posiada opcję
GATHER STALE, która pozwala na analizę tylko tych tabel, w których zmianie uległo ponad
10% wierszy. W celu jej użycia należy najpierw włączyć monitorowanie wybranych tabel. Na
przykład:
ALTER TABLE winners MONITORING;
Można zapoznać się z informacjami na temat liczby zmian w danej tabeli poprzez użycie
zapytania względem perspektywy USER_TAB_ MODIFICATIONS. Sprawdzenia tego, czy
monitorowanie dla danej tabeli zostało włączone, dokonuje się poprzez odczyt wartości
kolumny MONITORING w perspektywie USER_TABLES.
Przy włączonym monitorowaniu można uruchomić GATHER_SCHEMA_ STATS używając
opcji GATHER STALE:
EXECUTE SYS . DBMS_STATS . GATHER_SCHEMA_STATS (OWNNAME=>

'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE

OPTIONS=>'GATHER STALE');
Ponieważ określono GATHER STALE, tabela będzie analizowana tylko, gdy od poprzedniej
analizy zmieni się ponad 10% jej wierszy.

Kopiowanie statystyk

przy użyciu pakietu DBMS_STATS

Pakiet DBMS_STATS daje możliwość kopiowania statystyk z jednego schematu do drugiego
lub z jednej bazy danych do drugiej przy zastosowaniu poniższej procedury.
Krok 1. Utworzenie tabeli służącej do przechowania statystyk, jeśli jeszcze taka nie istnieje:

EXECUTE SYS.DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=>
'HROA',STATTAB=>'HROA_STAT_TABLE');
Krok 2. Zapełnienie tabeli statystykami ze schematu, z którego następuje kopiowanie:

EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=> 'HROA',
STATTAB=> 'HROA_STAT_TABLE',STAT_ID=> 'HROA_21SEP_2001');
Krok 3. Jeśli statystyki są kopiowane do innej bazy danych (na przykład z bazy produkcyjnej
do rozwojowej), wymagany jest eksport oraz import tabeli statystyk:
exp hrpa/secret@prod file=stats tabels=hroa_stat_table
imp hrpa/secret@dev file=stats tabels=hroa_stat_table
Krok 4. Wstawienie statystyk do docelowego słownika danych schematu. W przykładzie
poniższym statystyki są ładowane do schematu HROA_TEST z tabeli o nazwie
HROA_STAT_TABLE:

EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>
'HROA_TEST', STATTAB=>'HROA_STAT_TABLE', STAT_ID=>

'HROA_21SEP_2001', STATOWN=>'HROA');

Manipulowanie statystykami przy użyciu pakietu DBMS_STATS

Często użytkownik chce określić, czy optymalizator kosztowy użyje tego samego planu
wykonania w bazie produkcyjnej, jakiego używa w bazie rozwojowej i testowej. Można tego
dokonać używając DBMS_ STATS.SET_TABLE_STATS w celu zmodyfikowania statystyk
dla tabeli w środowisku rozwojowym lub testowym tak, aby odpowiadały środowisku
produkcyjnemu. Optymalizator korzysta z liczby wierszy, liczby bloków i liczby różnych
wartości w kolumnach w celu określenia tego, czy użyty powinien zostać indeks, czy należy
zastosować przegląd całej tabeli.
W poniższym przykładzie zakłada się, że tabela WINNERS z bazy produkcyjnej zawiera
l000000 wierszy w 6 000 bloków:
EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=> 'HROA_DEV,
TABNAME=>'WINNERS', NUMROWS=>1000000, NUMBLKS=>6000);
Bez względu na to, jak wiele wierszy faktycznie istnieje w bazie testowej, optymalizator
kosztowy będzie działał tak, jakby istniało ich l000000.
Optymalizator korzysta także w celu podjęcia decyzji o użyciu indeksu z liczby różnych
wartości w każdej z kolumn. Jeśli liczba różnych wartości jest mniejsza niż 10% liczby
wszystkich wierszy tabeli, optymalizator
decyduje się zazwyczaj na przeprowadzenie przeglądu całej tabeli w miejsce użycia indeksu.
Zmiany odsetka liczby różnych wartości dla danej kolumny dokonuje się w następujący
sposób:
EXECUTE SYS.DBMS_STATS.SET_COLUMN_STATS (OWNNAME=> 'F70PSOFT',

TABNAME=>'PS_LED+AUTH_TBL',
COLNAME=>'OPRID', DISTCNT=>971);

Przywracanie poprzedniej wersji statystyk

Zazwyczaj ponowna analiza schematu i określenie wysokiej liczby odsetka wierszy dla
rozmiaru testowego zwiększa wydajność. Niestety, czasem wystąpić może sytuacja odwrotna.
Czasem nowe statystyki powodują określenie o wiele gorszego planu wykonania, niż miało to
miejsce wcześniej. Można uniknąć ryzyka większych problemów wykorzystując pakiet
DBMS_STATS do sporządzenia kopii bieżących statystyk tak, by w razie konieczności
można je było później przywrócić. Wymaga to wykonania następujących działań.
Krok 1. Należy wyeksportować statystyki schematu do tabeli statystyk. Jeśli tabela ta jeszcze
nie istnieje, należy ją utworzyć za pomocą procedury DBMS_STATS .
CREATE_STATS_TABLE. Eksportowanie wykonuje się w sposób następujący:
EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_.STATS
(OWNNAME=> 'HROA', STATTAB=>'HROA_STAT_TABLE',
STATID=> 'PRE_21SEP_2001');
Krok 2. Należy utworzyć nowe statystyki:
EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS
(OWNNAME=> 'HROA', ESTIMATE_PERCENT=>10,
DEGREE=>4, CASCADE= >TRUE) ;
Krok 3. Jeśli pojawią się problemy z nieodpowiednim wyborem ścieżki wykonania w wyniku
utworzenia nowych statystyk, należy przywrócić poprzedni stan statystyk ładując je z tabeli
statystyk:
EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=> 'HROA',

STATTAB='HROA_STAT_TABLE', STATID=> 'PRE_21SEP_2001');

Wykorzystanie scenariuszy
dla spójnych planów wykonania

Firma Oracle wprowadziła scenariusze (outlines) w systemie Oracle8i w celu umożliwienia
posiadania predefiniowanych planów wykonania dla poleceń SQL. Można zatem zapewnić
spójność bez konieczności zmiany samego kodu SQL. Scenariusze mogą być używane w
przypadku gotowego oprogramowania w celu zapewnienia stabilności planu wykonania bez
potrzeby ingerowania w kod SQL.

Rejestrowanie scenariuszy

Scenariusz nie jest niczym więcej niż zachowanym planem wykonania, który używa system
Oracle (zamiast określać nowy plan na podstawie bieżących statystyk). Aby można było
skorzystać ze scenariuszy, należy je najpierw zarejestrować. Można rejestrować scenariusze
dla pojedynczych poleceń lub dla wszystkich poleceń wydanych w danej sesji, a nawet - dla
wszystkich poleceń wydanych w danej instancji bazy danych.

Rejestrowanie scenariusza dla polecenia SQL
Scenariusz dla danego polecenia można zarejestrować korzystając z polecenia CREATE OR
REPLACE OUTLINE,. Na przykład:

CREATE OR REPLACE OUTLINE aug0901
FOR CATEGORY harness_racing

ON select *
from winners

where owner > ' G% ' ;
W przykładzie tym aug901 jest nazwą tworzonego scenariusza, zaś harness_racing nazwą
kategorii lub grupy powiązanych scenariuszy.

Rejestrowanie scenariuszy

dla wszystkich poleceń SQL danej sesji
Można włączyć rejestrowanie scenariuszy dla danej sesji wydając polecenie ALTER
SESSION CREATE_STORED_OUTLINES.
Należy podać nazwę kategorii w celu zapisania do niej scenariuszy:

ALTER SESSION
SET CREATE_STORED_OUTLINES =GENERAL_LEDGER;
Podanie wartości TRUE oznacza zapisanie scenariuszy w kategorii domyślnej, która nosi
nazwę DEFAULT:

ALTER SESSION
SET CREATE_STORED_OUTLINES=TRUE;
Podanie wartości FALSE powoduje zablokowanie rejestrowania scenariuszy:

ALTER SESSION
SET CREATE_STORED_OUTLINES=FALSE;

Rejestrowanie scenariuszy dla całego systemu
Składnia polecenia służącego do umożliwienia rejestracji scenariuszy na poziomie całego
systemu jest bardzo podobna do składni polecenia ALTER SESSION. Należy po prostu użyć
ALTER SYSTEM. Na przykład:

ALTER SYSTEM
SET CREATE_STORED_OUTLINES

=GENERAL_LEDGER NOOVERRIDE;
Opcja NOOVERRIDE użyta w powyższym przykładzie pozwala uniknąć usunięcia
scenariuszy z kategorii GENERAL_LEDGER. Gdyby jej nie podano, wszystkie polecenia
przechowywane w kategorii GENERAL_ LEDGER zostałyby usunięte. Przy jej podaniu
scenariusze nowych poleceń są dodawane do istniejącej kategorii. Jeśli pojawi się to samo
polecenie z uaktualnionym scenariuszem, zostanie ono zachowane.
Podobnie wygląda zablokowanie rejestrowania scenariuszy w ramach
systemu:
ALTER SYSTEM

SET CREATE_STORED_OUTLINES = FALSE;

Udostępnianie scenariuszy

W tym momencie scenariusze zostały zachowane. W celu umożliwienia systemowi Oracle ich
wykorzystania należy je udostępnić.

Udostępnianie scenariuszy w ramach sesji

Udostępnienie scenariuszy w ramach sesji odbywa się poprzez wydanie polecenia ALTER
SESSION USE_STORED_OUTLINES.
Określenie nazwy kategorii oznacza udostępnienie scenariuszy w niej zarejestrowanych:
ALTER SESSION

SET USE_STORED_OUTLINES =GENERAL_LEDGER ;
Podanie wartości TRUE oznacza korzystanie z kategorii domyślnej
DEFAULT:
ALTER SESSION

SET CREATE_STORED_OUTLINES=TRUE;
Podanie wartości FALSE blokuje użycie scenariuszy:
ALTER SESSION

SET CREATE_STORED_OUTLINES=FALSE;

Udostępnianie scenariuszy w ramach systemu

W celu udostępnienia scenariuszy w ramach całego systemu należy użyć polecenia ALTER

SYSTEM zamiast ALTER SESSION. Pod innymi względami składnia jest identyczna. Na
przykład:

ALTER SYSTEM
SET USE_STORED_OUTLINES

=GENERAL_LEDGER NOOVERRIDE;
W tym przypadku wszystkie istniejące scenariusze mogą być używane wraz ze scenariuszami
z kategorii GENERAL_LEDGER. Gdyby nie podano opcji NOOVERRIDE, scenariusze z
kategorii GENERAL_LEDGER zastąpiłyby scenariusze używane w danym momencie.
Podobnie wygląda sprawa zablokowania scenariuszy w ramach systemu:
ALTER SYSTEM

SET USE_STORED_OUTLINES=FALSE;

Zarządzanie scenariuszami

Scenariusze można przeglądać w celu sprawdzenia tego, czy zostały zarejestrowane
prawidłowo, a ponadto - w celu zapoznania się z zarejestrowanym planem wykonania. Można
także przenosić scenariusze z jednego schematu do drugiego.

Przeglądanie scenariuszy

Aby przeglądać scenariusze w celu sprawdzenia tego, czy są rejestrowane, należy wykonać
zapytanie względem tabel OUTLN.OL$ oraz OUTLN.OL$HINTS. Tabela OUTLN.OL$
zawiera polecenia SQL, zaś OUTLN. OL$HINTS zawiera wskazówki optymalizatora
używane w tych poleceniach.
Oprócz tabel, których właścicielem jest użytkownik OUTLN, można także przeglądać własne
scenariusze wykonując zapytania względem perspektyw USER_OUTLINES oraz
USER_OUTLINES_HINTS (a także perspektyw DBA_* oraz ALL_*). Na przykład:
SELECT uo.name, sql_text, hint

FROM user_outlines uo,
user_outline_hints uoh

WHERE uo.name = uoh.name
ORDER BY join_pos;

SYS_OUTLINE_010805222913599
select owner ||','|| first ||','|| second ||’,’|| third |

|','|| points
from winners_current_month

where points > 4
and owner is not null

order by points desc
NO_FACT(WINNERS_CURRENT_MONTH)
W celu sprawdzenia tego, czy scenariusz jest w danym momencie używany dla polecenia
SQL, można sprawdzić kolumnę OUTLINE_ CATEGORY tabeli V$SQL w wierszu
zawierającym polecenie SQL, które nas interesuje.

Przenoszenie scenariuszy pomiędzy bazami danych
Przenoszenie scenariuszy pomiędzy bazami danych sprowadza się do wyeksportowania i
zaimportowania ich. Na przykład:
EXP OUTLN/OUTLN FILE=OUTLN_21SEP2001.DMP TABLES=OL$, OL$HINTS
BUFFER=262144
IMP OUTLN/OUTLN FILE=OUTLN_21SEP2001.DMP BUFFER=262144 IGNORE=Y
Przykład ten przenosi scenariusze dla wszystkich schematów. Po zaimportowaniu można
usunąć wiersze z tabel OL$ oraz OL$HINTS w docelowej bazie danych dla schematów,
których nie zamierza się przenosić.

Postępowanie w przypadku literałów
Wiele aplikacji korzysta z literałów zamiast zmiennych dowiązanych. Oznacza to, że
polecenia SQL często będą podobne i powinny korzystać z tego samego planu wykonania.
Jednakże - z uwagi na to, że jest jedna wartość - wartość literału różna dla różnych poleceń
współużytkowanie planu wykonania nie ma miejsca.
Niestety, system Oracle nie pomaga użytkownikom w tym względzie, nawet parametry, takie
jak CURSOR_SHARING = FORCE lub SIMILAR, mają niewielkie znaczenie. Z pewnością
jednak firma Oracle zajmie się tym problemem w przyszłości.