Hi,
We have one query that is running very slow in the 10.2.0.4 database after upgrading from 9.2.0.8 HP-UX B11.31. In 9.2.0.8 database it used to take 5 mins. But in the new 10.2.0.4 database it takes around 36 mins.The query is given below.
SELECT a.transaction_date,
a.ORD_PROD_BE_ID,
a.SLS_POSTD_DT_BE_ID,
a.net_trd_amt,
a.trd_actl_un_qty,
t.FISC_MO_CD,
t.FY_CD
FROM fact_net_trd_sls a,
dim_tm_mv t,
dim_prod b,
(SELECT DISTINCT kit_prod_cd FROM kal_kit_bom) c
WHERE b.be_id = a.ORD_PROD_BE_ID
AND b.end_date >SYSDATE
AND t.be_id = a.SLS_POSTD_DT_BE_ID
AND t.end_date > SYSDATE
AND c.kit_prod_cd (+)= b.base_prod_cd
AND t.FY_CD IN (SELECT DISTINCT FY_CD FROM DIM_tm_MV T WHERe T.DAY_STRT_PRD_OF_TM=TRUNC(SYSDATE))
and nvl2(c.kit_prod_cd,'K','FG') = 'FG'
ORDER BY a.ORD_PROD_BE_ID,a.TRANSACTION_DATE;
The plan in 9i is below,
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2861K| 1585M| | 170K|
| 1 | VIEW | | 2861K| 1585M| | 170K|
| 2 | SORT UNIQUE | | 2861K| 654M| 1397M| 170K|
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN OUTER | | | | | |
|* 5 | HASH JOIN | | 1998K| 445M| 4056K| 63419 |
|* 6 | TABLE ACCESS BY INDEX ROWID | DIM_PROD | 53189 | 3428K| | 283 |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 8 | BITMAP INDEX FULL SCAN | XN3_DIM_PROD | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | FACT_NET_TRD_SLS | 4081 | 203K| | 118 |
| 10 | NESTED LOOPS | | 1998K| 320M| | 57607 |
|* 11 | HASH JOIN SEMI | | 490 | 57330 | | 208 |
|* 12 | TABLE ACCESS FULL | DIM_TM_MV | 15180 | 1260K| | 195 |
| 13 | TABLE ACCESS BY INDEX ROWID| DIM_TM_MV | 1 | 32 | | 1 |
|* 14 | INDEX RANGE SCAN | XN4_DIM_TM_MV | 1 | | | 1 |
|* 15 | INDEX RANGE SCAN | FACT_NET_TRD_SLS_IDX3 | 4081 | | | 54 |
| 16 | TABLE ACCESS FULL | KAL_KIT_BOM | 14175 | 85050 | | 24 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL2("KAL_KIT_BOM"."KIT_PROD_CD",'K','FG')='FG')
4 - access("B"."BASE_PROD_CD"=("KAL_KIT_BOM"."KIT_PROD_CD"(+)))
5 - access("B"."BE_ID"="A"."ORD_PROD_BE_ID")
6 - filter("B"."END_DATE">SYSDATE@!)
11 - access("SYS_ALIAS_0000"."FY_CD"="T"."FY_CD")
12 - filter("SYS_ALIAS_0000"."END_DATE">SYSDATE@!)
14 - access("T"."DAY_STRT_PRD_OF_TM"=TRUNC(SYSDATE@!))
15 - access("SYS_ALIAS_0000"."BE_ID"="A"."SLS_POSTD_DT_BE_ID")
Note: cpu costing is off
36 rows selected.
The plan in 10g is below.
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 34 1382
VIEW 34 19 K 1382
SORT UNIQUE 34 7 K 1382
FILTER
HASH JOIN OUTER 34 7 K 1375
MAT_VIEW ACCESS BY INDEX ROWID WHSUSR.DIM_TM_MV 1 81 1
NESTED LOOPS 24 5 K 1350
NESTED LOOPS 740 105 K 1336
MERGE JOIN CARTESIAN 1 95 1332
TABLE ACCESS BY INDEX ROWID WHSUSR.DIM_PROD 52 K 3 M 279
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN WHSUSR.XN3_DIM_PROD
BUFFER SORT 1 30 9223372036 G
SORT UNIQUE 1 30 1
MAT_VIEW ACCESS BY INDEX ROWID WHSUSR.DIM_TM_MV 1 30 1
INDEX RANGE SCAN WHSUSR.XN4_DIM_TM_MV 1 1
TABLE ACCESS BY INDEX ROWID TRANSDATA.FACT_NET_TRD_SLS 740 36 K 4
INDEX RANGE SCAN TRANSDATA.FACT_NET_TRD_SLS_IDX2 2 K 1
INDEX RANGE SCAN WHSUSR.XN1_DIM_TM_MV 1 1
TABLE ACCESS FULL EDW_DBA.KAL_KIT_BOM 14 K 83 K 24
We have checked that the statistics are uptodate. Please help with suggestions.
Thanks in advane.