Configure Query Caching

You configure cache storage and other parameters in Fusion Middleware Control and in the NQSConfig.INI file, for both the query cache and the global cache.

You can also set in the Console, see Prompt Options in Configure Advanced System Settings in the Console.

You also must decide on a strategy for flushing outdated cache entries; see Monitor and Manage the Cache.

This section contains the following topics:

• Use Fusion Middleware Control to Enable and Disable Query Caching

• Use Fusion Middleware Control to Set Query Cache Parameters

• Manually Edit Additional Query Cache Parameters

• Use Fusion Middleware Control to Set Global Cache Parameters

• Manually Edit Additional Global Cache Parameters

Use Fusion Middleware Control to Enable and Disable Query Caching

You can use Fusion Middleware Control to enable or disable query caching.

The query cache is enabled by default.

Before you begin this procedure, ensure that you're familiar with the information in Use Fusion Middleware Control.

1. Go to the Business Intelligence Overview page, as described in Display Oracle Analytics Server Pages in Fusion Middleware Control.
2. Display the Performance tab of the Configuration page.
3. Click Lock and Edit to enable changes to be made.
4. To enable query caching, select Cache enabled. To disable query caching, deselect Cache enabled.
5. Click Apply, then click Activate Changes.
6. Return to the Business Intelligence Overview page and click Restart.

For information about corresponding configuration file elements, see Map User Interface Labels with Configuration File Elements.

Use 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're familiar with the information in Use Fusion Middleware Control.

1. Go to the Business Intelligence Overview page, as described in Display Oracle Analytics Server Pages in Fusion Middleware Control.
2. Display the Performance tab of the Configuration page.
3. Click Lock and Edit to enable changes to be made.
4. Complete the elements using the descriptions in the help topic for the page.

   • 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 corresponding configuration file elements, see Map User Interface Labels with Configuration File Elements.

Manually Edit Additional Query Cache Parameters

You can set additional query cache parameters in the NQSConfig.INI file.

Parameters include:

• 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 About Cache Hits for more information about when cache is hit.

  The cache storage directories 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 isn't cached.

• Typically, if a query gets a cache hit from a previously run query, then the new query isn't 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 run query.

See Configuration File Settings for more information about the additional query cache parameters.

Use Fusion Middleware Control to Set Global Cache Parameters

Setting global cache parameters ensures consistency across system cache configurations.

Before you begin this procedure, ensure that you're familiar with the information in Use Fusion Middleware Control.

1. Go to the Business Intelligence Overview page, as described in Display Oracle Analytics Server Pages in Fusion Middleware Control.
2. Display the Performance tab of the Configuration page.
3. Click Lock and Edit to enable changes to be made.
4. Complete 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 corresponding configuration file elements, see Map User Interface Labels with Configuration File Elements.

Manually Edit Additional Global Cache Parameters

You can set additional global cache parameters in the NQSConfig.INI file.

Parameters include:

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

  BI_DOMAIN/servers/obisn/logs

See Configuration File Settings.