Query taking 40 hours to execute [message #675817] |
Mon, 22 April 2019 03:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6ec38bf5c20839c0c7df90717bf69dde?s=64&d=mm&r=g) |
mjpatil
Messages: 6 Registered: April 2019
|
Junior Member |
|
|
my query is taking long time to execute. we have only query execute access, Please help me in optimizing query.
SELECT
Pvin.CALENDAR_WEEK_START_DT as Vintage_Wk,
sp.PLANNING_PROD_NR,
sp.LOC_CD,
PHor.CALENDAR_WEEK_START_DT as Monday_Dt,
Round(sp.REG_DDS_APO_FCST_QT,0) as DPG,
DIST_DMD_TLB_CONF_QT+DIST_DMD_PLANNED_QT+DEP_DMD_COMP_QT+REG_DDS_APO_FCST_QT AS "Total Demand"
FROM IPD.M_SP_WEEKLY sp
INNER JOIN
(SELECT fq.BW_LOAD_WK, fq.VERSION_TYPE, fq.LOC_CD, fq.PLANNING_PROD_NR
FROM IPD.M_SP_WEEKLY fq
where
fq.BUSINESS_GROUP_ID = '05'
and
NEXT_DAY(fq.BW_LOAD_DT-7,'MON')>=NEXT_DAY(sysdate-42,'MON')
and
NEXT_DAY(fq.BW_LOAD_DT-7,'MON')<NEXT_DAY(sysdate-14,'MON')
GROUP BY fq.BW_LOAD_WK, fq.VERSION_TYPE, fq.LOC_CD, fq.PLANNING_PROD_NR
HAVING (Sum(fq.REG_DDS_APO_FCST_QT)>0)) HF
ON
sp.LOC_CD=HF.LOC_CD
and
SP.PLANNING_PROD_NR = HF.PLANNING_PROD_NR
and
SP.BW_LOAD_WK = HF.BW_LOAD_WK
and
SP.VERSION_TYPE = HF.VERSION_TYPE
INNER JOIN
(SELECT
pv.CALENDAR_WEEK_START_DT,
pv.CALENDAR_SAP_ISO_YEAR_WEEK_CD
FROM PMRDB_MV.DATE_DAY_DIM pv
GROUP BY
pv.CALENDAR_WEEK_START_DT,
pv.CALENDAR_SAP_ISO_YEAR_WEEK_CD
HAVING
(pv.CALENDAR_WEEK_START_DT)>=NEXT_DAY(sysdate-42,'MON')
and
(pv.CALENDAR_WEEK_START_DT)<NEXT_DAY(sysdate-14,'MON')
ORDER BY
pv.CALENDAR_WEEK_START_DT desc) PVin
ON
SP.BW_LOAD_WK = PVin.CALENDAR_SAP_ISO_YEAR_WEEK_CD
INNER JOIN
(SELECT
ph.CALENDAR_WEEK_START_DT,
ph.CALENDAR_SAP_ISO_YEAR_WEEK_CD
FROM
PMRDB_MV.DATE_DAY_DIM ph
GROUP BY
ph.CALENDAR_WEEK_START_DT,
ph.CALENDAR_SAP_ISO_YEAR_WEEK_CD
HAVING
(ph.CALENDAR_WEEK_START_DT)>sysdate-1200
And
(ph.CALENDAR_WEEK_START_DT)<sysdate+1200
ORDER BY
ph.CALENDAR_WEEK_START_DT Asc) PHor
ON
SP.CALENDAR_SAP_ISO_YEAR_WEEK_CD = PHor.CALENDAR_SAP_ISO_YEAR_WEEK_CD
WHERE
sp.BUSINESS_GROUP_ID='05'
and
SP.VERSION_TYPE = 'NRP'
AND
PHor.CALENDAR_WEEK_START_DT>=PVin.CALENDAR_WEEK_START_DT
And PHor.CALENDAR_WEEK_START_DT<PVin.CALENDAR_WEEK_START_DT+280
ORDER BY
PVin.CALENDAR_WEEK_START_DT, PLANNING_PROD_NR, LOC_CD, PHor.CALENDAR_WEEK_START_DT
--moderator edit: added [code] tags, please do so yourself in future.
[Updated on: Mon, 22 April 2019 04:29] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: Query taking 40 hours to execute [message #675823 is a reply to message #675821] |
Mon, 22 April 2019 04:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6ec38bf5c20839c0c7df90717bf69dde?s=64&d=mm&r=g) |
mjpatil
Messages: 6 Registered: April 2019
|
Junior Member |
|
|
This part of the query is taking long time to execute.
SELECT fq.BW_LOAD_WK, fq.VERSION_TYPE, fq.LOC_CD, fq.PLANNING_PROD_NR
FROM IPD.M_SP_WEEKLY fq
where
fq.BUSINESS_GROUP_ID = '05'
and
NEXT_DAY(fq.BW_LOAD_DT-7,'MON')>=NEXT_DAY(sysdate-42,'MON')
and
NEXT_DAY(fq.BW_LOAD_DT-7,'MON')<NEXT_DAY(sysdate-14,'MON')
GROUP BY fq.BW_LOAD_WK, fq.VERSION_TYPE, fq.LOC_CD, fq.PLANNING_PROD_NR
HAVING (Sum(fq.REG_DDS_APO_FCST_QT)>0)
|
|
|
|
Re: Query taking 40 hours to execute [message #675825 is a reply to message #675824] |
Mon, 22 April 2019 05:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Please use [code] tags when you paste code. I have asked you to do this before.
Your exec plan is unreadable. You need to use SQL*Plus, not some GUI tool, and get the plan like this:orclx>
orclx> explain plan for
2 select * from emp where empno=7369;
Explained.
orclx> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
14 rows selected.
orclx>
|
|
|
|
|
|
Re: Query taking 40 hours to execute [message #675842 is a reply to message #675834] |
Tue, 23 April 2019 04:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mjpatil wrote on Tue, 23 April 2019 07:22Please find the execution plan
-------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |
| 1 | FAST DUAL | | 1 | 2 |
-------------------------------------------------
Note
-----
- 'IPD.PLAN_TABLE' is old version
Read what you posted.
What tables does it reference?
Does it reference any of the tables in your query?
If not (and it's very obviously not) why did you post it?
I'm going to give you the benefit of the doubt and assume you're not trolling, but in that case you're not thinking through what you do at all.
Get an explain plan for your query and post that.
|
|
|