Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

How to Read an Execution Plan

Chris
July 01, 2020

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

Chris

July 01, 2020
Tweet

More Decks by Chris

Other Decks in Technology

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. How the #@!% do I make it faster?! Copyright ©

    2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  4. 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
  5. 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
  6. -------------------------------------- | 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
  7. 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
  8. 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
  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 How do we draw the arrows on this?
  10. 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
  11. -------------------------------------- | 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
  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 Parent Children
  13. -------------------------------------- | 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
  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. 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!
  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. | 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
  18. | 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
  19. -------------------------------------- | 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
  20. 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
  21. -------------------------------------- | 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
  22. 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
  23. 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
  24. 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
  25. --------------------------------------------------- | 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
  26. --------------------------------------------------- | 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?
  27. --------------------------------------------------- | 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…
  28. --------------------------------------------------- | 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?
  29. --------------------------------------------------- | 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?
  30. --------------------------------------------------- | 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?
  31. --------------------------------------------------- | 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
  32. --------------------------------------------------- | 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
  33. Possible problems Incorrect row estimates Copyright © 2020 Oracle and/or

    its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  34. --------------------------------------------------------------- | 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
  35. --------------------------------------------------------------- | 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
  36. --------------------------------------------------------------- | 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
  37. --------------------------------------------------------------- | 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
  38. --------------------------------------------------------------- | 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
  39. --------------------------------------------------------------- | 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 * ~
  40. --------------------------------------------------------------- | 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
  41. 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
  42. ------------------------------------------------------------- | 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
  43. ------------------------------------------------------------- | 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
  44. Possible problems Incorrect row estimates Part II Copyright © 2020

    Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  45. 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';
  46. ------------------- | 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 | | -------------------
  47. ------------------- | 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 | -------------------
  48. 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
  49. ------------------- | 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 | -------------------
  50. 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
  51. ------------------- | 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
  52. 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
  53. ------------------- | 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
  54. Possible problems Inefficient access Copyright © 2020 Oracle and/or its

    affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  55. --------------------------------------------------- | 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
  56. --------------------------------------------------- | 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
  57. 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
  58. --------------------------------------------------- | 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
  59. --------------------------------------------------- | 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
  60. Possible problems Repeating Work Copyright © 2020 Oracle and/or its

    affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  61. --------------------------------------------------- | 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
  62. 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
  63. ---------------------------------------------------- | 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
  64. Possible problems Reading Many Rows Returning Few Copyright © 2020

    Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  65. ------------------------------------------------- | 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
  66. 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
  67. 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
  68. ------------------------------------------------------------- | 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
  69. Possible problems Unrealistic Requirements Copyright © 2020 Oracle and/or its

    affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  70. 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
  71. 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
  72. 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
  73. 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
  74. #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