Avatar of MIHIR KAR
MIHIR KAR
Flag for India asked on

Oracle Insert Running longer!

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);

Open in new window


Please let me know if you need more info.

Thanks!
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Mark Geerlings

8/22/2022 - Mon
lcohan

I would use the EXISTS instead of the IN like:

...
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 EXISTS 
                (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 
	WHERE SW.DMDUNIT= DMDUNIT, SW.RIN_LOC=RIN_LOC, SW.DLV_LOC= DLV_LOC, SW. MDS_STS= MDS_STS, SW. SLS_TRS_TYP= SLS_TRS_TYP,  SW.ZIP_CD= ZIP_CD, SW.TRS_DT= TRS_DT,  SW.SCM_IND= SCM_IND, SW. SLS_DAT_SRC= SLS_DAT_SRC);

Open in new window

slightwv (䄆 Netminder)

You might be waiting for locks.  Going from memory the APPEND hint locks the table.  If it cannot get the lock, it will wait.
Alex [***Alex140181***]

You might be waiting for locks.  Going from memory the APPEND hint locks the table.  If it cannot get the lock, it will wait.
Affirmative!

Please check for locks and/or deadlocks:
http://atozoracle.blogspot.com/2009/01/how-to-check-any-deadlock-and-other.html
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Alex [***Alex140181***]

I agree with Mark; as an add-on, please have a look here:

http://www.dba-oracle.com/t_table_fragmentation.htm

Especially the "Fixing table fragmentation" chapter ;-)
SOLUTION
Mark Geerlings

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.