Link
Embed
Share
Beginning
This slide
Copy link URL
Copy link URL
Copy iframe embed code
Copy iframe embed code
Copy javascript embed code
Copy javascript embed code
Share
Tweet
Share
Tweet
Slide 1
Slide 1 text
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
Slide 2
Slide 2 text
SQL is awesome! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 3
Slide 3 text
but… Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 4
Slide 4 text
How the #@!% do I make it faster?! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 5
Slide 5 text
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
Slide 6
Slide 6 text
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
Slide 7
Slide 7 text
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
Slide 8
Slide 8 text
Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 9
Slide 9 text
-------------------------------------- | 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
Slide 10
Slide 10 text
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
Slide 11
Slide 11 text
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
Slide 12
Slide 12 text
-------------------------------------- | 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?
Slide 13
Slide 13 text
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
Slide 14
Slide 14 text
-------------------------------------- | 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
Slide 15
Slide 15 text
-------------------------------------- | 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
Slide 16
Slide 16 text
-------------------------------------- | 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
Slide 17
Slide 17 text
-------------------------------------- | 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
Slide 18
Slide 18 text
Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon SELECT HASH JOIN BRICKS COLOURS
Slide 19
Slide 19 text
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!
Slide 20
Slide 20 text
-------------------------------------- | 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
Slide 21
Slide 21 text
Level up! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 22
Slide 22 text
| 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
Slide 23
Slide 23 text
Level up! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 24
Slide 24 text
| 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
Slide 25
Slide 25 text
Level up! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 26
Slide 26 text
-------------------------------------- | 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
Slide 27
Slide 27 text
Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Where's the join? Ryan McGuire / Gratisography
Slide 28
Slide 28 text
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
Slide 29
Slide 29 text
-------------------------------------- | 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
Slide 30
Slide 30 text
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
Slide 31
Slide 31 text
Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 32
Slide 32 text
Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ¯\_(ツ)_/¯
Slide 33
Slide 33 text
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?
Slide 34
Slide 34 text
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
Slide 35
Slide 35 text
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
Slide 36
Slide 36 text
--------------------------------------------------- | 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
Slide 37
Slide 37 text
--------------------------------------------------- | 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?
Slide 38
Slide 38 text
--------------------------------------------------- | 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…
Slide 39
Slide 39 text
--------------------------------------------------- | 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?
Slide 40
Slide 40 text
--------------------------------------------------- | 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?
Slide 41
Slide 41 text
--------------------------------------------------- | 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?
Slide 42
Slide 42 text
--------------------------------------------------- | 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
Slide 43
Slide 43 text
--------------------------------------------------- | 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
Slide 44
Slide 44 text
Possible problems Incorrect row estimates Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 45
Slide 45 text
--------------------------------------------------------------- | 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
Slide 46
Slide 46 text
--------------------------------------------------------------- | 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
Slide 47
Slide 47 text
--------------------------------------------------------------- | 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
Slide 48
Slide 48 text
--------------------------------------------------------------- | 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
Slide 49
Slide 49 text
--------------------------------------------------------------- | 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
Slide 50
Slide 50 text
--------------------------------------------------------------- | 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 * ~
Slide 51
Slide 51 text
--------------------------------------------------------------- | 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
Slide 52
Slide 52 text
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
Slide 53
Slide 53 text
------------------------------------------------------------- | 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
Slide 54
Slide 54 text
------------------------------------------------------------- | 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
Slide 55
Slide 55 text
Possible problems Incorrect row estimates Part II Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 56
Slide 56 text
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';
Slide 57
Slide 57 text
------------------- | 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 | | -------------------
Slide 58
Slide 58 text
------------------- | 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 | -------------------
Slide 59
Slide 59 text
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
Slide 60
Slide 60 text
------------------- | 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 | -------------------
Slide 61
Slide 61 text
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
Slide 62
Slide 62 text
------------------- | 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
Slide 63
Slide 63 text
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
Slide 64
Slide 64 text
------------------- | 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
Slide 65
Slide 65 text
Possible problems Inefficient access Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 66
Slide 66 text
--------------------------------------------------- | 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
Slide 67
Slide 67 text
--------------------------------------------------- | 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
Slide 68
Slide 68 text
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
Slide 69
Slide 69 text
--------------------------------------------------- | 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
Slide 70
Slide 70 text
--------------------------------------------------- | 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
Slide 71
Slide 71 text
Possible problems Repeating Work Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 72
Slide 72 text
--------------------------------------------------- | 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
Slide 73
Slide 73 text
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
Slide 74
Slide 74 text
---------------------------------------------------- | 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
Slide 75
Slide 75 text
Possible problems Reading Many Rows Returning Few Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 76
Slide 76 text
------------------------------------------------- | 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
Slide 77
Slide 77 text
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
Slide 78
Slide 78 text
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
Slide 79
Slide 79 text
------------------------------------------------------------- | 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
Slide 80
Slide 80 text
Possible problems Unrealistic Requirements Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
Slide 81
Slide 81 text
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
Slide 82
Slide 82 text
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
Slide 83
Slide 83 text
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
Slide 84
Slide 84 text
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
Slide 85
Slide 85 text
#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