| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> How to read EXECUTION PLAN (defining guideline)?
>> The statement in the performance tuning guide is wrong,
>> and the suggestion it makes has only ever been a rough
>> guideline.
Jonathan (ALL),
Can you please help to define the rule for execution plan reading?
PS Or the key as always “it’s depends”?
PPS Why Oracle can’t add step execution number to each line in PLAN_TABLE,
I wonder? It will make live easer for everyone.
Jurijs
+371 9268222 (+2 GMT)
-----oracle-l-bounce_at_freelists.org wrote: -----
To: <chupit_at_gmail.com>, <oracle-l_at_freelists.org>
From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: oracle-l-bounce_at_freelists.org
Date: 01/04/2005 11:44PM
Subject: Re: Mysterious FILTER operation ;)
The statement in the performance tuning guide is wrong, and the suggestion it makes has only ever been a rough guideline.
This particular type of FILTER was present in (at least) 7.2, (this specific FILTER appeared only in 9i, it doesn't appear in 8i).
For a more traditional example of a plan where the statement is wrong, consider a simple nested loop:
nested loop
table t1 full
table t2 by rowid
index unique t2_pk (unique)
The first action is the start of the scan of table T1, it is NOT the rightmost action - which is the index access into t2_pk.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004
This is very interesting observation that I would like to discuss.
According to the "Performance Tuning Guide", for example, the
execution order begins with the line that is the furthest indented to
the right
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/optimops.htm#73843
But this experiment (v_p1>v_p2) ) proves that real execution order can be different. If we have LIO equal to 0, than INDEX RANGE SCAN was not performed and FILTER was performed before RANGE SCAN, this is also proved by sql_trace execution plan:
Rows Row Source Operation
------- ---------------------------------------------------1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=57 us) 0 FILTER (cr=0 pr=0 pw=0 time=8 us)
As we can see from time column to FILTER step consumed 8 us but INDEX RANGE SCAN consumed 0 us (was not executed).
Can somebody share some thoughts about real execution path of the statement?
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 06 2005 - 08:10:40 CST
 |
 |