Úvod do optimalizace v Oracle 9i

1. Úvod do optimalizace v Oracle 9i Jaroslav Tykal, Jiří Dokulil

2. Optimalizátor • určuje způsob vyhodnocení každého SQL dotazu • kvalita určuje i efektivitu zpracování • pro uživatele plně transparentní • obvykle „cost-based“ (první implementace v Oracle 7 – 1992)

3. Části optimalizátoru • Transformace SQL • Výběr exekučního plánu • Výběr zpracování dle statistik a ceny operace • Optimalizace za běhu • Optimalizace na základě pravidel

4. Transformace SQL • Cíl: • vytvořit k původnímu SQL dotazu dotaz sémanticky ekvivalentní, který půjde efektivněji zpracovat • Typy transformací: • heuristické • cost-based – výběr nejlepší z propočítaných strategií

5. Heuristické transformace • vlastnosti: • aplikují se kdykoliv je to možné • výstup vždy stejný nebo lepší • nesnižuje výkon databáze • typy: • úprava pohledů („view merging“) • „zploštění“ dotazů • generování tranzitivních predikátů • eliminace společných podvýrazů • predikáty pushdown a pullup, CUBE, …

6. Jednoduché pohledy • Definujeme: • CREATE VIEW TEST_VIEW AS SELECT ENAME, DNAME, SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO • Dotaz: • SELECT ENAME, DNAME FROM TEST_VIEW WHERE SAL > 10000 • Po transformaci: • SELECT ENAME, DNAME FROM EMP E, DEPT D WHERE D.DEPTNO = E.DEPTNO AND E.SAL > 10000

7. Složitější pohledy (s agreg. funkcí) • Definujeme: • CREATE VIEW AVG_SAL_VIEW AS SELECT DEPTNO, AVG(SAL) AVG_SAL_DEPT FROM EMP GROUP BY DEPTNO • Požadujeme: • SELECT DEPT.NAME, AVG_SAL_DEPT FROM DEPT, AVG_SAL_VIEW WHERE DEPT.DEPTNO = AVG_SAL_VIEW.DEPTNO AND DEPT.LOC = ‘OAKLAND’ • Po transformaci: • SELECT DEPT.NAME, AVG(SAL) FROM DEPT, EMP WHERE DEPT.DEPTNO=EMP.DEPTNO AND DEPT.LOC = ‘OAKLAND’ GROUP BY DEPT.ROWID, DEPT.NAME

8. Subquery „flattening“ • převod mnoha typů dotazů pomocí join, semi-join nebo anti-join • příklad: • SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO IN (SELECT E.DEPTNO FROM EMP E WHERE E.SAL > 10000) • mnoho způsobů jak vyhodnotit • zvážení možných transformací a výběr nejlepší na základě ceny

9. bez transformací tabulka EMP se prochází pro každý záznam v DEPT velká složitost Exekuční plán (1)

10. lepší plán pomocí semi join, který eliminuje duplicitní hodnoty z vnitřní tabulky v tomto případě zvoleno hash semi join Oracle nabízí také sort-merge semi join a nested-loop semi join Po transformaci (pseudo SQL): SELECT DNAME FROM EMP E, DEPT D WHERE D.DEPTNO <semijoin> E.DEPTNO AND E.SAL > 10000 Exekuční plán (2)

11. unique sort eliminuje duplicitni položky DEPTNO v tabulce EMP Po transformaci: SELECT D.DNAME FROM (SELECT DISTINCT DEPTNO FROM EMP) E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 10000 Exekuční plán (3)

12. Generování tranzitivního predikátu • Př: počet položek, které byly doručeny v den objednání • SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN ‘1-JAN-2002’ AND ’31-JAN-2002’ • Po transformaci (přidání dodatečného predikátu) • SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN ‘1-JAN-2002’ AND ’31-JAN-2002’ AND L_SHIPDATE BETWEEN ‘1-JAN-2002’ AND ’31-JAN-2002’

