 Explain Plan [message #538334] |
Thu, 05 January 2012 05:45  |
pokhraj_d
Messages: 117
Registered: December 2007
|
Senior Member |
|
|
Hi All,
I ran sql tuning advisor and the advisor produce the below explain plan. What I am not able to understand the predicate information section. What is meant for filter or access at line 9,15,17,18?
==================
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 188 | 117G (6)|999:59:59 |
| 1 | SORT UNIQUE | | 1 | 188 | 117G (6)|999:59:59 |
| 2 | TABLE ACCESS BY INDEX ROWID | PS_EX_SH_PST_TAO11 | 1 | 64 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 188 | 117G (6)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 14G| 1617G| 96G (7)|999:59:59 |
| 5 | MERGE JOIN CARTESIAN | | 17G| 1484G| 36M (10)| 69:16:13 |
| 6 | MERGE JOIN CARTESIAN | | 47M| 1548M| 131K (7)| 00:15:10 |
| 7 | MERGE JOIN CARTESIAN | | 463K| 9961K| 1816 (3)| 00:00:13 |
| 8 | TABLE ACCESS BY INDEX ROWID| PS_EX_DSTACT_TAO11 | 6719 | 40314 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | PS_EX_DSTACT_TAO11 | 1 | | 2 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 69 | 1104 | 1814 (3)| 00:00:13 |
| 11 | TABLE ACCESS FULL | PS_BUS_UNIT_TBL_EX | 69 | 1104 | 0 (0)| 00:00:01 |
| 12 | BUFFER SORT | | 103 | 1236 | 131K (7)| 00:15:10 |
| 13 | TABLE ACCESS FULL | PS_BUS_UNIT_TBL_GL | 103 | 1236 | 0 (0)| 00:00:01 |
| 14 | BUFFER SORT | | 359 | 21181 | 36M (10)| 69:16:13 |
|* 15 | TABLE ACCESS FULL | PS_EX_SH_DST_TAO11 | 359 | 21181 | 1 (0)| 00:00:01 |
| 16 | BUFFER SORT | | 1 | 31 | 96G (7)|999:59:59 |
|* 17 | TABLE ACCESS FULL | PS_EXBUSET2_TAO11 | 1 | 31 | 6 (17)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PSAEX_SH_PST_TAO11 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("D"."PROCESS_INSTANCE"=6788383 AND "D"."DST_ACCT_TYPE"='EXA')
filter("D"."DST_ACCT_TYPE"='EXA')
15 - filter("A"."PROCESS_INSTANCE"=6788383)
17 - filter("G"."SETID"='GLOBL' AND "G"."PROCESS_INSTANCE"=6788383)
18 - access("B"."PROCESS_INSTANCE"=6788383 AND "B"."SHEET_ID"="A"."SHEET_ID" AND
"B"."LINE_NBR"="A"."LINE_NBR" AND "A"."BUSINESS_UNIT_GL"="B"."BUSINESS_UNIT_GL" AND
"D"."DST_CNTRL_ID"="B"."DST_CNTRL_ID" AND "B"."PYMNT_STATUS"='N' AND "B"."CANCEL_ACTION"='N' AND
"B"."PYMNT_SELCT_STATUS"='P' AND "B"."POST_STATUS_AP"='U' AND "B"."GROSSUP"='N')
filter("B"."PYMNT_STATUS"='N' AND "B"."CANCEL_ACTION"='N' AND
"B"."PYMNT_SELCT_STATUS"='P' AND "B"."POST_STATUS_AP"='U' AND "B"."GROSSUP"='N' AND
"D"."BUSINESS_UNIT_GL"="B"."BUSINESS_UNIT_GL" AND "D"."DST_CNTRL_ID"="B"."DST_CNTRL_ID" AND
"C"."BUSINESS_UNIT"="B"."BUSINESS_UNIT_GL" AND "E"."BUSINESS_UNIT_GL"="B"."BUSINESS_UNIT_GL" AND
"G"."BUSINESS_UNIT_GL"="B"."BUSINESS_UNIT_GL" AND "A"."BUSINESS_UNIT_GL"="B"."BUSINESS_UNIT_GL")
|
|
|
|
| Re: Explain Plan [message #538337 is a reply to message #538334] |
Thu, 05 January 2012 05:49  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
It's your where clauses/join conditions, basically.
mkr02@ORA11GMK> explain plan for
2 select * from dual where dummy='X'
3 /
Explained.
Elapsed: 00:00:00.20
mkr02@ORA11GMK> @xplan
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DUMMY"='X')
13 rows selected.
Elapsed: 00:00:00.50
So in the simplistic example above, access table dual and filter records per the criteria.
i.e. for all records, check the column "DUMMY" and discard anything which is not 'X'
[Updated on: Thu, 05 January 2012 05:50] Report message to a moderator |
|
|
|
| Re: Explain Plan [message #538339 is a reply to message #538337] |
Thu, 05 January 2012 05:59 |
pokhraj_d
Messages: 117
Registered: December 2007
|
Senior Member |
|
|
Awesome....
Just one point.
At the example at line 9 I need to filter only ("D"."DST_ACCT_TYPE"='EXA').
Am I correct?
9 - access("D"."PROCESS_INSTANCE"=6788383 AND "D"."DST_ACCT_TYPE"='EXA')
filter("D"."DST_ACCT_TYPE"='EXA')
|
|
|
|
| Re: Explain Plan [message #538340 is a reply to message #538337] |
Thu, 05 January 2012 06:05 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Step 18 in your plan has both FILTER and ACCESS predicate information. That step is an index range scan. What is happening is that it is SCANNING the index and reading ONLY those rows from the index that match the ACCESS predicates. Every index entry that matches the ACCESS predicates is read, but then the FILTER predicates are processed; some rows will be kept and some will be discarded.
Every row in the index will fall into one of the following categories:
A - Matches the ACCESS predicates and the FILTER predicates
B - Matches the ACCESS predicates but not the FILTER predicates
C - Does not match the ACCESS predicates
Step 18 of the plan READS every row in category A and B, but only RETURNS the rows in category A.
In this way, an Index Range Scan that RETURNS only a few rows can appear really efficient, but it is really processing (and discarding) perhaps thousands or millions more.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
