About the Oracle BI Server Query Cache
You can configure the Oracle BI Server to maintain a local, disk-based cache of query result sets (query cache).
The query cache enables the Oracle BI Server to satisfy many subsequent query requests without accessing back-end data sources, thereby increasing query performance.
As updates occur on the back-end databases, the query cache entries can become stale. Therefore, you must periodically remove entries from the query cache using one of the following methods:
-
Manually. In the Oracle BI Administration Tool, in the Manage menu, select Cache to open the Cache Manager. The Cache Manager provides the most flexibility in choosing which cache entries to purge and when to purge them, but it requires manual intervention. See Using the Cache Manager.
-
Automatically. In the Administration Tool, you can disable cache for the system, set caching attributes for a specific physical table, and use Oracle Business Intelligence event tables to purge cache automatically. See Monitoring and Managing the Cache.
-
Programmatically. The Oracle BI Server provides ODBC-extension functions for purging cache entries programmatically. These functions give you the choice and the timing flexibility of the Cache Manager with the automation of event tables. You can write your own scripts to call these functions at times that fit your needs. See Purging and Maintaining Cache Using ODBC Procedures.
The parameters that control query caching are located in Fusion Middleware Control and in the NQSConfig.INI file, described in Configuration File Settings. See Using Agents to Seed the Oracle BI Server Cache.
This section contains the following topics:
Query Cache Architecture
The query cache consists of cache storage space, cache metadata, and cache detection in query compilation.
The process of the Oracle BI Server accessing the cache metadata is very fast. If the metadata shows a cache hit, then the bulk of the query processing is eliminated, and the results are immediately returned to the user. The process of adding the new results to the cache is independent of the results being returned to the user; the only effect on the running query is the resources that are consumed in the process of writing the cached results.
Query cache entries are portable across different operating systems, such as Windows or UNIX 64-bit architectures. Incompatible cache entries are automatically removed.
Note that query cache entries are not portable across different releases of Oracle Business Intelligence, such as between Version 10.1.3.2 and 11.1.1.
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 the NQSConfig.INI file parameter DISABLE_SUBREQUEST_CACHING to YES. See Configuration File Settings.
Advantages of Caching
The fastest way to process a query is to skip the bulk of the processing and use a precomputed answer.
With query caching, the Oracle BI Server stores the precomputed results of queries in a local cache. If another query can use those results, then all database processing for that query is eliminated. This can result in dramatic improvements in the average query response time.
In addition to improving performance, being able to answer a query from a local cache conserves network resources and processing time on the database server. Network resources are conserved because the intermediate results do not have to come over the network to the Oracle BI Server. Not running the query on the database frees the database server to do other work. If the database uses a charge back system, then it could save money in the budget as well.
Another benefit of using the cache to answer a query is savings in processing time on the Oracle BI Server, especially if the query results are retrieved from multiple databases. Depending on the query, there might be considerable join and sort processing in the server. If the query is already calculated, then this processing is avoided, freeing server resources for other tasks.
To summarize, query caching has the following advantages:
-
Dramatic improvement of query performance
-
Less network traffic
-
Reduction in database processing
-
Reduction in Oracle BI Server processing overhead
Costs of Caching
Query caching has many obvious benefits, but also certain costs.
-
Disk space for the cache
-
Administrative costs of managing the cache
-
Potential for cached results being stale
-
CPU and disk I/O on server computer
With cache management, the benefits typically far outweigh the costs.
The following sections discuss the costs of caching.
Disk Space
The query cache requires dedicated disk space.
How much space depends on the query volume, the size of the query result sets, and how much disk space that you choose to allocate to the cache. For performance purposes, use a disk exclusively for caching, and ensure that it is a high performance, high reliability type of disk system.
Administrative Tasks
Some administrative tasks are associated with caching. You must set the cache persistence time for each physical table appropriately, knowing how often data in that table is updated.
When the frequency of the update varies, you must keep track of when changes occur and purge the cache manually when necessary. You can also create a cache event polling table and modify applications to update the polling table when changes to the databases occur, making the system event-driven.
The Oracle BI Server also provides ODBC-extension functions for purging cache entries programmatically. You can write your own scripts to call these functions at the appropriate times.
Keeping the Cache Up To Date
If the cache entries are not purged when the data in the underlying databases changes, then queries can potentially return results that are out of date.
You must evaluate whether this is acceptable. It might be acceptable to allow the cache to contain some stale data. You must decide what level of stale data is acceptable and then configure (and follow) a set of rules to reflect those levels.
For example, suppose an application analyzes corporate data from a large conglomerate, and you are performing yearly summaries of the different divisions in the company. New data does not materially affect the queries because the new data affects only next year's summaries. In this case, the trade-offs for deciding whether to purge the cache might favor leaving the entries in the cache.
Suppose, however, that the databases are updated three times a day and you are performing queries on the current day's activities. In this case, you must purge the cache much more often, or perhaps consider not using the cache at all.
Another scenario is that you rebuild the data mart from the beginning at periodic intervals (for example, once per week). In this example, you can purge the entire cache as part of the process of rebuilding the data mart, ensuring that you never have stale data in the cache.
Whatever your situation, you must evaluate what is acceptable for noncurrent information returned to the users.
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 entry.
See Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for information about enabling shared logon for connection pools.
About the Refresh Interval for XML Data Sources
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.
The default interval setting is Infinite, meaning that the XML data source is not automatically refreshed.
The refresh interval setting determines the time interval after which the Oracle BI Server XML Gateway connection is refreshed, as follows:
-
For URLs that begin with
http://orhttps://, the gateway is refreshed when it detects that the interval has expired. -
For URLs that reside on a local or network drive, the gateway is refreshed when the interval has expired and the system detects that the URLs have been modified.
For more information about XML data sources, see Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
About the Global Cache
In a clustered environment, Oracle BI Presentation Services 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 Presentation Services nodes push to and pull from the logical event queue. Each Oracle BI Presentation Services still maintains its own local query cache for regular queries.
The figure below depicts global caching in a clustered environment. It shows three Oracle BI Presentation Services nodes sharing a global cache. The global cache stores seeding or purging events held in a logical event queue. The arrows from Node 2 and Node 3 to the shared cache show Oracle BI Presentation Services Node 2 pushing a seeding event to the queue and Oracle BI Presentation Services Node 3 pushing a purging event to the queue. The arrows from the shared storage to each Oracle BI Presentation Services node show each node pulling from the common location. This occurs on a periodic basis and enables participating Oracle BI Presentation Services nodes to obtain updates to the logical event queue made by other Oracle BI Presentation Services.
The Oracle BI Presentation Services node processes a seeding or purging event locally first in its caching system. It then pushes the event to the global cache on the shared storage. During the push event, the active Oracle BI Presentation Services node locks the logical event queue on the shared storage and then pushes in the seeding or purging event. If there is a conflict between seeding and purging (for example, one node wants to seed a query and another node wants to purge the same query), then the event that comes in last wins.
The logical event queue in the global cache on the shared storage is composed of seeding and purging events from individual Oracle BI Presentation Services nodes. The queue is sorted according to the timestamp of the events. Hence, clocks on all Oracle BI Presentation Services nodes participating in cluster must be synchronized.
Each Oracle BI Presentation Services node polls the global cache on a periodic basis for new cache entries. This polling frequency is configurable. A snapshot of the queued logical events on the shared storage is pulled back to the node and a local logical event queue is constructed and then processed.
Note:
The process of populating or purging seeded caches across all Oracle BI Presentation Services nodes that participate in the cluster does not occur in real time, and the elapse of the process is affected by multiple factors, such as the predefined polling interval, network bandwidth, and CPU loads.
Because the query cache result set tends to get large, network bandwidth might pose a constraint. Therefore, the following must be chosen carefully:
-
The set of caches that qualify for seeded cache
-
The time interval for BI nodes to pick up seeded caches from shared storage (to avoid network congestion)
The primary global cache parameters are configured in Fusion Middleware Control. Additional, optional parameters are configured in the NQSConfig.INI file for each Oracle BI Presentation Services node that participates in the cluster. See Using Fusion Middleware Control to Set Global Cache Parameters and Manually Editing Additional Global Cache Parameters.
A seeding or purging procedure is submitted to a specific Oracle BI Presentation Services node. If that Oracle BI Presentation Services is a node in a BI cluster and the global cache parameters have been defined in Oracle BI Presentation Services configuration files, then the seeding or purging events are propagated across all Oracle BI Presentation Services nodes that participate in the same clustered environment.