Explain Plan Explained Jonathan Lewis June 2000 jl computer Consultancy, uk
Download 31.59 Kb.
|
- Navigate this page:
- What is an Execution Plan
- Where do you find Execution Plans
- Access Methods
- Parents and Children
- The Presentation
© Jonathan Lewis page. Explain Plan - Explained Jonathan Lewis © June 2000 JL Computer Consultancy, UK SummaryPerhaps 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.
select
ep1.v1, ep2.v2, ep3.n2 from ep3, ep2, ep1 where (
or ep3.v2 = 'ASDA' ) and ep2.n1 = ep3.n1 and ep2.n2 = ep3.n3 and ep1.n1 = ep2.n1 ;
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
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)
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: -- --- --- --- --------------------------------------------------- 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)
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 ChildrenThe 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 PresentationThe 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:
|