Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00347 008327 11063102 na godz. na dobę w sumie
Optymalizacja Oracle SQL. Leksykon kieszonkowy - książka
Optymalizacja Oracle SQL. Leksykon kieszonkowy - książka
Autor: Liczba stron: 128
Wydawca: Helion Język publikacji: polski
ISBN: 83-7197-983-5 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> sql - programowanie
Porównaj ceny (książka, ebook, audiobook).
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: '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.
Znajdź podobne książki Ostatnio czytane w tej kategorii

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\GEKYURÎ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)

Gdzie kupić całą publikację:

Optymalizacja Oracle SQL. Leksykon kieszonkowy
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: