We have a number of process chains that run during the day loading data from our SAP R/3 system into SAP BW. They usually take about thirty minutes to run. This week they've been taking over an hour.
We compared this weeks runs against last weeks runs using /SSA/BWT but the runtimes of the individual load and activation steps are roughly the same.
The one thing that we've noticed is that the chain appears to pause ("On Hold") while SAPLRSSM deletes data from TESTDATRNRPART0.
Haven't managed to find much information about this online, but it seems to be a temporary table that is used when data is moved from the PSA tables to an InfoObject.
Can anyone tell us more about this table, and give us any pointers why it could be taking longer than normal?
Best Answer
In the end we discovered (with help from SAP) that the reason that deleting from this table was taking longer than normal (and then causing the process chain take longer to complete) is that the Cost Based Optimiser (CBO) within the Oracle database was deciding not to use the index for this table.
And the reason it wasn't using the index is that the DBMS_STATS job that produces statistics about the table was running at a time when the table was empty.
The solution was to hard-code statistics for this one table, so that the CBO always picks an execution plan that uses the index. More detail in SAP Note 1020260.