| How to optimize this plan (merged) [message #415996] |
Thu, 30 July 2009 02:12  |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Sir/Madam,
Here is my problem that I want to share with you,
In environment where there is a slowdown I am seeing the below plan. I suspect that the FILTER at line 19 and concatenateion
is the cause of slowdown because in other env1 plan is there is no CONCATENATION. (Ofcourse, data in env1 >> env2)
Using NO_EXPAND hint makes the plan similar to the one in env2. Can we use this hint to avoid the CONCATENATION in the plan?
Please advice.
Please make a note that the sql is a dynamic one.The sql that I talked about in my previous post ( name of object masked).
http://www.orafaq.com/forum/t/148291/0/
Here I am passing only the timeframe (start dt-end dt) to the sql
env1:
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 616 | 71 (5)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 616 | 71 (5)| 00:00:01 |
| 2 | CONCATENATION | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN | | 1 | 308 | 35 (3)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID | EIN_TBL | 1 | 31 | 4 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 208 | 23 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 177 | 19 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 159 | 15 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 76 | 13 (0)| 00:00:01 |
|* 10 | INDEX FAST FULL SCAN | PK_WP | 1 | 55 | 9 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| CSI_TBL | 2 | 42 | 4 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | XIF001CSI_TBL | 2 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | TR_TBL | 1 | 83 | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_P_TR_TBL | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PF_TBL | 1 | 18 | 4 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | XP_TR_TBL2 | 1 | | 2 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | XPKEIN_TBL | 1 | | 3 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | LKP_TBL | 36 | 3600 | 11 (0)| 00:00:01 |
|* 19 | FILTER | | | | | |
|* 20 | HASH JOIN | | 1 | 308 | 35 (3)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | EIN_TBL | 1 | 31 | 4 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 208 | 23 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 177 | 19 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 1 | 159 | 15 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 76 | 13 (0)| 00:00:01 |
|* 26 | INDEX FAST FULL SCAN | PK_WP | 1 | 55 | 9 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID| CSI_TBL | 2 | 42 | 4 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | XIF001CSI_TBL | 2 | | 2 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | TR_TBL | 1 | 83 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_P_TR_TBL | 1 | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | PF_TBL | 1 | 18 | 4 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | XP_TR_TBL2 | 1 | | 2 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | XPKEIN_TBL | 1 | | 3 (0)| 00:00:01 |
|* 34 | TABLE ACCESS FULL | LKP_TBL | 36 | 3600 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Env2
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 259 | 37 (6)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 259 | 37 (6)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN | | 1 | 259 | 36 (3)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | EIN_TBL | 1 | 29 | 3 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 209 | 26 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 180 | 23 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 163 | 21 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 56 | 20 (0)| 00:00:01 |
|* 9 | INDEX FAST FULL SCAN | PK_WP | 1 | 35 | 18 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| CSI_TBL | 1 | 21 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | XIF001CSI_TBL | 1 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | TR_TBL | 1 | 107 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PKP_TR_TBL | 1 | | 0 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | FP_TBL | 1 | 17 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | XFAX_TR_TBL2 | 2 | | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | XPKEIN_TBL | 1 | | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | LKP_TBL | 37 | 1850 | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to optimize this plan (merged) [message #416123 is a reply to message #415996] |
Thu, 30 July 2009 09:57  |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Sir , Can the repeating access path be an issue for slow down?
19 | FILTER | | | | | |
|* 20 | HASH JOIN | | 1 | 308 | 35 (3)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | EIN_TBL | 1 | 31 | 4 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 208 | 23 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 177 | 19 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 1 | 159 | 15 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 76 | 13 (0)| 00:00:01 |
|* 26 | INDEX FAST FULL SCAN | PK_WP | 1 | 55 | 9 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID| CSI_TBL | 2 | 42 | 4 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | XIF001CSI_TBL | 2 | | 2 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | TR_TBL | 1 | 83 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_P_TR_TBL | 1 | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | PF_TBL | 1 | 18 | 4 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | XP_TR_TBL2 | 1 | | 2 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | XPKEIN_TBL | 1 | | 3 (0)| 00:00:01 |
|* 34 | TABLE ACCESS FULL | LKP_TBL | 36 | 3600 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
