Understanding Oracle BI EE Presentation Server Cache(s)

Within the Oracle BI System, multiple components provide a caching mechanism to re-use previously executed Reports to improve query performance of subsequent requests. The following diagram provides an Overview of the main Architecture and main caching components:

Oracle BI Server Cacheing cropped

By default, the Oracle BI Presentation Server Cache is not actived. However, the Oracle BI Presentation Server additionally provides a Oracle BI Presentation Server Cache which can not be deactived. This “Cursor” Cache can (only) be viewed from the Session Monitor Administration Page http://localhost:9704/analytics/saw.dll?Sessions:

session monitor

Thus, even when the Oracle BI Presentation Server Cache is not actived, subsequent execution of the same Analysis maybe retrieved from the Oracle BI Presentation Server Cursor Cache which may often mislead the perception of cacheing.

For the first execution of an Analysis a new Cursor and Cursor Cache maybe created eventually:

cache monitor

A subsequent execution of the same Report will return instantly (even with no Presentation Server Cache configured) since the Report can be retrieved from the Presentation Server Cursor Cache. No additional Cursor will be created and only the time of execution will be updated for the previous Cursor.

cache monitor 2

As said before, the Cursor Cache can not be disabled and the Cursor Cache can only be purged manually from the Session Monitor. However, a Report/Analysis can be foreced to bypass the Presentation Server Cursor Cache (and Presentation Server Cache) by checking the bypass Presentation Server Cache option on the Advanced Tab of a Analysis.

bypass

The disk directory of the Cursor Cache is not documented by Oracle, but they are stored under the query cache folders of:

C:\oracle\middleware\instances\instance1\tmp\OracleBIPresentationServicesComponent\coreapplication_obips1\querycache_0

Unbenannt2

Which places a result set file for the respective Presentation Server Cursor Cache:

Unbenannt4

To active the regular Presentation Server Cache, the instanceconfig.xml needs to be edited. The instance config can typically found here (for 11g):

C:\oracle\middleware\instances\instance1\config\OracleBIPresentationServerComponent\coreapplication_obips1

To active the Caching the following xml elements need to be added to the file:

<ServerInstance>
  <Cache>
    <Query>
      <MaxEntries>100</MaxEntries>
      <MaxExpireMinutes>60</MaxExpireMinutes>
      <MinExpireMinutes>10</MinExpireMinutes>
      <MinUserExpireMinutes>10</MinUserExpireMinutes>
    </Query>
  </Cache>
<ServerInstance>

After restarting the Presentation Server, the Cache Files will be created in the following directory:

C:\oracle\middleware\instances\instance1\tmp\OracleBIPresentationServicesComponent\coreapplication_obips1\obis_temp

Summary: Even with no Presentation Server Caching enabled, the Presentation Server Cursor Cache will cache result sets to fulfill subsequent Analysis requests from the Client.

Advertisements

Disable Cache on Query Level

When testing it is often useful to disable the Cache in order to avoid Cache Hits and to see the SQL Generated by the BI Server. This can be achieved with the following code placed in the advanced Tab of BI Ansers 11g in the Prefix of the SQL Area:

SET VARIABLE DISABLE_CACHE_HIT=1;