Oracle Database Discussions

Hi,

I'm migrating an 110GB DW from 9i HPUX (450MB SGA) to a 10gR2 RAC 2-nodes env, SGA is 4GB. We
found many reports run forever without returning. We capture a query for examination and compared
two execution plans on 9i and 10g. We found in 10g in a nested loop, Oracle did FTS on a very small
table,7496 rows, instead of using the index sacn as it did on 9i. If we put an index hint, it
returns result in seconds!

My problem is that these queries are generated from the middle tier, I can't put the hint in real
life!

Since I exported from 9i and imported into 10g, I have gathered the statistics on schema, table and
index level. What should I do to tell CBO to use the index?

The fact confused me that the hinted query costs 150 times more but returned results in seconds!

Here are the plans, notice the ID 21:

SYSTEM @rbcmvrac> set autotrace traceonly explain
SYSTEM @rbcmvrac>
1 select
2 GL_Balances.Portfolio_Group as Portfolio_Group,
3 GL_Balances.Portfolio_Group_Description as Portfolio_Group_Description,
4 GL_Balances.Portfolio_Code as Portfolio_Code,
5 GL_Balances.Portfolio_Component as Portfolio_Component,
6 GL_Balances.Portfolio_Currency as Portfolio_Currency,
7 GL_Balances.GL_Account as GL_Account,
8 GL_Balances.GL_Account_Description as GL_Account_Description,
9 GL_Balances.GL_Account_Type as GL_Account_Type,
10 GL_Balances.Cash_Account_Flag as Cash_Account_Flag,
11 GL_Balances.As_of_Date as As_of_Date,
12 GL_Balances.Adjusted_Through_Date as Adjusted_Through_Date,
13 GL_Balances.Ending_Balance as Ending_Balance,
14 GL_Balances.Most_Current_Flag as Most_Current_Flag
15 from
16 (select
17 MV_FUND_GROUPS.FUND_GROUP as Portfolio_Group,
18 V_MV_FUND_GROUPS_HD_FILTERED.FUND_GROUP_DESCRIPTION as Portfolio_Group_Description,
19 MV_GL_BALANCES.FUND_CODE as Portfolio_Code,
20 MV_GL_BALANCES.FUND_COMPONENT as Portfolio_Component,
21 MV_GL_BALANCES.CURRENCY as Portfolio_Currency,
22 MV_GL_BALANCES.GL_ACCOUNT as GL_Account,
23 ST_GL_ACCOUNTS.DESCRIPTION as GL_Account_Description,
24 ST_GL_ACCOUNTS.GL_ACCOUNT_TYPE as GL_Account_Type,
25 ST_GL_ACCOUNTS.CASH_ACCOUNT as Cash_Account_Flag,
26 MV_GL_BALANCES.AS_OF_DATE as As_of_Date,
27 MV_GL_BALANCES.ADJ_THROUGH_DT as Adjusted_Through_Date,
28 MV_GL_BALANCES.ENDING_BALANCE as Ending_Balance,
29 MV_GL_BALANCES.MOST_CURRENT_FLAG as Most_Current_Flag
30 from
31 RBC_MVIEW.MV_FUND_GROUPS MV_FUND_GROUPS
32 join
33 (select
34 V_MV_FUND_GROUPS_HD_FILTERED.FUND_GROUP as FUND_GROUP,
35 V_MV_FUND_GROUPS_HD_FILTERED.FUND_GROUP_DESCRIPTION as FUND_GROUP_DESCRIPTION
36 from
37 RBC_MVIEW.V_MV_FUND_GROUPS_HD_FILTERED V_MV_FUND_GROUPS_HD_FILTERED
38 where
39 (V_MV_FUND_GROUPS_HD_FILTERED.USER_ID = 'rbc_abol')
40 ) V_MV_FUND_GROUPS_HD_FILTERED
41 on (V_MV_FUND_GROUPS_HD_FILTERED.FUND_GROUP = MV_FUND_GROUPS.FUND_GROUP)
42 join
43 RBC_MVIEW.MV_GL_BALANCES MV_GL_BALANCES
44 on (MV_FUND_GROUPS.FUND_CODE = MV_GL_BALANCES.FUND_CODE)
45 join
46 RBC_MVIEW.MV_FUND_COMPONENTS MV_FUND_COMPONENTS
47 on ((MV_FUND_COMPONENTS.FUND_CODE = MV_GL_BALANCES.FUND_CODE) and (MV_FUND_COMPONENTS.FUND_COMPONENT =
48 MV_GL_BALANCES.FUND_COMPONENT))
49 join
50 (select
51 ST_GL_ACCOUNTS.GL_ACCOUNT,
52 ST_GL_ACCOUNTS.GL_ACCOUNT_TYPE,
53 ST_GL_ACCOUNTS_DESC.DESCRIPTION,
54 ST_GL_ACCOUNTS.CASH_ACCOUNT
55 from
56 RBC_MVIEW.ST_GL_ACCOUNTS ST_GL_ACCOUNTS,
57 RBC_MVIEW.ST_GL_ACCOUNTS_DESC
58 where ST_GL_ACCOUNTS.GL_ACCOUNT = ST_GL_ACCOUNTS_DESC.GL_ACCOUNT and 'EN' = ST_GL_ACCOUNTS_DESC."LANGUAGE")
59 ST_GL_ACCOUNTS
60 on (MV_GL_BALANCES.GL_ACCOUNT = ST_GL_ACCOUNTS.GL_ACCOUNT)
61 where
62 (MV_FUND_GROUPS.FUND_GROUP in ('AEGON')) and
63 (((MV_FUND_COMPONENTS.DATE_CLOSED is NULL) or (MV_FUND_COMPONENTS.DATE_CLOSED > MV_GL_BALANCES.AS_OF_DATE)) and
64 ((MV_FUND_COMPONENTS.COMPONENT_INACTIVE_DATE is NULL) or (MV_FUND_COMPONENTS.COMPONENT_INACTIVE_DATE >
65 MV_GL_BALANCES.AS_OF_DATE)))
66* ) GL_Balances
67 ;

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 244K| 49M| 99 (5)|
| 1 | HASH GROUP BY | | 244K| 49M| 99 (5)|
|* 2 | HASH JOIN | | 122 | 25986 | 94 (4)|
|* 3 | HASH JOIN | | 122 | 20984 | 86 (3)|
|* 4 | HASH JOIN | | 122 | 18422 | 80 (2)|
|* 5 | INDEX RANGE SCAN | PK_MV_FUND_GROUPS | 9 | 216 | 2 (0)|
| 6 | NESTED LOOPS | | 23632 | 2930K| 78 (2)|
| 7 | MERGE JOIN CARTESIAN | | 2 | 144 | 67 (2)|
| 8 | NESTED LOOPS | | 1 | 44 | 3 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID| MV_FUND_GROUPS_HEADER | 1 | 31 | 2 (0)|
|* 10 | INDEX UNIQUE SCAN | PK_MV_FUND_GROUPS_HEADER | 1 | | 1 (0)|
| 11 | SORT AGGREGATE | | 1 | 7 | |
|* 12 | INDEX RANGE SCAN | PK_MV_FUND_GROUPS | 9 | 63 | 2 (0)|
|* 13 | INDEX FULL SCAN | IDX_MV_USER_GROUPS_IX1 | 1 | 13 | 1 (0)|
| 14 | SORT AGGREGATE | | 1 | 7 | |
|* 15 | INDEX RANGE SCAN | PK_MV_FUND_GROUPS | 9 | 63 | 2 (0)|
| 16 | SORT AGGREGATE | | 1 | 22 | |
| 17 | NESTED LOOPS | | 10 | 220 | 2 (0)|
|* 18 | INDEX RANGE SCAN | PK_MV_FUND_GROUPS | 9 | 108 | 2 (0)|
|* 19 | INDEX UNIQUE SCAN | PK_MV_FUND_USER_GROUPS | 1 | 10 | 0 (0)|
| 20 | BUFFER SORT | | 7496 | 204K| 66 (2)|
| 21 | TABLE ACCESS FULL | MV_FUND_COMPONENTS | 7496 | 204K| 64 (2)|
|* 22 | TABLE ACCESS BY INDEX ROWID | MV_GL_BALANCES | 12610 | 677K| 8 (0)|
|* 23 | INDEX RANGE SCAN | IDX_MV_GL_BALANCES_F | 17 | | 3 (0)|
| 24 | TABLE ACCESS FULL | ST_GL_ACCOUNTS | 1172 | 24612 | 5 (0)|
|* 25 | TABLE ACCESS FULL | ST_GL_ACCOUNTS_DESC | 1172 | 48052 | 7 (0)|
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ST_GL_ACCOUNTS"."GL_ACCOUNT"="ST_GL_ACCOUNTS_DESC"."GL_ACCOUNT")
3 - access("MV_GL_BALANCES"."GL_ACCOUNT"="ST_GL_ACCOUNTS"."GL_ACCOUNT")
4 - access("MV_FUND_GROUPS"."FUND_CODE"="MV_GL_BALANCES"."FUND_CODE" AND
"MV_FUND_GROUPS"."FUND_GROUP"="MV_FUND_GROUPS_HEADER"."FUND_GROUP")
5 - access("MV_FUND_GROUPS"."FUND_GROUP"='AEGON')
10 - access("MV_FUND_GROUPS_HEADER"."FUND_GROUP"='AEGON')
filter(NVL( (SELECT COUNT(*) FROM RBC_MVIEW."MV_FUND_GROUPS" "MV_FUND_GROUPS" WHERE
"MV_FUND_GROUPS"."FUND_GROUP"=:B1),0)<>0)
12 - access("MV_FUND_GROUPS"."FUND_GROUP"=:B1)
13 - access("MV_USER_GROUPS"."USER_ID"='rbc_abol')
filter("MV_USER_GROUPS"."USER_ID"='rbc_abol' AND NVL( (SELECT COUNT(*) FROM
RBC_MVIEW."MV_FUND_GROUPS" "MV_FUND_GROUPS" WHERE
"MV_FUND_GROUPS"."FUND_GROUP"=:B1),0)=NVL( (SELECT COUNT(*) FROM
RBC_MVIEW."MV_FUND_USER_GROUPS" "MV_FUND_USER_GROUPS",RBC_MVIEW."MV_FUND_GROUPS"
"MV_FUND_GROUPS" WHERE "MV_FUND_GROUPS"."FUND_GROUP"=:B2 AND
"MV_FUND_GROUPS"."FUND_CODE"="MV_FUND_USER_GROUPS"."FUND_CODE" AND
"MV_FUND_USER_GROUPS"."USER_GROUP"=:B3),0))
15 - access("MV_FUND_GROUPS"."FUND_GROUP"=:B1)
18 - access("MV_FUND_GROUPS"."FUND_GROUP"=:B1)
19 - access("MV_FUND_USER_GROUPS"."USER_GROUP"=:B1 AND
"MV_FUND_GROUPS"."FUND_CODE"="MV_FUND_USER_GROUPS"."FUND_CODE")
22 - filter(("MV_FUND_COMPONENTS"."DATE_CLOSED" IS NULL OR
"MV_FUND_COMPONENTS"."DATE_CLOSED">"MV_GL_BALANCES"."AS_OF_DATE") AND
("MV_FUND_COMPONENTS"."COMPONENT_INACTIVE_DATE" IS NULL OR
"MV_FUND_COMPONENTS"."COMPONENT_INACTIVE_DATE">"MV_GL_BALANCES"."AS_OF_DATE"))
23 - access("MV_FUND_COMPONENTS"."FUND_CODE"="MV_GL_BALANCES"."FUND_CODE" AND
"MV_FUND_COMPONENTS"."FUND_COMPONENT"="MV_GL_BALANCES"."FUND_COMPONENT")
25 - filter("ST_GL_ACCOUNTS_DESC"."LANGUAGE"='EN')

