Oracle Insert Running longer!

MIHIR KAR
MIHIR KAR used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

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

Most Valuable Expert 2012
Distinguished Expert 2018

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.
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Database Administrator
Commented:
Are you aware of what the "INSERT /*+ append */ ..." syntax does to the blocks of your table?  This forces Oracle to start adding new records above the "high water mark" in the table (that is: in new, empty blocks beyond any blocks that already have some records, whether those blocks are full, or not).  If the number of new records that you add with this syntax is large enough each time to fill at least a few data blocks, this may be OK.  But, if the number of records being added this way doesn't even fill one data block, and you do this frequently, you will have a table that includes lots of wasted (empty) bytes in partially-filled blocks.  This will have an impact on query performance because your buffer cache then is partly used by empty bytes that don't actually contain data.
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
Commented:
Yes, Oracle inserts can be faster if you use the INSERT /*+ append */ ..." syntax.  This is especially true if your table and indexes are set to "nologging",  Be aware that "nologging" in Oracle only applies to a very small set of operations, like: INSERT /*+ append */.  All "normal" SQL operations in the table will be logged regardless of your logging/nologging setting on the table.

In my experience, it is very rare that the INSERT /*+ append */ ..." syntax is a good idea because of its impact on *ALL* future query performance in the table.  In rare cases when you are loading a large percentage of a table's records, this syntax can be a good idea.  For daily jobs, it is usually not a good idea.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial