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!
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.
Please let me know if you need more info.
Thanks!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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);
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
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 ;-)
http://www.dba-oracle.com/t_table_fragmentation.htm
Especially the "Fixing table fragmentation" chapter ;-)
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial