Systemy zarządzania bazami danych

1. Systemy zarządzania bazami danych 7. Wskazówki dla optymalizatora 7. Wskazówki dla optymalizatora

2. 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 7. Wskazówki dla optymalizatora

3. Ś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; 7. Wskazówki dla optymalizatora

4. 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'; 7. Wskazówki dla optymalizatora

5. 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; 7. Wskazówki dla optymalizatora

6. 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 */ 7. Wskazówki dla optymalizatora

7. 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 */ 7. Wskazówki dla optymalizatora

8. 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 7. Wskazówki dla optymalizatora

9. 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; 7. Wskazówki dla optymalizatora

10. 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 */ 7. Wskazówki dla optymalizatora

11. 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) 7. Wskazówki dla optymalizatora

12. Rozwiń podzapytania • Rozwiń /*+ UNNEST */ • Nie rozwijaj /*+ NO_UNNEST */ SELECT /*+ UNNEST */title FROM StarsIn WHERE starName IN (SELECT name FROM MovieStar WHERE birthdate LIKE ‘%1960’); 7. Wskazówki dla optymalizatora

13. 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*/ 7. Wskazówki dla optymalizatora

14. 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) 7. Wskazówki dla optymalizatora

15. 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 ... 7. Wskazówki dla optymalizatora

16. 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; 7. Wskazówki dla optymalizatora

17. 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 */ 7. Wskazówki dla optymalizatora

18. 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; 7. Wskazówki dla optymalizatora

19. 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; 7. Wskazówki dla optymalizatora

20. 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 FROM employees hr_emp; 7. Wskazówki dla optymalizatora

21. 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 7. Wskazówki dla optymalizatora

22. 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 7. Wskazówki dla optymalizatora

23. 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; 7. Wskazówki dla optymalizatora

24. 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%’; 7. Wskazówki dla optymalizatora

25. 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; 7. Wskazówki dla optymalizatora

26. 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; 7. Wskazówki dla optymalizatora

27. 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)*/ 7. Wskazówki dla optymalizatora

28. 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 7. Wskazówki dla optymalizatora

29. 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; 7. Wskazówki dla optymalizatora