Query caching is a persistent cache mechanism. When you ask for data with a query, the data are retrieved from the cache instead to ask again the data source.
OBIEE has two query caches:
The following article talks about the BI Server Query Cache.
The query cache consists of :
This is an:
cache that can supports:
The cached results act much like precomputed aggregates in that they can dramatically accelerate query performance.
All security attributes remain in force for the cached results, so users can access only the data they are authorized to view.
To ensure the information stored in the cache is current with the underlying databases, the BI Server administrator can set up cache purging rules.
The query cache is not shared across clustered servers (except iBots).
Caching occurs by default at the subrequest level, which results in multiple cache entries for some SQL statements. Caching subrequests improves performance and the cache hit ratio, especially for queries that combine real-time and historical data. To disable subrequest caching, set DISABLE_SUBREQUEST_CACHING to YES.
When you have some trouble, you may want to purge it or in a development environment simply to disable it.
Queries (dashboards and reports for BI Presentation Service) can be scheduled with Delivers and the results cached to make information instantly accessible.
With the help of an agent, you can seed the cache. (It seems that you need to have a BIAdmin privilege to do it).
You can configure it with the help of global parameters and of BI Server parameters.
If shared logon has been enabled for a particular connection pool, then the cache can be shared across users and does not need to be seeded for each user. If shared logon has not been enabled and a user-specific database login is used, then each user generates their own cache entries.
In a clustered environment, Oracle BI Servers can be configured to access a shared cache called the global cache.
This global cache resides on a shared file system storage device and stores:
The seeding and purging events are sorted by time and stored on the shared storage as a logical event queue. Individual Oracle BI Server nodes push to and pull from the logical event queue. Each Oracle BI Server still maintains its own local query cache for regular queries.
Typically, XML data sources are updated frequently and in real time. Setting a refresh interval for XML data sources is analogous to setting cache persistence for database tables. The refresh interval is a time interval after which the XML data sources are to be queried again directly, rather than using results in cache. This refresh interval is specified on the XML tab of the Connection Pool dialog box.
The default interval setting is Infinite, meaning that the XML data source is not automatically refreshed.
The refresh interval setting specifies the time interval after which the Oracle BI Server XML Gateway connection will be refreshed.