Reading an execution plan puzzle
From: Milen Kulev <makulev_at_gmx.net>
Date: Sun, 17 Feb 2008 16:14:56 +0100
Message-ID: <001101c87177$daf62330$6401a8c0@trivadis.com>
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 |
...
| 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 |
Date: Sun, 17 Feb 2008 16:14:56 +0100
Message-ID: <001101c87177$daf62330$6401a8c0@trivadis.com>
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_at_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 | | | | | |INDEX FAST FULL SCAN on USER_ALLOWED_SITE_B_IX, but now the execution plan is (at least for me) Much more readable.
| 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
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 aBug 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
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Feb 17 2008 - 09:14:56 CST