We help IT Professionals succeed at work.

Oracle Insert Running longer!

147 Views
Last Modified: 2018-09-17
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!
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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***]Software Developer
CERTIFIED EXPERT

Commented:
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
Database Administrator
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
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 ;-)
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions