Step "'DB Statistics Refresh" of /CPMB/LIGHT_OPTIMIZE is very slow and fails after 2 hours

[Former Member]

The Lite Optimization Data Manager Package (executed via the BW process chain /CPMB/LIGHT_OPTIMIZE) job takes a lot of time, and sometimes is cancelled after 2 hours. In the ST12 trace, a lot of time is spent on table RSSELDONE which appears to be not related to BPC at all. Here the job is checking the status of the requests in the InfoCube to get the lock for  'DB Statistics'.



Reason and Prerequisites:

Newly input data leads the system into generating a new open request, and if it is opened just before 'DB Statistics' step, the lock for 'DB Statistics' cannot be obtained. The system tries the lock 3600 times, and after that (7200 seconds required), this step fails.

Solution:

Implement SAP Note 1709595 Block data input during the whole process of optimize. This Note requires upgrade to the next patch level, but you can manually change the optimization process chain.

The steps in light optimization before this Note:

START->MODIFY->DELETE INDEX->CREATE INDEX->LOAD MODE ON->PLAN MODE ON->ROLL UP->COMPRESS->UPDATE DB STATISTICS->END

The idea here is that the step "UPDATE DB STATISTICS" should start before "PLAN MODE ON". But step "PLAN MODE ON" needs to wait for step  "UPDATE DB STATISTICS" to be finished. After step "UPDATE DB STATISTICS" started, it is OK to change back to "PLAN MODE ON".

When plan mode is changed to "LOAD MODE ON", the user will not able to input data through input schedule. So you can try the process chain below, change the plan mode to "LOAD MODE ON", immediately  trigger the  "UPDATE DB STATISTICS". Meanwhile it will delay 3 seconds and change the plan mode on in parallel. 

 

This process chain has not been tested.



 

Other related SAP Note:

1625384 How to determine which variant step of a BPC process chain has failed

1704602 - Light Optimize fails with error "RSSM_PROCESS_DBSTAT terminated because InfoCube/DSO Object [Tech.Name] could not be locked"

1632608 - Lite Optimize in BPC NW fails on the 'Plan Mode On' variant step