Note
-----
- 'PLAN_TABLE' is old version

SYSTEM @rbcmvrac>
1 select
2 GL_Balances.Portfolio_Group as Portfolio_Group,
3 GL_Balances.Portfolio_Group_Description as Portfolio_Group_Description,
4 GL_Balances.Portfolio_Code as Portfolio_Code,
5 GL_Balances.Portfolio_Component as Portfolio_Component,
6 GL_Balances.Portfolio_Currency as Portfolio_Currency,
7 GL_Balances.GL_Account as GL_Account,
8 GL_Balances.GL_Account_Description as GL_Account_Description,
9 GL_Balances.GL_Account_Type as GL_Account_Type,
10 GL_Balances.Cash_Account_Flag as Cash_Account_Flag,
11 GL_Balances.As_of_Date as As_of_Date,
12 GL_Balances.Adjusted_Through_Date as Adjusted_Through_Date,
13 GL_Balances.Ending_Balance as Ending_Balance,
14 GL_Balances.Most_Current_Flag as Most_Current_Flag
15 from
16 (select /*+ ordered index(MV_FUND_COMPONENTS PK_MV_FUND_COMPONENTS)*/
17 MV_FUND_GROUPS.FUND_GROUP as Portfolio_Group,
18 V_MV_FUND_GROUPS_HD_FILTERED.FUND_GROUP_DESCRIPTION as Portfolio_Group_Description,
19 MV_GL_BALANCES.FUND_CODE as Portfolio_Code,
20 MV_GL_BALANCES.FUND_COMPONENT as Portfolio_Component,
21 MV_GL_BALANCES.CURRENCY as Portfolio_Currency,
22 MV_GL_BALANCES.GL_ACCOUNT as GL_Account,
23 ST_GL_ACCOUNTS.DESCRIPTION as GL_Account_Description,
24 ST_GL_ACCOUNTS.GL_ACCOUNT_TYPE as GL_Account_Type,
25 ST_GL_ACCOUNTS.CASH_ACCOUNT as Cash_Account_Flag,
26 MV_GL_BALANCES.AS_OF_DATE as As_of_Date,
27 MV_GL_BALANCES.ADJ_THROUGH_DT as Adjusted_Through_Date,
28 MV_GL_BALANCES.ENDING_BALANCE as Ending_Balance,
29 MV_GL_BALANCES.MOST_CURRENT_FLAG as Most_Current_Flag
30 from
31 RBC_MVIEW.MV_FUND_GROUPS MV_FUND_GROUPS
32 join
33 (select
34 V_MV_FUND_GROUPS_HD_FILTERED.FUND_GROUP as FUND_GROUP,
35 V_MV_FUND_GROUPS_HD_FILTERED.FUND_GROUP_DESCRIPTION as FUND_GROUP_DESCRIPTION
36 from
37 RBC_MVIEW.V_MV_FUND_GROUPS_HD_FILTERED V_MV_FUND_GROUPS_HD_FILTERED
38 where
39 (V_MV_FUND_GROUPS_HD_FILTERED.USER_ID = 'rbc_abol')
40 ) V_MV_FUND_GROUPS_HD_FILTERED
41 on (V_MV_FUND_GROUPS_HD_FILTERED.FUND_GROUP = MV_FUND_GROUPS.FUND_GROUP)
42 join
43 RBC_MVIEW.MV_GL_BALANCES MV_GL_BALANCES
44 on (MV_FUND_GROUPS.FUND_CODE = MV_GL_BALANCES.FUND_CODE)
45 join
46 RBC_MVIEW.MV_FUND_COMPONENTS MV_FUND_COMPONENTS
47 on ((MV_FUND_COMPONENTS.FUND_CODE = MV_GL_BALANCES.FUND_CODE) and (MV_FUND_COMPONENTS.FUND_COMPONENT =
48 MV_GL_BALANCES.FUND_COMPONENT))
49 join
50 (select
51 ST_GL_ACCOUNTS.GL_ACCOUNT,
52 ST_GL_ACCOUNTS.GL_ACCOUNT_TYPE,
53 ST_GL_ACCOUNTS_DESC.DESCRIPTION,
54 ST_GL_ACCOUNTS.CASH_ACCOUNT
55 from
56 RBC_MVIEW.ST_GL_ACCOUNTS ST_GL_ACCOUNTS,
57 RBC_MVIEW.ST_GL_ACCOUNTS_DESC
58 where ST_GL_ACCOUNTS.GL_ACCOUNT = ST_GL_ACCOUNTS_DESC.GL_ACCOUNT and 'EN' = ST_GL_ACCOUNTS_DESC."LANGUAGE")
59 ST_GL_ACCOUNTS
60 on (MV_GL_BALANCES.GL_ACCOUNT = ST_GL_ACCOUNTS.GL_ACCOUNT)
61 where
62 (MV_FUND_GROUPS.FUND_GROUP in ('AEGON')) and
63 (((MV_FUND_COMPONENTS.DATE_CLOSED is NULL) or (MV_FUND_COMPONENTS.DATE_CLOSED > MV_GL_BALANCES.AS_OF_DATE)) and
64 ((MV_FUND_COMPONENTS.COMPONENT_INACTIVE_DATE is NULL) or (MV_FUND_COMPONENTS.COMPONENT_INACTIVE_DATE >
65 MV_GL_BALANCES.AS_OF_DATE)))
66* ) GL_Balances
67 ;

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 244K| 49M| 15698 (1)|
| 1 | HASH GROUP BY | | 244K| 49M| 15698 (1)|
|* 2 | HASH JOIN | | 122 | 25986 | 15693 (1)|
|* 3 | HASH JOIN | | 122 | 20984 | 15686 (1)|
| 4 | NESTED LOOPS | | 122 | 18422 | 15680 (1)|
| 5 | NESTED LOOPS | | 122 | 15006 | 15558 (1)|
| 6 | NESTED LOOPS | | 1 | 68 | 6 (17)|
|* 7 | HASH JOIN | | 1 | 55 | 5 (20)|
|* 8 | INDEX RANGE SCAN | PK_MV_FUND_GROUPS | 9 | 216 | 2 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID| MV_FUND_GROUPS_HEADER | 1 | 31 | 2 (0)|
|* 10 | INDEX UNIQUE SCAN | PK_MV_FUND_GROUPS_HEADER | 1 | | 1 (0)|
| 11 | SORT AGGREGATE | | 1 | 7 | |
|* 12 | INDEX RANGE SCAN | PK_MV_FUND_GROUPS | 9 | 63 | 2 (0)|
|* 13 | INDEX FULL SCAN | IDX_MV_USER_GROUPS_IX1 | 1 | 13 | 1 (0)|
| 14 | SORT AGGREGATE | | 1 | 7 | |
|* 15 | INDEX RANGE SCAN | PK_MV_FUND_GROUPS | 9 | 63 | 2 (0)|
| 16 | SORT AGGREGATE | | 1 | 22 | |
| 17 | NESTED LOOPS | | 10 | 220 | 2 (0)|
|* 18 | INDEX RANGE SCAN | PK_MV_FUND_GROUPS | 9 | 108 | 2 (0)|
|* 19 | INDEX UNIQUE SCAN | PK_MV_FUND_USER_GROUPS | 1 | 10 | 0 (0)|
| 20 | TABLE ACCESS BY INDEX ROWID | MV_GL_BALANCES | 53254 | 2860K| 15553 (1)|
|* 21 | INDEX RANGE SCAN | IDX_MV_GL_BALANCES_F | 53254 | | 346 (1)|
|* 22 | TABLE ACCESS BY INDEX ROWID | MV_FUND_COMPONENTS | 1 | 28 | 1 (0)|
|* 23 | INDEX UNIQUE SCAN | PK_MV_FUND_COMPONENTS | 1 | | 0 (0)|
| 24 | TABLE ACCESS FULL | ST_GL_ACCOUNTS | 1172 | 24612 | 5 (0)|
|* 25 | TABLE ACCESS FULL | ST_GL_ACCOUNTS_DESC | 1172 | 48052 | 7 (0)|
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ST_GL_ACCOUNTS"."GL_ACCOUNT"="ST_GL_ACCOUNTS_DESC"."GL_ACCOUNT")
3 - access("MV_GL_BALANCES"."GL_ACCOUNT"="ST_GL_ACCOUNTS"."GL_ACCOUNT")
7 - access("MV_FUND_GROUPS"."FUND_GROUP"="MV_FUND_GROUPS_HEADER"."FUND_GROUP")
8 - access("MV_FUND_GROUPS"."FUND_GROUP"='AEGON')
10 - access("MV_FUND_GROUPS_HEADER"."FUND_GROUP"='AEGON')
filter(NVL( (SELECT COUNT(*) FROM RBC_MVIEW."MV_FUND_GROUPS" "MV_FUND_GROUPS" WHERE
"MV_FUND_GROUPS"."FUND_GROUP"=:B1),0)<>0)
12 - access("MV_FUND_GROUPS"."FUND_GROUP"=:B1)
13 - access("MV_USER_GROUPS"."USER_ID"='rbc_abol')
filter("MV_USER_GROUPS"."USER_ID"='rbc_abol' AND NVL( (SELECT COUNT(*) FROM
RBC_MVIEW."MV_FUND_GROUPS" "MV_FUND_GROUPS" WHERE
"MV_FUND_GROUPS"."FUND_GROUP"=:B1),0)=NVL( (SELECT COUNT(*) FROM
RBC_MVIEW."MV_FUND_USER_GROUPS" "MV_FUND_USER_GROUPS",RBC_MVIEW."MV_FUND_GROUPS"
"MV_FUND_GROUPS" WHERE "MV_FUND_GROUPS"."FUND_GROUP"=:B2 AND
"MV_FUND_GROUPS"."FUND_CODE"="MV_FUND_USER_GROUPS"."FUND_CODE" AND
"MV_FUND_USER_GROUPS"."USER_GROUP"=:B3),0))
15 - access("MV_FUND_GROUPS"."FUND_GROUP"=:B1)
18 - access("MV_FUND_GROUPS"."FUND_GROUP"=:B1)
19 - access("MV_FUND_USER_GROUPS"."USER_GROUP"=:B1 AND
"MV_FUND_GROUPS"."FUND_CODE"="MV_FUND_USER_GROUPS"."FUND_CODE")
21 - access("MV_FUND_GROUPS"."FUND_CODE"="MV_GL_BALANCES"."FUND_CODE")
22 - filter(("MV_FUND_COMPONENTS"."DATE_CLOSED" IS NULL OR
"MV_FUND_COMPONENTS"."DATE_CLOSED">"MV_GL_BALANCES"."AS_OF_DATE") AND
("MV_FUND_COMPONENTS"."COMPONENT_INACTIVE_DATE" IS NULL OR
"MV_FUND_COMPONENTS"."COMPONENT_INACTIVE_DATE">"MV_GL_BALANCES"."AS_OF_DATE"))
23 - access("MV_FUND_COMPONENTS"."FUND_CODE"="MV_GL_BALANCES"."FUND_CODE" AND
"MV_FUND_COMPONENTS"."FUND_COMPONENT"="MV_GL_BALANCES"."FUND_COMPONENT")
25 - filter("ST_GL_ACCOUNTS_DESC"."LANGUAGE"='EN')

Note
-----
- 'PLAN_TABLE' is old version