About
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 BI Server Query Cache
The following article talks about the BI Server Query Cache.
The query cache consists of :
- cache storage space,
- cache retrieval
- and cache detection in query compilation
This is an:
- on-demand,
- shareable
- and secure
cache that can supports:
- subsets,
- aggregations
- and derivations.
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 a data set cache managed by the BI Server.
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 OBIEE 10G/11G - Logical Query Result Cache Parameters to YES.
Articles Related
Management
Purge it
When you have some trouble, you may want to purge it or in a development environment simply to disable it.
Why caching doesn't work
Be careful, the server cache has several reasons to not cache a logical query.
Cache Location
- 11g: [middlware_home]/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/cache
- 10g: OracleBIData\cache
Cache Seeding
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).
Configuration
You can configure it with the help of global parameters and of BI Server parameters.
Cache Sharing Across Users
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.
Shared Cache (Global Cache)
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:
- purging events,
- seeding events (often generated by agents),
- and result sets that are associated with seeding events.
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.
Data source
XML
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.
- For URLs that begin with http:// or https://, the gateway will refresh when it detects that the interval has expired.
- For URLs that reside on a local or network drive, the gateway will refresh when the interval has expired and the system detects that the URLs have been modified.