Share
Embed

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