Selective Deletion in DSO is taking lot of time
Selective Deletion in DSO is taking a lot of time to complete.On 19th when the Program ran for 166 million records it took 7 hrs to complete whereas when the program ran on 20th for 320 million records it took only 45 mins to complete.The difference that I observed between the job logs was that on the 20th,the index deletion took place before the records got deleted selectively through the program and the index deletion did not take place on the 19th.What could be the reason behind this? Both the times the same standard program was executed through the process chain and there were no other processes running when the selective deletion step was running.
There are no secondary indexes created on the DSO.
The reason was that according to the note given by Vengal there is a PARAMETER is called BW_SELDEL_PERC_THRES which specifies the percentage (integral) from which the deletion is now performed using COPY/RENAME instead of DELETE.This parameter is compared with ([number of records to be deleted]/[number of all records in the table])*100.In our system this parameter was set to 40.So if the ([number of records to be deleted]/[number of all records in the table])*100<40 the system follows the Delete Procedure in which data is deleted in chunks of 1 lac records.And if ([number of records to be deleted]/[number of all records in the table])*100>40 the system follows COPY/RENAME procedure in which data gets deleted at once instead of in chunks of 1 lac records.As a result of this the COPY/RENAME procedure takes much less time compared to the Delte procedure.In our case on the 20th the percentage exceeded 40 hence COPY/RENAME procedure was used and so the deletion finished in 45 mins.But on the 19th it was less than 40 hence DELETE procedure was used to delete the data and hence it took more than 7 hrs for the deletion job to finish.