13. Eliminace opakujicích se podvýrazů • Př: zaměstnance v Dallasu, kteří jsou víceprezident nebo mají plat vyšší než 100 000 • SELECT * FROM EMP, DEPT WHERE (EMP.DEPTNO = DEPT.DEPTNO AND LOC=‘Dallas’ AND SAL > 100000) OR (EMP.DEPTNO = DEPT.DEPTNO AND LOC=‘Dallas’ AND JOB_TITLE=‘VICE PRESIDENT’) • Po transformaci: • SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND LOC=‘Dallas’ AND (SAL > 100000ORJOB_TITLE=‘VICE PRESIDENT’)

14. Operátory pushup, pulldown • dotaz může obsahovat více pohledů a poddotazů s mnoha podmínkami • Oracle může tyto podmínky přesunovat dovnitř, ven nebo mezi jednotlivými pohledy a poddotazy • důsledky: • data pro GROUP BY nebo JOIN bývají menší • lze použít jiné metody pro vyhodnocení (access path), které nejsou možné před úpravou

15. Operátory pushup, pulldown • Příklad pushdown • Zadáno • CREATE VIEW EMP_AGG AS SELECTDEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUPBY DEPTNO • Dotaz • SELECT DEPTNO, AVG_SAL FROM EMP_AGG WHEREDEPTNO = 10 • Po transformaci • SELECT DEPTNO, AVG(SAL) FROM EMP WHEREDEPTNO = 10GROUPBY DEPTNO

16. Prořezání dotazu s „CUBE“ (1) • CUBE – rozšíření SQL operátoru GROUP BY, které umožňuje více agragací v jednom SQL dotazu • transformací některých dotazů lze docílit významného ořezání dat pro agregační funkci • dotazy pocházejí obvykle od analytických nástrojů (aplikací), které používají předdefinovaný základ „cube“

17. Prořezání dotazu s „CUBE“ (2) • Původní dotaz • SELECT MONTH, REGION, DEPARTMENT, REVENUE FROM ( SELECT MONTH, REGION, DEPARTMENT, SUM(SALES_AMOUNT) AS REVENUE FROM SALES GROUPBYCUBE (MONTH, REGION, DEPT) ) WHEREMONTH = ‘JAN-2001’ • Transformovaný dotaz • SELECT MONTH, REGION, DEPARTMENT, REVENUE FROM ( SELECT MONTH, REGION, DEPARTMENT, SUM(SALES_AMOUNT) AS REVENUE FROM SALES WHEREMONTH = ‘JAN-2001’GROUPBYMONTH, CUBE (REGION, DEPT) ) WHEREMONTH = ‘JAN-2001’

18. Konverze vnějšího spojení • provádí se v případě, kdy vnější spojení dává stejný výsledek jako spojení vnitřní • konverze na vnitřní spojení může umožnit další optimalizace, které nejsou na vnějším spojení možná • spojování pohledů („view merging“) • změnu pořadí spojování

19. Cost-based transformace • vlastnosti: • používají se pro mnoho typů transformací • transformovaný dotaz je porovnán s původním dotazem a podle předpokládané složitosti (ceny) vyhodnocení, je vybrán nejlepší • typy: • použití materializovaných pohledů • OR „rozšíření“ • transformace „hvězdy“ • predikát pushdown pro vnější spojení pohledů

20. Materializované pohledy • vlastnosti: • bývají menší • mohou obsahovat předpočítané hodnoty (vyhodnocené agregační funkce) • transformace: • spočívá v nahrazování čtení z několika tabulek čtením z materializovaných pohledů • může výrazně urychlit vyhodnocení dotazu • nemusí být vždy urychlením (př.: základní tabulka je vhodně indexovaná, m.p. ne) • další implementované optimalizace: • provázání hierarchie dimenze u uložení dat ve formátu „Hvězdy“ (data warehouse)

