Presentation interpreting execution plans for sql statements

<Insert Picture Here>
Explaining the Explain Plan:
Interpreting Execution Plans for SQL Statements
Maria Colgan
Senior Principal Product Manager

3
Agenda
•  What is an execution plan
•  How to generate a plan
•  What is a good plan for the optimizer
•  Understanding execution plans
–  Cardinality
–  Access paths
–  Join type
–  Join order
•  Execution plan examples

4
What is an Execution plan?
•  Execution plans show the detailed steps necessary to
execute a SQL statement
•  These steps are expressed as a set of database
operators that consumes and produces rows
•  The order of the operators and their implementation is
decided by the optimizer using a combination of query
transformations and physical optimization techniques
•  The display is commonly shown in a tabular format,
but a plan is in fact tree-shaped

5
What is an Execution plan?
Query: 
SELECT prod_category, avg(amount_sold)
FROM sales s, products p
WHERE p.prod_id = s.prod_id
GROUP BY prod_category;
Tabular representation of plan
-----------------------------------------------------------
Id Operation Name
-----------------------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
---------------------------------------------------------- 
GROUP BY
HASH JOIN
TABLE ACCESS
SALES
TABLE ACCESS
PRODUCTS
Tree-shaped representation of plan

6
Agenda
–  Cardinality
–  Access paths
–  Join type
–  Join order

7
How to get an Execution Plan
Two methods for looking at the execution plan
1. EXPLAIN PLAN command
–  Displays an execution plan for a SQL statement without actually
executing the statement
2. V$SQL_PLAN
–  A dictionary view introduced in Oracle 9i that shows the execution
plan for a SQL statement that has been compiled into a cursor in
the cursor cache
Either way use DBMS_XPLAN package to display plans
Under certain conditions the plan shown with EXPLAIN PLAN
can be different from the plan shown using V$SQL_PLAN

8
How to get an Execution Plan Example 1
•  EXPLAIN PLAN command & dbms_xplan.display
function
SQL> EXPLAIN PLAN FOR SELECT prod_name, avg(amount_sold)
GROUP BY prod_name;
SQL> SELECT plan_table_output
FROM table(dbms_xplan.display('plan_table',null,'basic')); 
       ------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
-------------------------------------------

9
How to get an Execution Plan Example 2
•  Generate & display execution plan for last SQL
stmts executed in a session
SQL>SELECT prod_category, avg(amount_sold)
GROUP BY prod_category;
SQL> SELECT plan_table_output
FROM table(dbms_xplan.display_cursor(null,null,'basic')); 
       ------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
-------------------------------------------

10
Agenda
–  Cardinality
–  Access paths
–  Join type
–  Join order

11
What’s a Good Plan for the Optimizer?
The Optimizer has two different goals
•  Serial execution: It’s all about cost
–  The cheaper, the better
•  Parallel execution: it’s all about performance
–  The faster, the better
Two fundamental questions:
•  What is cost?
•  What is performance?

12
What is Cost?
•  A magically number the optimizer makes up?
•  Resources required to execute a SQL statement?
•  Result of complex calculations?
•  Estimate of how long it will take to execute a statement?
Actual Definition
•  Cost represents units of work or resources used
•  Optimizer uses CPU & IO as units of work
•  Cost is an estimate of the amount of CPU and the number of disk
I/Os, used to perform an operation
Cost is an internal Oracle measurement

13
What is performance?
•  Getting as many queries completed as possible?
•  Getting fastest possible elapsed time using the fewest
resources?
•  Getting the best concurrency rate?
Actual Definition
•  Performance is fastest possible response time for a query
•  Goal is to complete the query as quickly as possible
•  Optimizer does not focus on resources needed to execute the plan

14
Agenda
–  Cardinality
–  Access paths
–  Join type
–  Join order

15
Cardinality
What is it?
•  Estimate of number rows that will be returned by each operation
How does the Optimizer Determine it?
•  Cardinality for a single column equality predicate = total num of rows
num of distinct values
–  For example: A table has 100 rows, a column has 10 distinct values
=> cardinality=10 rows
•  More complicated predicates have more complicated cardinality
calculation
Why should you care?
•  Influences everything! Access method, Join type, Join Order etc

16
How to Identify Cardinality in an Execution Plan
Cardinality the estimated
# of rows returned
Determine correct cardinality using a SELECT COUNT(*) from each table applying
any WHERE Clause predicates belonging to that table

17
Check Cardinality Estimates
SELECT /*+ gather_plan_statistics */ p.prod_name, SUM(s.quantity_sold)
WHERE s.prod_id =p.prod_id GROUP By p.prod_name ;
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
Compare the estimated number of rows returned for each operation in the
plan to actual rows returned

18
Check Cardinality using SQL Monitor
SQL Monitor is the easiest way to compare the estimated number of rows returned for each
operation in a parallel plan to actual rows returned

19
Solutions to incorrect Cardinality estimates
Cause Solution
Stale or missing statistics DBMS_STATS
Data Skew Create a histogram
Multiple single column predicates
on a table
Create a column group using
DBMS_STATS.CREATE_EXTENDED_STATS
Function wrapped column Create statistics on the funct wrapped
column using
DBMS_STATS.CREATE_EXTENDED_STATS
Multiple columns used in a join Create a column group on join columns using
DBMS_STATS.CREATE_EXTENDED_STAT
Complicated expression
containing columns from multiple
tables
Use dynamic sampling level 4 or higher

20
Agenda
–  Cardinality
–  Access paths
–  Join type
–  Join order

21
Access Paths – Getting the data
Access Path Explanation
Full table scan Reads all rows from table & filters out those that do not meet the where clause
predicates. Used when no index, DOP set etc
Table access by Rowid Rowid specifies the datafile & data block containing the row and the location of
the row in that block. Used if rowid supplied by index or in where clause
Index unique scan Only one row will be returned. Used when stmt contains a UNIQUE or a
PRIMARY KEY constraint that guarantees that only a single row is accessed
Index range scan Accesses adjacent index entries returns ROWID values Used with equality on
non-unique indexes or range predicate on unique index (<.>, between etc)
Index skip scan Skips the leading edge of the index & uses the rest Advantageous if there are
few distinct values in the leading column and many distinct values in the non-
leading column
Full index scan Processes all leaf blocks of an index, but only enough branch blocks to find 1st
leaf block. Used when all necessary columns are in index & order by clause
matches index struct or if sort merge join is done
Fast full index scan Scans all blocks in index used to replace a FTS when all necessary columns
are in the index. Using multi-block IO & can going parallel
Index joins Hash join of several indexes that together contain all the table columns that are
referenced in the query. Wont eliminate a sort operation
Bitmap indexes uses a bitmap for key values and a mapping function that converts each bit
position to a rowid. Can efficiently merge indexes that correspond to several
conditions in a WHERE clause

22
How to Identify Access Path in an Execution Plan
If the wrong access method is being used check cardinality, join order…
Look in Operation section to
see how an object is being
accessed

