Transcript wyklad07

Systemy zarządzania bazami
danych
7. Wskazówki dla optymalizatora
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
1
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
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
2
Ś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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
3
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';
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
4
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
5
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 */
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
6
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 */
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
7
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
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
8
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
9
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 */
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
10
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)
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
11
Rozwiń podzapytania
• Rozwiń /*+ UNNEST */
• Nie rozwijaj /*+ NO_UNNEST */
SELECT /*+ UNNEST */ title
FROM StarsIn
WHERE starName IN (SELECT name
FROM MovieStar
WHERE birthdate LIKE ‘%1960’);
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
12
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 */
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
13
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)
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
14
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 ...
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
15
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
16
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 */
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
17
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
18
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
19
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
20
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
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
21
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
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
22
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
23
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%’;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
24
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
25
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
26
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)*/
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
27
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
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
28
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;
Oryginał: Oracle Corp.
7. Wskazówki dla optymalizatora
29