OraFAQ Forum: Performance Tuning » same query different explain plan

Home » RDBMS Server » Performance Tuning » same query different explain plan (11g ,solaris)

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

same query different explain plan [message #451000]

Mon, 12 April 2010 01:25

orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA

Member

Hi,
following are the different explain plan for this query:-

Code: [Select all] [Show/ hide]

select a14.SYS_MON_NAME  SYS_MON_NAME,
 a11.PRJ_SECTOR_CD  PRJ_SECTOR_CD,
 sum(a11.NET_ERN_SVC_US_RESTAT)  WJXBFS1
from GDWDM.GLOB_AGG_DUNS_LOS_SEC_24 a11
 join GDWDM.GLOB_FYTD_PER_END_DT_RELAT a12
   on  (a11.PER_END_DT = a12.FYTD_PER_END_DT)
 join GDWDM.GLOB_DUNS_VIEW a13
   on  (a11.ULT_PAR_DUNS_CD = a13.ULT_PAR_DUNS_CD)
 join GDWDM.GLOB_PER_INFO a14
   on  (a12.PER_END_DT = a14.PER_END_DT)
 join GDWDM.GLOB_TERR_PER_DIM a15
   on  (a11.PRJ_ENG_TERRITORY_CD = a15.PRJ_ENG_TERRITORY_CD and 
 a12.PER_END_DT = a15.PER_END_DT)
where (a13.ULT_CLIENT_SEGMENT_CD in ('10')
 and a13.ULT_CHANNEL in ('CHANNEL 1')
 and a15.CURR_PER_TERR_INDIC in ('Y')
 and a15.TERR_FY_EQUALS_SYS_FY in ('Y'))
group by a14.SYS_MON_NAME,
 a11.PRJ_SECTOR_CD 

9i -- 
| Id  | Operation                              |  Name                       | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                             |     1 |    87 |    40 |
|   1 |  SORT GROUP BY                         |                             |     1 |    87 |    40 |
|*  2 |   HASH JOIN                            |                             |     1 |    87 |    32 |
|*  3 |    HASH JOIN                           |                             |     1 |    72 |    29 |
|*  4 |     HASH JOIN                          |                             |     1 |    56 |    26 |
|*  5 |      TABLE ACCESS BY INDEX ROWID       | GLOB_TERR_PER_DIM           |     1 |    16 |     2 |
|*  6 |       INDEX RANGE SCAN                 | GTPD_CUPETEIN               |   106 |       |     1 |
|   7 |      TABLE ACCESS BY INDEX ROWID       | GLOB_AGG_DUNS_LOS_SEC_24    |    22 |   880 |    21 |
|   8 |       BITMAP CONVERSION TO ROWIDS      |                             |       |       |       |
|   9 |        BITMAP AND                      |                             |       |       |       |
|  10 |         BITMAP MERGE                   |                             |       |       |       |
|  11 |          BITMAP KEY ITERATION          |                             |       |       |       |
|* 12 |           TABLE ACCESS BY INDEX ROWID  | GLOB_TERR_PER_DIM           |     1 |    16 |     2 |
|* 13 |            INDEX RANGE SCAN            | GTPD_CUPETEIN               |   106 |       |     1 |
|* 14 |           BITMAP INDEX RANGE SCAN      | GLAGDULOSE24_PRENTECD       |       |       |       |
|  15 |         BITMAP MERGE                   |                             |       |       |       |
|  16 |          BITMAP KEY ITERATION          |                             |       |       |       |
|  17 |           TABLE ACCESS BY INDEX ROWID  | GLOB_DUNS_VIEW              |     1 |    18 |     2 |
|  18 |            BITMAP CONVERSION TO ROWIDS |                             |       |       |       |
|  19 |             BITMAP AND                 |                             |       |       |       |
|* 20 |              BITMAP INDEX SINGLE VALUE | GLDUVI_ULCH                 |       |       |       |
|* 21 |              BITMAP INDEX SINGLE VALUE | GLDUVI_ULCLSECD             |       |       |       |
|  22 |           BITMAP CONVERSION FROM ROWIDS|                             |       |       |       |
|* 23 |            INDEX RANGE SCAN            | GLAGDULOSE24_ULPADUCD       |       |       |     6 |
|  24 |     TABLE ACCESS FULL                  | GLOB_FYTD_PER_END_DT_RELAT  |  1326 | 21216 |     2 |
|  25 |    TABLE ACCESS FULL                   | GLOB_PER_INFO               |   204 |  3060 |     2 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A12"."PER_END_DT"="A14"."PER_END_DT")
   3 - access("A12"."PER_END_DT"="A15"."PER_END_DT" AND "A11"."PER_END_DT"="A12"."FYTD_PER_END_DT")
   4 - access("A11"."PRJ_ENG_TERRITORY_CD"="A15"."PRJ_ENG_TERRITORY_CD")
   5 - filter("A15"."TERR_FY_EQUALS_SYS_FY"='Y')
   6 - access("A15"."CURR_PER_TERR_INDIC"='Y')
  12 - filter("A15"."TERR_FY_EQUALS_SYS_FY"='Y')
  13 - access("A15"."CURR_PER_TERR_INDIC"='Y')
  14 - access("A11"."PRJ_ENG_TERRITORY_CD"="A15"."PRJ_ENG_TERRITORY_CD")
  20 - access("A13"."ULT_CHANNEL"='CHANNEL 1')
  21 - access("A13"."ULT_CLIENT_SEGMENT_CD"='06')
  23 - access("A11"."ULT_PAR_DUNS_CD"="A13"."ULT_PAR_DUNS_CD")

11g - PLAN_TABLE_OUTPUT

Code: [Select all] [Show/ hide]

Plan hash value: 1048710843
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                            |     1 |   107 |    24   (9)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                            |     1 |   107 |    24   (9)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                            |       |       |            |          |
|   3 |    NESTED LOOPS                       |                            |     1 |   107 |    23   (5)| 00:00:01 |
|*  4 |     HASH JOIN                         |                            |     1 |    90 |    22   (5)| 00:00:01 |
|   5 |      NESTED LOOPS                     |                            |       |       |            |          |
|   6 |       NESTED LOOPS                    |                            |     1 |    74 |    17   (0)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN           |                            |     1 |    34 |     3   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID   | GLOB_DUNS_VIEW             |     1 |    18 |     2   (0)| 00:00:01 |
|   9 |          BITMAP CONVERSION TO ROWIDS  |                            |       |       |            |          |
|  10 |           BITMAP AND                  |                            |       |       |            |          |
|* 11 |            BITMAP INDEX SINGLE VALUE  | GLDUVI_ULCLSECD            |       |       |            |          |
|* 12 |            BITMAP INDEX SINGLE VALUE  | GLDUVI_ULCH                |       |       |            |          |
|  13 |         BUFFER SORT                   |                            |     1 |    16 |     1   (0)| 00:00:01 |
|* 14 |          TABLE ACCESS BY INDEX ROWID  | GLOB_TERR_PER_DIM          |     1 |    16 |     1   (0)| 00:00:01 |
|* 15 |           INDEX RANGE SCAN            | GTPD_CUPETEIN              |   106 |       |     0   (0)| 00:00:01 |
|  16 |        BITMAP CONVERSION TO ROWIDS    |                            |       |       |            |          |
|  17 |         BITMAP AND                    |                            |       |       |            |          |
|  18 |          BITMAP CONVERSION FROM ROWIDS|                            |       |       |            |          |
|* 19 |           INDEX RANGE SCAN            | GLAGDULOSE24_ULPADUCD      |    39 |       |     2   (0)| 00:00:01 |
|* 20 |          BITMAP INDEX SINGLE VALUE    | GLAGDULOSE24_PRENTECD      |       |       |            |          |
|  21 |       TABLE ACCESS BY INDEX ROWID     | GLOB_AGG_DUNS_LOS_SEC_24   |     1 |    40 |    17   (0)| 00:00:01 |
|  22 |      TABLE ACCESS FULL                | GLOB_FYTD_PER_END_DT_RELAT |  1326 | 21216 |     4   (0)| 00:00:01 |
|* 23 |     INDEX UNIQUE SCAN                 | U_GLPEIN_PEENDT            |     1 |       |     0   (0)| 00:00:01 |
|  24 |    TABLE ACCESS BY INDEX ROWID        | GLOB_PER_INFO              |     1 |    17 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A12"."PER_END_DT"="A15"."PER_END_DT" AND "A11"."PER_END_DT"="A12"."FYTD_PER_END_DT")
  11 - access("A13"."ULT_CLIENT_SEGMENT_CD"='10')
  12 - access("A13"."ULT_CHANNEL"='CHANNEL 1')
  14 - filter("A15"."TERR_FY_EQUALS_SYS_FY"='Y')
  15 - access("A15"."CURR_PER_TERR_INDIC"='Y')
  19 - access("A11"."ULT_PAR_DUNS_CD"="A13"."ULT_PAR_DUNS_CD")
  20 - access("A11"."PRJ_ENG_TERRITORY_CD"="A15"."PRJ_ENG_TERRITORY_CD")
  23 - access("A12"."PER_END_DT"="A14"."PER_END_DT")

11G is taking more time than 9i query exection. Why ?????

Thanks in advance

[Moderator (RL)] Added CODE tags - you can do it yourself next time]

[Updated on: Mon, 12 April 2010 05:07] by Moderator

Report message to a moderator

Re: same query different explain plan [message #451060 is a reply to message #451000]

Mon, 12 April 2010 04:54

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

11G is going for nested loops instead of hash join.
Try using a hash hint.

Report message to a moderator

Re: same query different explain plan [message #451413 is a reply to message #451000]

Wed, 14 April 2010 09:01

mjohanss
Messages: 1
Registered: April 2010
Location: gothenburg, Sweden

Junior Member

Hello !

A lot of things has happened between 9i and 11g.
A changed plan is not uncommon. 11g Has something call plan stability. Which means that a new plan wont be used if it is worse then the existing one (costbased).

this link shows examples from 9i to 11g upgrade:
www.oracle.com/.../oracle11g/upgrade/.../9i_to_11g_real_world_customer_experience.pdf

It shows how to preserve plans after upgrade.

But if you don't want/can do that now since you already moved.
try to: alter session set optimizer_features_enable='9.2.0'

it reverse some parameters back to 9i settings. This might help you catch the old plan. If it does you need to find out which parameter causes the change.

Thanks
/Magnus

Report message to a moderator

Previous Topic:	SQL Logic (merged by CM)
Next Topic:	Compare Query Performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 14 07:35:47 CDT 2025