Selective Deletion in DSO is taking lot of time

[shalaka_golde]

Hi,

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.

Regards,

Shalaka

[shalaka_golde · Accepted solution]

I have resolved the issue by myself.

[shalaka_golde · Accepted solution]

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.

[former_member186053 · Accepted solution]

Hi Shalaka,

Thanks for the initiative and explained the root cause in a simple way, will be useful for future reference.

Cheers,

Vengal.

[former_member186053]

Hi Shalaka,

Below sap note gives you an idea how this selective deletion works.

792435 - Selective deletion: Using temporary tables

Regards,

Vengal.

[shalaka_golde]

Hi Vengal,

Can you please paste the details of this note here as I do not yet have access to service.sap.com?

Regards,

Shalaka

[former_member186053]

Hi,

Go through reason and prerequisites.

Symptom

After you selectively delete data from an InfoCube or an Operational Data Store (ODS), there may be indexes that were deleted previously, or technical table settings are no longer as you expect.

Other Terms

Selective deletion, technical settings, INITRANS, indexes

Reason and Prerequisites

Deleting data from a table is one of the most expensive operations (in terms of the time and resource consumption for rollback). This is because if you delete using a DELETE statement, even if deletion of the last record fails, the database must be able to restore the previous table status using ROLLBACK.

If you want to delete data (for example, from the F table of an InfoCube), in some circumstances, it may be better to select the data that is not to be deleted in a temporary table, to delete the original table and then to rename the temporary table accordingly.

The copying of the data that is not to be deleted into a temporary table can be carried out more efficiently with statements optimized for the relevant database, such as CREATE TABLE ... AS SELECT ..., than deletion using the DELETE statement.

In tests, a threshold of 10% has been found to be useful. Therefore, if more than 10% of the data is deleted in a BW InfoCube or an ODS table, the COPY/RENAME procedure is applied. The value of 10% has been firmly set in this case.

There may be situations where the value is not ideal, or even situations where the COPY/RENAME procedure should be completely deactivated. This may be because, for instance, certain changes to the fact tables performed manually are to be retained that cannot be changed using BW methods.

The change in this note provides a new parameter for the RSADMIN table through which this threshold can be configured.
The PARAMETER is called BW_SELDEL_PERC_THRES and specifies the percentage (integral) from which the deletion is now performed using COPY/RENAME instead of DELETE. The relationship is compared [number of records to be deleted]/[number of all records in the table] for BW_SELDEL_PERC_THRES/100 (therefore figures in the area of 0 - 1). If the parameter is not set, the default value of 10 is valid. Insert the parameter with program SAP_RSADMIN_MAINTAIN into the RSADMIN table (or use the program change or delete it).

Example: If the parameter is set to 50 and 5 of 100 records are deleted from the F table, you delete using DELETE. If, for instance, 110 records of 200 existing records are deleted from the E table, you delete using COPY/RENAME.

This note was provided with a correction for BW7 on July 17, 2007.

After the deletion, the tables are in the same tablespace as before.

Regards,

Vengal.

[shalaka_golde]

Hi Vengal,

I read the note and what I understood was if the parameter exceeds 10% the COPY/RENAME procedure is applied.So in that case is data deleted in one go instead of in chunks of 100000 which usually happens?

Regards,

Shalaka

[former_member186053]

Hi Shalaka,

May be that would be the reason in your case. And also note discuss about some parameter settings that impact the performance.

Regards,

Vengal.

[shalaka_golde]

Also please note that on 19th the data got deleted in chunks of 100000 records whereas on the 20th the records got deleted in one go.What could be the reason behind this?

[Former Member]

Hi,

The reason might be the lack of Work processors during this time. Please check.

Thanks and Regards,

Mahesh

[shalaka_golde]

No.As I already mentioned that there were no processes running when this process chain was running on both the days.