troubleshooting Question

Oracle Insert Running longer!

Avatar of MIHIR KAR
MIHIR KARFlag for India asked on
Oracle DatabaseSQL
6 Comments2 Solutions152 ViewsLast Modified:
Hi Expert,

I have below insert which took longer in production Machine. Sometimes it got stuck. Please help me on this.

I used to kill and re run the job but the query  stuck at the same insert query. I checked the "select" and found that there isn’t any record being pulled by the sub query so ideally it is inserting 0 records then also the query is stuck. Not sure why it is running longer.

/*INSERTING INTO SLS_ERROR WHEN THE QTY_ACT IS LESS THAN 0 */
INSERT /*+ append */ INTO STSC.SLS_ERROR
        (DMDUNIT
        ,DMDGROUP
        ,RIN_LOC
        ,DLV_LOC
        ,SLS_DAT_SRC
        ,SLS_TRS_TYP
        ,ZIP_CD
        ,MDS_STS
        ,TRS_DT
        ,WK_STA_DT
        ,QTY_ACT
        ,QTY_LST
        ,QTY_USR
        ,QTY_LIFT
        ,SLL_AMT
        ,SLS_AGG_MTD
        ,ON_PROMO_FL
        ,SCM_IND
       ,ROW_CRT_DT
        ,ROW_LST_UPD_SRC
        ,ROW_LST_UPD_DT
        ,ERROR_CD
        ,ERROR_TS
        ,STEP_NO
        ,ERROR_DESCR)
--  added use_nl hint because for some reason Oracle chose to do hash join with SH table first and taking nearly 20 hours to complete.
SELECT /*+ index(sh) use_nl(sh) */ -- 3/22/12 PawelS - changed hint to index(sh) because of limiting records to only those from SLS_WRK
        DMDUNIT
        ,DMDGROUP
        ,RIN_LOC
        ,DLV_LOC
        ,SLS_DAT_SRC
        ,SLS_TRS_TYP
        ,ZIP_CD
        ,MDS_STS
        ,TRS_DT
        ,WK_STA_DT
        ,QTY_ACT
        ,QTY_LST
        ,QTY_USR
        ,QTY_LIFT
        ,SLL_AMT
        ,SLS_AGG_MTD
        ,ON_PROMO_FL
        ,SCM_IND
       ,ROW_CRT_DT
        ,ROW_LST_UPD_SRC
        ,SYSDATE
        ,200
        ,SYSDATE
        ,80
        ,'ZERO OR NEGATIVE QUANTITY'
        -- - added alias sh for the hint above to work
FROM STSC.UDT_SLS_HIST sh
WHERE ROW_LST_UPD_SRC='HFMADJ'
AND QTY_ACT<=0
--  - added limiting the search to records from SLS_WRK
AND (DMDUNIT, RIN_LOC, DLV_LOC, MDS_STS, SLS_TRS_TYP, ZIP_CD, TRS_DT, SCM_IND, SLS_DAT_SRC) IN
                (SELECT /*+ PARALLEL(SW,18) FULL(SW) */
                        DMDUNIT, RIN_LOC, DLV_LOC, MDS_STS, SLS_TRS_TYP, ZIP_CD, TRS_DT, SCM_IND, SLS_DAT_SRC
                 FROM STSC.SLS_WRK SW);

Please let me know if you need more info.

Thanks!
ASKER CERTIFIED SOLUTION
Mark Geerlings
Database Administrator, retired

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros