| 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:-
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
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 #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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
