Execution plans

1. Execution plans Lubomír Andrle lubomir.andrle@unicorn.eu 6. přednáška 11.11.2013

2. Agenda • Shrnutí předchozích poznatků … • Jakzískat execution plan • Jak umět číst execution plan • Metody joinů

3. Opakování ;) • Shrnutí z minulých přednášek • Executionplány vznikají v rámci hard parsu • Vznikají na základě statistik • Vyčíslením nejvýhodnějšího costu • Jsou uloženy v librarycache(shared pool) • pozor: jedno sqlmůže mít několik executionplans • childcursory • Jeden executionplan může být sdílen různými sql

4. Získání execution plan

5. Získání execution plan • Oracle poskytuje 4 metody pro získání execution plan • SQL příkazemEXPLAIN PLAN • Dynamický dotaz do performance view • Pouze z librarycache • Automatic Workload Repository (AWR) • Pomocí aktivace tracing podpory

6. Příkaz EXPLAIN PLAN • Statement • SELECT, INSERT, UPDATE, MERGE, DELETE, CREATETABLE, CREATE INDEX, ALTER INDEX • ID • Nepovinné, identifikuje příkaz v plan table • Table • Plan table

7. Příkaz EXPLAIN PLAN - výstup • EXPLAIN PLAN FOR SELECT * FROM T1 WHERE col1= 10 ORDER BY col2; • SELECT * FROM table(dbms_xplan.display); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT || 5 | 185 | 4 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 5 | 185 | 4 (25)| 00:00:01 | |* 2 | TABLE ACCESS FULL | T1 | 5 | 185 | 3 (0) | 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(„COL1"=10)

8. Dotaz do performance view • Dotaz do librarycache • Důležité view • v$sql_plan • Podobné informace jako v plan table • v$sql_plan_statistics • Informace o spuštění příkazu (Doba běhu, počet řádků, …) • v$sql_workarea • Paměťové nároky dotazu • v$sql_plan_statistics_all • Sloučení předešlých view

9. Performance view a display cursor • Pro jednodušší interpretaci plánů • Použití packagedbms_xplan • SELECT * FROM table(dbms_xplan.display_cursor('1hqjydsjbvmwq',0)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ SQL_ID 1hqjydsjbvmwq, child number 0 ------------------------------------- SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID = P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount' ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 517 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | |* 2 | HASH JOIN | | 913K| 26M| 517 (4)| 00:00:07 | |* 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 17 (0)| 00:00:01 | | 4 | PARTITION RANGE ALL| | 918K| 8075K| 494 (3)| 00:00:06 | | 5 | TABLE ACCESS FULL | SALES | 918K| 8075K| 494 (3)| 00:00:06 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."PROMO_ID"="P"."PROMO_ID") 3 - filter("PROMO_SUBCATEGORY"='online discount')

10. AWR • Úložiště zodpovědné za sběr informací souvisejících s výkonem • Slouží pro účely „self-tuningu“ • Při vytváření snímku (snapshot) může AWR sbírat plány

11. AWR - ukázka SELECT * FROM table(dbms_xplan.display_awr('1hqjydsjbvmwq')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------ SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID = P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount' ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 517 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | | 2 | HASH JOIN | | 913K| 26M| 517 (4)| 00:00:07 | | 3 | TABLE ACCESS FULL | PROMOTIONS | 23 | 483 | 17 (0)| 00:00:01 | | 4 | PARTITION RANGE ALL| | 918K| 8075K| 494 (3)| 00:00:06 | | 5 | TABLE ACCESS FULL | SALES | 918K| 8075K| 494 (3)| 00:00:06 | ------------------------------------------------------------------------------------

12. Možnosti trasování • Události • Event 10053 • Ideální možnost pochopení CBO ;) • ALTER SESSION SET events '10053 trace name context forever' • Pozor čtení tracefiles není jednoduché • Event 10132 • Vhodné pro historii všech execution plan vztažených k nějakému modulu • Aktivovat pouze v opodstatněných případech • Události nejsou oficiálně podporovány!

13. Čtení execution plan

14. Packagedbms_xplan – 1. sekce • Výstup - první sekce • sql_id • Pouze v případě generování pomocí funkcí display_cursoradisplay_awr • child number • Spolu se sql_ididentifikuje child cursor • Součástí pouze při použití display_cursor • SQL příkaz SQL_ID cmm8zrzv2v2s5, child number 0 ------------------------------------- SELECT t2.* FROM t t1, t t2 WHERE t1.n = t2.n AND t1.id > 6 AND t2.id BETWEEN 6 AND 19

15. Packagedbms_xplan – 2. sekce • Výstup – druhé sekce Plan hashvalue: 1338433605 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 49 (100)| | |* 1 | HASH JOIN | | 14 | 7756 | 49 (3)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| T | 14 | 7392 | 4 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_PK | 14 | | 2 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | T | 994 | 25844 | 44 (0)| 00:00:01 | ------------------------------------------------------------------------------------

16. Packagedbms_xplan – 2. sekce • Sloupce (vždy zobrazeny) • Id operace • Operation • Name objektu • Sloupce (Oracle odhaduje) • Rows – odhad počtu vrácených řádků operace • Bytes - odhad velikosti dat operace • Cost (%CPU) – odhad ceny operace • Time – odhad délky trvání operace • A další …

17. Packagedbms_xplan – 3. sekce • Predikáty Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."N"="T2"."N") 3 - access("T2"."ID">=6 AND "T2"."ID"<=19) 4 - filter("T1"."ID">6)

18. Vztahy Parent - Child • Execution plan je stromová struktura • Zachycuje pořadí spuštění operací • Plus vztahy mezi operacemi • Pravidla vztahů mezi operacemi • Rodič má jednoho nebo více potomků • Potomek má právě jednoho rodiče • Operace bez rodiče je vždy kořen stromu • Rodič je vždy před svými dětmi (IDparent< IDchild)

19. Ukázka vztahů • Vztahy operací v execution plan --------------------------------------- | Id | Operation | --------------------------------------- | 1 | UPDATE | | 2 | NESTED LOOPS | |* 3 | TABLE ACCESS FULL | |* 4 | INDEX UNIQUE SCAN | | 5 | SORT AGGREGATE | | 6 | TABLE ACCESS BY INDEX ROWID| |* 7 | INDEX RANGE SCAN | | 8 | TABLE ACCESS BY INDEX ROWID | |* 9 | INDEX UNIQUE SCAN | ---------------------------------------

20. Optimalizační operace I • COUNT STOPKEY • Má za úkol jakmile je to možné ukončit vykonávání • Operace má určený limit • Pozor např. v případě Order by klauzule SELECT * FROM emp WHERE rownum <= 10 ----------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | ----------------------------------------------------- |* 1 | COUNT STOPKEY || 1 | 10 | | 2 | TABLE ACCESS FULL| EMP | 1 | 10 | ----------------------------------------------------- 1 - filter(ROWNUM<=10)

21. Optimalizační operace II • FILTER • Nemusí být vždy aplikována pouze na filtrování dat svých potomků • Může zabránit úplnému spuštění operace-potomka SELECT * FROM emp WHERE job = 'CLERK' AND 1 = 2 -------------------------------------------------------------------- | Id | Operation | Name | Starts | A-Rows | -------------------------------------------------------------------- |* 1 | FILTER | | 1 | 0 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 0 | 0 | |* 3 | INDEX RANGE SCAN | EMP_JOB_I | 0 | 0 | -------------------------------------------------------------------- 1 - filter(NULL IS NOT NULL) 3 - access(JOB='CLERK')

22. Metody joins

23. Metody joins • Slouží k joinům dvou datových množin • Nested LoopsJoins • HashJoins • Sort-mergejoins • Cartesianjoins

24. Metoda NestedLoops • Join dvou množin dat • Outerloop – spuštěn pouze jednou • Innerloop – spuštěn n-krát • Nestedloop v pseudo kódu selectempno, ename, dnamelocfromemp, deptwhereemp.deptno = dept.deptno Foreachrow in (selectempno, ename, deptnofromemp) loop For (selectdname, locfromdeptwheredeptno = outer.deptno) loop Ifmatchthenpasstherow on to next step Ifinnerjoin and no matchthendiscardtherow Ifouterjoin and no match set innercolumnvalues to null and pass to next step End loop; End loop;

25. Metoda Hashjoin • Velká příprava před vlastním průchodem • Hashjoin v pseudo kódu selectempno, ename, dnamelocfromemp, dept whereemp.deptno = dept.deptno determinedthesmallerrow set selectdname, loc, deptnofromdept hashthedeptnocolumn and build a hash table selectempno, ename, deptnofromemp hashthedeptnocolumn and build a hashtable ifmatchconfirmrow if no match – discardrow

26. Metoda sort-mergejoin • Záleží na pořadí množin • Paměťově a časově náročné • Pouze pro malé množiny dat • Setřídění a párové porovnání klíčů

27. Sort-mergejoin - ukázka

28. Cartesionjoin • Kartézský join je použit při absenci podmínky • „Všechno se vším“ • Pozor na počty řádku – A rows x B rows = totalrows

29. Q&A