OBIEE 10G/11G - Logical Query Result Cache Parameters

About

You can set the query cache application parameters:

for 10g in the file OBIEE - NQSConfig.ini.
For some parameters in 11g, with the help of FMW Control > Business Intelligence > CoreApplication > Capacity Management tab.

Example

[CACHE]

ENABLE = YES;
DATA_STORAGE_PATHS = "d:\OracleBI\cache" 256MB, "f:\OracleBI\cache" 200MB ;
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 Cache Parameters
GLOBAL_CACHE_STORAGE_PATH = "<directory name>" 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 Business Intelligence Server caching. The parameters that control query caching are described in this section.

Parameters

ENABLE

Specifies whether the cache system is enabled.

For 10g, modify the parameter in the file nqsconfig.ini. When set to NO, caching is disabled. When set to YES, caching is enabled.
For 11g, modify the parameter with FMW Control > Business Intelligence > CoreApplication > Capacity Management tab

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.

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.

Rules :

The maximum capacity in bytes, kilobytes, megabytes or gigabytes. The maximum capacity for each path is 4 GB.

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.
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}] ;

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.

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.

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.

For 10g, modify the parameter in the file nqsconfig.ini.
For 11g, modify the parameter with FMW Control > Business Intelligence > CoreApplication > Capacity Management tab

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.

For 10g, modify the parameter in the file nqsconfig.ini. When set to NO, caching is disabled. When set to YES, caching is enabled.
For 11g, modify the parameter with FMW Control > Business Intelligence > CoreApplication > Capacity Management tab

POPULATE_AGGREGATE_ROLLUP_HITS

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.

Normally, when a user ask for information that exist in the cache but at a lowest level, OBIEE server retrieve the cache hit, aggregate the measures but the result is not put into the cache.

Example :

A user ask for the sales by district, region
A cache is created
An other user ask for the sales by region
OBIEE server use the first entry to retrieve the information set for the second query. This is referred to as a rollup cache hit.
The result is returned to the user but not inserted in the cache.

Setting this parameter to TRUE may result in better performance, but results in more entries being added to the cache.

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.

MAX_SUBEXPR_SEARCH_DEPTH

MAX_SUBEXPR_SEARCH_DEPTH = 7;

GLOBAL_CACHE_STORAGE_PATH

For the cluster-aware caching feature,

MAX_GLOBAL_CACHE_ENTRIES

For the cluster-aware caching feature, it controls the maximum number of entries that are allowed in the global cache store.

CACHE_POLL_SECONDS

For the cluster-aware caching feature, it 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.

CLUSTER_AWARE_CACHE_LOGGING

For the cluster-aware caching feature, 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.

DISABLE_SUBREQUEST_CACHING

On 11g, 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 DISABLE_SUBREQUEST_CACHING to YES.

Global cache path

FMW Control > Business Intelligence > CoreApplication > Capacity Management tab.

Global cache size

FMW Control > Business Intelligence > CoreApplication > Capacity Management tab.

BI / ETL / DWH

Monday, 21 September 2015