How to Read an Execution Plan

An introduction to reading execution plans to understand SQL performance

For further discussion, read
https://blogs.oracle.com/oraclemagazine/how-to-read-an-execution-plan

Transcript

1. 1 How to Read an Execution Plan Chris Saxon, @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql youtube.com/c/TheMagicofSQL asktom.oracle.com Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
2. SQL is awesome! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
3. but… Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
4. How the #@!% do I make it faster?! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
5. Look at the execution plan Copyright © 2020 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
7. Copyright © 2020 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 © 2020 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 © 2020 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
11. 12 How to Read an Execution Plan Chris Saxon, @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql youtube.com/c/TheMagicofSQL asktom.oracle.com Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
12. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How do we draw the arrows on this?
13. Tree picture An execution plan is a tree Copyright © 2020 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 © 2020 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 © 2020 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
17. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
18. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SELECT HASH JOIN BRICKS COLOURS
19. Following an Execution Plan Go to the first unvisited leaf Go to its parent Repeat! 1 2 3 Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Depth-first search!
20. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL| COLOURS | | 3 | TABLE ACCESS FULL| BRICKS | -------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
21. Level up! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
22. | 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
23. Level up! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
24. | 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
25. Level up! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
26. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| BRICKS | | 3 | TABLE ACCESS FULL | COLOURS | -------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
27. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Where's the join? Ryan McGuire / Gratisography
28. select ( select count(*) from bricks b where b.colour = c.colour ) brick# from colours c; Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
29. -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| BRICKS | | 3 | TABLE ACCESS FULL | COLOURS | -------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
30. someone confused/frustrated – how does that help me?! But how do I make it faster?! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
31. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
32. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ¯\_(ツ)_/¯
33. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Distance ~600 km Est time: 5.5 hrs Actual time: 9 hrs Other routes?
34. 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
35. DBMS_XPlan set serveroutput off select /*+ gather_plan_statistics */... from ...; select * from table ( dbms_xplan.display_cursor ( null, null, 'ALLSTATS LAST' ) ); Copyright © 2020 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How many times did we do this?
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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How many rows did it think we'd 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon …how many rows did we really get?
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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How long did it take?
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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon How much work did we do?
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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Step specific
43. --------------------------------------------------- | 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 © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Cumulative
44. Possible problems Incorrect row estimates Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
45. --------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | --------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 94 | 56 | | HASH JOIN | | 1 | 42 | 24 | | MERGE JOIN CARTESIAN| | 1 | 5 | 120 | | TABLE ACCESS FULL | BRICKS | 1 | 1 | 24 | | BUFFER SORT | | 24 | 5 | 120 | | TABLE ACCESS FULL | TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | COLOURS | 1 | 100 | 3 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | --------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
46. --------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | --------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 94 | 56 | | HASH JOIN | | 1 | 42 | 24 | | MERGE JOIN CARTESIAN| | 1 | 5 | 120 | | TABLE ACCESS FULL | BRICKS | 1 | 1 | 24 | | BUFFER SORT | | 24 | 5 | 120 | | TABLE ACCESS FULL | TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | COLOURS | 1 | 100 | 3 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | --------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
47. --------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | --------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 94 | 56 | | HASH JOIN | | 1 | 42 | 24 | | MERGE JOIN CARTESIAN| | 1 | 5 | 120 | | TABLE ACCESS FULL | BRICKS | 1 | 1 | 24 | | BUFFER SORT | | 24 | 5 | 120 | | TABLE ACCESS FULL | TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | COLOURS | 1 | 100 | 3 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | --------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
48. --------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | --------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 94 | 56 | | HASH JOIN | | 1 | 42 | 24 | | MERGE JOIN CARTESIAN| | 1 | 5 | 120 | | TABLE ACCESS FULL | BRICKS | 1 | 1 | 24 | | BUFFER SORT | | 24 | 5 | 120 | | TABLE ACCESS FULL | TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | COLOURS | 1 | 100 | 3 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | --------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
49. --------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | --------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 94 | 56 | | HASH JOIN | | 1 | 42 | 24 | | MERGE JOIN CARTESIAN| | 1 | 5 | 120 | | TABLE ACCESS FULL | BRICKS | 1 | 1 | 24 | | BUFFER SORT | | 24 | 5 | 120 | | TABLE ACCESS FULL | TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | COLOURS | 1 | 100 | 3 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | --------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
50. --------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | --------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 94 | 56 | | HASH JOIN | | 1 | 42 | 24 | | MERGE JOIN CARTESIAN| | 1 | 5 | 120 | | TABLE ACCESS FULL | BRICKS | 1 | 1 | 24 | | BUFFER SORT | | 24 | 5 | 120 | | TABLE ACCESS FULL | TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | COLOURS | 1 | 100 | 3 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | --------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon * ~
51. --------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | --------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 94 | 56 | | HASH JOIN | | 1 | 42 | 24 | | MERGE JOIN CARTESIAN| | 1 | 5 | 120 | | TABLE ACCESS FULL | BRICKS | 1 | 1 | 24 | | BUFFER SORT | | 24 | 5 | 120 | | TABLE ACCESS FULL | TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | COLOURS | 1 | 100 | 3 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | --------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
52. begin dbms_stats.gather_table_stats ( user, 'bricks' ); dbms_stats.gather_table_stats ( user, 'toys' ); end; / Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
53. ------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | ------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 68 | 56 | | HASH JOIN | | 1 | 8 | 7 | | HASH JOIN | | 1 | 4 | 3 | | TABLE ACCESS FULL| COLOURS | 1 | 3 | 3 | | TABLE ACCESS FULL| TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | | TABLE ACCESS FULL | BRICKS | 1 | 24 | 24 | ------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
54. ------------------------------------------------------------- | Operation | Name | Starts | E-Rows | A-Rows | ------------------------------------------------------------- | SELECT STATEMENT | | 1 | | 56 | | HASH JOIN | | 1 | 68 | 56 | | HASH JOIN | | 1 | 8 | 7 | | HASH JOIN | | 1 | 4 | 3 | | TABLE ACCESS FULL| COLOURS | 1 | 3 | 3 | | TABLE ACCESS FULL| TOYS | 1 | 5 | 5 | | TABLE ACCESS FULL | PENS | 1 | 9 | 9 | | TABLE ACCESS FULL | BRICKS | 1 | 24 | 24 | ------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
55. Possible problems Incorrect row estimates Part II Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
56. select count (*) from bricks where colour = 'red' and shape = 'cylinder'; Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select count (*) from bricks where colour = 'red' and shape = 'prism';
57. ------------------- | E-Rows | A-Rows | ------------------- | | 1 | | 1 | 1 | | 3 | | ------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ------------------- | E-Rows | A-Rows | ------------------- | | 1 | | 1 | 1 | | 3 | | -------------------
58. ------------------- | E-Rows | A-Rows | ------------------- | | 1 | | 1 | 1 | | 3 | 8 | ------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ------------------- | E-Rows | A-Rows | ------------------- | | 1 | | 1 | 1 | | 3 | 0 | -------------------
59. begin dbms_stats.gather_table_stats ( user, 'bricks', method_opt => ' for columns ( colour, shape )' ); end; / Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
60. ------------------- | E-Rows | A-Rows | ------------------- | | 1 | | 1 | 1 | | 8 | 8 | ------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ------------------- | E-Rows | A-Rows | ------------------- | | 1 | | 1 | 1 | | 3 | 0 | -------------------
61. select count (*) from bricks where upper ( colour ) = 'RED'; Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
62. ------------------- | E-Rows | A-Rows | ------------------- | | 1 | | 1 | 1 | | 1 | 8 | ------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
63. begin dbms_stats.gather_table_stats ( user, 'bricks', method_opt => ' for columns ( upper ( colour ) )' ); end; / Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
64. ------------------- | E-Rows | A-Rows | ------------------- | | 1 | | 1 | 1 | | 8 | 8 | ------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
65. Possible problems Inefficient access Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
66. --------------------------------------------------- | Operation | Name | A-Rows | Buffers | --------------------------------------------------- | SELECT STATEMENT | | 1 | 13 | | HASH JOIN | | 1 | 13 | | TABLE ACCESS FULL| BRICKS | 1 | 7 | | TABLE ACCESS FULL| COLOURS | 1 | 6 | --------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
67. --------------------------------------------------- | Operation | Name | A-Rows | Buffers | --------------------------------------------------- | SELECT STATEMENT | | 1 | 13 | | HASH JOIN | | 1 | 13 | | TABLE ACCESS FULL| BRICKS | 1 | 7 | | TABLE ACCESS FULL| COLOURS | 1 | 6 | --------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
68. alter table bricks add constraint bricks_pk primary key ( brick_id ); create index colour_i on colours ( colour ); Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
69. --------------------------------------------------- | Operation | Name | Buffers | --------------------------------------------------- | SELECT STATEMENT | | 4 | | NESTED LOOPS | | 4 | | TABLE ACCESS BY INDEX … | BRICKS | 2 | | INDEX UNIQUE SCAN | BRICKS_PK | 1 | | TABLE ACCESS BY INDEX … | COLOURS | 2 | | INDEX RANGE SCAN | COLOUR_I | 1 | --------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
70. --------------------------------------------------- | Operation | Name | Buffers | --------------------------------------------------- | SELECT STATEMENT | | 4 | | NESTED LOOPS | | 4 | | TABLE ACCESS BY INDEX … | BRICKS | 2 | | INDEX UNIQUE SCAN | BRICKS_PK | 1 | | TABLE ACCESS BY INDEX … | COLOURS | 2 | | INDEX RANGE SCAN | COLOUR_I | 1 | --------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
71. Possible problems Repeating Work Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
72. --------------------------------------------------- | Operation | Name | Starts | Buffers | --------------------------------------------------- | SELECT STATEMENT | | 1 | 6 | | SORT AGGREGATE | | 3 | 18 | | TABLE ACCESS FULL| BRICKS | 3 | 18 | | TABLE ACCESS FULL | COLOURS | 1 | 6 | --------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
73. select count(*) brick# from colours c left join bricks b on b.colour = c.colour group by c.colour; Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
74. ---------------------------------------------------- | Operation | Name | Starts | Buffers | ---------------------------------------------------- | SELECT STATEMENT | | 1 | 12 | | HASH GROUP BY | | 1 | 12 | | HASH JOIN OUTER | | 1 | 12 | | TABLE ACCESS FULL| COLOURS | 1 | 6 | | TABLE ACCESS FULL| BRICKS | 1 | 6 | ---------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
75. Possible problems Reading Many Rows Returning Few Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
76. ------------------------------------------------- | Operation | Name | Starts | A-Rows | ------------------------------------------------- | SELECT STATEMENT | | 1 | 3 | | HASH GROUP BY | | 1 | 3 | | TABLE ACCESS FULL| BRICKS | 1 | 100K| ------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
77. create materialized view brick_mv enable query rewrite as select colour, count (*) from bricks group by colour; Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
78. create materialized view brick_mv enable query rewrite as select colour, count (*) from bricks group by colour; Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
79. ------------------------------------------------------------- | Operation | Name | Starts | A-Rows | ------------------------------------------------------------- | SELECT STATEMENT | | 1 | 3 | | MAT_VIEW REWRITE ACCESS FULL| BRICK_MV | 1 | 3 | ------------------------------------------------------------- Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
80. Possible problems Unrealistic Requirements Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
81. The SQL reads 10B rows in 10s Can you do it in 1ms? Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
82. someone confused/frustrated – how does that help me?! … Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Ryan McGuire / Gratisography
83. Getting Better Plans Check row estimates Create faster access paths Review requirements! 1 2 3 Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
84. Where do I go from here? Learn how to get a plan Get the scripts Watch the videos 1 2 3 https://bit.ly/exec-plan https://bit.ly/plan-scripts Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon https://bit.ly/sql-performance-videos
85. #MakeDataGreatAgain blogs.oracle.com/sql Ryan McGuire / Gratisography Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon