OraFAQ Forum: Performance Tuning » Order of steps in execution path and order of predicates in the Plan

Home » RDBMS Server » Performance Tuning » Order of steps in execution path and order of predicates in the Plan (Oracle 10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Order of steps in execution path and order of predicates in the Plan [message #548231]

Tue, 20 March 2012 23:34

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello

Which step in the following plan is the first step of execution
I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"
Is that correct?

In the "Predicate Information (identified by operation id):"
section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Code: [Select all] [Show/ hide]

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |     1 |  2364 |    15  (14)| 00:00:01 |
|*  1 |  FILTER                              |                      |       |       |            |       |
|   2 |   HASH GROUP BY                      |                      |     1 |  2364 |    15  (14)| 00:00:01 |
|   3 |    NESTED LOOPS                      |                      |     1 |  2364 |    14   (8)| 00:00:01 |
|   4 |     NESTED LOOPS                     |                      |     1 |  2313 |    13   (8)| 00:00:01 |
|   5 |      NESTED LOOPS                    |                      |     1 |  2281 |    12   (9)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER             |                      |     1 |  2255 |    11  (10)| 00:00:01 |
|*  7 |        HASH JOIN                     |                      |     1 |   175 |     6  (17)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN             | INDX_2               |    12 |   612 |     2   (0)| 00:00:01 |
|*  9 |         TABLE ACCESS FULL            | PYMNT_DUES           |    43 |  5332 |     3   (0)| 00:00:01 |
|  10 |        VIEW PUSHED PREDICATE         |                      |     1 |  2080 |     5   (0)| 00:00:01 |
|  11 |         NESTED LOOPS                 |                      |     1 |   154 |     5   (0)| 00:00:01 |
|  12 |          NESTED LOOPS                |                      |     1 |   103 |     4   (0)| 00:00:01 |
|* 13 |           TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE     |     1 |    32 |     2   (0)| 00:00:01 |
|* 14 |            INDEX RANGE SCAN          | INDX_BBS_1           |     3 |       |     1   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS BY INDEX ROWID| DAILY_CHECK          |     1 |    71 |     2   (0)| 00:00:01 |
|* 16 |            INDEX RANGE SCAN          | INDX_SEARCH          |     1 |       |     1   (0)| 00:00:01 |
|* 17 |          INDEX RANGE SCAN            | INDX_2               |     1 |    51 |     1   (0)| 00:00:01 |
|* 18 |       INDEX RANGE SCAN               | INDX_IAM_SR_NO       |     1 |    26 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                | INDX_2               |     1 |    32 |     1   (0)| 00:00:01 |
|* 20 |     INDEX RANGE SCAN                 | INDX_2               |     1 |    51 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------

Thanks and Regards
OraPratap

Report message to a moderator

Re: Order of steps in execution path and order of predicates in the Plan [message #548270 is a reply to message #548231]

Wed, 21 March 2012 02:09

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

Step 8 is first.

Report message to a moderator

Re: Order of steps in execution path and order of predicates in the Plan [message #548315 is a reply to message #548270]

Wed, 21 March 2012 06:31

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello John

Many Thanks for your reply

Doesn't step 13 look more indented?

Also please help me on the question no 2 in the post
Quote:

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Thanks and Regards
OraPratap

Report message to a moderator

Re: Order of steps in execution path and order of predicates in the Plan [message #548482 is a reply to message #548231]

Thu, 22 March 2012 07:34

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Have a read of this link. See if it helps you to understand how to interpret an execution plan.

http://blog.tanelpoder.com/2009/04/24/tracing-oracle-sql-plan-execution-with-dtrace/

And for your questions 2 you have not included the predicate section in your post .

Thanks

Raj

[Updated on: Thu, 22 March 2012 07:35]

Report message to a moderator

Previous Topic:	Ugly View Not Cooperating
Next Topic:	On which columns to apply index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 03:55:27 CDT 2025