Home » RDBMS Server » Performance Tuning » Explain Plan (Oracle 10g Hp-UX)
icon1.gif  Explain Plan [message #538334] Thu, 05 January 2012 05:45 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Query taking long time on MVs
Next Topic: HTML AWR Report
Goto Forum:
  


Current Time: Sun Sep 26 00:50:01 CDT 2021