Oracle Business Intelligence Infrastructure Installation and Configuration Guide > NQSConfig.INI File Reference >

Query Result Cache Section Parameters in the Configuration File

The parameters in the Query Result Cache Section provide configuration information for Oracle Business Intelligence Server caching. The parameters that control query caching are described in this section.

• For information about caching in Oracle BI, see the chapter on query caching in Oracle Business Intelligence Server Administration Guide.
• For information about how to use Delivers to seed the Oracle BI ServerCache, refer to the Oracle Business Intelligence Presentation Services Administration Guide.

ENABLE

Specifies whether the cache system is enabled.

When set to NO, caching is disabled. When set to YES, caching is enabled.

Example: ENABLE = NO ;

DATA_STORAGE_PATHS

Specifies one or more directory paths for where the cached query results data is stored and are accessed when a cache hit occurs. The maximum capacity in bytes, kilobytes, megabytes or gigabytes. The maximum capacity for each path is 4 GB. For optimal performance, the directories specified should be on high performance storage systems.

NOTE:  An Oracle Business Intelligence Server defined as a clustered server does not share cached data. The DATA_STORAGE_PATHS entry needs be unique for each server defined as a cluster participant.

Each directory listed needs to be an existing, fully-qualified, writable directory pathname, with double quotes ( " ) surrounding the pathname. Specify mapped directories only. UNC path names and network mapped drives are allowed only if the service runs under a qualified user account. To change the account under which the service is running, see the corresponding topic in the Oracle Business Intelligence Enterprise Edition Deployment Guide.

Specify multiple directories with a comma separated list. When you specify more than one directory, they should reside on different physical drives. (If you have multiple cache directory paths that all resolve to the same physical disk, both available and used space may be double-counted.)

Syntax: DATA_STORAGE_PATHS = "<full_directory_path_1>" sz[, "<full_directory_path_2>" sz{, "<full_directory_path_n>" sz}] ;

Example: DATA_STORAGE_PATHS = "d:\OracleBI\cache" 256MB, "f:\OracleBI\cache" 200MB ;

NOTE:  Specifying more than one directory per drive does not improve performance, because file input and output (I/O) takes place through the same I/O controller. In general, specify only one directory per disk drive. Specifying multiple directories on different drives may improve the overall I/O throughput of the Oracle Business Intelligence Server internally by distributing I/O across multiple devices.

The disk space requirement for the cached data depends on the number of queries that produce cached entries and the size of the result sets for those queries. The query result set size is calculated as row size (or the sum of the maximum lengths of all columns in the result set) times the result set cardinality (that is, the number of rows in the result set). The expected maximum should be the guideline for the space needed.

This calculation gives the high-end estimate, not the average size of all records in the cached result set. Therefore, if a result set's size is dominated by variable length character strings and if those strings' lengths have a normal distribution, you would expect the average record size to be about half of the maximum record size.

NOTE:  It is a best practice to use a value that is less than 4 GB, otherwise the value may exceed the maximum allowable value for an unsigned 32 bit integer.

MAX_ROWS_PER_CACHE_ENTRY

Specifies the maximum number of rows in a query result set to qualify for storage in the query cache. 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, the query is not cached.

When set to 0, there is no limit to the number of rows per cache entry.

Example: MAX_ROWS_PER_CACHE_ENTRY = 100000 ;

MAX_CACHE_ENTRY_SIZE

Specifies the maximum size for a cache entry. Potential entries that exceed this size are not cached. The default size is 1 MB.

Specify GB for gigabytes, KB for kilobytes, MB for megabytes, and no units for bytes.

Example: MAX_CACHE_ENTRY_SIZE = 1 MB ;

MAX_CACHE_ENTRIES

Specifies the maximum number of cache entries allowed in the query cache. Limiting the total number of cache entries provides another parameter with which to manage your cache storage. The actual limit of cache entries might vary slightly depending on the number of concurrent queries. The default value is 1000.

Example: MAX_CACHE_ENTRIES = 1000 ;

POPULATE_AGGREGATE_ROLLUP_HITS

Specifies whether to aggregate data from an earlier cached query result set and create a new entry in the query cache for rollup cache hits. The default value is NO.

Typically, if a query gets a cache hit from a previously executed query, then the new query is not added to the cache. A user may have a cached result set containing information at a particular level of detail (for example, sales revenue by ZIP Code). A second query may ask for this same information, but at a higher level of detail (for example, sales revenue by state). The POPULATE_AGGREGATE_ROLLUP_HITS parameter overrides this default when the cache hit occurs by rolling up an aggregate from a previously executed query, in this example, by aggregating data from the first result set stored in the cache. That is, Oracle BI sales revenue for all ZIP Codes in a particular state can be added to obtain the sales revenue by state. This is referred to as a rollup cache hit.

Normally, a new cache entry is not created for queries that result in cache hits. You can override this behavior specifically for cache rollup hits by setting POPULATE_AGGREGATE_ROLLUP_HITS to YES. Nonrollup cache hits are not affected by this flag. If a query result is satisfied by the cache—that is, the query gets a cache hit—then this query is not added to the cache. When this parameter is set to YES, then when a query gets an aggregate rollup hit (for example, "sales by region" is answered from "sales by district, region") then the result is put into the cache. Setting this parameter to TRUE may result in better performance, but results in more entries being added to the cache.

Example: POPULATE_AGGREGATE_ROLLUP_HITS = NO ;

USE_ADVANCED_HIT_DETECTION

When caching is enabled, each query is evaluated to determine whether it qualifies for a cache hit. A cache hit means that the server was able to use cache to answer the query and did not go to the database at all.

The Oracle BI Server can use query cache to answer queries at the same or higher level of aggregation.

Reasons Why a Query is Not Added to the Cache

Customers who rely on query result caching in the Oracle BI Server to meet their performance KPIs can use caching parameters to help determine why a cache hit did not occur. Logging facilities can help to diagnose common reasons for getting a cache miss, where the logical SQL query that was supposed to seed the cache did not get inserted into the cache. The following describes some of the situations when this might occur.

• Non-cacheable SQL element. If a SQL request contains Current_Timestamp, Current_Time, Rand, Populate, or a parameter marker then it is not added to the cache.
• Non-cacheable table. Physical tables in the Oracle BI Server repository can be marked 'non-cacheable'. If a query references any non-cacheable table then the query results will not be added to the cache.
• Cache hit. In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache.

  NOTE:  The exception is query hits that are aggregate roll-up hits. These are added to the cache if the NQSConfig.INI parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.

• Result set is too big.
• Query is cancelled. This can happen by explicit cancellation from Oracle BI Presentation Services or the Administration Tool, or implicitly through timeout.
• Oracle BI Server is clustered. Queries that fall into the 'cache seeding' family are propagated throughout the cluster. Other queries continue to be stored locally. Therefore, even though a query may be put into the cache on Oracle BI Server node 1, it may not be on Oracle BI Server node 2.

Level 4 of query logging is the best tool to diagnose whether the Oracle BI Server compiler intended to add the entry into the query result cache.

MAX_SUBEXPR_SEARCH_DEPTH

MAX_SUBEXPR_SEARCH_DEPTH = 7;

GLOBAL_CACHE_STORAGE_PATH

For the cluster-aware caching feature,

Example: GLOBAL_CACHE_STORAGE_PATH = "<directory name>" SIZE;

MAX_GLOBAL_CACHE_ENTRIES

For the cluster-aware caching feature,

Example: MAX_GLOBAL_CACHE_ENTRIES = 1000;

CACHE_POLL_SECONDS

For the cluster-aware caching feature,

Example: CACHE_POLL_SECONDS = 300;

CLUSTER_AWARE_CACHE_LOGGING

For the cluster-aware caching feature,

Example: CLUSTER_AWARE_CACHE_LOGGING = NO;