Darmowy fragment publikacji:
IDZ DO
IDZ DO
PRZYK£ADOWY ROZDZIA£
PRZYK£ADOWY ROZDZIA£
SPIS TREĎCI
SPIS TREĎCI
KATALOG KSI¥¯EK
KATALOG KSI¥¯EK
KATALOG ONLINE
KATALOG ONLINE
ZAMÓW DRUKOWANY KATALOG
ZAMÓW DRUKOWANY KATALOG
TWÓJ KOSZYK
TWÓJ KOSZYK
DODAJ DO KOSZYKA
DODAJ DO KOSZYKA
CENNIK I INFORMACJE
CENNIK I INFORMACJE
Optymalizacja Oracle SQL.
Leksykon kieszonkowy
Autor: Mark Gurry
T³umaczenie: Bart³omiej Garbacz
ISBN: 83-7197-983-5
Tytu³ orygina³u: Oracle SQL Tuning. Pocket Reference
Format: B5, stron: 128
Niezoptymalizowane polecenia SQL s¹ jednym z g³ównych czynników powoduj¹cych
ma³o wydajne dzia³anie systemu bazy danych. W niniejszej ksi¹¿ce Mark Gurry dzieli
siê z Czytelnikiem swoimi przemyġleniami dotycz¹cymi problemu optymalizacji. Autor
prezentuje rozwi¹zania wielu typowych problemów za pomoc¹ wbudowanych
w Oracle a optymalizatorów. Omawia miêdzy innymi:
ZAMÓW INFORMACJE
ZAMÓW INFORMACJE
O NOWOĎCIACH
O NOWOĎCIACH
ZAMÓW CENNIK
ZAMÓW CENNIK
• Problem wyboru optymalizatora
• Dzia³anie optymalizatora regu³owego (rule-based)
• Dzia³anie optymalizatora kosztowego (cost-based)
• Problemy wspólne dla obu optymalizatorów
CZYTELNIA
CZYTELNIA
FRAGMENTY KSI¥¯EK ONLINE
FRAGMENTY KSI¥¯EK ONLINE
„Optymalizacja Oracle SQL. Leksykon kieszonkowy” zaoszczêdzi wiele czasu
poġwiêconego na pisanie wydajnych zapytañ. Powinna siê znaleĥæ w biblioteczce
ka¿dego administratora i u¿ytkownika Oracle a.
Wydawnictwo Helion
ul. Chopina 6
44-100 Gliwice
tel. (32)230-98-63
e-mail: helion@helion.pl
5RKUVTGħEK
9UVúR O
1RV[OCNK\CVQT[53. O
Działanie optymalizatora regułowego..................................................10
Działanie optymalizatora kosztowego .................................................17
Częste nieporozumienia związane z optymalizatorami .......................25
Wybór optymalizatora...................................................p.......................26
2TQDNGO[KKEJTQ\YKæ\CPKC
YRT\[RCFMWQRV[OCNK\CVQTCTGIWđQYGIQ
Problem pierwszy: nieodpowiednia tabela sterująca ...........................28
Problem drugi: nieodpowiedni indeks .................................................29
Problem trzeci: nieodpowiedni indeks sterujący..................................30
Problem czwarty: użycie indeksu ORDER BY
zamiast indeksu WHERE ...................................................p..................32
2TQDNGO[KKEJTQ\YKæ\CPKC
YRT\[RCFMWQRV[OCNK\CVQTCMQU\VQYGIQ
Problem pierwszy: problem asymetrii .................................................33
Problem drugi: analizowanie nieodpowiednich danych.......................36
Problem trzeci: wspólne używanie optymalizatorów przy złączeniach ..38
Problem czwarty: wybieranie nieodpowiedniego indeksu...................41
Problem piąty: złączanie zbyt wielu tabel............................................44
Problem szósty: nieodpowiednie ustawienia parametrów
w pliku INIT.ORA ...................................................p............................45
2TQDNGO[YURÎNPG
FNCQRV[OCNK\CVQTCTGIWđQYGIQKMQU\VQYGIQ
Problem pierwszy: polecenia zapisane
w postaci uniemożliwiającej wykorzystanie indeksów........................52
Problem drugi: brak indeksów lub nieodpowiednie indeksy ...............56
Problem trzeci: korzystanie ze scalania indeksu jednokolumnowego .59
Problem czwarty: błędne użycie pętli zagnieżdżonych,
sortowania i łączenia lub złączeń haszujących ....................................61
Problem piąty: błędne użycie IN, EXISTS, NOT IN, NOT EXISTS
lub złączeń tabel...................................................p................................63
Problem szósty: niepotrzebne sortowanie ............................................69
Problem siódmy: zbyt wiele indeksów dla tabeli.................................72
Problem ósmy: użycie OR zamiast UNION ........................................74
Problem dziewiąty: tabele i indeksy z wieloma wierszami usuniętymi ..75
Inne problemy: intensywne używanie perspektyw ..............................78
Inne problemy: złączanie zbyt wielu tabel...........................................78
TQDPGRQTCF[FQV[E\æEGUVTQLGPKCRQNGEGē53.
Identyfikowanie złego kodu SQL ...................................................p.....79
Identyfikowanie długo wykonujących się poleceń SQL......................80
Użycie polecenia DECODE dla instrukcji wyboru IF/ELSE...............81
Zmienne dowiązane ...................................................p..........................82
-QT\[UVCPKG\GYUMC\ÎYGM53.
Ignorowanie wskazówek...................................................p...................85
Korzystanie ze wskazówek w perspektywach .......................................86
Dostępne wskazówki...................................................p.........................86
9[MQT\[UVCPKGRCMKGVW $/5A56#65
FQ\CT\æF\CPKCFCP[OKUVCV[UV[E\P[OK
Użycie pakietu DBMS_STATS do przyspieszenia procesu analizy..108
Kopiowanie statystyk przy użyciu pakietu DBMS_STATS ..............109
Manipulowanie statystykami przy użyciu pakietu DBMS_STATS ..110
Przywracanie poprzedniej wersji statystyk ........................................111
9[MQT\[UVCPKGUEGPCTKWU\[
FNCURÎLP[EJRNCPÎYY[MQPCPKC
Rejestrowanie scenariuszy ...................................................p..............112
Udostępnianie scenariuszy ...................................................p..............114
Zarządzanie scenariuszami...................................................p..............115
5MQTQYKF\ O
2TQDNGO[KKEJTQ\YKæ\CPKC
YRT\[RCFMW
QRV[OCNK\CVQTCMQU\VQYGIQ
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 wy-
stępowania), jakie Autorowi udało się zaobserwować.
Tabela 3. Często powtarzające się problemy w przypazdku optymalizatora
kosztowego
Problem
1. Problem asymetrii
2. Analizowanie nieodpowiednich danych
3. Wspólne używanie optymalizatorów przy złączeniach
4. Wybieranie nieodpowiedniego indeksu
5. Złączanie zbyt wielu tabel
6. Nieodpowiednie ustawienia parametrów w pliku INIT.ORA
Przypadków
30
25
20
20
5
5
2TQDNGORKGTYU\[ RTQDNGOCU[OGVTKK
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 ozna-
czenia transakcji zamkniętych (closed transactions), 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 co-
dziennie kilkaset razy, jednak czas odpowiedzi nie ojest 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 war-
tości O oraz C. Przy takim założeniu system Oracle przeprowadza
przegląd całej tabeli w celu pobrania danych o otwaortych transakcjach.
System Oracle będzie posiadał informację o asymetrii rozkładu da-
nych, czyli liczbie wierszy posiadających określoną wartość w zain-
deksowanych kolumnach, jeśli podczas wykonywania polecenia ANA-
LYZE 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 na-
stę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ł in-
formację 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 otrzy-
many zostanie dużo krótszy czas odpowiedzi:
Czas odpowiedzi: 0,259 sekund
Zazwyczaj, optymalizator kosztowy przeprowadza przegląd całej tabe-
li, 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 wykorzy-
stywało zmienne dowiązane — problem asymetrii wciąż mógł wystę-
pować nawet wtedy, gdy użyto opcji FOR ALL INDEXED CO-
LUMNS. 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żyo:
•
wartości literałów zapisywać w kodzie bezpośrednio (na przy-
kł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łowe-
go, pozostaje próba usunięcia statystyk indeksu za opomocą polecenia:
ANALYZE INDEX
TRANS_STATUS_NDX
DELETE STATISTICS;
Usunięcie statystyk indeksu poprawia sytuację, gdyż wymusza zacho-
wanie stosowane przez optymalizator regułowy, który zawsze korzysta
z istniejących indeksów (zamiast przeglądu całej taboeli).
79#)#
W systemie Oracle9i wartość zmiennych dowiązanych jest określana
przed podjęciem decyzji o planie wykonania, co eliminuje koniecz-
ność bezpośredniego zapisywania w kodzie wartości lriterałów.
2TQDNGOFTWIK
CPCNK\QYCPKGPKGQFRQYKGFPKEJFCP[EJ
Autor miał styczność z wieloma systemami, w których problemy
z wydajnością wynikały z tego, że tabele i indeksy nie były analizowa-
ne w czasie, gdy zawierały typowe ilości danych. Optymalizator kosz-
towy musi posiadać dokładne informacje (a w tym informacje o obję-
tości danych), aby mógł określić efektywny plan wykonoania.
Sytuacje, w których statystyki mogą zostać utracone lub stać się nie-
aktualne, stanowić może ponowne tworzenie tabeli lub jej przenosze-
nie, dodawanie indeksu lub tworzenie nowego środowiska. Na przy-
kł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 wy-
starczających informacji o bazie danych, którą zarządza i analizuje ta-
belę w momencie, gdy jest pusta, a nie wtedy, gdy po krótkim okresie
czasu ma ona setki lub tysiące wierszy.
5RQUÎDURTCYF\GPKCFCV[QUVCVPKGLCPCNK\[
W celu sprawdzenia tego, które tabele, indeksy i partycje zostały prze-
analizowane 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 otrzy-
mania pełnej listy perspektyw zawierających kolumnę LAST_ANA-
LYZED 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 przeprowa-
dzać jak najczęściej. Takie postępowanie może spowodować, że do-
strojone polecenie SQL ulegnie rozstrojeniu.
GE[\LCQE\CUKGCPCNK\[
Ponowne analizowanie tabel i indeksów może być równie niebezpiecz-
ne, jak dostosowywanie indeksów i w idealnej sytuacji powinno być
przeprowadzane na kopii bazy produkcyjnej przed ostatecznym wpro-
wadzeniem zmian w faktycznej bazie produkcyjnej.
Oprogramowanie firmy Peoplesoft jest przykładem aplikacji, która ko-
rzysta z tymczasowych tabel do przechowywania danych, których na-
zwy 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 doanych.
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 in-
formację 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 tym-
czasowych danymi w celu przeprowadzenia analizy. Potem można ta-
bele 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środ-
ków w Australii, który korzystał z oprogramowania Peoplesoft, czas wy-
konania procesu wsadowego spadł z 36 godzin do mniejo niż 30 minut.
Jeśli analizowanie tabel przechowujących dane tymczasowe zawierają-
ce produkcyjne ilości danych nie rozwiązuje problemów wydajnościo-
wych, 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 DE-
LETE STATISTICS. Po ich usunięciu ważną sprawą jest zapewnie-
nie 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 staty-
styki. Jeśli tabele tymczasowe są wykorzystywane oddzielnie i złącze-
nia występują tylko pomiędzy nimi samymi, wtedy preferowanym po-
dejściem jest często wykorzystanie zasad działania optymalizatora
regułowego.
2TQDNGOVT\GEK YURÎNPGWľ[YCPKG
QRV[OCNK\CVQTÎYRT\[\đæE\GPKCEJ
Jak wspomniano wcześniej, w sytuacji, gdy tabele podlegają złączeniu
i jedna z nich zostanie zanalizowana, zaś pozostałe tabele nie, optyma-
lizator 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 wyokonania.
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 RANGE SCAN TRANS_NDX1
Czas odpowiedzi: 3,1 sekund
W innym systemie, który Autor także dostrajał, twórca oprogramo-
wania 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 in-
deksów miał daleko sięgający — negatywny — wpływ na wydaj-
ność. 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ę ozmniejszył.
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 systema-
mi 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.
2TQDNGOE\YCTV[
Y[DKGTCPKGPKGQFRQYKGFPKGIQKPFGMUW
Optymalizator kosztowy wybiera czasem indeks podrzęodny, 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 za-
pytania 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 cztero-
krotny zysk czasu bardzo ucieszył użytkownika systeomu.
Jednak pojawiły się inne problemy. Ten sam indeks był idealnym kan-
dydatem 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 1 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, optymali-
zator kosztowy ponownie go nie wziął pod uwagę. Jedyna różnica po-
między poleceniem bieżącym a poprzednim polegała na tym, że doty-
czyło ono raczej zakresu okresów obrachunkowych (accounting
period) dla roku fiskalnego (fiscal year), a nie po prostu jednego okre-
su obrachunkowego.
Dla powyższego wyrażenia WHERE używany był ten sam, co poprzed-
nio, nieodpowiedni indeks z kolumnami (business_unit, ledger,
fiscal_year, account). I ponownie — po zmierzeniu czasu wyko-
nania 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 szybosze 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 dane-
go indeksu jest wykorzystanie jednej ze wskazówek, które pozwalają na
jego określenie. Jest to dobre rozwiązanie, jednak wiele ośrodków korzy-
sta 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 per-
spektywy. Będzie ona przydatna, jeśli polecenie SQL, którego wydaj-
ność 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 in-
deksu, jeśli usunie się jego statystyki. Czasem można także użyć po-
lecenia 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ć wy-
konane czynności. Jeszcze inna metoda polega na próbie zmniejsze-
nia parametru OPTIMIZER_INDEX_COST_ADJ* do wartości z prze-
działu 10 do 50.
Podsumowując trzeba odpowiedzieć na pytanie o to, dlaczego optyma-
lizator kosztowy podejmuje takie nieodpowiednie decyzje. Po pierwsze
— należy podkreślić, że zła decyzja dotycząca planu wykonania to ra-
czej wyjątek niż reguła. Przykłady z niniejszego podrozdziału pokazu-
ją, że kolumny są rozpatrywane raczej indywidualnie niż grupowo.
Gdyby tak było, w pierwszym z prezentowanych przykładów optyma-
___________________________
*
Wartość tego parametru ustawia się w pliku INIT.ORA — przyp. tłum
lizator kosztowy stwierdziłby — bez konieczności odtworzenia indek-
su 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 pole-
cenie SQL żąda dostępu do większości z nich, to optymalizator kosz-
towy często pomija taki indeks. Dzieje się tak, mimo że rozpatrywane
razem kolumny są ściśle określone i zapytanie zwrócoi 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.
2TQDNGORKæV[ \đæE\CPKG\D[VYKGNWVCDGN
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. Zapyta-
nie wybiera wszystkie dane związane z przedsiębiorstwem o identyfi-
katorze rachunku (kolumna acct_id) równym 777818. Przedsiębior-
stwo 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.
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ł 1 se-
kundy. 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, opty-
malizator 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 Wa-
szyngton. Proces ten może zająć nawet kilka minut, co zapewne spo-
woduje, ż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 wszyst-
kich 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 wydajoność.
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 re-
guł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 spo-
sobem może być utworzenie perspektywy zawierającej wskazówkę
(o ile jest to dopuszczalne i możliwe w przypadku uożywanego pakietu).
Pobierz darmowy fragment (pdf)
