Explain Plan Explained Jonathan Lewis June 2000 jl computer Consultancy, uk



Download 31.59 Kb.
Date28.05.2018
Size31.59 Kb.
#50910

© Jonathan Lewis page.

Explain Plan - Explained

Jonathan Lewis © June 2000

JL Computer Consultancy, UK

Summary


Perhaps the greatest benefit of SQL lies in the fact that the programmer describes what data is needed, but doesn't have to worry about the exact steps needed to acquire it. Given an SQL statement, the database engine will work out a path to find the data without further intervention.

Apart from releasing the programmer from the chore of coding laborious amounts of detail into their program (open index file, find key value, open data file, go to specific location etc.) this means that the infrastructure of the database can be modified without first revisiting and rewriting access code.

Occasionally, of course, the database engine takes too long to return the results of a query, or works very inefficiently, and it is necessary to find out what access path it is using, and make sure that it uses a more efficient one. Oracle supplies the EXPLAIN PLAN facility to tell you about the path Oracle will use for a query, and the cost information that Oracle has used to decide that that path is the optimal path. This article is an introduction to interpreting the output of EXPLAIN PLAN.

What is an Execution Plan


Consider the query:

select


ep1.v1, ep2.v2, ep3.n2

from ep3, ep2, ep1

where

(

ep3.v1 = 'Tyne Tees'



or ep3.v2 = 'ASDA'

)

and ep2.n1 = ep3.n1



and ep2.n2 = ep3.n3

and ep1.n1 = ep2.n1

;
Oracle clearly has to visit the three tables EP1, EP2 and EP3, but which does it visit first, what method does it use to get into the table, and what mechanisms does it use to connect rows from different tables. In theory we do not need to know; in practice we may want to work out (before building the database) if there is likely to be an efficient way of answering this query; we may have a problem with performance revolving around this query, and suspect that Oracle is doing the job in a sub-optimal way. We can get Oracle to describe how it will handle this query, using one of 4 methods to acquire the execution plan.

Where do you find Execution Plans


The safest place to find execution plans is in trace file, generated by issuing the statement:

alter session set sql_trace true;


Because this is a record of how Oracle actually performed as it executed the statement, it HAS to be correct. Unfortunately the output is a little sparse, and hard to read. However, it does tell us which tables were used, the methods applied, and the quantity of data returned by each step. It omits index names (though it supplies object numbers), and can be confusing since the count of rows returned is actually summed across all rows. Sometimes, it just doesn't appear anyway.

id=1 cnt=301 pid=0 pos=0 obj=0 op='HASH JOIN '

id=2 cnt=200 pid=1 pos=1 obj=21461 op='TABLE ACCESS FULL EP1 '

id=3 cnt=303 pid=1 pos=2 obj=0 op='NESTED LOOPS '

id=4 cnt=304 pid=3 pos=1 obj=21465 op='TABLE ACCESS BY INDEX ROWID EP3 '

id=5 cnt=304 pid=4 pos=1 obj=0 op='BITMAP CONVERSION TO ROWIDS '

id=6 cnt=2 pid=5 pos=1 obj=0 op='BITMAP OR '

id=7 cnt=2 pid=6 pos=1 obj=21466 op='BITMAP INDEX SINGLE VALUE '

id=8 cnt=2 pid=6 pos=2 obj=21467 op='BITMAP INDEX SINGLE VALUE '

id=9 cnt=303 pid=3 pos=2 obj=21463 op='TABLE ACCESS BY INDEX ROWID EP2 '

id=10 cnt=606 pid=9 pos=1 obj=21464 op='INDEX UNIQUE SCAN '

To get a more readable output from the flat trace file, you can use the TKPROF facility, with a command line like:

tkprof ora_23154.trc ora_23154.prf explain=jpl1/jpl1
Apart from producing execution plans for each (non-SYS) statement, this will also sum the logical and physical I/O, and CPU and elapsed cost of execution.

call count cpu elapsed disk query current rows

------ ------ -------- -------- -------- -------- --------- --------

Parse 1 0.01 0.01 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 22 0.01 0.03 160 934 16 301

------ ------ ------- --------- -------- ------- --------- ---------

total 24 0.00 0.00 160 934 16 301


Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: HINT: FIRST_ROWS

301 HASH JOIN

200 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EP1'

303 NESTED LOOPS

304 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'EP3'

304 BITMAP CONVERSION (TO ROWIDS)

2 BITMAP OR

2 BITMAP INDEX (SINGLE VALUE) OF 'EP3_BI1'

2 BITMAP INDEX (SINGLE VALUE) OF 'EP3_BI2'

303 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'EP2'

606 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'EP2_PK' (UNIQUE)
In fact, under 8.1.5 (and possibly earlier versions of Oracle) you may find that tkprof produces two plans for the statement. One which is a tidied up, version of the plan in the trace file, and another which is the plan that is appropriate to the user (and row level security policy), and database statistics at the time tkprof was run.

You can see from the plan above the same statistics that were reported in the trace file, but object numbers have been replaced with object names, more precision has been introduced about how the objects have been used (e.g. unique scan of unique index). Also the ID/PID value have been hidden, replaced implicitly by the indentation in the plan.

One possible drawback to using trace files and tkprof is that you HAVE to execute the SQL statement (or at least start executing it) to get the plan. You do have an alternative in SQL*Plus with the AUTOTRACE facility. My usual variant uses the following lead-in so that I don't get awkward line wraps and truncated text for the parallel or remote bits. With this lead-in, AUTOTRACE produces an execution path for every statement you attempt to execute, but SQL*Plus does not run the statement.

set autotrace traceonly explain

column plan_plus_exp format a100

set linesize 130

set trimspool on

set long 20000


Sample output:

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=402 Card=303

Bytes=81204)

1 0 HASH JOIN (Cost=402 Card=303 Bytes=81204)

2 1 TABLE ACCESS (FULL) OF 'EP1' (Cost=5 Card=200 Bytes=7600)

3 1 NESTED LOOPS (Cost=393 Card=303 Bytes=69690)

4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EP3' (Cost=90 Card=303

Bytes=26967)

5 4 BITMAP CONVERSION (TO ROWIDS)

6 5 BITMAP OR

7 6 BITMAP INDEX (SINGLE VALUE) OF 'EP3_BI1'

8 6 BITMAP INDEX (SINGLE VALUE) OF 'EP3_BI2'

9 3 TABLE ACCESS (BY INDEX ROWID) OF 'EP2' (Cost=1 Card=1000

Bytes=141000)

10 9 INDEX (UNIQUE SCAN) OF 'EP2_PK' (UNIQUE)

Other options for AUTOTRACE allow you to execute the statement, and get a report of the performance statistics (such as disk reads, buffer gets, etc.). Optionally you can choose to do this without actually displaying the results of the underlying query.

However, before using AUTOTRACE, you will need to install a 'plan table' by running the script utlxpln.sql which may be found in the directory $ORACLE_HOME/rdbms/admin. Before you can use the statistics option, you will have to connect as SYS and execute a script called plustrce.sql that can be found in the $ORACLE_HOME/sqlplus/admin directory. This script creates the PLUSTRACE role and grants it to the DBA role with admin option. If you are not a DBA, you will then need the PLUSTRACE role granted to your id.

Finally we come to EXPLAIN PLAN. Use the same utlxplan.sql script to create a plan table (I usually create in the SYSTEM schema, and grant access to PUBLIC, with a PUBLIC SYNONYM - I also tend to create an index on (parent_id, id) when using Oracle 7.3 and partition views.

Using explain plan takes two steps - explaining the plan, which means using the command to make Oracle write the execution plan into the plan table, and then displaying the plan by issuing an SQL statement (Oracle 8.1.5 has a couple of good examples in the scripts utlxplp.sql and utlxpls.sql in $ORACLE_HOME/rdbms/admin).

explain plain

set statement_id = userenv('sessionid')

for {your sql statement}


A fairly typical output from the query you then execute against the plan_table would probably be something like:

Id Par Pos Ins Plan

-- --- --- --- ---------------------------------------------------

0 402 SELECT STATEMENT (first_rows) Cost (402,303,81204)

1 0 1 HASH JOIN Cost (402,303,81204)

2 1 1 3 TABLE ACCESS (analyzed) JPL1 EP1 (full)

Cost (5,200,7600)

3 1 2 NESTED LOOPS Cost (393,303,69690)

4 3 1 1 TABLE ACCESS (analyzed) JPL1 EP3 (by index rowid)

Cost (90,303,26967)

5 4 1 BITMAP CONVERSION (to rowids)

6 5 1 BITMAP OR

7 6 1 BITMAP INDEX JPL1 EP3_BI1 (single value)

8 6 2 BITMAP INDEX JPL1 EP3_BI2 (single value)

9 3 2 2 TABLE ACCESS (analyzed) JPL1 EP2 (by index rowid)

Cost (1,1000,141000)

10 9 1 INDEX (analyzed) UNIQUE JPL1 EP2_PK (unique scan)
The question is - what does it all mean ?

Access Methods


The number of different basic methods that Oracle has for acquiring data is extremely small. These are: find an entry in an index using a 'binary chop' method, optionally stepping along the index afterwards; find an entry in an index by examining every entry; find an entry in a table by going to the correct rowid; find an entry in a table by examining every single entry. Examples of this type of activity come in lines like:

BITMAP INDEX JPL1 EP3_BI1 (single value)

INDEX (analyzed) UNIQUE JPL1 EP2_PK (unique scan)

TABLE ACCESS (analyzed) JPL1 EP1 (full)


Each method can produce a set of rows (possibly a very small set containing zero or one rows) which may then be passed on for further processing. Again, the number of options for handling row sets is pretty small: combine two sets of rows, filter one set of rows against another, sort rows, group rows. Examples of this type of activity come in lines like:

HASH JOIN

NESTED LOOPS

BITMAP OR


There are two critical variants in the operations that combine sets of rows - some operations (such as HASH JOIN) require the sets to be built in their entirety before they can be manipulated; other operations (such as NESTED LOOPS) allow a combining operation to start as soon as the first rows become available from the dependent sets.

The difficulty in interpreting execution paths comes from the enormous variety of ways in which access operations and combining operations can be mixed and matched.


Parents and Children


The key item of information that allows you to interpret an execution plan is visible (with different degrees of clarity and labelling) in all but the output of tkprof. Every line in the plan has an ID and a PARENT ID.

Once you have observed this fact, the operation rule is very simple. An operation described in any one row depends upon receiving as its input the output from all its child rows. For example, in the execution plan above -

Operation 6 - BITMAP OR - is the parent to operation 7 (BITMAP INDEX JPL1 EP3_BI1) and operation 8 (BITMAP INDEX JPL1 EP3_BI2), so is dependent upon receiving input from those two operations.

Less trivially, Operation 3 - NESTED LOOP - is the parent to operations 4 and 9, "(TABLE ACCESS (analyzed) JPL1 EP3 (by index rowid)" and "TABLE ACCESS (analyzed) JPL1 EP2 (by index rowid)" respectively. Regardless of what other operation have to take place to generate the two table-row row-sets, Operation 3 can only occur when rows start appearing from operations 4 and 9.



Once you focus on this ID/PARENT ID relationship, it soon becomes apparent that an execution plan can be decomposed stage by stage, so that its interpretation requires you to consider just one or two items at a time.

The Presentation


The presentation will describe in some detail, with live demonstrations, the different types of access path, and row-set combination methods that Oracle uses, and will end by decomposing the sample plan given above.

EOUG Conference: May 2000

Download 31.59 Kb.

Share with your friends:




The database is protected by copyright ©ininet.org 2024
send message

    Main page