To enable the Oracle BI Server Cache, the Cache needs to be activated within the Oracle Enterprise Manager:
Additionally, the NQSConfig file contains additional parameter to steer the entry and usage of the Oracle BI Server Cache:
If the Cache is enabled, the created Cache entries can be viewed on the hard disk, but also the BI Admin Tool. The Cache files are placed in:
These Cache entries hold the information about the logical SQL used to created the Cache and the returned result set for the query. The Cache Entries can also be viewed from the BI Admin Tool to provide further information about the respective Cache Entry and e.g. the last usage:
Some Reports/Analysis may not qualify for a creation of a Oracle BI Server Cache entry. This can also be seen in the Session log, with the following statement:
The logical query block fail to hits or seed the cache in subrequest level due to [[ only one subrequest ]]
In general, this is due to one of the following reasons given from the Official Documentation:
- Noncacheable SQL element. If a SQL request contains CURRENT_TIMESTAMP, CURRENT_TIME, RAND, POPULATE, or a parameter marker, then it is not added to the cache.
- Noncacheable table. Physical tables in the Oracle BI Server repository can be marked “noncacheable.” If a query references any noncacheable table, then the query results are not added to the cache.
- Cache hit. In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache.
- The exception is query hits that are aggregate roll-up hits. These are added to the cache if the NQSConfig.INI parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to YES.
- Result set is too big.This situation occurs when you exceed the size set in DATA_STORAGE_PATHS, or if you have rows in excess of the number set in MAX_ROWS_PER_CACHE_ENTRY. See Section A.3.2, “DATA_STORAGE_PATHS” and Section A.3.3, “MAX_ROWS_PER_CACHE_ENTRY” for more information.
- Query is canceled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through timeout.
- Oracle BI Server is clustered. Queries that fall into the ‘cache seeding’ family are propagated throughout the cluster. Other queries continue to be stored locally. Therefore, even though a query might be put into the cache on Oracle BI Server node 1, it might not be on Oracle BI Server node 2.
Additionall one of the following cases may also prevent from entries being created within the Cache:
- Caching is not enabled (CACHE=N in the NQSConfig.INI file)
- The WHERE clause in the logical SQL is not semantically the same, or not a logical subset of a cached statement
- the columns in the SELECT list not yet exist in the cached query, or they are not able to be calculated from them
- It has not equivalent join conditions, so that the resultant joined table of any incoming query is not the same as (or a subset of) the cached results
- If DISTINCT is used, the cached copy has to use this attribute as well
- Aggregation levels are not compatible, since they either have to be the same or more aggregated than the cached query
- No further aggregation (for example, RANK,MAX) can be used in the incoming query
(sub request / sub Report)
- Any ORDER BY clause has to use columns that are also in the cached SELECT list
As an example, the following (main) Report:
Is based on the results of another Analysis, which also uses an Aggregation:
The main Report is executed, but no Cache Entry is created:
This can also be analyzed from the Session log for the SQL for this statement:
Since, the Sub Report also contains an Aggregation, no Cache Entry will be created for the Report. This can be seen in the Session log (may be required to set log level to 3 or even 4 to see below message):
To allow the Oracle BI Server to create a Cache Entry, the Analysis needs to be re-written. E.g. the Sub Report needs to be removed and needs to be part of the (main) Report. In the above case, by creating the Aggregate in the (main) Report and by the “BY” clause within the formula to specify the Group element.
Once, a Cache Entry is created for one User, it may also be shared to fulfill a request from another User. This can be seen from the Oracle BI Admin Tool within the Cache Manager Screen:
Sometimes Users are also mislead in terms of Cache usage since Oracle BI provides two cache systems:
And even if the Oracle BI Presentation Server Cache is not enabled (by default not enabled) the Oracle BI Presentation Server still has a Cursor Cache which can be viewed from the Session Monitor (http://localhost:9704/analytics/saw.dll?Sessions) and which can not be disabled and only purged manually from the Session Monitor “Cancel All Cursor” Button.