How to Read an Execution Plan

Transcript

1. Your SQL Office Hours session will begin soon… How to Read an Execution Plan Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql
2. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Welcome to Ask TOM Office Hours!
3. SQL is awesome but… Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
4. How the #@!% do I make it faster?! Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
5. Look at the execution plan Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
6. someone confused/frustrated – how does that help me?! How the #@%$ does that help? Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
7. Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon An execution plan is the optimizer's directions
8. Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
9. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
10. someone confused/frustrated – how does that help me?! Those don't look like directions… Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
11. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How do we draw the arrows on this?
12. Tree picture An execution plan is a tree Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
13. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
14. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 |SELECT STATEMENT | | | 1 |HASH JOIN | | | 2 |TABLE ACCESS FULL| COLOURS | | 3 |TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
15. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Parent Children
16. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Parent Sibling
17. Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SELECT HASH JOIN BRICKS COLOURS
18. Following an Execution Plan Go to the first unvisited leaf Go to its parent Repeat! 1 2 3 Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Depth-first search!
19. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
20. Level up! Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
21. | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | HASH JOIN | | | 3 | HASH JOIN | | | 4 | TABLE ACCESS FULL| COLOURS | | 5 | TABLE ACCESS FULL| TOYS | | 6 | TABLE ACCESS FULL | PENS | | 7 | TABLE ACCESS FULL | BRICKS | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
22. Level up! Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
23. | 0 | SELECT STATEMENT | | | 1 | SORT ORDER BY | | | 2 | HASH GROUP BY | | | 3 | HASH JOIN | | | 4 | TABLE ACCESS FULL | COLOURS | | 5 | VIEW | | | 6 | UNION-ALL | | | 7 | TABLE ACCESS FULL| BRICKS | | 8 | TABLE ACCESS FULL| TOYS | | 9 | TABLE ACCESS FULL| PENS | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
24. Level up! Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
25. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| BRICKS | | 3 | TABLE ACCESS FULL | COLOURS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
26. Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Where's the join? Ryan McGuire / Gratisography
27. select ( select count(*) from bricks b where b.colour = c.colour ) brick# from colours c; Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
28. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| BRICKS | | 3 | TABLE ACCESS FULL | COLOURS | -------------------------------------- Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
29. someone confused/frustrated – how does that help me?! But how do I make it faster?! Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
30. Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
31. Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ¯\_(ツ)_/¯
32. Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Distance 5,000 miles Est time: 11hrs Actual time: 10.5 hrs Other routes?
33. Plan Tuning Questions Row Estimates Are they accurate? I/O Reads How much work did we do? Access Methods Can we read the rows more efficiently? Step Duration How long did each operation take? Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
34. DBMS_XPlan set serveroutput off select /*+ gather_plan_statistics */... from ...; select * from table ( dbms_xplan.display_cursor ( null, null, 'ALLSTATS LAST' ) ); Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
35. --------------------------------------------------- | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------- | 1 | | 56 |00:00:00.01 | 24 | | 1 | 68 | 56 |00:00:00.01 | 24 | | 1 | 8 | 7 |00:00:00.01 | 18 | | 1 | 4 | 3 |00:00:00.01 | 12 | | 1 | 3 | 3 |00:00:00.01 | 6 | | 1 | 5 | 5 |00:00:00.01 | 6 | | 1 | 9 | 9 |00:00:00.01 | 6 | | 1 | 24 | 24 |00:00:00.01 | 6 | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
36. --------------------------------------------------- | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------- | 1 | | 56 |00:00:00.01 | 24 | | 1 | 68 | 56 |00:00:00.01 | 24 | | 1 | 8 | 7 |00:00:00.01 | 18 | | 1 | 4 | 3 |00:00:00.01 | 12 | | 1 | 3 | 3 |00:00:00.01 | 6 | | 1 | 5 | 5 |00:00:00.01 | 6 | | 1 | 9 | 9 |00:00:00.01 | 6 | | 1 | 24 | 24 |00:00:00.01 | 6 | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How many times did we do this?
37. --------------------------------------------------- | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------- | 1 | | 56 |00:00:00.01 | 24 | | 1 | 68 | 56 |00:00:00.01 | 24 | | 1 | 8 | 7 |00:00:00.01 | 18 | | 1 | 4 | 3 |00:00:00.01 | 12 | | 1 | 3 | 3 |00:00:00.01 | 6 | | 1 | 5 | 5 |00:00:00.01 | 6 | | 1 | 9 | 9 |00:00:00.01 | 6 | | 1 | 24 | 24 |00:00:00.01 | 6 | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How many rows did it think we'd get…
38. --------------------------------------------------- | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------- | 1 | | 56 |00:00:00.01 | 24 | | 1 | 68 | 56 |00:00:00.01 | 24 | | 1 | 8 | 7 |00:00:00.01 | 18 | | 1 | 4 | 3 |00:00:00.01 | 12 | | 1 | 3 | 3 |00:00:00.01 | 6 | | 1 | 5 | 5 |00:00:00.01 | 6 | | 1 | 9 | 9 |00:00:00.01 | 6 | | 1 | 24 | 24 |00:00:00.01 | 6 | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon …how many rows did we really get?
39. --------------------------------------------------- | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------- | 1 | | 56 |00:00:00.01 | 24 | | 1 | 68 | 56 |00:00:00.01 | 24 | | 1 | 8 | 7 |00:00:00.01 | 18 | | 1 | 4 | 3 |00:00:00.01 | 12 | | 1 | 3 | 3 |00:00:00.01 | 6 | | 1 | 5 | 5 |00:00:00.01 | 6 | | 1 | 9 | 9 |00:00:00.01 | 6 | | 1 | 24 | 24 |00:00:00.01 | 6 | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How long did it take?
40. --------------------------------------------------- | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------- | 1 | | 56 |00:00:00.01 | 24 | | 1 | 68 | 56 |00:00:00.01 | 24 | | 1 | 8 | 7 |00:00:00.01 | 18 | | 1 | 4 | 3 |00:00:00.01 | 12 | | 1 | 3 | 3 |00:00:00.01 | 6 | | 1 | 5 | 5 |00:00:00.01 | 6 | | 1 | 9 | 9 |00:00:00.01 | 6 | | 1 | 24 | 24 |00:00:00.01 | 6 | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How much work did we do?
41. --------------------------------------------------- | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------- | 1 | | 56 |00:00:00.01 | 24 | | 1 | 68 | 56 |00:00:00.01 | 24 | | 1 | 8 | 7 |00:00:00.01 | 18 | | 1 | 4 | 3 |00:00:00.01 | 12 | | 1 | 3 | 3 |00:00:00.01 | 6 | | 1 | 5 | 5 |00:00:00.01 | 6 | | 1 | 9 | 9 |00:00:00.01 | 6 | | 1 | 24 | 24 |00:00:00.01 | 6 | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Step specific
42. --------------------------------------------------- | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------- | 1 | | 56 |00:00:00.01 | 24 | | 1 | 68 | 56 |00:00:00.01 | 24 | | 1 | 8 | 7 |00:00:00.01 | 18 | | 1 | 4 | 3 |00:00:00.01 | 12 | | 1 | 3 | 3 |00:00:00.01 | 6 | | 1 | 5 | 5 |00:00:00.01 | 6 | | 1 | 9 | 9 |00:00:00.01 | 6 | | 1 | 24 | 24 |00:00:00.01 | 6 | Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Cumulative
43. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | DEMO
44. Where do I go from here? Learn how to get a plan Get the scripts Start playing! 1 2 3 http://bit.ly/exec-plan http://bit.ly/plan-scripts Copyright © 2019 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
45. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography