Performance: Mass delete in the COEP table

[Former Member]

Hi,

we're performing a client copy, but the delete of the old client takes a lot of time (more than 1 day). It seems to be executing the following statement:

DELETE FROM "SAPSR3"."COEP" WHERE "MANDT" = '100' AND ROWNUM <= 7919;

When running the explain in Oracle, we get this output:

So, the CPU cost is 398000.

The Z02 index it seems to use, does contain MANDT, PERNR and OBJNR.

Is there a way we can speed up this delete? (archiving is already disabled)

[JimSpath]

Ignoring the business risk of "archiving disabled", what is your I/O subsystem doing?

Log switch much?

Log write sync time?

[Former Member]

I've created an index with only the MANDT field, restarted the delete and now in 4,5H around 32.000.000 records got deleted. The cost via this index got reduced to 77183.

[Former Member]

Good to hear Gregory, So deletion is faster and you will see copy soon.

Regards,

Harish Karra

[Former Member]

Hi Gregory,

How many processes that you selected for the client copy? Are other processes active and deleting data from different tables?

If yes, let it go and wait for the completion.

You can delete the data from bigger tables by executing delete statement from sqlplus but you need to be more careful while giving MANDT.

Regards,

Harish Karra

[Former Member]

Hi Harish,

the copy itself is running with 8 processes, but in SM50 we can see that only 1 background process is taking care of the deletion. For the rest there are no heavy loads on the system. The processing speed is about 2 million records in 14 hours.

Regards,

Gregory

[Former Member]

Hi Gregory,

I think deletion is done for all other tables other than COEP that is the reason SM50 is showing only one table.

If you can wait, let the copy process go as it is. If you have any time constraint then you may go ahead and delete entries from sqlplus (Make sure you are deleting data only of 100, where MANDT='100').

Regards,

Harish Karra