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!
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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) 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 DeveloperCommented:
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 Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
Mark GeerlingsDatabase AdministratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex [***Alex140181***]Software DeveloperCommented:
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 AdministratorCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.