21. Materializované pohledy (příklad) • Máme materializovaný pohled • CREATE MATERIALIZED VIEW SALES_SUMMARY AS SELECT SALES.CUST_ID, TIME.MONTH, SUM(SALES_AMOUNT) AMT FROM SALES, TIME WHERE SALES.TIME_ID = TIME.TIME_ID • Dotaz • SELECT CUSTOMER.CUST_NAME, TIME.MONTH, SUM(SALES.SALES_AMOUNT) FROM SALES, CUSTOMER, TIME WHERE SALES.CUST_ID = CUST.CUST_ID AND SALES.TIME_ID = TIME.TIME_ID GROUP BY CUSTOMER.CUST_NAME, TIME.MONTH • Po přepsání • SELECT CUSTOMER.CUST_NAME, SALES_SUMMARY.MONTH, SALES_SUMMARY.AMT FROM CUSTOMER, SALES_SUMMARY WHERE CUSTOMER.CUST_ID = SALES_SUMMARY.CUST_ID

22. Rozšíření dotazu - OR • Přepis OR v části WHERE pomocí konstrukce SELECT … UNION SELECT … bez použití OR pro každou takovou podmínku • Dotaz • SELECT* FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND (P1.PORT_NAME = ‘OAKLAND’ ORP2.PORT_NAME = ‘OAKLAND’) • Po transformaci • SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND P1.PORT_NAME = ‘OAKLAND’ UNION SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND P2.PORT_NAME <> ‘OAKLAND’ AND P1.PORT_NAME <> ‘OAKLAND’

23. Schema hvězdy • strategie uchovávání dat používaná v oblasti datových skladů • typicky obsahuje jednu nebo více obsáhlých tabulek (tabulek faktů) a mnoho menších tabulek (tabulky dimenzí) – obsahují popisná data • Oracle podporuje techniku vyhodnocování dotazů nazývanou „star transformation“ • funguje na principu vkládání poddotazů do položeného dotazu • vložené dotazy a bitmapové indexy umožňují efektivnější přístup k datům v tabulce faktů

24. Schema hvězdy - příklad • Datové schema příkladu Tabulka faktů Dimenze času 2 úrovně: den, čtvrtletí

25. Schema hvězdy - příklad • Před transformací SELECT STORE.STATE, SUM(S.AMOUNT) FROM SALES S, DAY D, QUARTER Q, PRODUCT P, STORE WHERE S.DAY_ID = D.DAY_IDAND D.QUARTER_ID = Q.QUARTER_ID AND S.PRODUCT_ID = P.PRODUCT_IDAND S.STORE_ID = S.STORE_ID AND P.PRODUCT_CATEGORY = ‘BEVERAGES’ANDQ.QUARTER_NAME = ‘2001Q3’ GROUP BY STORE.STATE • Po transformaci SELECT STORE.STATE, SUM(SALES.AMOUNT) FROM SALES, STORE WHERE SALES.STORE_ID = STORE.STORE_ID AND SALES.DAY_IDIN (SELECT DAY.DAY_ID FROM DAY D, QUARTER Q WHERE D.QUARTER_ID = Q.QUARTER_ID AND Q.QUARTER_NAME = ‘2001Q3’) AND SALES.PRODUCT_IDIN (SELECT PRODUCT.PRODUCT_ID FROM PRODUCT WHEREPRODUCT.PRODUCT_CATEGORY = ‘BEVERAGES’) GROUP BY STORE.STATE

