Home » SQL & PL/SQL » SQL & PL/SQL » Query taking 40 hours to execute (SQL developer)
Query taking 40 hours to execute [message #675817] Mon, 22 April 2019 03:18 Go to next message
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 #675820 is a reply to message #675817] Mon, 22 April 2019 04:37 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

You need to provide a lot more information, starting with the execution plan.
However, one point is clear: you should remove the ORDER BY clauses in the sub-queries, they make it impossible o merge the sub-queries into the outer query.
Re: Query taking 40 hours to execute [message #675821 is a reply to message #675820] Mon, 22 April 2019 04:41 Go to previous messageGo to next message
mjpatil
Messages: 6
Registered: April 2019
Junior Member
Thanks for the reply.

I dont have access to run execution plan on SQL developer.

I am not getting how to merge the sub-queries into the outer query. It could be great if you provide me modified query.

Thanks for the help.

Re: Query taking 40 hours to execute [message #675822 is a reply to message #675821] Mon, 22 April 2019 04:47 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
It would be great if you provided me with a purchase order to cover my time as I do your job for you Smile

I've already told you one obvious change. You had better talk to your DBA about getting exec plans. You can't tune SQL without them.
Re: Query taking 40 hours to execute [message #675823 is a reply to message #675821] Mon, 22 April 2019 04:48 Go to previous messageGo to next message
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 #675824 is a reply to message #675822] Mon, 22 April 2019 05:13 Go to previous messageGo to next message
mjpatil
Messages: 6
Registered: April 2019
Junior Member
Attached HTML file is the execution plan of the query.
  • Attachment: exeplan.png
    (Size: 112.21KB, Downloaded 95 times)
Re: Query taking 40 hours to execute [message #675825 is a reply to message #675824] Mon, 22 April 2019 05:28 Go to previous messageGo to next message
John Watson
Messages: 7933
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 #675834 is a reply to message #675825] Tue, 23 April 2019 01:22 Go to previous messageGo to next message
mjpatil
Messages: 6
Registered: April 2019
Junior Member
Please 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
Re: Query taking 40 hours to execute [message #675835 is a reply to message #675834] Tue, 23 April 2019 01:27 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
I think you are trolling: being deliberately stupid in an attempt to make people angry.

Goodbye.
Re: Query taking 40 hours to execute [message #675836 is a reply to message #675835] Tue, 23 April 2019 01:36 Go to previous messageGo to next message
mjpatil
Messages: 6
Registered: April 2019
Junior Member
bye

[Updated on: Tue, 23 April 2019 02:02]

Report message to a moderator

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
cookiemonster
Messages: 13626
Registered: September 2008
Location: Rainy Manchester
Senior Member
mjpatil wrote on Tue, 23 April 2019 07:22
Please 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.
Previous Topic: pl/sql procedure compile error PLS-00103
Next Topic: Inserting %RowType objects "faster"
Goto Forum:
  


Current Time: Sat Jun 15 20:28:35 CDT 2019