OBIEE Cache Management

1.   Oracle BI Server Query Cache

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:

• 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.
• 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

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.

1.1. 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, and across 32-bit and 64-bit architectures. Incompatible cache entries are automatically removed. For example, you do not have to manually remove cache files when switching between 32-bit and 64-bit systems.

Note that query cache entries are not portable across different releases of Oracle Business Intelligence, such as between Version 10.1.3.2 and 11g Release 1 (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.

1.2.  Advantages of Caching

The fastest way to process a query is to skip the bulk of the processing and use a pre-computed answer. With query caching, the Oracle BI Server stores the pre-computed results of queries in a local cache. If another query can use those results, then all databases 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

1.3. 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.

1.3.1.     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.

1.3.2.     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.

1.3.3.     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.

1.3.4.     CPU Usage and Disk I/O

Although usually it is very minor, query caching does require a small amount of CPU time and adds to the disk I/O. In most cases, the CPU usage and disk I/O is insignificant. The disk I/O might be noticeable only when queries return large data sets.

1.4. 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 a own cache entry.

1.5. 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:// or https://, 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 Oracle Fusion Middleware Metadata Repository Builder’s Guide for Oracle Business Intelligence Enterprise Edition.

2.   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.

Figure 7-1 depicts global caching in a clustered environment. It shows three Oracle BI Server 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 Server Node 2 pushing a seeding event to the queue and Oracle BI Server Node 3 pushing a purging event to the queue. The arrows from the shared storage to each Oracle BI Server node show each node pulling from the common location. This occurs on a periodic basis and enables participating Oracle BI Server nodes to obtain updates to the logical event queue made by other Oracle BI Servers.

Figure 7-1 Global Caching

The Oracle BI Server 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 Server 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 Server nodes. The queue is sorted according to the timestamp of the events. Hence, clocks on all Oracle BI Server nodes participating in cluster must be synchronized.

Each Oracle BI Server 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 are pulled back to the node and a local logical event queue is constructed and then processed.

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 Server node that participates in the cluster. For more information about configuring these parameters, see Section 7.5.4, “Using Fusion Middleware Control to Set Global Cache Parameters” and Section 7.5.5, “Manually Editing Additional Global Cache Parameters.”

A seeding or purging procedure is submitted to a specific Oracle BI Server node. If that Oracle BI Server is a node in a BI cluster and the global cache parameters have been defined in Oracle BI Server configuration files, then the seeding or purging events are propagated across all Oracle BI Server nodes that participate in the same clustered environment.

3.   Oracle BI Presentation Services Cache Settings

When users run analyses, Presentation Services can cache the results of those analyses. Presentation Services determines if subsequent analyses can use cached results. If the cache can be shared, then subsequent analyses are not stored.

The files for the Presentation Services cache have names such as nQS_xxxx_x_xxxxxx.TMP. The files are created by the ODBC driver but generally do correspond to ODBC requests that the Presentation Services cache keeps open. The files are stored in the following directory:

ORACLE_INSTANCE\tmp\OracleBIPresentationServices\coreapplication_obipsn\obis_temp

The files for the cache are removed whenever Presentation Services shuts down cleanly. If Presentation Services shuts down unexpectedly, then various cache files might be left on disk. You can delete the files when Presentation Services is not running.

The Presentation Services cache is different from the cache that is accessed by the Oracle BI Server. You can change the defaults for the Presentation Services cache by modifying the instanceconfig.xml file to include the cache entries.

The following procedure provides information about configuration changes with which you can manage the Presentation Services cache.

To manually edit the settings for managing the cache:

• Open the instanceconfig.xml file

• Locate the section in which you must add the elements that are described in Table 7-7. Note:
Avoid specifying values of less than 3 minutes for the elements that affect minutes. At such a low amount of time, refreshes can occur frequently, which can negatively affect performance and cause flickering on the screen.
• Include the elements and their ancestor elements as appropriate, as shown in the following example: 
 
    
      100
      60
      10
      10
    
 
• Save your changes and close the file.
• Restart Oracle Business Intelligence.

4. Enterprise Manager.

Open EM > Business Intelligence > Coreapplication > Capacity Management > Performance

5.   Controlling cache at physical layer in Administrator tool

6.    Improving Oracle BI Web Client Performance

Performance of the Oracle BI web client by configuring the web server to serve up all static files, as well as enabling compression for both static and dynamic resources. By enabling caching and content expiration on the web server, web browsers can determine how often to reload the static files from the server.

In Oracle BI EE, static files are located in ORACLE_HOME/bifoundation/web/appv2. Follow the instructions for the web server to set up static file caching and compression for the files located in this directory.

1.   Configuring Apache HTTP Server for Static File Caching

This example configuration assumes that you have installed the web server plug-in that enables Apache HTTP Server to proxy requests to Oracle WebLogic Server. Make sure that the PLUGIN_HOME/lib directory is added to LD_LIBRARY_PATH, or equivalent for your operating system.

JVM Heap Size

You can change the default JVM heap size for the Administration Server and Managed Servers by setting the USER_MEM_ARGS parameter in the startup script for Oracle WebLogic Server. The following procedure sets the same values for both the Administration Server and Managed Servers.

To change the default JVM heap size:

• Use the WebLogic Server Administration Console to shut down the servers gracefully.
• Open the setDomainEnv.sh file (or setDomainEnv.bat on Windows systems) for editing. You can find this file in the DOMAIN_HOME/bin directory.
• Locate the IF statement for USER_MEM_ARGS and place the insertion point before that statement.
• Set the -Xmx argument for USER_MEM_ARGS. The following list shows examples of how to set USER_MEM_ARGS for various operating systems:
  • UNIX shell script (.sh)
USER_MEM_ARGS=”-Xms256m -Xmx1024m -XX:CompileThreshold=8000 -xx:PermSize=128m -XX:MaxPermSize=512m”
  • export USER_MEM_ARGS
  • UNIX C shell script (.csh)
setenv USER_MEM_ARGS=”-Xms256m -Xmx1024m -XX:CompileThreshold=8000 -xx:PermSize=128m -XX:MaxPermSize=512m”
  • Windows command script (.bat)
set USER_MEM_ARGS=”-Xms256m -Xmx1024m -XX:CompileThreshold=8000 -xx:PermSize=128m -XX:MaxPermSize=512m”
• 
The arguments for USER_MEM_ARGS can vary, depending on whether you are using SunVM or JRockit. For example, PermSize and MaxPerm Size apply only to SunVM and generate warnings for JRockit.
• After setting the parameter, save and close the file, then restart the Administration Server and Managed Servers for the changes to take effect.

In a scaled-out system, repeat these steps for each domain home. Note that if you scale out the system at a future point, then you must perform these steps for the new node (the settings are not copied to the new node’s domain home).