RSAN_ODS_DATA_DELETE Extremely slow

[alex_wiebe]

We have a process that loads a number of records into a DSO. It takes well under 15 minutes (lots of calculations). If we need to re-run the process, the process chain performs a selective delete of the previous run. The delete alone takes 12-14 _hours_! CPU, Memory and disk i/o are all very quiet.

How do I trace what it is doing and get it to perform better?

SM50 and detail screen shots attached.

[alex_wiebe]

Quick overview: The process chain first performs a selective delete, then runs a program to insert the records. Both steps are effectively keyed by FISCPER. This makes the job idempotent. Especially if it takes only a few minutes to run.

This week in production, the first run of the period took < 10 minutes, the second took 14 hours.

In our test environment, I setup the first run and it took < 10 minutes. I ran it again and it took 14 hours.

Figured I had a reproducible problem and made the original post above.

This morning, the business ran the job again in production and it took < 10 minutes! (No index, etc changes made).

Our test system was restarted today, and once back I was able to run the job 3 times in a row - back-to-back-to-back and all runtimes were < 10 minutes.

I am not sure a secondary index is the solution here. I will poke at this periodically to see if I can reproduce it.

[Former Member]

Hi,

Did you checked the system log via tcode SM21 selecting the date when it took long time. If any DB error it should show there.

Thanks,

-J

[alex_wiebe]

In DB02 we are noticing warnings around missing statistics for the offending table. We are currently attempting to reproduce.

[Former Member]

Hi,

Is the selection screen variant for the selective deletion program uses the key of the DSO.

If not you can try with creating secondary index on those selection fields.

Thanks

-J

[Former Member]

Hi,

You can activate SQL trace (ST05) during selective deletion

[FCI]

Hi Alex,

Have a look to the DB monitor (DB02), I guess you will see a SQL request running (DELETE ?). Check the where clause and see if if an index could help.

Regards,

Frederic