To enable query caching, select Cache enabled To disable query caching, deselect

Cache enabled.

Click the Help button on the page to access the page-level help.

5. Click Apply, then click Activate Changes.

6. Return to the Business Intelligence Overview page and click Restart.

For information about using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 22, "Introducing the Oracle BI Systems Management API."

7.5.2 Using Fusion Middleware Control to Set Query Cache Parameters

You can use Fusion Middleware Control to set the maximum number of cache entries in the query cache and the maximum size for a single cache entry.

Before you begin this procedure, ensure that you are familiar with the information in

Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."

To use Fusion Middleware Control to set query cache parameters:

1. Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."

2. Display the Performance tab of the Capacity Management page.

3. Click Lock and Edit Configuration to allow changes to be made.

4. Complete the elements using the descriptions in the Help topic for the page. Click the Help button on the page to access the page-level help for the following options:

■ Maximum cache entry size

■ Maximum cache entries

5. Click Apply, then click Activate Changes.

6. Return to the Business Intelligence Overview page and click Restart.

For information about using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 22, "Introducing the Oracle BI Systems Management API."

Configuring Query Caching

7.5.3 Manually Editing Additional Query Cache Parameters

You can set additional query cache parameters in the NQSConfig.INI file, including the following:

■ The DATA_STORAGE_PATHS parameter specifies one or more directories for query

cache storage, and the maximum size for each storage directory. These directories are used to store the cached query results and are accessed when a cache hit occurs. See Section 7.7.1, "About Cache Hits" for more information about when cache is hit.

The cache storage directories should reside on high performance storage devices, ideally devoted solely to cache storage. When the cache storage directories begin to fill up, the entries that are least recently used (LRU) are discarded to make space for new entries.

■ The MAX_ROWS_PER_CACHE_ENTRY parameter controls the maximum number of

rows for any cache entry. Limiting the number of rows is a useful way to avoid using up the cache space with runaway queries that return large numbers of rows. If the number of rows a query returns is greater than the value specified in the

MAX_ROWS_PER_CACHE_ENTRY parameter, then the query is not cached.

■ Typically, if a query gets a cache hit from a previously executed query, then the

new query is not added to the cache. The POPULATE_AGGREGATE_ROLLUP_HITS

parameter overrides this default when the cache hit occurs by rolling up an aggregate from a previously executed query.

See Appendix A, "NQSConfig.INI File Configuration Settings" for more information about the additional query cache parameters.

7.5.4 Using Fusion Middleware Control to Set Global Cache Parameters

Before you begin this procedure, ensure that you are familiar with the information in

Section 3.2, "Using Fusion Middleware Control to Update Oracle Business Intelligence Configuration Settings."

To use Fusion Middleware Control to set global cache parameters:

1. Go to the Business Intelligence Overview page, as described in Section 2.2.2, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."

2. Display the Performance tab of the Capacity Management page.

3. Click Lock and Edit Configuration to allow changes to be made.

4. Complete the elements using the descriptions in the Help topic for the page. Click the Help button on the page to access the page-level help for the following options:

■ Global cache path

■ Global cache size

5. Click Apply, then click Activate Changes.

6. Return to the Business Intelligence Overview page and click Restart.

For information about using methods in the Oracle BI Systems Management API to change configuration settings, see Chapter 22, "Introducing the Oracle BI Systems Management API."

Monitoring and Managing the Cache

Managing Performance Tuning and Query Caching 7-15

7.5.5 Manually Editing Additional Global Cache Parameters

You can set additional global cache parameters in the NQSConfig.INI file, including the following:

■ The MAX_GLOBAL_CACHE_ENTRIES parameter controls the maximum number of

entries that are allowed in the global cache store.

■ The CACHE_POLL_SECONDS parameter specifies the interval in seconds at which

the Oracle BI Server pulls from the logical event queue to synchronize with other server nodes in the cluster.

■ The CLUSTER_AWARE_CACHE_LOGGING parameter controls whether logging is

turned on for the global cache. Change this setting to YES only for debugging purposes.

Log entries appear in nqquery.log. You can find this file at:

ORACLE_INSTANCE\diagnostics\logs\OracleBIServerComponent\coreapplication_obisn

See Appendix A, "NQSConfig.INI File Configuration Settings" for more information about the additional global cache parameters.

7.6 Monitoring and Managing the Cache

To manage the changes in the underlying databases and to monitor cache entries, you must develop a cache management strategy. You need a process to invalidate cache entries when the data in the underlying tables that compose the cache entry have changed, and a process to monitor, identify, and remove any undesirable cache entries. This section contains the following topics:

■ Section 7.6.1, "Choosing a Cache Management Strategy"

■ Section 7.6.2, "Purging and Maintaining Cache Using ODBC Procedures" ■ Section 7.6.3, "How Repository Changes Affect the Query Cache"

7.6.1 Choosing a Cache Management Strategy

The choice of a cache management strategy depends on the volatility of the data in the underlying databases and the predictability of the changes that cause this volatility. It also depends on the number and types of queries that comprise your cache and the usage those queries receive. This section provides an overview of the various approaches to cache management.

7.6.1.1 Disable Caching for the System

You can disable caching for the entire system to stop all new cache entries and stop any new queries from using the existing cache. Disabling caching lets you enable it at a later time without losing any entries that are stored in the cache.

Temporarily disabling caching is a useful strategy in situations where you might suspect having stale cache entries, but want to verify if they are actually stale before purging those entries or the entire cache. If you find that the data stored in the cache is still relevant, or after you have safely purged problem entries, then you can safely enable the cache. If necessary, purge the entire cache or the cache that is associated with a particular business model before enabling the cache again.

See Section 7.5.1, "Using Fusion Middleware Control to Enable and Disable Query Caching" for more information.

Monitoring and Managing the Cache

7.6.1.2 Caching and Cache Persistence Timing for Specified Physical Tables

You can set a cacheable attribute for each physical table, enabling you to specify whether queries for that table are added to the cache to answer future queries. If you enable caching for a table, then any query involving the table is added to the cache. All tables are cacheable by default, but some tables might not be good candidates to include in the cache unless you use the Cache Persistence Time settings. For example, suppose that you have a table that stores stock ticker data that is updated every minute. You could use the Cache Persistence Time settings to purge the entries for that table every 59 seconds.

You can also use the Cache persistence time field to specify how long the entries for this table should be kept in the query cache. This is useful for data sources that are updated frequently.

To set the caching attributes for a specific physical table: