Systemy zarządzania bazami danych

Systemy zarządzania bazami danych

7. Wskazówki dla optymalizatora

Wskazówki dla optymalizatora

• Wskazują plan najlepszy wg programisty

• Usztywniają plan wykonania zapytania

– Jeśli coś się w bazie zmieni, nie zmieni się plan

• Czasem są nieodzowne (gdy optymalizator sobie nie radzi, a aplikacja musi przecież działać)

• Lepiej ich jednak unikać

• Dalsza część wykładu jest też pokazem

bogactwa opcji optymalizacyjnych

Ścieżka dostępu

• Zrób full scan:

SELECT /*+ FULL(e) */ employee_id, last_name FROM employees e

WHERE last_name LIKE :b1;

• Nie używaj indeksu:

SELECT /*+ NO_INDEX(e emp_empid) */

employee_id

FROM employees e

WHERE employee_id > 200;

Indeksowe ścieżki dostępu

• Użyj indeksu:

SELECT /*+ INDEX (e emp_department_ix)*/ * FROM employees e

WHERE department_id > 50;

• Zrób fast full scan indeksu:

SELECT /*+ INDEX_FFS(e emp_name_ix) */

first_name FROM employees e;

• Zrób skip scan indeksu:

SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name FROM employees e

WHERE first_name = 'Steven';

Operuj mnogościowo na indeksach

SELECT /*+ INDEX_COMBINE(e emp_manager_ix

emp_department_ix) */ * FROM employees e

WHERE manager_id = 108 OR department_id = 110;

SELECT /*+ INDEX_JOIN(e emp_manager_ix

emp_department_ix) */ department_id FROM employees e

WHERE manager_id < 110 AND department_id < 50;

Transformacje zapytania

• Bez transformacji

SELECT /*+ NO_QUERY_TRANSFORMATION */

employee_id, last_name FROM (SELECT * FROM employees e) v

WHERE v.last_name = 'Smith';

• Zamień OR na UNION ALL

SELECT /*+ USE_CONCAT */ * FROM employees e

WHERE manager_id = 108 OR department_id = 110;

• Nie rób tego: /*+ NO_EXPAND */

Użyj widoków zmaterializowanych

• Użyj:

SELECT /*+ REWRITE(s) */ t.calendar_month, sum(s.amount_sold) AS dollars FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month desc;

• Nie używaj: /*+ NO_REWRITE */

Widok zmaterializowany

CREATE MATERIALIZED VIEW salesByTid REFRESH NEXT Sysdate + 1 AS

SELECT s.time_id, sum(s.amount_sold) AS dollars FROM sales s

GROUP BY s.time_id;

• To jest agregacja, której możemy użyć

• Potrzebujemy bowiem grubszej

agregacji

Wynik przepisania

• Początkowe zapytanie

SELECT /*+ REWRITE(s) */ t.calendar_month,

sum(s.amount_sold) AS dollars FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month desc;

• Po przepisaniu i użyciu widoku salesByTid:

SELECT t.calendar_month, sum(s.dollars) AS dollars

FROM salesByTid s, times t WHERE s.time_id = t.time_id

GROUP BY t.calendar_month desc;

Rozwiń definicję widoku

• Rozwiń (też lokalnego jak tu)

SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_sal

FROM employees e1,

(SELECT department_id, avg(salary) avg_sal

FROM employees e2 GROUP BY department_id) v WHERE e1.department_id = v.department_id

AND e1.salary > v.avg_salary;

• Nie rozwijaj /*+ NO_MERGE */

Po rozwinięciu definicji widoku

• Po zmianach

SELECT e1.last_name, e1.salary, avg(e2.salary) FROM employees e1, employees e2

WHERE e1.department_id = e2.department_id GROUP BY e1.last_name, e1.salary,

e2.department_id

HAVING e1.salary > avg(e2.salary);

• Czy jest lepiej?

• Raczej koszmarnie, bo wynik pośredniego złączenia

jest ogromny (jak iloczyn kartezjański)

Rozwiń podzapytania

• Rozwiń /*+ UNNEST */

• Nie rozwijaj /*+ NO_UNNEST */

SELECT /*+ UNNEST */ title FROM StarsIn

WHERE starName IN (SELECT name FROM MovieStar

WHERE birthdate LIKE ‘%1960’);

Transformacja STAR

• Wykonaj

SELECT /*+ STAR_TRANSFORMATION */ *

FROM sales s, times t, products p, channels c WHERE s.time_id = t.time_id

AND s.prod_id = p.product_id

AND s.channel_id = c.channel_id AND p.product_status = 'obsolete';

• Nie wykonuj /*+ NO_STAR_TRANSFORMATION * /

Po transformacji STAR

• Użyteczne w hurtowniach dla tabel faktów

• Gdy są indeksy bitmapowe

SELECT *

FROM sales s

WHERE s.prod_id IN (SELECT product_id FROM products

WHERE p.product_status = 'obsolete‘)

• Potem wynik jest łączony z tabelami wymiarów

( products, channels, times )

Wskazanie faktów i wymiarów

• Tabela faktów /*+ FACT(sales) */

• Tabela wymiarów /*+ NO_FACT(products) */

SELECT /*+ STAR_TRANSFORMATION

FACT(s) NO_FACT(t) NO_FACT(p) NO_FACT(c) */ * FROM sales s, times t, products p, channels c

WHERE ...

Kolejność złączeń

• Wskazana we wskazówce

SELECT /*+ LEADING(e j) */ *

FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id

AND e.hire_date = j.start_date;

• Wg kolejności w klauzuli FROM

SELECT /*+ORDERED */

o.order_id, c.customer_id, l.unit_price * l.quantity FROM customers c, order_items l, orders o

WHERE c.cust_last_name = :b1

AND o.customer_id = c.customer_id

AND o.order_id = l.order_id;

Metoda złączenia (iteracja)

• Iteracja (lista preferowanych tabel wewnętrznych)

– Tabela orders będzie zewnętrzna

SELECT /*+ USE_NL(l h) */ h.c_id, l.unit_price * l.quantity FROM orders h ,order_items l

WHERE l.order_id = h.order_id;

• Iteracja z indeksem

SELECT /*+ USE_NL_WITH_INDEX(l item_order_id _ix)*/ * FROM orders h, order_items l

WHERE l.order_id = h.order_id AND h.order_id > 3500;

• Wyklucz złączenie iteracyjne /* NO_USE_NL */

Metoda złączenia (scalanie)

• Użyj złączenia przez scalanie

SELECT /*+ USE_MERGE(e d) */ *

FROM employees e, departments d

WHERE e.department_id = d.department_id;

• Wyklucz złączenie przez scalanie

SELECT /*+ NO_USE_MERGE(e d) */ * FROM employees e, departments d

WHERE e.department_id = d.department_id

ORDER BY d.department_id;

Metoda złączenia (haszowanie)

• Użyj złączenia haszowanego

SELECT /*+ USE_HASH(l h) */ * FROM orders h, order_items l WHERE l.order_id = h.order_id AND l.order_id > 3500;

• Wyklucz złączenie haszowane

SELECT /*+ NO_USE_HASH(e d) */ * FROM employees e, departments d

WHERE e.department_id = d.department_id;

Przetwarzanie równoległe

• Określamy równoległość

SELECT /*+ FULL(e) PARALLEL(e, 5) */ name FROM employees e;

• Równoległość domyślna dla tabeli

SELECT /*+FULL(e) PARALLEL(e, DEFAULT)*/ * FROM employees e

WHERE hiredate < SYSDATE – 100;

• Wykonaj sekwencyjnie

SELECT /*+ NO_PARALLEL(hr_emp) */

last_name

Partycjonowanie tabel

• Określamy sposób podziału tabeli przed przetwarzaniem równoległym

– Hash – podziel przez haszowanie

– Partition – partycjonuj tabelę zgodnie z jakimś przepisem (np. drugiej tabeli)

– Broadcast – wyślij całość do każdej partycji

– None – użyj fizycznego partycjonowania

tabeli

Kombinacje partycjonowania

• Hash, hash (pierwsza jest zewnętrzna)

– Podziel obie tabele funkcją haszującą

• Broadcast, None LUB None, Broadcast

– Użyj partycjonowania jednej tabeli (None) a drugą (Broadcast) wyślij do całości do serwera każdej

partycji pierwszej tabeli

• Partition, None LUB None, Partition

– Użyj partycjonowania jednej tabeli (None) do podziału drugiej na partycje (Partition)

• None, None

– Złącz tylko pasujące partycje. Obie tabele muszą być

tak samo partycjonowane

Przykłady partycjonowania

• Rozrzuć zawartość tabel za pomocą funkcji haszującej

SELECT /*+PQ_DISTRIBUTE(HASH, HASH)*/ * FROM r,s

WHERE r.c=s.c;

• Użyj partycjonowania tabeli s , roześlij całą r

SELECT /*+ ORDERED

PQ_DISTRIBUTE(BROADCAST, NONE )*/ * FROM r,s

WHERE r.c=s.c;

Równoległe użycie indeksu

• Jak bardzo ma być zrównoleglone przeszukiwane zakresowe?

SELECT /*+PARALLEL_INDEX(e,emp_name,5 )*/

*

FROM employee e

WHERE e.name LIKE ‘Smi%’;

• Nie zrównoleglaj przeszukiwania indeksu:

SELECT /*+NO_PARALLEL_INDEX(e)*/ * FROM employee e

WHERE e.name LIKE ‘Smi%’;

Staraj się trzymać w RAM

• Tzn. wstaw na koniec kolejki LRU

SELECT /*+ FULL(e) CACHE(e) */ last_name FROM employees hr_emp;

• Albo: wstaw na poczatek kolejki LRU (zrób MRU)

SELECT /*+ FULL(e) NOCACHE(e) */

last_name

FROM employees hr_emp;

Predicate move-around

• Przesuń warunek do widoku (też inline )

SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ * FROM employees e,

(SELECT manager_id FROM employees ) v WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

• Robi się złączenie zależne:

SELECT * FROM employees e LEFT JOIN (SELECT manager_id FROM employees

WHERE e.manager_id = manager_id)

WHERE e.employee_id = 100;

Predykaty, kolejności...

• Nie przenoś predykatów /*+

NO_PUSH_PRED(v)*/

• Oblicz podzapytanie najpierw:

SELECT /*+ NO_MERGE(v) PUSH_SUBQ(v) */ * FROM employees e,

(SELECT manager_id FROM employees ) v WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

• Oblicz podzapytanie na końcu

/*+NO_PUSH_SUBQ(v)*/

Miejsce obliczeń

• Wskazujemy zdalną lokalizację do wykonania obliczeń:

SELECT /*+ DRIVING_SITE(d) */ *

FROM employees e, departments@rsite d

WHERE e.department_id = d.department_id;

• Bez wskazówki obliczenie byłoby lokalne ze ściągnięciem tabeli departments z serwera rsite

• Ze wskazówką obliczenie odbędzie się na rsite

po wysłaniu tam tabeli employees

Dynamiczne próbkowanie

• Dynamiczne próbkowanie polega na

odrzuceniu zapamiętanych statystyk i użyciu oszacowań wynikających z próbkowania

danych (np. ocena selektywności warunku)

• Poziom próbkowania to liczba całkowita z zakresu od 0 do 10 (poziom 10 oznacza najdokładniejsze próbkowanie)

SELECT /*+ DYNAMIC_SAMPLING(e 1) */ * FROM employees e

WHERE salary BETWEEN 1007 AND 2356;