Disabling the Query Cache for BICS

Similar to OBIEE the Query Cache can be disabled on Report Level within BICS.

However, the approach is slightly different to OBIEE, since BICS doesnt provide the Pre or Post-fix box on the Advanced Tab of the Report Builder (Answers).

The below example shows the configuration and Behaviour of disabling the Query Cache in a Step-by-step approach:

When creating a (simple) Query, a Cache entry is created for the returned result set:

image

For any subsequent Report, the Cache is analysed if it can be used to fulfill the subsequent Report. E.g. when adding a Filter for Channel “Catalog”, the above Result set could be used:

image

Giving the following Result:

image

When analysing the Query log, the Cache hit is also shown in the log itself:

image

In order to bypass the Query Cache, the Query Cache needs to be disabled for the respective Report by adding the following command (setting of internal server variable) into the Report:

SET VARIABLE DISABLE_CACHE_HIT=1;

To do this, the SQL displayed on the Advanced Tab of the Report Builder (Answers) needs to be copied to the clipboard and clicking the new Analysis button.

image

The previously copied SQL needs to be pasted in the popup Windows with the Disable Cache Hit command as prefix and the 0 needs to be removed from the pasted SQL:

image

The Presentation Server Cache can also by bypassed by checking the check-box at the top of the screen. When navigating to the Results tab of Answers, the Report is run again and the Query log can be checked again:

image

The Query log also shows that now the Query is not retrieved from the Cache, but retrieved from the Database.

A Technote also exist for further information on Oracle Support.

Advertisements