1 / 29

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

ulla-mckay
Download Presentation

Systemy zarządzania bazami danych

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related