What should I check why Oracle server takes more then 20 sec to return UNIQUE constraint violation error for specific data?

One of our processes is processing over 30000 data one day with multi process and some time gets UNIQUE constraint violation error in 1 sec

but it takes more then 20 sec to return UNIQUE constraint violation error for specific data.

Query is same as below. (Modified only table name)

MERGE  
INTO  TableA S
USING (
SELECT NVL(:sccm_cd , ' ') SCCM_CD
 , NVL(:oder_dt , ' ') ODER_DT
 , NVL(:mrkt_dstn_cd, ' ') MRKT_DSTN_CD
 , NVL(:oder_no , ' ') ODER_NO
 , NVL(:cncd_unpr , 0) CNCD_UNPR
 , B.SLBY_FEE_GRD_CD
 , B.ACCT_MNGR_EMPL_NO
 , C.AO_FEE_GRD_CD
  FROM DUAL A
 , TableB B
 , TableC C
 WHERE 1 = 1
   AND B.SCCM_CD = :sccm_cd
   AND B.ACNO = :acno
   AND C.SCCM_CD( ) = B.SCCM_CD
   AND C.EMPL_NO( ) = B.ACCT_MNGR_EMPL_NO 
      ) T
ON (     S.sccm_cd       = T.sccm_cd
     AND S.oder_dt         = T.oder_dt
     AND S.mrkt_dstn_cd = T.mrkt_dstn_cd 
     AND S.oder_no        = T.oder_no
     AND S.cncd_unpr     = T.cncd_unpr 
     )
WHEN MATCHED THEN
     UPDATE 
     SET S.cncd_qty       = S.cncd_qty         NVL(:cncd_qty     ,0)
           , S.slby_fee        = S.slby_fee          NVL(:slby_fee      ,0)
           , S.slby_fee_srtx = S.slby_fee_srtx   NVL(:slby_fee_srtx,0)
           , S.idx_fee_amt   = S.idx_fee_amt     NVL(:idx_fee_amt ,0)
           , S.cltr_fee          = S.cltr_fee           NVL(:cltr_fee        ,0)
           , S.trtx                = S.trtx                 NVL(:trtx             ,0)     
           , S.otc_fee        = S.otc_fee                 NVL(:otc_fee             ,0)     
           , S.wht_fee       = S.wht_fee                 NVL(:wht_fee             ,0)                                   
WHEN NOT MATCHED THEN
     INSERT (
              sccm_cd
        , oder_dt
        , mrkt_dstn_cd
        , oder_no
        , cncd_unpr
        , acno
        , item_cd
        , slby_dstn_cd
        , md_dstn_cd
        , cncd_qty
        , stlm_dt
        , trtx_txtn_dstn_cd
        , proc_cmpl_dstn_cd
        , item_dstn_cd
        , slby_fee_grd_cd
        , slby_fee
        , slby_fee_srtx
        , idx_fee_amt
        , cltr_fee
        , trtx     
        , wht_fee        
        , otc_fee
            , acct_mngr_empl_no
            , ao_fee_grd_cd
            
        )
        VALUES
        (     T.sccm_cd
            , T.oder_dt
            , T.mrkt_dstn_cd
            , T.oder_no
            , T.cncd_unpr
            , :acno
            , :item_cd
            , :slby_dstn_cd
            , :md_dstn_cd
            , NVL(:cncd_qty     ,0) 
            , DECODE(:mrkt_dstn_cd, 'TN', T.oder_dt, :stlm_dt)
            , :trtx_txtn_dstn_cd
            , '0'
            , :item_dstn_cd
            , NVL(:slby_fee_grd_cd, T.SLBY_FEE_GRD_CD)
            , NVL(:slby_fee        ,0)
            , NVL(:slby_fee_srtx ,0)
            , NVL(:idx_fee_amt   ,0)
            , NVL(:cltr_fee          ,0)
            , NVL(:trtx                ,0)
            , NVL(:wht_fee     , 0)                        
            , NVL(:otc_fee    , 0)
            , T.acct_mngr_empl_no
            , T.ao_fee_grd_cd
            
          )

CodePudding user response：

There could be multiple reasons for it. I will list here some of the possible causes for this behavior.

Concurrency issue

Your insert might be waiting for other operations, like other inserts or updated or deletions.

Network issues

It is possible that for some reason your network is overwhelmed with requests or, if the server is remote, this could be an internet speed issue as well.

Server load

The server might be overwhelmed with lots of jobs to do.

Slow query

It's also possible that the select you use in your insert command is very slow. It would make sense to test its speed. Also, it would make sense to test insert speed as well.