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