Reading an execution plan puzzle
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 17 Feb 2008 16:14:56 +0100
Hello Listers,
Recently I have got puzzled when I tried to read the following execution plan .
The SQL was:
explain plan for
SELECT /*+ leading(A C@subq1) index(A SUPPLIER_SCHEDULE_UK)
opt_param('_or_expand_nvl_predicate', 'FALSE') */ OBJID,
OBJVERSION
FROM IFSAPP.SUPPLIER_SCHEDULE A
WHERE VENDOR_NO = NVL(:B5 ,VENDOR_NO) AND
CONTRACT = NVL(:B4 , CONTRACT) AND
PART_NO = NVL(:B3 , PART_NO) AND
NVL(AGREEMENT_ID, '@') = NVL(:B2 , NVL(AGREEMENT_ID, '@')) AND
SUPP_SCHEDULE_TYPE_DB = NVL(:B1 , SUPP_SCHEDULE_TYPE_DB) AND
---OBJSTATE IN ('Created', 'Out Of Tolerance', 'Tol Check In Process')
OBJSTATE IN ('Superceded')
AND
CONTRACT IN
(SELECT IFSAPP.USER_ALLOWED_SITE_API.AUTHORIZED(CONTRACT)
FROM IFSAPP.USER_ALLOWED_SITE
)
AND
EXISTS
(SELECT 1
FROM IFSAPP.SUPP_SCHED_AGREEMENT_PART B
WHERE A.VENDOR_NO=B.VENDOR_NO AND
A.CONTRACT=B.CONTRACT AND
A.AGREEMENT_ID=B.AGREEMENT_ID AND
A.PART_NO=B.PART_NO AND
NVL(:B6 , B.APPROVER_ID) = B.APPROVER_ID
) AND
A.SCHEDULE_NO =
(
select /*+ qb_name(subq1) */ MAX(C.SCHEDULE_NO)
FROM IFSAPP.SUPPLIER_SCHEDULE C
where
C.VENDOR_NO = A.VENDOR_NO AND
C.CONTRACT = A.CONTRACT AND
C.PART_NO = A.PART_NO AND
C.SUPP_SCHEDULE_TYPE_DB = A.SUPP_SCHEDULE_TYPE_DB
and rownum =1
)
ORDER BY
VENDOR_NO,
CONTRACT,
PART_NO,
SUPP_SCHEDULE_TYPE_DB DESC,
SCHEDULE_NO ;
Output of "explain plan for" was:
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 110 | 609 (5)| 00:00:06 |
| 1 | SORT ORDER BY | |
1 | 110 | 609 (5)| 00:00:06 |
| 2 | NESTED LOOPS SEMI | |
1 | 110 | 603 (5)| 00:00:06 |
|* 3 | TABLE ACCESS BY INDEX ROWID | SUPPLIER_SCHEDULE_TAB |
1 | 82 | 602 (5)| 00:00:06 |
|* 4 | INDEX FULL SCAN | SUPPLIER_SCHEDULE_UK |
1 | | 601 (5)| 00:00:06 |
|* 5 | INDEX FAST FULL SCAN | USER_ALLOWED_SITE_B_IX |
2 | 12 | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | |
1 | 39 | | |
| 7 | FIRST ROW | |
1 | 39 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN (MIN/MAX)| SUPPLIER_SCHEDULE_UK |
1 | 39 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | SUPP_SCHED_AGREEMENT_PART_TAB |
1 | 28 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SUPP_SCHED_AGREEMENT_PART_PK |
1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ROWSTATE"='Superceded' AND
NVL("AGREEMENT_ID",'@')=NVL(:B2,NVL("AGREEMENT_ID",'@')))
4 - filter("PART_NO"=NVL(:B3,"PART_NO") AND
"SUPP_SCHEDULE_TYPE"=NVL(:B1,"SUPP_SCHEDULE_TYPE") AND
"CONTRACT"=NVL(:B4,"CONTRACT") AND
"VENDOR_NO"=NVL(:B5,"VENDOR_NO") AND EXISTS (SELECT /*+ */ 0 FROM
IFSAPP."USER_ALLOWED_SITE_TAB" "USER_ALLOWED_SITE_TAB" WHERE
"USER_ALLOWED_SITE_API"."AUTHORIZED"("CONTRACT")=:B1) AND
"SCHEDULE_NO"= (SELECT /*+ QB_NAME ("SUBQ1")*/
MAX("SCHEDULE_NO") FROM IFSAPP."SUPPLIER_SCHEDULE_TAB"
"SUPPLIER_SCHEDULE_TAB" WHERE
"SUPP_SCHEDULE_TYPE"=:B2 AND "VENDOR_NO"=:B3 AND "CONTRACT"=:B4
AND "PART_NO"=:B5))
5 - filter("USER_ALLOWED_SITE_API"."AUTHORIZED"("CONTRACT")=:B1)
8 - access("PART_NO"=:B1 AND "CONTRACT"=:B2 AND "VENDOR_NO"=:B3 AND
"SUPP_SCHEDULE_TYPE"=:B4)
9 - filter("APPROVER_ID"=NVL(:B6,"APPROVER_ID"))
10 - access("AGREEMENT_ID"="AGREEMENT_ID" AND "VENDOR_NO"="VENDOR_NO" AND
"CONTRACT"="CONTRACT" AND
"PART_NO"="PART_NO")
I could not interpret the right sequence of steps 4,5 and 6.
After tracing the SQL with event 10046 I have got the following execution plan
(from the trace file):
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=19954 pr=0 pw=0 time=343900 us)
0 NESTED LOOPS SEMI (cr=19954 pr=0 pw=0 time=343866 us)
0 TABLE ACCESS BY INDEX ROWID SUPPLIER_SCHEDULE_TAB (cr=19954 pr=0
pw=0 time=343841 us)
5022 INDEX FULL SCAN SUPPLIER_SCHEDULE_UK (cr=15701 pr=0 pw=0
time=322874 us)(object id 198167)
1 INDEX FAST FULL SCAN USER_ALLOWED_SITE_B_IX (cr=12 pr=0 pw=0
time=1368 us)(object id 9690)
5022 SORT AGGREGATE (cr=15066 pr=0 pw=0 time=112146 us)
5022 COUNT STOPKEY (cr=15066 pr=0 pw=0 time=93179 us)
5022 FIRST ROW (cr=15066 pr=0 pw=0 time=75120 us)
5022 INDEX RANGE SCAN (MIN/MAX) SUPPLIER_SCHEDULE_UK (cr=15066 pr=0
pw=0 time=62607 us)(object id 198167)
0 INDEX RANGE SCAN SUPP_SCHED_AGREEMENT_PART_UK (cr=0 pr=0 pw=0 time=0
us)(object id 198165)
Clearly, here the first step was step 4 (INDEX FULL SCAN SUPPLIER_SCHEDULE_UK
), which is the
Parent step of INDEX FAST FULL SCAN USER_ALLOWED_SITE_B_IX and SORT AGGREGATE
steps .
Afterwards I have changed slightly the sql statement , using the full/aliased
object notation of CONTACT column
Of IFSAPP.SUPPLIER_SCHEDULE A table (here I am providing only the changed lines:
"CONTRACT IN" -> "A.CONTRACT IN"
"IFSAPP.USER_ALLOWED_SITE_API.AUTHORIZED(CONTRACT)" ->
"IFSAPP.USER_ALLOWED_SITE_API.AUTHORIZED(A.CONTRACT)"):
...
A.CONTRACT IN
(SELECT /*+ qb_name(subq2) */
IFSAPP.USER_ALLOWED_SITE_API.AUTHORIZED(A.CONTRACT)
FROM IFSAPP.USER_ALLOWED_SITE S
)
AND
...
The "new" execution plan is now:
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 110 | 661 (5)| 00:00:06 |
| 1 | SORT ORDER BY | |
1 | 110 | 661 (5)| 00:00:06 |
| 2 | NESTED LOOPS SEMI | |
1 | 110 | 655 (5)| 00:00:06 |
|* 3 | TABLE ACCESS BY INDEX ROWID | SUPPLIER_SCHEDULE_TAB |
1 | 82 | 654 (5)| 00:00:06 |
|* 4 | INDEX FULL SCAN | SUPPLIER_SCHEDULE_UK |
1 | | 653 (5)| 00:00:06 |
|* 5 | FILTER | |
| | | |
| 6 | INDEX FAST FULL SCAN | USER_ALLOWED_SITE_B_IX |
1 | | 2 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | |
1 | 39 | | |
|* 8 | COUNT STOPKEY | |
| | | |
| 9 | FIRST ROW | |
1 | 39 | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN (MIN/MAX)| SUPPLIER_SCHEDULE_UK |
1 | 39 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | SUPP_SCHED_AGREEMENT_PART_UK |
1 | 28 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
The only difference is the FILTER step between INDEX FULL SCAN on
SUPPLIER_SCHEDULE_UK and
INDEX FAST FULL SCAN on USER_ALLOWED_SITE_B_IX, but now the execution plan is
(at least for me)
Much more readable.
My questions are :
1) Should I always trace (with event 10046 or 10053) the SQL to get the "full"
(with all the steps) execution plan?
2) Is there any systematic "approach" (apart from trial & error) to reveal the
hidden (the this case FLTER) steps ?
3) Why is "explain plan for" not able to ident properly parent/child steps ?
Perhaps there is a
Bug related to LEVEL pseudo variable when using CONNECT BY ?
I am awaiting your comments impatiently ;)
Best Regards. Milen
P.S. There is no difference in the performance of both "versions" of this SQL
--
//www.freelists.org/webpage/oracle-l
Other related posts:
