Home » RDBMS Server » Performance Tuning » How to optimize this plan (merged)
How to optimize this plan (merged) [message #415996] Thu, 30 July 2009 02:12 Go to next message
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

Hint to avoid CONCATENATION in plan [message #416011 is a reply to message #415996] Thu, 30 July 2009 02:52 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Is there any hint to avoid CONCATENATION in plan
Thanks
Re: Hint to avoid CONCATENATION in plan [message #416012 is a reply to message #416011] Thu, 30 July 2009 02:54 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I didn't get you.
What do you mean by that?

By
Vamsi
Re: Hint to avoid CONCATENATION in plan [message #416014 is a reply to message #416012] Thu, 30 July 2009 03:08 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Please refer to my previous post
http://www.orafaq.com/forum/f/6/140956/

Is there a way to avoid CONCATENATE in the plan

Thanks
Re: Hint to avoid CONCATENATION in plan [message #416021 is a reply to message #416014] Thu, 30 July 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is related to your previous post then why creating a new topic?
Your link does not point to any topic.

Regards
Michel
Re: Hint to avoid CONCATENATION in plan [message #416057 is a reply to message #416021] Thu, 30 July 2009 05:16 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I did make it as a separate question though its related to my previous post.
http://www.orafaq.com/forum/t/148439/140956/
Re: How to optimize this plan [message #416060 is a reply to message #415996] Thu, 30 July 2009 05:27 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
19-34 is repeating....
Re: How to optimize this plan (merged) [message #416123 is a reply to message #415996] Thu, 30 July 2009 09:57 Go to previous messageGo to next message
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 |
------------------------------------------------------------------------------------------------------------------



Re: How to optimize this plan (merged) [message #416404 is a reply to message #415996] Sat, 01 August 2009 10:44 Go to previous message
amardeep.sidhu
Messages: 7
Registered: October 2007
Junior Member
You haven't posted the exact version. Also your link doesn't point to any post. Moreover is this plan output from EXPLAIN PLAN ? Then also post the predicates section.

Regards,
Amardeep Sidhu
Previous Topic: Recompute plan each time for the sql
Next Topic: How To Eliminate Sort Unique
Goto Forum:
  


Current Time: Sat Dec 03 15:53:00 CST 2016

Total time taken to generate the page: 0.22315 seconds