1 Reply Latest reply on Sep 4, 2014 6:51 AM by user13430807

    Delete Query

    2745276

      Hello All,

       

      Greetings for the day !!

       

      Recently one of our weekly production Job started processing for long time. From the AWR report for the job run time frame we could see that delete query is taking 0.20 seconds for each execution. For every single row deletion the delete query is taking 0.20 seconds.

       

      Basically a select query is executed  on table A based on few condition which will retrieve say 18 k records  whose transactions has to be committed for the week. This 18k records are further divided into a batch of 1000 records  and  in a for loop  each record is passed to a function and if the function returns  success then the record is deleted from the table A . So for each success the row is deleted the delete query is taking around 0.20 sec for each execution. So if there are 18K records the time taken is around 3500 seconds

       

      Also we are collecting the stats on A before the job is triggered on the table A.

       

      Also from the execution plan we could see that the sql statement is ignoring the index and going for a full table scan and the table A has  around 200000 rows.

       

      Please share your thoughts on the same.

       

       

      Thanks and Regards,

      Syed