[OBIEE 10g] Query Result Cache Section Paramaters

Query Result Cache Section Parameters

###############################################################################
#
#  Query Result Cache Section
#
###############################################################################

[ CACHE ]

ENABLE = YES;
// A comma separated list of pair(s)
//   e.g. DATA_STORAGE_PATHS = "d:\OracleBIData\nQSCache" 500 MB;
DATA_STORAGE_PATHS = "C:\OracleBIData\cache" 500 MB;
MAX_ROWS_PER_CACHE_ENTRY = 100000;  // 0 is unlimited size
MAX_CACHE_ENTRY_SIZE = 1 MB;
MAX_CACHE_ENTRIES = 1000;
POPULATE_AGGREGATE_ROLLUP_HITS = NO;
USE_ADVANCED_HIT_DETECTION = NO;

MAX_SUBEXPR_SEARCH_DEPTH = 7;

// Cluster-aware cache
// GLOBAL_CACHE_STORAGE_PATH = "" SIZE;
// MAX_GLOBAL_CACHE_ENTRIES = 1000;
// CACHE_POLL_SECONDS = 300;
// CLUSTER_AWARE_CACHE_LOGGING = NO;

###############################################################################

The parameters in the Query Result Cache Section provide configuration information for Oracle BI Server caching. The query cache is enabled by default. After deciding on a strategy for flushing outdated entries, you should configure the cache storage parameters in Fusion Middleware Control and in the NQSConfig.INI file.

Note that query caching is primarily a run-time performance improvement capability.

As the system is used over a period of time, performance tends to improve due to cache hits on previously executed queries. The most effective and pervasive way to optimize query performance is to use the Aggregate Persistence Wizard and aggregate navigation.

This section describes only the parameters that control query caching. For information about how to use caching in Oracle Business Intelligence, including information about how to use agents to seed the Oracle BI Server cache, see Chapter 7, "Managing Performance Tuning and Query Caching."

ENABLE

Note: The ENABLE parameter is centrally managed by Fusion
Middleware Control and cannot be changed by manually editing
NQSConfig.INI, unless all configuration through Fusion Middleware
Control has been disabled (not recommended).

The Cache enabled option on the Performance tab of the Capacity
Management page in Fusion Middleware Control corresponds to the
ENABLE parameter. See Section 7.5.1, "Using Fusion Middleware
Control to Enable and Disable Query Caching" for more information.



Specifies whether the cache system is enabled. When set to NO, caching is disabled.

When set to YES, caching is enabled. The query cache is enabled by default.

Example: ENABLE = YES;

A.3.2 DATA_STORAGE_PATHS

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

Each path listed must be an existing, writable path name, with double quotes ( " ) surrounding the path name. Specify mapped directories only. UNC path names and network mapped drives are allowed only if the service runs under a qualified user account.

You can specify either fully qualified paths, or relative paths. When you specify a path that does not start with "/" (on UNIX) or ":" (on Windows), the Oracle BIServer assumes that the path is relative to the local writable directory. For example, if you specify the path "cache," then at run time, the Oracle BI Server uses the following:

ORACLE_INSTANCE/bifoundation/OracleBIServerComponent/coreapplication_obisn/cache

Specify multiple directories with a comma-delimited list. When you specify multiple directories, they should reside on different physical drives. (If you have multiple cache




Note: Multiple Oracle BI Servers across a cluster do not share cached
data. Because of this, the DATA_STORAGE_PATHS entry must be
unique for each clustered server. To ensure this, enter a relative path
so that cache is stored in the local writable directory for each Oracle BI
Server, or enter different fully qualified paths for each server.


directory paths that all resolve to the same physical disk, then both available and used space might be double-counted.)

Syntax: DATA_STORAGE_PATHS = "path_1" sz[, "path_2" sz{, "path_n"
sz}];


Example: DATA_STORAGE_PATHS = "cache" 256 MB;

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 the size of a result set is dominated by variable length character strings, and if the length of those strings are distributed normally, you would expect the average record size to be about half of the maximum record size.

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

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

Note: Specifying multiple directories for each 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
for each disk drive. Specifying multiple directories on different drives
might improve the overall I/O throughput of the Oracle BI Server
internally by distributing I/O across multiple devices.

Note: It is a best practice to use a value that is less than 4 GB.
Otherwise, the value might exceed the maximum allowable value for
an unsigned 32-bit integer, because values over 4 GB cannot be
processed on 32-bit systems. It is also a best practice to use values less
than 4 GB on 64-bit systems.

Create multiple paths if you have values in excess of 4 GB.

Note: The MAX_CACHE_ENTRY_SIZE parameter is centrally
managed by Fusion Middleware Control and cannot be changed by
manually editing NQSConfig.INI, unless all configuration through
Fusion Middleware Control has been disabled (not recommended).
The Maximum cache entry size option on the Performance tab of the
Capacity Management page in Fusion Middleware Control
corresponds to the MAX_CACHE_ENTRY_SIZE parameter. See
Section 7.5.2, "Using Fusion Middleware Control to Set Query Cache
Parameters" for more information.