Optymalizacja_Oracle_SQL_Leksykon_kieszonkowy_oporsq.pdf

(263 KB) Pobierz
IDZ DO
PRZYK£ADOWY ROZDZIA£
SPIS TRE CI
KATALOG KSI¥¯EK
KATALOG ONLINE
ZAMÓW DRUKOWANY KATALOG
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
TWÓJ KOSZYK
DODAJ DO KOSZYKA
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:
•
Problem wyboru optymalizatora
•
Dzia³anie optymalizatora regu³owego (rule-based)
•
Dzia³anie optymalizatora kosztowego (cost-based)
•
Problemy wspólne dla obu optymalizatorów
„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.
CENNIK I INFORMACJE
ZAMÓW INFORMACJE
O NOWO CIACH
ZAMÓW CENNIK
CZYTELNIA
FRAGMENTY KSI¥¯EK ONLINE
Wydawnictwo Helion
ul. Chopina 6
44-100 Gliwice
tel. (32)230-98-63
e-mail: helion@helion.pl
5RKU VTG EK
9UVúR
1RV[OCNK\CVQT[ 53.
Działanie optymalizatora regułowego..................................................10
Działanie optymalizatora kosztowego .................................................17
Częste nieporozumienia związane z optymalizatorami .......................25
Wybór optymalizatora..........................................................................26
2TQDNGO[ K KEJ TQ\YKæ\CPKC
Y RT\[RCFMW QRV[OCNK\CVQTC TGIW 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.....................................................................32
2TQDNGO[ K KEJ TQ\YKæ\CPKC
Y RT\[RCFMW QRV[OCNK\CVQTC MQU\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 ...............................................................................45
2TQDNGO[ YURÎNPG
FNC QRV[OCNK\CVQTC TGIW QYGIQ K MQU\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...................................................................................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
&TQDPG RQTCF[ FQV[E\æEG UVTQLGPKC RQNGEG 53.
Identyfikowanie złego kodu SQL ........................................................79
Identyfikowanie długo wykonujących się poleceń SQL......................80
U ycie polecenia DECODE dla instrukcji wyboru IF/ELSE...............81
Zmienne dowiązane .............................................................................82
-QT\[UVCPKG \G YUMC\ÎYGM 53.
Ignorowanie wskazówek......................................................................85
Korzystanie ze wskazówek w perspektywach .......................................86
Dostępne wskazówki............................................................................86
9[MQT\[UVCPKG RCMKGVW &$/5A56#65
FQ \CT\æF\CPKC FCP[OK UVCV[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\[UVCPKG UEGPCTKWU\[
FNC URÎLP[EJ RNCPÎY Y[MQPCPKC
Rejestrowanie scenariuszy .................................................................112
Udostępnianie scenariuszy .................................................................114
Zarządzanie scenariuszami.................................................................115
5MQTQYKF\
2TQDNGO[ K KEJ TQ\YKæ\CPKC
Y RT\[RCFMW
QRV[OCNK\CVQTC MQU\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 przypadku 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%
2TQDNGO RKGTYU\[ RTQDNGO CU[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 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 war-
toś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 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:

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin