Automatic SQL Tuning in Oracle 10g

Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, SDM-2 2008
Plan prezentacji
Jak pisać wydajne zapytania SQL ?
Wydajność poleceń SQL, a fizyczny projekt bazy danych, czyli
o… indeksach, partycjach i widokach zmaterializowanych
Jak Oracle, DB2 oraz SQL Server automatycznie optymalizują
zapytania i dostęp do danych
Klika słów o pracy magisterskiej
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
2
Wydajność systemu Oracle
Czynniki wpływające na wydajność Oracle
2%
18%
Kod aplikacji
Projekt bazy danych
Strojenie bazy danych
20%
60%
Strojenie systemu
operacyjnego
Na podstawie: C. Millsap J. Holt Optimizing Oracle Performance
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
3
Metody dostępu do tabel
pełny przegląd tabeli
dostęp przez ROWID
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
4
Współdzielenie zapytań SQL
Warunki korzystania z współdzielonego zapytania:
dokładna (co do znaku) zgodność treści pomiędzy zapytaniem
zadanym oraz przechowywanym w obszarze współdzielonym
SELECT * FROM EMP
≠
SELECT * From EMP
obiekty, do których odnosi się zapytanie muszą być dokładnie tymi
samymi obiektami co w zapytaniu współdzielonym
nazwy zmiennych związanych muszą być identyczne
SELECT * FROM EMP WHERE id = :manager_id
≠
SELECT * FROM EMP WHERE id = :worker_id
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
5
Zapytania – dobre praktyki (1)
Należy unikać * w klauzuli SELECT
Wykorzystywanie klauzuli WHERE zamiast HAVING
SELECT r.nazwa, COUNT(*) as ilosc
FROM region r, sklep s
WHERE r.region_id = s.region_id
GROUP BY r.nazwa
HAVING r.nazwa != ‘mazowieckie’
AND r.nazwa != ‘śląskie’
AND ilosc < 5
Zapytania SQL: wydajność i optymalizacja
SELECT r.nazwa, COUNT(*) as ilosc
FROM region r, sklep s
WHERE r.region_id = s.region_id
AND r.nazwa != ‘mazowieckie’
AND r.nazwa != ‘śląskie’
GROUP BY r.nazwa
HAVING ilosc < 5
Łukasz Twardokęs, Politechnika Warszawska
6
Zapytania – dobre praktyki (2)
należy unikać zbędnych klauzul DISTINCT
gdy zastosowanie klauzuli DISTINCT jest konieczne, należy
zastanowić się czy nie można jej tak przekształcić aby
zastosować operator EXISTS
SELECT DISTINCT p.nazwa
FROM produkt p, sprzedaz s
WHERE p.produkt_id = s.produkt_id
SELECT p.nazwa
FROM produkt p
WHERE EXISTS ( SELECT 1
FROM sprzedaz s
WHERE p.produkt_id = s.produkt_id )
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
7
Zapytania – dobre praktyki (3)
UNION ALL zamiast UNION
(SELECT s.nazwa, s.miasto, s.ulica
FROM sklep s, region r
WHERE s.region_id = r.region_id
AND r.kod = ‘SL’ )
UNION
(SELECT s.nazwa, s.miasto, s.ulica
FROM sklep s, region r
WHERE s.region_id = r.region_id
AND s.kod = ‘MZ’ )
Zapytania SQL: wydajność i optymalizacja
(SELECT s.nazwa, s.miasto, s.ulica
FROM sklep s, region r
WHERE s.region_id = r.region_id
AND r.kod = ‘SL’ )
UNION ALL
(SELECT s.nazwa, s.miasto, s.ulica
FROM sklep s, region r
WHERE s.region_id = r.region_id
AND s.kod = ‘MZ’ )
Łukasz Twardokęs, Politechnika Warszawska
8
Zapytania – dobre praktyki (4)
NOT EXIST zamiast NOT IN
SELECT s.numer, s.nazwa
FROM sklep s
WHERE s.region_id NOT IN (SELECT r.region_id
FROM region r
WHERE r.kod IN (‘SL’, ‘MZ’) )
SELECT s.numer, s.nazwa,
FROM sklep s
WHERE NOT EXISTS (SELECT 1
FROM region r
WHERE IN (‘SL’, ‘MZ’) )
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
9
Zapytania – wykorzystanie indeksów (1)
obliczenia na indeksowanej kolumnie blokują korzystanie z
indeksu
SELECT p.nazwa, p.cena
FROM produkt p
WHERE ps.cena * 0,2 > 100
SELECT p.nazwa, p.cena
FROM produkt p
WHERE ps.cena > 500
konkatenacja indeksowanych kolumn
SELECT s.nazwa
FROM sklep s, region r
WHERE s.region_id = r.region_id
AND r.nazwa || ‘-’ ||s.miasto = ‘mazowieckie-Warszawa’
SELECT s.nazwa
FROM sklep s, region r
WHERE s.region_id = r.region_id
AND r.nazwa = ‘mazowieckie’
AND s.miasto = ‘Warszawa’
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
10
Zapytania – wykorzystanie indeksów (2)
zastosowanie funkcji na indeksowanej kolumnie
SELECT …
SELECT …
FROM sklep s
FROM sklep s
WHERE UPPER(s.nazwa) = ‘SUPERSAM’ WHERE s.nazwa = ‘SUPERSAM’
niejawna konwersja typów
SELECT s.nazwa, s.miasto
FROM sklep s
WHERE s.numer = 1234
SELECT s.nazwa, s.miasto
FROM sklep s
WHERE s.numer = ‘1234’
SELECT s.nazwa, s.miasto
FROM sklep s
WHERE TO_NUMBER(s.numer) = 1234
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
11
Zapytania – wykorzystanie indeksów (3)
wykorzystanie w warunku porównania operatorów:
NOT, !=, <>
blokuje korzystanie z indeksu
Efektywniej na indeksowanej kolumnie wykonywane jest
porównanie >=, niż >
Zastosowanie wzorca w klauzuli LIKE, zawierającego na
początku znak specjalny % lub _ blokuje wykorzystanie indeksu
SELECT p.nazwa, p.cena
FROM produkt p
WHERE p.nazwa LIKE ‘%orzechy%’
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
12
Struktury fizyczne poprawiające wydajność
Indeksy
 B-drzewa
 Bitmapowe
 …
Partycje
 Range
 List
 Hash
 Jednopoziomowe partycjonowanie
 Partycjonowanie złożone
Widoki zmaterializowane
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
13
Automatyczne strojenie zapytań
Oracle – SQL Tuning Advisor oraz SQL Access Advisor
DB2 – DB2 Design Advisor
SQL Server – Database Tuning Advisor
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
14
Przykład interfejsu – Oracle (1)
Rysunek 1. Uruchomienie SQL Tuning Advisor
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
15
Przykład interfejsu – Oracle (2)
Rysunek 2. Wynik działania SQL Tuning Advisor
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
16
Przykład interfejsu – Oracle (3)
Rysunek 3. Nowy plan zapytania po implementacji wskazówki SAL Tuning Advisor
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
17
Kilka słów o pracy magisterskiej
Temat : „Badanie wydajności zapytań na przykładzie trzech
komercyjnych baz danych Oracle, DB2 oarz SQL Server”
Promotor: dr inż. Michał Rudowski
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
18
Literatura
C. Millsap, J. Holt: Optimizing Oracle performance, O’Reilly, 2003
B. Dageville, D. Das, K. Dias, K.Yagoube, M. Zait, M. Ziauddin:
Automatic SQL Tuning in Oracle 10g,
P. S. Sadore: How to write efficient SQK Queries with Tims &
Tricks
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
19
Pytania
Zapytania SQL: wydajność i optymalizacja
Łukasz Twardokęs, Politechnika Warszawska
20