Home » RDBMS Server » Performance Tuning » Order of steps in execution path and order of predicates in the Plan (Oracle 10.2.0.4)
Order of steps in execution path and order of predicates in the Plan [message #548231] Tue, 20 March 2012 23:34 Go to next message
orapratap
Messages: 113
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?

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
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 Go to previous messageGo to next message
John Watson
Messages: 4862
Registered: January 2010
Location: Global Village
Senior Member
Step 8 is first.
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 Go to previous messageGo to next message
orapratap
Messages: 113
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
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 Go to previous message
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:
  


Current Time: Sat Dec 20 10:50:00 CST 2014

Total time taken to generate the page: 0.12602 seconds