Optymalizacja w systemie Oracle.

Optymalizatory.

Kiedy użytkownik wykonuje polecenia SQL jeden z komponentów systemu bazodanowego, zwany optymalizatorem, musi podjąć decyzję dotyczącą najlepszego dostępu do danych, na których polecenie operuje.

W systemie Oracle wyróżniamy dwa typy optymalizatorów:

- regułowe (RBO – Rule - Based Optimizer).

- kosztowe (CBO – Cost - Based Optimizer).

W celu określenia optymalnego scenariusza wykonania, optymalizatory biorą pod uwagę następujące kwestie:

- składnię wydanego polecenia.

- warunki, które muszą spełniać dane (fraza WHERE).

- tabele bazy danych, do których dostępu wymaga polecenie.

- wszystkie możliwe indeksy, jakie mogą być wykorzystywane do pobrania danych z tabeli.

- wersję systemu zarządzania bazą danych Oracle.

- bieżący typ optymalizatora – wskazówki polecenia SQL (hints).

- wszystkie dostępne statystyki dotyczące obiektu, utworzone za pomocą polecenia

‘analize” – CBO.

- fizyczną lokalizację tabel rozproszonych SQL.

- rozszerzenia pliku konfiguracyjnego systemu Oracle – init.ora.

Optymalizator regułowy RBO w celu ustalenia ścieżki dostępu do danych korzysta z tzw. reguł pierwszeństwa. Jest ich dwadzieścia.

Kiedy system Oracle korzysta z RBO?

- w pliku init.ora znajduje się linia:

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

- wykonano polecenie:

alter session set optimizer_mode = rule

- wykonano polecenie:

alter session set optimizer_mode = choose

oraz dla żadnej tabeli związanej z wykonywanym poleceniem nie wykonano statystyk

-

w wykonywanym poleceniu użyto odpowiednie wskazówki +rule.

1z7

Działanie optymalizatora regułowego opiera się w głównej mierze na tzw. złotych regułach, które określają rangi warunków. Reguły te pozwalają optymalizatorowi określić ścieżkę wykonywanego polecenia, miejsce i czas wykorzystania indeksu, itp.

Ranga

Warunek

1

Rowid = ws (rowid – identyfikator wiersza, który go rozpoznaje poprzez adres) 2

złączenie klastrowe (cluster join) z kluczem unikalnym bądź głównym = ws (klastry są alternatywną do tabel formą składowania danych, zorientowaną na poprawę efektywności wyszukiwania danych; w Oracle wyróżniamy klastry indeksowe i haszowe)

3

klucz klastra haszowego (hash cluster) z kluczem unikalnym lub głównym = ws 4

cały indeks skonkatenowany = ws

5

kolumna z indeksem unikalnym = ws

6

cały klucz klastra = ws (indeks)

7

klucz klastra haszowego = ws

8

cały klucz klastra = ws (haszowany)

9

cały nieunikalny indeks skonkatenowany = ws

10

scalenie indeksu nieunikatowego

11

cały indeks skonkatenowany = ws

12

większość kolumn wiodących indeksu unikatowego = ws

13

zaindeksowana kolumna między dolną a górną wartością lub zaindeksowana kolumna typu like ‘ABC%’ (przedział ograniczony)

14

zaindeksowana nieunikatowa kolumna między dolną a górną wartością lub

zaindeksowana kolumna typu like ‘ABC%’ (przedział ograniczony)

15

unikatowa kolumna zaindeksowana lub wartość stała (przedział nieograniczony) 16

nieunikatowa kolumna zaindeksowana lub wartość stała (przedział nieograniczony)

17

równość kolumn niezaindeksowanych = kolumna lub ws (złączenie przez scalenie i sortowanie)

18

MAX lub MIN pojedynczych kolumn w indeksach

19

ORDER BY całego indeksu

20

przegląd całej tabeli

Są też inne praktyczne reguły (wytyczne) związane z optymalizacją, o których przedstawione złote reguły nic nie mówią:

1. jedynie indeksy pojedynczych kolumn ulegają scaleniu, np.:

SELECT kolumna1, ... FROM emp WHERE

emp_name = ‘Kowalski’;

Index1(dept_no);

Index2(emp_no, emp_name);

2. jeśli wszystkie kolumny należące do pewnego indeksu zostały wymienione we frazie WHERE polecenia SQL, wówczas taki indeks zostanie użyty, np.:

SELECT kolumna1, ... FROM emp WHERE

emp_name = ‘Nowak’

AND emp_no = 180

AND dept_no = 15;

2z7

Index1(emp_name);

Index2(emp_name, dept_no, cost_cent);

3. jeśli wiele indeksów odpowiada specyfikacji we frazie WHERE i wszystkie posiadają taka samą liczbę kolumn, wówczas użyty zostanie indeks utworzony najpóźniej.

4. jeśli następuje dostęp do wielu kolumn indeksu przy użyciu operatora = , to ma on pierwszeństwo prze operatorami takimi jak: LIKE, BETWEEN (== mają pierwszeństwo przed = LIKE lub = BETWEEN), np.:

SELECT kolumna1, ... FROM emp WHERE

emp_name LIKE ‘Nowak’

AND emp_no = 170

AND dept_no = 15

AND emp_cate = ‘urzędnik’

AND emp_class = ‘c1’;

Index1(emp_cate, emp_class, emp_name);

Index2(emp_no, dept_no);

5. wybierany jest ten indeks, który odwołuje sie do większej liczby kolumn we frazie WHERE (chodzi o odsetek), np.:

SELECT kolumna1, ... FROM emp WHERE

emp_name like ‘Kowal’

AND emp_no = 150

AND emp_class = ‘c1’;

Index1(emp_name, emp_class, emp_category); //67%

Index2(emp_no, dept_no); //30%

6. jeśli dwie tabele ulegają złączeniu, to RBO musi wybrać tabelę sterującą. Wybór tabeli może mieć ogromny wpływ na wydajność (w przypadku użycia przez optymalizatora pętli nasted loops). Dzieje się tak dlatego, ponieważ najpierw zostaje zwrócony wiersz z tabeli sterującej, a później odpowiadające wiersze z drugiej tabeli. Ważne jest, żeby w tabeli sterującej wybrano jak najmniejszą liczbę wierszy. Aby wybrać, która z tabel ma być sterującą, RBO korzysta z pięciu reguł:

1. posiadanie przez tabelę indeksu klucza unikatowego lub głównego sugeruje jej wybór na sterującą.

2. indeks, w przypadku którego dostęp do jego wszystkich kolumn jest przy użyciu operatora = ma pierwszeństwo przed indeksem, z którego wybierana jest część kolumn.

3. tabela, która posiada wyższy odsetek swoich kolumn w indeksie jest wybierana jako sterująca.

4. tabela zawierająca indeks dwukolumnowy, do której następuje odwołanie we frazie WHERE zapytania ma pierwszeństwo przed tabelą, zawierającą dwa indeksy jednokolumnowe.

5. jeśli dwie tabele posiadają taką samą liczbę kolumn w indeksie, wtedy tabelą sterującą jest ostatnia tabela wymieniona we frazie FROM, np.:

3z7

SELECT kolumna1, ... FROM dept p, emp e WHERE

e.emp_name = ‘Kowal’

AND d.dept_name = ‘kadry’

AND d.dept_no = e.dept_no;

Fazy optymalizacji polecenia SQL w systemie Oracle.

1

analiza polecenia

dokonanie możliwych

2

przekształceń

łączenie perspektyw z treścią

3

polecenia

wybór metody i celu

słownik bazy danych

4

optymalizacji

(wskazówki)

wybór metody dostępu do

5

tabel

określenie kolejności o

6

sposobów złączania tabel

1. analiza zapytania oraz wyznaczenie wartości wyrażeń do postaci stałych (np. jeśli w zapytaniu SQL jest ¾ - 1, to optymalizator –0.25).

2. przekształcenie poleceń do innej postaci. Optymalizator może przekształcić złożone zapytanie w zapytanie równoważne, ale wykonywane efektywnie (np. podzapytanie skorelowane na złączenie).

3. następuje agregacja w miarę możliwości zapytań, definiujących perspektywy (view) z poleceniami SQL, operującymi na tych perspektywach.

4. podjęcie decyzji w jakim trybie (RBO czy CBO) nastąpi wyznaczenie planu wykonania.

5. wybór metody dostępu do danych, np. przy wykorzystaniu indeksu.

6. określenie kolejności i sposobu łączenia tabel.

Istnieje

możliwość sprawdzenia

czy

komenda

SQL

została

poprawnie

zoptymalizowana poprzez śledzenie wykonania jej kodu lub podejrzenie (podgląd) za pomocą polecenia EXPLAIN PLAN planu wykonania wybranego przez optymalizator.

EXPLAIN PLAN

SET STATEMENT_ID = ‘test’

INTO plan_table

FOR

SELECT ename, oname FROM emp, dept

WHERE emp.dept_no = dept.dept_no

AND emp.ename = ‘Kowalski’;

4z7

Id

Operation

Name Rows Bytes Cost

0 Select statement

1

50

3

1 Hash join

1

50

3

2 Table accessfull

emp

1

32

1

3 Table accessfull

dept

4

72

1

Problemy występujące w przypadku optymalizatora regułowego i ich

rozwiązania:

Problem RBO

Zakres

Nieodpowiednia tabela sterująca

40%

Nieodpowiedni indeks

40%

Nieodpowiedni indeks sterujący

10%

Użycie indeksu we frazie ORDER BY zamiast WHERE

10%

Tabele: Transakcja (nazwa_k, źródło_kosztowe, identyfikator_typu, konto, produkt, klient, status, data_transakcji, typ_transakcji, ...)

Konto (nazwa_k, ...)

Czasopismo (jednostka_biznesowa, konto, data_czas, ...)

Nieodpowiednia tabela sterująca.

1. SELECT COUNT (*) FROM Konto a, Transakcja b WHERE b. źródło_kosztowe =

‘klient masowy’ AND a.nazwa_k = ‘konto 61’ AND a.nazwa_k = b.nazwa_k; //19s 2. SELECT COUNT (*) FROM Transakcja b, Konto a WHERE b. źródło_kosztowe =

‘klient masowy’ AND a.nazwa_k = ‘konto 61’ AND a.nazwa_k = b.nazwa_k; //1.9s Bardzo ważne jest, by ostatnia tabela we frazie FROM posiadała jak najmniejszą liczbę wierszy.

Nieodpowiedni indeks.

W wyrażeniu WHERE zawartych jest często wiele indeksów, z których może skorzystać RBO. Optymalizator nie wie jednak na ilu wierszach operuje każdy z indeksów.

WHERE jednostka_biznesowa = ‘BIOO’ AND konto = 815342 AND data_czas between ’01-01-2002’ AND ’01-03-2002’;

Indeks1 (jednostka_biznesowa)

Indeks2 (jednostka_biznesowa, konto, data_czas)

RBO wybierze Indeks1, kierując się swoimi regułami, mimo że Indeks2 działa o wiele szybciej. Najlepszym rozwiązaniem jest usunięcie Indeksu1.

Nieodpowiedni indeks sterujący.

Kolejność, w jakiej podaje się warunki we frazie WHERE mają największy wpływ na wydajność. Jeśli występują dwa indeksy o takich samych rangach, np. indeksy jednokolumnowe, to system Oracle scala te indeksy. Kolejność scalania może mieć bardzo duży wpływ na wykonanie polecenia.

5z7

1. SELECT COUNT (*) FROM Transakcja WHERE źródło_kosztowe = ‘klient masowy’

AND identyfikator_typu = 10; //>4s

2. SELECT COUNT (*) FROM Transakcja WHERE identyfikator_typu = 10 AND

źródło_kosztowe = ‘klient masowy’; //≈1s

Użycie indeksu we frazie ORDER BY zamiast WHERE.

SELECT a1, a2, … FROM Transakcja WHERE (typ_transakcji LIKE ‘I%’) ORDER BY

Konto;

Indeks1 unique(Konto)

Indeks2 (typ_transakcji)

RBO próbuje za wszelka cenę uniknąć sortowania, wybiera więc indeks, który zawiera kolumnę z frazy ORDER BY zamiast indeksu z frazy WHERE. Rozwiązaniem jest konkatenacja (połączenie) obu indeksów i usunięcie indeksów pojedynczych.

Problemy występujące w przypadku optymalizatora kosztowego i ich

rozwiązania:

Problem CBO

Zakres

Problem asymetrii

30%

Analiza nieodpowiednich danych

25%

Wspólne używanie optymalizatorów przy złączeniu

20%

Wybór nieodpowiedniego indeksu

20%

Złączenie zbyt wielu tabel

<5%

Nieodpowiednia konfiguracja pliku startowego init.ora

<5%

Problem asymetrii.

W tabeli Transakcja jest kolumna status, która może przyjmować dwie wartości:

- o – open transaction

- c – close transaction

Transakcji c jest > 1000000, natomiast transakcji o jest około 100.

SELECT konto, klient, produkt, data_transakcji FROM Transakcja WHERE status = ‘o’;

//16s

W powyższym przypadku optymalizator CBO decyduje się na wykonanie pełnego przeglądu (full scan). Spowodowane to jest tym, że atrybut we frazie WHERE przyjmuje dwie wartości, o czym wie optymalizator. Optymalizator nie wie nic natomiast o liczbie wierszy, które posiadają wartość ‘o’ lub ‘c’ w kolumnie status. CBO założył, że rozkład jest równomierny 50/50.

ANALYZE TABLE Transakcja COMPUTE STATISTIC FOR ALL INDEXED

COLUMNS; //0.3s

6z7

Najważniejsze problemy wspólne dla obu typów optymalizatorów: Problem

Zakres

Polecenie zapisane w postaci uniemożliwiającej wykorzystanie indeksów

25%

Brak indeksów lub nieodpowiednie indeksy

16%

Korzystanie ze scalania indeksu jednokolumnowego

15%

Błędne użycie zagnieżdżeń sortowania, łączenia

12%

Błędne użycie fraz IN, EXISTS, NOT IN, NOT EXISTS lub złączeń

8%

Największy wpływ na wydajność poleceń SQL niezależnie od typu optymalizatora oraz innych szczegółowych rozwiązań mają następujące fakty:

1. odpowiednie wykorzystanie indeksów.

2. odpowiednia kolejność łączenia tabel i indeksów.

3. kolejność we frazie WHERE.

7z7