23
Access Path examples
A table countries contains 10K rows & has a primary key on
country_id – What plan would you expect for these queries?
Select country_id, name from countries
where country_id in ('AU','FR','IE‘);
Select country_id, name from countries
where country_id between 'AU' and 'IE';

24
Access Path examples
A table countries contains 10K rows & has a primary key on
country_id – What plan would you expect for these queries?
Select country_id, name from countries where name='USA';

25
Common Access Path issues
Issue Cause
Uses a table scan instead of index DOP on table but not index, value of MBRC
Picks wrong index Stale or missing statistics
Cost of full index access is cheaper than
index look up followed by table access
Picks index that matches most # of column

26
Agenda
–  Cardinality
–  Access paths
–  Join type
–  Join order

27
Join Types
Join Type Explanation
Nested Loops joins For every row in the outer table, Oracle accesses all the rows in the
inner table Useful when joining small subsets of data and there is
an efficient way to access the second table (index look up)
Hash Joins The smaller of two tables is scan and resulting rows are used to build
a hash table on the join key in memory. The larger table is then
scan, join column of the resulting rows are hashed and the values
used to probing the hash table to find the matching rows. Useful
for larger tables & if equality pred
Sort Merge joins Consists of two steps:
1.  Both the inputs are sorted on the join key.
2.  The sorted lists are merged together.
Useful when the join condition between two tables is an inequality
condition or one of the table is already ordered e.g. index access
Cartesian Joins Joins every row from one data source with every row from the other
data source, creating the Cartesian Product of the two sets. Only
good if tables are very small. Only choice if there is no join
condition specified in query
Outer Joins Returns all rows that satisfy the join condition and also returns all of
the rows from the table without the (+) for which no rows from the
other table satisfy the join condition

28
How to Identify Join Type in an Execution Plan
If wrong join type is used check stmt is written correctly & cardinality estimates
Look in the Operation section
to check the right join type is
used

29
Join Type Example 1
What Join type should be use for this Query?
SELECT e.last_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE d.departments_name IN ('Marketing‘,'Sales')
AND e.department_id=d.department_id;
Employees has 107 rows
Departments has 27 rows
Foreign key relationship between Employees and Departments on dept_id

30
Join Type Example 2
SELECT o.customer_id, l.unit_price * l.quantity
FROM oe.orders o ,oe.order_items l
WHERE l.order_id = o.order_id;
Orders has 105 rows
Order Items has 665 rows

31
Join Type Example 3
SELECT o.order_id,0.order_date,e.name
FROM oe.orders o , hr.employees e;
Orders has 105 rows

32
Join Type Example 3
Cartesian product not always bad
•  Chosen when the number of rows being joined is low
•  Commonly used to join multiple small dimensions to
one large fact table
CUSTOMERSPRODUCTS
PROMOTIONS
s.prod_id=p.prod_id s.cust_id=c.cust_id
SALES
s.promo_id=c.promo_id

33
Join Type Example 3
Cartesian product
•  By joining the three small dimension tables together
first we minimize the number of rows being carried
through the joins

34
Join Type Example 4
SELECT d.department_id,e.emp_id
FROM hr.employees e FULL OUTER JOIN hr.departments d
ON e.department_id = d.department_id
ORDER BY d.department_id;
Departments has 27 rows
Foreign key relationship between Employees and Departments on dept_id

35
What causes the wrong Join Type to be selected
Issue Cause
Nested loop
selected instead
of hash join
Cardinality estimate on the left side is under
estimated triggers Nested loop to be selected
Hash join selected
instead of nested
loop
In case of a hash join the Optimizer doesn’t taken
into consideration the benefit of caching. Rows on
the left come in a clustered fashion or (ordered)
so the probe into the right is less expensive
Cartesian Joins Cardinality underestimation

36
Agenda
–  Cardinality
–  Access paths
–  Join type
–  Join order

37
Join Orders
The order in which the tables are join in a multi table stmt
•  Ideally start with the table that will eliminate the most rows
•  Strongly affected by the access paths available
Some basic rules
•  Joins guaranteed to produce at most one row always go first
–  Joins between two row sources that have only one row each
•  When outer joins are used the table with the outer join
operator must come after the other table in the predicate
•  If view merging is not possible all tables in the view will be
joined before joining to the tables outside the view

38
How to Identify Join order in an Execution Plan
If the join order is not correct, check the statistics, cardinality & access methods
1
2
3
Want to start with the table that
reduce the result set the most
4
5

39
Finding the Join Order for complex SQL statement
The leading hint tells
you the join order
•  It can be hard to determine Join Order for Complex SQL
statements but it is easily visible in the outline data of plan
SELECT *
FROM
table(dbms_xplan.display_cursor(FORMAT=>’TYPICAL +outline’);

40
What causes the wrong Join Order
Causes
Incorrect single table cardinality estimates
Incorrect join cardinality estimates

41
Agenda
–  Cardinality
–  Access paths
–  Join type
–  Join order

42
Example SQL Statement
•  Find all the employees who make as much or more than
their manager
SELECT e1.last_name, e1.job_title, e1.total_comp
FROM (SELECT e.manager_id, e.last_name, j.job_title,
(e.salary+e.commission_pct) total_comp
FROM employees e, jobs j, departments d
WHERE d.department_name = 'Sales'
AND e.department_id = d.department_id
AND e.job_id = j.job_id ) e1,
(SELECT e.employee_id, (e.salary+e.commission_pct) tc
FROM employees e, departments d
WHERE d.department_name = ‘Sales'
AND e.department_id = d.department_id ) e2
WHERE e1.manager_id = e2.employee_id
AND e1.total_comp >= e2.tc;

43
Is it a good Execution plan?
Means no stats gathered
strong indicator this won’t be
best possible plan
1. Is the estimated number of
rows being returned
accurate?
2. Are the cardinality
estimates accurate?
3.Are the access
method correct?

44
Example Cont’d Execution plan
5. Is the join order correct? Is the table that
eliminates the most rows accessed first?
4. Are the right join types being used?
1
2
3
4
5

45
What does the Plan Tree look like?
MERGE JOIN
CARTESIAN
TABLE ACCESS
EMPLOYEES
TABLE ACCESS
DEPARTMENT
TABLE ACCESS
EMPLOYEES
HASH JOIN
INDEX UNIQUE SCAN -
TABLE ACCESS
DEPARTMENT
NESTED LOOP INDEX UNIQUE SCAN -
TABLE ACCESS JOBS
NESTED LOOP

46
Example Cont’d Execution plan - Solution
2. Cardinalities are correct
and with each join number of
rows reduced
1. Only 1 row is actually returned and
the cost is 4 lower now
4. Join types have
changed to be all NL
3. Access methods
have changed for
some tables
1
2
3
5. The join
order has
changed
5
4

47
What does the Plan Tree look like?
TABLE ACCESS
DEPARTMENT
NESTED LOOP
INDEX UNIQUE SCAN -
TABLE ACCESS
DEPARTMENT
NESTED LOOP
INDEX UNIQUE SCAN -
TABLE ACCESS JOBS
NESTED LOOP
NESTED LOOP
INDEX RANGE SCAN -
TABLE ACCESS
EMPLOYEES
INDEX RANGE SCAN -
TABLE ACCESS
EMPLOYEES

49
The preceding is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into any
contract. It is not a commitment to deliver any
material, code, or functionality, and should not be
relied upon in making purchasing decisions.
The development, release, and timing of any
features or functionality described for Oracle’s
products remains at the sole discretion of Oracle.

Presentation interpreting execution plans for sql statements

Recommended

More Related Content

What's hot (20)

Viewers also liked (11)

Similar to Presentation interpreting execution plans for sql statements (20)

More from xKinAnx (20)

Recently uploaded (20)

Presentation interpreting execution plans for sql statements