The Oracle BI Presentation Services caching could be an issue with high volume and large queries.  A solution may be to limit the amount that can be cached. Currently, the default maximum entries are set at 500 for a maximum of 60 minutes. The path of the BI Presentation cache is found in:

1

ORACLE_INSTANCE\tmp\OracleBIPresentationServices\coreapplication_obipsn\obis_temp

One way of working around this would be to limit the caching parameters in the BI Presentation Services by modifying the instanceconfig.xml file in the following path:

1

ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obipsn

1. Open the instanceconfig.xml file

2. Locate the section in which you must add the elements that are  described in the table below

3. Include the elements and their ancestor elements as appropriate, as shown in the following example:

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

4. Save your changes and close the file.

5. Restart Oracle Business Intelligence.

Definitions of Elements in the instanceconfig.xml that need to be changed:

Element: MaxEntries
Description: Specifies the maximum number of open record sets that Presentation Services keeps open at any one time. The minimum value is 3. For systems under significant loads, you can increase this value to 700 or 1000.
Default Value: 500

Element: MaxExpireMinutes
Description: Specifies the maximum amount of time, in minutes, that an entry in the cache can exist before it is removed. Depending on the number of analyses being run, an entry might be removed before the time limit expires.
Default Value: 60 minutes

Element: MinExpireMinutes
Description: Specifies the minimum amount of time, in minutes, that an entry in the cache can exist before it is removed. The setting for CacheMinUserExpireMinutes can force an entry for a particular user to exist for a longer time than that specified by the CacheMaxExpireMinutes element.
Default Value: 10

Element: MinUserExpire  Minutes
Description: Specifies the minimum amount of time, in minutes, that an entry in the cache can exist after it has been viewed by a user.For example, if CacheMaxExpireMinutes is set to 60 minutes and a user views the entry during the 59th minute, the entry exists for that user for an additional 10 minutes. The user can continue paging through the data without requiring a new analysis to be run.
Default Value: 10