Home » RDBMS Server » Performance Tuning » Query running way too slow (Oracle 9.2.0.8 (HP-UX))
| Query running way too slow [message #477733] |
Mon, 04 October 2010 02:47  |
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
|
Member |
|
|
Hi,
We used to execute the query contrt_edw.sql in prod database and it used to run good,but our developers changed the query and developed contr_edw_v2a and since then it started running too slow. I have attached the links to see the queries and also the explain plan of the new developed query which is running slow.
The original query is
SELECT SUBSTR(dt.fisc_mo_cd,3,4)||SUBSTR(dt.fisc_mo_cd,8,2)||'|'||
T9.CORE_SRC_REG_ID||'|'||
T9.CORE_REG_NM||'|'||
T9.CORE_SRC_DIV_ID||'|'||
T9.CORE_DIV_NM||'|'||
T7.INDV_CUST_NM||'|'||
T7.INDV_CUST_NO||'|'||
DECODE(nvl(T7.INDV_CUST_NO,'*'), '*', T7.INDV_CUST_NO || ' - ' || T7.INDV_CUST_NM, T7.INDV_CUST_NO || ' - ' || T7.INDV_CUST_NM)||'|'||
'HOLD' ||'|'|| -- decode(nvl(t7.BG_ID_NO,'*'),'*',decode(t7.GOVT_COT_IND,'Y','Government','WCPP'),'Buying Group')||'|'||
decode(SUBSTR(t1.CONTR_PRD_TIER_NO,1,7),'0000000', 'Non-Contract', 'Contract')||'|'||
T5.BG_ID_NO||'|'||
T5.BG_NM||'|'||
'Endo-Mechanical'||'|'||
'Endo-Mechanical'||'|'||
T3.DIV_NM||'|'||
T3.DIV_CD||'|'||
T3.MJR_GRP_NM||'|'||
T3.MJR_GRP_CD||'|'||
T3.MNR_GRP_NM||'|'||
T3.MNR_GRP_CD||'|'||
T3.BASE_PROD_CD||'|'||
SUBSTR(t1.CONTR_PRD_TIER_NO,1,7)||'|'||
T6.CONTR_TIER_TXT||'|'||
T2.CONTR_STRNG_NM||'|'||
SUBSTR(t1.CONTR_PRD_TIER_NO,1,7)||' - '||t2.CONTR_STRNG_NM||'|'||
SUM((T3.COACT_SCOST_AMT * T1.PROD_QTY)*decode(t9.CORE_pct_SPLT_NO,'.3333','.333333',t9.CORE_pct_SPLT_NO))||'|'||
SUM(T1.PROD_QTY * decode(t9.CORE_pct_SPLT_NO,'.3333','.333333',t9.CORE_pct_SPLT_NO))||'|'||
SUM((T1.PROD_SLS_AMT)*decode(t9.CORE_pct_SPLT_NO,'.3333','.333333',t9.CORE_pct_SPLT_NO))||'|'||
SUM((T1.PROD_LST_AMT)*decode(t9.CORE_pct_SPLT_NO,'.3333','.333333',t9.CORE_pct_SPLT_NO))||'|'||
SUM((T1.PROD_LST_AMT-T1.PROD_SLS_AMT)*decode(t9.CORE_pct_SPLT_NO,'.3333','.333333',t9.CORE_pct_SPLT_NO))||'|'||
SUM((T1.PROD_SLS_AMT-T3.COACT_SCOST_AMT)*decode(t9.CORE_pct_SPLT_NO,'.3333','.333333',t9.CORE_pct_SPLT_NO))||'|'||
SUM((T4.PRC_BOOK_AMT*T1.PROD_QTY)*decode(t9.CORE_pct_SPLT_NO,'.3333','.333333',t9.CORE_pct_SPLT_NO))||'|'||
T9.IDN_DESCN_NM_TXT
FROM FACT_CNSMR_SLS T1 INNER JOIN DIM_CUST_MV T7 ON T1.cust_be_id = T7.object_id
INNER JOIN DIM_PROD T3 ON T1.fg_be_id = T3.be_id
INNER JOIN DIM_PROD_PRC_BOOK_MV T4 ON T3.base_prod_oid = T4.base_prod_oid
INNER JOIN DIM_CUST_ALGN_MV T9 ON T1.cust_be_id = T9.cust_be_id
INNER JOIN DIM_BUYG_GRP T5 ON T1.BG_ID_NO_BE_ID = T5.BE_ID
INNER JOIN DIM_TM_MV DT ON T1.COMP_dt_be_id= dt.be_id
LEFT OUTER JOIN CONTR@EESCIP20 T2 ON SUBSTR(t1.CONTR_PRD_TIER_NO,1,7) = T2.CONTR_NO
LEFT OUTER JOIN CONTR_PRD_TIER@EESCIP20 T6 ON
t1.CONTR_PRD_TIER_NO = T6.CONTR_NO||T6.CONTR_PRD_NO||T6.CONTR_TIER_NO
WHERE
dt.fy_cd >= 'FY2009'
and T1.rptg_yr_cd in ('2009','2010')
AND trunc(sysdate-to_char(sysdate+1,'D')) BETWEEN T5.START_DATE AND T5.end_date
AND trunc(sysdate-to_char(sysdate+1,'D')) BETWEEN T3.START_DATE AND T3.end_date
AND T4.PRC_BOOK_TYP_CD = 'D'
AND to_date(T1.RPTG_YR_CD||T1.RPTG_YR_MO||'15','YYYYMMDD') between
T4.PROD_PRC_BOOK_FISC_STRT_DT and T4.PROD_PRC_BOOK_FISC_END_DT
AND T9.CURRENT_FLG = 'Y'
AND T7.CURRENT_FLG = 'Y'
and T9.algn_strc_cd = 'AS11'
AND T7.INDV_CUST_STT_CD <> 'ZZ'
GROUP BY SUBSTR(dt.fisc_mo_cd,3,4)||SUBSTR(dt.fisc_mo_cd,8,2)||'|'||
T9.CORE_SRC_REG_ID,
T9.CORE_REG_NM,
T9.CORE_SRC_DIV_ID,
T9.CORE_DIV_NM,
T7.INDV_CUST_NM,
T7.INDV_CUST_NO,
DECODE(nvl(T7.INDV_CUST_NO,'*'), '*', T7.INDV_CUST_NO || ' - ' || T7.INDV_CUST_NM, T7.INDV_CUST_NO || ' - ' || T7.INDV_CUST_NM),
decode(SUBSTR(t1.CONTR_PRD_TIER_NO,1,7),'0000000', 'Non-Contract', 'Contract'),
T5.BG_ID_NO,
T5.BG_NM,
T3.DIV_NM,
T3.DIV_CD,
T3.MJR_GRP_NM,
T3.MJR_GRP_CD,
T3.MNR_GRP_NM,
T3.MNR_GRP_CD,
T3.BASE_PROD_CD,
SUBSTR(t1.CONTR_PRD_TIER_NO,1,7),
T6.CONTR_TIER_TXT,
T2.CONTR_STRNG_NM,
SUBSTR(t1.CONTR_PRD_TIER_NO,1,7)||' - '||t2.CONTR_STRNG_NM,
T9.IDN_DESCN_NM_TXT;
SPOOL OFF
exit;
And the updated query is
SELECT a.FISC_YR_MO||'|'||
a.CORE_SRC_REG_ID||'|'||
a.CORE_REG_NM||'|'||
a.CORE_SRC_DIV_ID||'|'||
a.CORE_DIV_NM||'|'||
a.INDV_CUST_NM||'|'||
a.INDV_CUST_NO||'|'||
DECODE(nvl(a.INDV_CUST_NO,'*'), '*', a.INDV_CUST_NO || ' - ' || a.INDV_CUST_NM, a.INDV_CUST_NO || ' - ' || a.INDV_CUST_NM)||'|'||
'HOLD' ||'|'|| -- decode(nvl(t7.BG_ID_NO,'*'),'*',decode(t7.GOVT_COT_IND,'Y','Government','WCPP'),'Buying Group')||'|'||
decode(b.CONTR_NO,'0000000', 'Non-Contract', 'Contract')||'|'||
a.BG_ID_NO||'|'||
a.BG_NM||'|'||
'Endo-Mechanical'||'|'||
'Endo-Mechanical'||'|'||
a.DIV_NM||'|'||
a.DIV_CD||'|'||
a.MJR_GRP_NM||'|'||
a.MJR_GRP_CD||'|'||
a.MNR_GRP_NM||'|'||
a.MNR_GRP_CD||'|'||
a.BASE_PROD_CD||'|'||
b.CONTR_NO||'|'||b.CONTR_TIER_TXT||'|'||
b.CONTR_STRNG_NM||'|'||
b.CONTR_NO||' - '||b.CONTR_STRNG_NM||'|'||
((a.COACT_SCOST_AMT * a.PROD_QTY)*decode(a.CORE_pct_SPLT_NO,'.3333','.333333',a.CORE_pct_SPLT_NO))||'|'||
(a.PROD_QTY * decode(a.CORE_pct_SPLT_NO,'.3333','.333333',a.CORE_pct_SPLT_NO))||'|'||
((a.PROD_SLS_AMT)*decode(a.CORE_pct_SPLT_NO,'.3333','.333333',a.CORE_pct_SPLT_NO))||'|'||
((a.PROD_LST_AMT)*decode(a.CORE_pct_SPLT_NO,'.3333','.333333',a.CORE_pct_SPLT_NO))||'|'||
((a.PROD_LST_AMT-a.PROD_SLS_AMT)*decode(a.CORE_pct_SPLT_NO,'.3333','.333333',a.CORE_pct_SPLT_NO))||'|'||
((a.PROD_SLS_AMT-a.COACT_SCOST_AMT)*decode(a.CORE_pct_SPLT_NO,'.3333','.333333',a.CORE_pct_SPLT_NO))||'|'||
((a.PRC_BOOK_AMT*a.PROD_QTY)*decode(a.CORE_pct_SPLT_NO,'.3333','.333333',a.CORE_pct_SPLT_NO))||'|'||
a.IDN_DESCN_NM_TXT
FROM
( select /*+ NO_INDEX(T1 FACT_CNSMR_SLS_IDX6) */
SUBSTR(dt.fisc_mo_cd,3,4)||SUBSTR(dt.fisc_mo_cd,8,2) fisc_yr_mo,
T9.CORE_SRC_REG_ID,
T9.CORE_REG_NM,
T9.CORE_SRC_DIV_ID,
T9.CORE_DIV_NM,
T7.INDV_CUST_NM,
T7.INDV_CUST_NO,
T5.BG_ID_NO,
T5.BG_NM,
T3.DIV_NM,
T3.DIV_CD,
T3.MJR_GRP_NM,
T3.MJR_GRP_CD,
T3.MNR_GRP_NM,
T3.MNR_GRP_CD,
T3.BASE_PROD_CD,
T1.CONTR_PRD_TIER_NO,
T3.COACT_SCOST_AMT,
T1.PROD_QTY,
T9.CORE_PCT_SPLT_NO,
T1.PROD_SLS_AMT,
T1.PROD_LST_AMT,
T4.PRC_BOOK_AMT,
T9.IDN_DESCN_NM_TXT
from
FACT_CNSMR_SLS T1 INNER JOIN DIM_CUST_MV T7 ON T1.cust_be_id = T7.object_id
INNER JOIN DIM_PROD T3 ON T1.fg_be_id = T3.be_id
INNER JOIN DIM_PROD_PRC_BOOK_MV T4 ON T3.base_prod_oid = T4.base_prod_oid
INNER JOIN DIM_CUST_ALGN_MV T9 ON T1.cust_be_id = T9.cust_be_id
INNER JOIN DIM_BUYG_GRP T5 ON T1.BG_ID_NO_BE_ID = T5.BE_ID
INNER JOIN DIM_TM_MV DT ON T1.COMP_dt_be_id= dt.be_id
WHERE
dt.fy_cd >= 'FY2009'
AND T1.rptg_yr_cd in ('2009','2010')
AND trunc(sysdate-to_char(sysdate+1,'D')) BETWEEN T5.START_DATE AND T5.end_date
AND trunc(sysdate-to_char(sysdate+1,'D')) BETWEEN T3.START_DATE AND T3.end_date
AND T4.PRC_BOOK_TYP_CD = 'D'
AND to_date(T1.RPTG_YR_CD||T1.RPTG_YR_MO||'15','YYYYMMDD') between
T4.PROD_PRC_BOOK_FISC_STRT_DT and T4.PROD_PRC_BOOK_FISC_END_DT
AND T9.CURRENT_FLG = 'Y'
AND T7.CURRENT_FLG = 'Y'
AND T9.algn_strc_cd = 'AS11'
AND T7.INDV_CUST_STT_CD <> 'ZZ'
) a INNER JOIN
(
select distinct
T2.CONTR_NO,
T6.CONTR_TIER_TXT,
T2.CONTR_STRNG_NM,
T6.CONTR_NO||T6.CONTR_PRD_NO||T6.CONTR_TIER_NO CONTR_PRD_TIER_NO
from contr@eescip20 T2, contr_prd_tier@eescip20 T6
where T2.CONTR_NO = T6.CONTR_NO
union
select '0000000' CONTR_NO,
'NOT CONTRACTED' CONTR_TIER_TXT,
'NO CONTRACT' CONTR_STRNG_NM,
'00000000000' CONTR_PRD_TIER_NO
from dual
) b ON a.CONTR_PRD_TIER_NO = b.CONTR_PRD_TIER_NO;
SPOOL OFF
exit;
Please help to improve the query. The database version is Oracle 9.2.08.
Explain plan is
Plan
SELECT STATEMENT CHOOSE Cost: 2,695 Bytes: 326,890 Cardinality: 337
31 HASH JOIN PARALLEL_TO_SERIAL :Q195061005 Cost: 2,695 Bytes: 326,890 Cardinality: 337
25 NESTED LOOPS PARALLEL_TO_PARALLEL :Q195061004 Cost: 2,376 Bytes: 256,734 Cardinality: 306
22 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q195061004 Cost: 2,362 Bytes: 173,736 Cardinality: 228
19 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q195061004 Cost: 2,351 Bytes: 119,756 Cardinality: 182
16 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q195061004 Cost: 2,348 Bytes: 106,834 Cardinality: 182
3 TABLE ACCESS BY INDEX ROWID PARALLEL_FROM_SERIAL WHSUSR.DIM_PROD_PRC_BOOK_MV :Q195061000 Cost: 56 Bytes: 3,030,880 Cardinality: 94,715
2 BITMAP CONVERSION TO ROWIDS
1 BITMAP INDEX SINGLE VALUE WHSUSR.XN2_DIM_PROD_PRC_BOOK_MV
15 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q195061004 Cost: 2,292 Bytes: 4,185,810 Cardinality: 7,542
6 TABLE ACCESS BY INDEX ROWID PARALLEL_FROM_SERIAL WHSUSR.DIM_PROD :Q195061001 Cost: 269 Bytes: 43,086 Cardinality: 129
5 BITMAP CONVERSION TO ROWIDS
4 BITMAP INDEX FULL SCAN WHSUSR.XN3_DIM_PROD
14 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q195061004 Cost: 2,023 Bytes: 48,538,451 Cardinality: 219,631
7 TABLE ACCESS FULL PARALLEL_FROM_SERIAL WHSUSR.DIM_BUYG_GRP :Q195061002 Cost: 16 Bytes: 1,166 Cardinality: 11
13 INLIST ITERATOR PARALLEL_COMBINED_WITH_PARENT :Q195061004
12 PARTITION RANGE ITERATOR PARALLEL_COMBINED_WITH_PARENT :Q195061004 Partition #: 16 Partitions accessed #KEY(INLIST)
11 PARTITION LIST ALL PARALLEL_COMBINED_WITH_PARENT :Q195061004 Partition #: 17 Partitions accessed #1 - #12
10 TABLE ACCESS BY LOCAL INDEX ROWID PARALLEL_COMBINED_WITH_PARENT TRANSDATA.FACT_CNSMR_SLS :Q195061004 Cost: 2,006 Bytes: 826,146,890 Cardinality: 7,183,886 Partition #: 17 Partitions accessed #KEY(INLIST)
9 BITMAP CONVERSION TO ROWIDS PARALLEL_COMBINED_WITH_PARENT :Q195061004
8 BITMAP INDEX SINGLE VALUE PARALLEL_COMBINED_WITH_PARENT TRANSDATA.FACT_CNSMR_SLS_BIDX8 :Q195061004 Partition #: 17 Partitions accessed #KEY(INLIST)
18 TABLE ACCESS BY INDEX ROWID PARALLEL_COMBINED_WITH_PARENT WHSUSR.DIM_TM_MV :Q195061004 Cost: 1 Bytes: 71 Cardinality: 1
17 INDEX RANGE SCAN NON-UNIQUE PARALLEL_COMBINED_WITH_PARENT WHSUSR.XN1_DIM_TM_MV :Q195061004 Cost: 1 Cardinality: 1
21 TABLE ACCESS BY INDEX ROWID PARALLEL_COMBINED_WITH_PARENT WHSUSR.DIM_CUST_ALGN_MV :Q195061004 Cost: 1 Bytes: 104 Cardinality: 1
20 INDEX RANGE SCAN NON-UNIQUE PARALLEL_COMBINED_WITH_PARENT WHSUSR.XN0_DIM_CUST_ALGN_MV :Q195061004 Cost: 2 Cardinality: 16
24 TABLE ACCESS BY INDEX ROWID PARALLEL_COMBINED_WITH_PARENT WHSUSR.DIM_CUST_MV :Q195061004 Cost: 1 Bytes: 77 Cardinality: 1
23 INDEX RANGE SCAN NON-UNIQUE PARALLEL_COMBINED_WITH_PARENT WHSUSR.XN1_DIM_CUST_MV :Q195061004 Cost: 2 Cardinality: 2
30 VIEW PARALLEL_FROM_SERIAL WHS_CUBES. :Q195061003 Cost: 319 Bytes: 855,561 Cardinality: 6,531
29 SORT UNIQUE Cost: 319 Bytes: 881,550 Cardinality: 6,531
28 UNION-ALL
26 REMOTE SERIAL_FROM_REMOTE EESCIP20.WORLD Cost: 136 Bytes: 1,509,486 Cardinality: 22,871
27 TABLE ACCESS FULL SYS.DUAL Cost: 2 Cardinality: 1
[Updated on: Mon, 04 October 2010 04:55] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Query running way too slow [message #477762 is a reply to message #477759] |
Mon, 04 October 2010 05:05  |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Get the explain plan in the same format as your other post - that's way too hard to read.
Also is that for the first query or the second?
Please supply explain plans for both queries.
|
|
|
|
| Re: Query running way too slow [message #477767 is a reply to message #477762] |
Mon, 04 October 2010 05:21 |
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
|
Member |
|
|
The explain plan is as follows,
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 337 | 319K| 2699 | | |
|* 1 | HASH JOIN | | 337 | 319K| 2699 | | |
| 2 | NESTED LOOPS | | 306 | 250K| 2380 | | |
| 3 | NESTED LOOPS | | 228 | 169K| 2366 | | |
| 4 | NESTED LOOPS | | 182 | 116K| 2355 | | |
|* 5 | HASH JOIN | | 182 | 104K| 2352 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DIM_PROD_PRC_BOOK_MV | 94715 | 2959K| 56 | | |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | | |
|* 8 | BITMAP INDEX SINGLE VALUE | XN2_DIM_PROD_PRC_BOOK_MV | | | | | |
|* 9 | HASH JOIN | | 7546 | 4089K| 2296 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | DIM_PROD | 129 | 43086 | 273 | | |
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 12 | BITMAP INDEX FULL SCAN | XN3_DIM_PROD | | | | | |
|* 13 | HASH JOIN | | 219K| 46M| 2023 | | |
|* 14 | TABLE ACCESS FULL | DIM_BUYG_GRP | 11 | 1166 | 16 | | |
| 15 | INLIST ITERATOR | | | | | | |
| 16 | PARTITION RANGE ITERATOR | | | | |KEY(I) |KEY(I) |
| 17 | PARTITION LIST ALL | | | | | 1 | 12 |
| 18 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_CNSMR_SLS | 7183K| 787M| 2006 |KEY(I) |KEY(
| 19 | BITMAP CONVERSION TO ROWIDS | | | | | | |
|* 20 | BITMAP INDEX SINGLE VALUE | FACT_CNSMR_SLS_BIDX8 | | | |KEY(I) |KEY(I) |
|* 21 | TABLE ACCESS BY INDEX ROWID | DIM_TM_MV | 1 | 71 | 1 | | |
|* 22 | INDEX RANGE SCAN | XN1_DIM_TM_MV | 1 | | 1 | | |
|* 23 | TABLE ACCESS BY INDEX ROWID | DIM_CUST_ALGN_MV | 1 | 104 | 1 | | |
|* 24 | INDEX RANGE SCAN | XN0_DIM_CUST_ALGN_MV | 16 | | 2 | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | DIM_CUST_MV | 1 | 77 | 1 | | |
|* 26 | INDEX RANGE SCAN | XN1_DIM_CUST_MV | 2 | | 2 | | |
| 27 | VIEW | | 6531 | 835K| 319 | | |
| 28 | SORT UNIQUE | | 6531 | 860K| 319 | | |
| 29 | UNION-ALL | | | | | | |
| 30 | REMOTE | | 22871 | 1474K| 136 | | |
| 31 | TABLE ACCESS FULL | DUAL | 1 | | 2 | | |
---------------------------------------------------------------------------------------------------- |
|
|
|
|
|
| Re: Query running way too slow [message #477852 is a reply to message #477798] |
Mon, 04 October 2010 21:51 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
So, the change from the good-performing query to bad-performing involved joining in an inline-view that involves remote tables.
Generally speaking, SQLs combining local and remote tables are notoriously difficult to tune. Only the most trivial of cases ever work out well.
You really need to make those remote tables local ones, and then index them appropriately. Materialized Views are a good way to replicate the contents of tables across databases, but be aware that -depending on how you set it up - the local copy (the Materialized View) may not necessarily always be up to date.
Talk to your DBA about replication.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Wed May 15 02:31:23 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
