Intermittent performance issue of refreshing BPC query- long runtime on table RSR_CACHE_FFB

[Former Member]

BPC front users have experienced intermittent poor performance on MDX queries. When you open a query at first time, it may take some time because OLAP engine will fetch the data from DB. If result of query was saved in the OLAP cache then a second query efresh can directly fetch the data from OLAP cache which is much faster. From user point of view, the performance of query is not consistent.

 

But in special scenarios, reading data from OLAP cache could be much slower than reading data from DB. In transaction SM66, you can find most of the work process hanging on reading table RSR_CACHE_FFB.



If you record a ST12 trace, select RSR_CACHE_FFB takes 77% of the total backend runtime.



Talbe is small and there is nothing unusal in the SQL execution plan.



In ST05 trace, a lot time is spent on LOBSTAT.



 

 

In Transaction ST10, you may find buffer status of table RSR_CACHE_FFB is pending.

 

Further test:

Firstly you can find the MDX statement of BPC query from transaction SLG1.(Application logs)

Then goto Transaction MDXTEST->Menu-> Set Debug Flags, you will find the option "Do not use Cache"



 

Test 1: Run MDX statement without cache.

 

Most of the runtime is spent on fetching data from E and F fact table of BPC Infocube.



Test 2: Run MDX statemetn with cache, you will find similar trace as:



 

Table RSR_CACHE_FFB is table to store persistent OLAP Cache result when you activate "Persistence Mode" in Transaction RSRCACHE.

Performance is better after we deleted all OLAP cache in transaction RSRCACHE.

Performance is better when use OLAP Cache Mode “Main memory cache without swap”.

Checked the network between application server and DB server.

 

Summary:

Scenarios from Integrated Planning are prone to cause very large numbers of cache entries for the corresponding Plan Data Queries. In such situations the cache table may got corrupted and performance may become throttled heavily.

 

It is not only for BPC, but also for other planning tools.

 

Solution:

Delete all OLAP cache and then implement SAP Note 1777509  to fix the problem.

 

SAP Note 1711747 can help restrict the number of entries in OLAP Cache.