Cache Management

Cache is the most important feature used for obtaining an optimized report and dashboard performance. However when managed poorly, it might lead to stale data appearing in reports. Hence cache must be purged at intervals based on the right perception. E.g. cache must be deleted after ETL has completed the data load to the warehouse tables.

Cache management in OBIEE is done from multiple points and this is determined by who is purging it.

Cache Access Point	OBIEE Role
Online RPD file	Developer
Analysis Page	Report End Users and Developers
Scripts	Administrators

Enabling cache

Caching must be enabled first for this to be managed. To do this open the nqsconfig file and edit the following (in red)

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

#

#  Query Result Cache Section

#

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

[CACHE]

ENABLE = YES;  # This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager 

As this option is controlled from Enterprise Manager, restarting the BI Service will reset this option to what is define is Enterprise Manager. Hence for permanently setting this option to Yes/NO this must be done in EM. Navigation : Open EM > Business Intelligence > Coreapplication > Capacity Management > Performance
See Screenshot

  Deleting cache from RPD

Open rpd in online mode

Navigate to Manage>Cache

The cache manager screen shows the various SQL stored in Cache

One may see the sql related to each cache entry using “Show SQL”.

“Show SQL” option of the above step opens up a window with the Logical SQL of the cached query

“Purge” can be used to delete one or more cache entries.

Create a connection as shown above with Database as ODBC Basic

Create a connection pool called AnalyticsWeb with call interface as ODBC 2.0

Create a new Analysis

Select  “Create Direct Database Request”

Specify the Connection Pool created in first step(this connection pool is created in rpd exclusively for cache management, don’t use the one for reports)

SQL Statement : Specify the OBIEE command for deleting cache, it can be either of the following :

 Call SAPurgeAllCache()  -- Deletes all cache info

Call SAPurgeCacheByTable( 'Datwarehouse',  '','DWH', 'XW_SALES_F');  -- deletes specific table cache

Call SAPurgeCacheByDatabase( 'Datwarehouse' );   -- Deletes all cache related to a specific database

Call SAPurgeCacheByQuery('SELECT   0 s_0,    "Sales Subject Area"."Time - Dimension"."MONTH_NAME" s_1, "Sales Subject Area"."Fact Sales"."COST_PRICE" s_2 FROM "Sales Subject Area" ORDER BY 1, 2 ASC NULLS LAST ; ') -– Deletes specific logical SQL Cache

Validate SQL and Retrieve Columns: This will validate the command issued  in previous step

Click on results Tab, The message in RESULT_MESSAGE column indicates the success of cache deletion operation.

Click on results Tab, The message in RESULT_MESSAGE column indicates the success of cache deletion operation.

 Deleting Cache using a script

One can create a simple .txt file and specify the cache deletion command, which can be one or more of the following :

Call SAPurgeAllCache()  -- Deletes all cache info

Call SAPurgeCacheByTable( 'Datwarehouse',  '','DWH', 'XW_SALES_F');  -- deletes specific table cache

Call SAPurgeCacheByDatabase( 'Datwarehouse' );   -- Deletes all cache related to a specific database

Call SAPurgeCacheByQuery('SELECT   0 s_0,    "Sales Subject Area"."Time - Dimension"."MONTH_NAME" s_1, "Sales Subject Area"."Fact Sales"."COST_PRICE" s_2 FROM "Sales Subject Area" ORDER BY 1, 2 ASC NULLS LAST ; ') -– Deletes specific logical SQL Cache

 e.g. c:\del_cache.txt has the command Call SAPurgeAllCache() 

This script can be run using the following command :

D:\OBIEE11G\Oracle_BI1\bifoundation\server\bin\nqcmd –d coreapplication_OH833456789 –u admin –p weblogic –s c:\del_cache.txt

-d : OBIEE Driver Name : Copu from windows DSN Names

-u: Administrator user name

-p: Administrator password

-s: script path

 NQConfig.ini file parameters related to cache management

 Query Result Cache Section Parameters

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

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.

 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;

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 quotation marks ( " ) 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 BI Server 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

Note:

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

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 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;

Note:

Specifying multiple directories for each drive does not improve performance, because file input and output (I/O) occurs 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.

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 the maximum record size.

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.

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:

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.

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

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

Example: MAX_CACHE_ENTRY_SIZE = 20 MB;

MAX_CACHE_ENTRIES

Note:

The MAX_CACHE_ENTRIES 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 entries option on the Performance tab of the Capacity Management page in Fusion Middleware Control corresponds to the MAX_CACHE_ENTRIES parameter.

Specifies the maximum number of cache entries allowed in the query cache to help manage 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 might have a cached result set that contains information at a particular level of detail (for example, sales revenue by ZIP code). A second query might 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 Business Intelligence 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 parameter. 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, then the result is put into the cache. Setting this parameter to YES might 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 later level of aggregation.

The parameter USE_ADVANCED_HIT_DETECTION enables an expanded search of the cache for hits. The expanded search has a performance impact, which is not easily quantified because of variable customer requirements. Customers that rely heavily on query caching and are experiencing misses might want to test the trade-off between better query matching and overall performance for high user loads. 

MAX_SUBEXPR_SEARCH_DEPTH

Lets you configure how deep the hit detector looks for an inexact match in an expression of a query. The default is 5.

For example, at level 5, a query on the expression SIN(COS(TAN(ABS(ROUND(TRUNC(profit)))))) misses on profit, which is at level 7. Changing the search depth to 7 opens up profit for a potential hit.

DISABLE_SUBREQUEST_CACHING

When set to YES, disables caching at the subrequest (subquery) level. The default value is NO.

Caching subrequests improves performance and the cache hit ratio, especially for queries that combine real-time and historical data. In some cases, however, you might disable subrequest caching, such as when other methods of query optimization provide better performance.

Example: DISABLE_SUBREQUEST_CACHING = NO;

GLOBAL_CACHE_STORAGE_PATH

Note:

The GLOBAL_CACHE_STORAGE_PATH 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 Global cache path and Global cache size options on the Performance tab of the Capacity Management page in Fusion Middleware Control correspond to the GLOBAL_CACHE_STORAGE_PATH parameter.

In a clustered environment, Oracle BI Servers can be configured to access a shared cache that is referred to as the global cache. The global cache resides on a shared file system storage device and stores seeding and purging events and the result sets that are associated with the seeding events.

This parameter specifies the physical location for storing cache entries shared across clustering. This path must point to a network share. All clustering nodes share the same location.

You can specify the size in KB, MB, or GB, or enter a number with no suffix to specify bytes.

Syntax: GLOBAL_CACHE_STORAGE_PATH = "directory name" SIZE;

Example: GLOBAL_CACHE_STORAGE_PATH = "C:\cache" 250 MB;

MAX_GLOBAL_CACHE_ENTRIES

The maximum number of cache entries stored in the location that is specified by GLOBAL_CACHE_STORAGE_PATH.

Example: MAX_GLOBAL_CACHE_ENTRIES = 1000;

CACHE_POLL_SECONDS

The interval in seconds that each node polls from the shared location that is specified in GLOBAL_CACHE_STORAGE_PATH.

Example: CACHE_POLL_SECONDS = 300;

CLUSTER_AWARE_CACHE_LOGGING

Turns on logging for the cluster caching feature. Used only for troubleshooting. The default is NO.

Example: CLUSTER_AWARE_CACHE_LOGGING = NO;