26. Schema hvězdy • Transformované SQL je zpracováno ve dvou částech • potřebné řádky z tabulky faktů jsou vybrány pomocí bitmap indexu (v tomto případě se přistupuje pomocí DAY_ID a PRODUCT_ID, které jsou nalezeny pomocí poddotazů • na nalezené řádky je proveden join s tabulkou STORE • tento postup je patentovaný Oracle

27. Pushdown pro vnější spojení • při použití vnějšího spojení nelze přehodit pořadí spojovaných tabulek • Oracle používá operátor Pushdown, který přesune podmínku do jedné ze spojovaných tabulek • vnější spojení pak lze urychlit za použití indexů • ne vždy musí být rychlejší než původní spojení

28. Část 2 – určení pořadí spojení Access path selection

29. Předpoklady • velké množství datových struktur • tabulky • běžné tabulky • tabulky organizované jako index • hnízděné tabulky • klastrované tabulky • … • indexy • B-stromy • bitmapové indexy • bitmapové spojovací indexy (bitmap join indexes) • B-stromy indexující podle funkce • bitmapové indexy indexující podle funkce • doménové indexy

30. Předpoklady 2 • existuje velké množství (14) různých metod práce s indexy • přímo operace spojení má také přes deset verzí

31. Trocha počtů • Při 5 tabulkách existuje 5!=120 různých pořadí spojení tabulek • pro každé spojení existuje mnoho různých plánů, používajících různé kombinace indexů, přístupových metod a metod spojování • celkem pro 5 tabulek existuje několik tisíc plánů, což je dostatečně malé množství, aby bylo možné uvážit všechny • pro 10 tabulek už však dostaneme přes 3,5 milionů možných pořadí a hodně přes 100 milionů možných plánů • hrubá síla tedy není řešením

32. Jak moc je třeba optimalizovat? • Oracle používá adaptivní vyhledávací strategii, která má zajistit to, že doba strávená hledáním optimálního řešení nepřesáhne zlomek doby potřebné na vyhodnocení • pokud bude dotaz trvat 1 vteřinu, nemá smysl jej 10 vteřin optimalizovat • pokud dotaz pravděpodobně poběží několik hodin, je vhodné věnovat několik vteřin nebo i minut na nalezení lepšího plánu

33. Heuristiky • na začátku tvorby exekučního plánu je před samotným prohledáváním prostoru plánů použito několik heuristik, které mají šanci najít optimální -nebo alespoň dostatečně dobrý- plán okamžitě • není velkým překvapením, že Oracle o nich nic podrobnějšího neprozradí

34. Bitmapové indexy • dva typy • „pravé“ – komprimované bitové mapy uložené v databázi • dynamické – B-stromy, které jsou za běhu převedeny na bitové mapy • uložení bitmapového indexu může vést k až desetinásobné úspoře místa, což vede díky menšímu počtu I/O operací k až desetinásobnému nárůstu výkonu • velká síla těchto indexů je ve vyhodnocování podmínek propojených pomocí AND, OR a NOT

35. Bitmapové indexy 2 • dynamické bitmapové indexy při práci využívají hešování a vyžadují přístup k tabulce přes její rowid, aby dávaly správné výsledky • „skutečné“ bitmapové indexy jsou schopné vrátit přesný výsledek pouze zpracováním indexu • SELECT COUNT(*) FROM ZÁKAZNÍCI WHERE stát=‘ČR’ AND stav=‘svobodný’ • pokud pro tento dotaz existují bitmapové indexy nad stát a stav, pak je možné získat výsledek provedením AND na tyto indexy a spočítáním jedniček

36. Bitmapové indexy 3 • Oracle umožňuje použít více různých typů indexů pro přistup k jedné tabulce • při použití bitmapového a jiného indexu (např. B-strom) je druhý index převeden na dynamický bitmapový

37. Bitmapové spojovací indexy (bitmap join indexes) • tyto indexy slouží ke zrychlení konkrétních spojení • součástí jejich definice je, které tabulky a přes jakou podmínku budou spojovány • klasický přiklad použití je pro spojení faktové a dimensionální tabulky v datovém skladu. Například pro fakta o prodeji a dimenzi produkt je možné vytvořit index nad prodeji spojenými přes cizí klíč na produkt, ale jako indexovaný sloupec určit kategorii produktu. Takový index pak umožní najít prodeje produktů z určité kategorie bez nutnosti skutečně provést spojení těchto tabulek

38. Bitmapové spojovací indexy (bitmap join indexes) • takto vytvořený index bude navíc menší • počet kategorií produktů bude jistě meňší než počet produktů • menší kardinalita u bitmapového indexu znamená jeho menší velikost • tyto indexy je možné kombinovat s indexy nad tabulkami a jejich kombinace bude jako kombinace běžných bitmapových indexů

39. Aplikační doménové indexy • Oracle podporuje aplikační doménové indexy, což jsou uživatelské struktury, které umožňují efektivní přitup k datům jako dokumenty, obrázky a video. • těmto indexům je možné přiřadit statistiky a cenové funkce • optimalizátor pak dokáže tyto indexy používat ve optimalizacích stejně jako vestavěné indexy

40. Sekvenční přístup k indexu • Oracle dokáže projít index jako tabulku, tedy nikoliv jako strom, ale sekvenčně • pokud chceme z tabulky vybrat jen sloupce, které jsou všechny obsaženy v indexu, a jsme nuceni projít je celá • index bude pravděpodobně menší než tabulka, takže ušetříme I/O operace

41. Spojení dat z více indexů • pokud existuje několik indexů, které dohromady obsahují všechna data potřebná pro dotaz • podmínka WHERE je použita na tyto indexy a každý vrátí množinu záznamů • tyto množiny jsou spojeny na základě jejich rowid • má význam, pokud podkladová tabulka má hodně sloupců, ale potřebných je jen několik málo

42. Využití indexu bez podmínky na první sloupec • může se stát, že v podmínce dotazu není žádné omezení na hodnoty v prvním sloupci indexu • přesto může mít použití tohoto indexy smysl, pokud existují podmínky na dalších sloupcích a počet různých hodnot v prvním sloupci indexu je omezený • pak je možné pro každou hodnotu prvního sloupce zkusit použít podmínku na další sloupce • může se hodit, pokud neexistuje žádný přesně vyhovující index a jediná alternativa je sekvenční průchod tabulkou nebo indexem

43. Eliminace třídění • třídění velkých dat je náročné • důvodů pro třídění je více • ORDER BY • GROUP BY • DISTINCT • často je možné se vyhnout tomu, že data jsou skutečně tříděna • přístup podle indexu zajistí automaticky setřídění • existence UNIQUE omezení odstraní nutnost třídění kvůli distinct • nenáročné utřídění malých dat někde na počátku vyhodnocení může zajistit požadované utřídění celých dat • …

44. Eliminace třídění 2 • celkově je tento problém řešen tvorbou dvou plánů vyhodnocení • obvyklý plán na základě lokálních optimalizací • plán, který je zaměřen právě na využití přístupů pro eliminaci třídění • pro oba plány je nakonec spočítána celková cena • často se plán eliminující třídění ukáže jako lepší

45. Paralelizmus • jeden dotaz je možné pustit na více procesorech nebo dokonce na více uzlech • stupeň paralelismu je téměř neomezený • …a víc toho Oracle neprozradí

46. Hints – nápovědy • optimalizátor není dokonalý, mlže vytvořit suboptimální plán • použití by mělo být řídké • vhodné pro řešení otázek jako • Má smysl udržovat v databázi tento index? • údržba indexů něco stojí • pokud je index používán jen v jednom dotazu, je možné jej zrušit bez velké ztráty výkonu v tomto dotaze • pomocí nápovědy je možné tento experiment snadno provést, bez smazání a nového postavení indexu • v době jejich vytvoření mlže být vhodné, ale při nárůstu velikosti tabulky nebo jiné změně situace už může být plán poskytnutý optimalizátorem lepší

47. Určení ceny dotazu • přesnost určení ceny dotazu zásadně ovlivňuje efektivnost optimalizátoru • vychází ze • znalosti principu všech používaných přístupových metod • statistik o objektech v databázi • statistik o výkonu použitého HW • vliv různých optimalizací • různé cache • optimalizace I/O operací • paralelizmus • … • cíle optimalizace • podle nastavení administrátora se optimalizuje • doba na vrácení prvního řádku • doba na vrácení prvních N řádků • doba na vrácení prvních celého výsledku

48. Statistiky – objekty v databázi • počet bloků a řádek v tabulce • počet úrovní v B-stromu u indexu • statistiky pro každý sloupce • slouží pro odhad výsledku podmínky WHERE • minimální hodnota • maximální hodnota • počet různých hodnot • histogramy

49. Statistiky – systém • výkon HW • výkon CPU • výkon v I/O operacích • kombinace těchto hledisek není na základě pevného vzorce, ale pozorovaného chování systému za běžné zátěže

50. Statistiky – uživatelské • pro uživatelem definované funkce a doménové indexy • optimalizátor je na to připravený a díky tomu dokáže pracovat s uživatelskými funkcemi a indexy stejně jako s vlastními