Cache Management Techniques

Introduction

Cache Management in OBIEE is done from various points and is determined by the source which is purging it.

The cache can be managed by using the following techniques:

1. Using Fusion Middleware (FMW) Control to configure caching

2. Using NQSConfig.ini to manually edit cache parameters

3. Setting caching and cache persistence for tables

4. Using the Cache Manager

5. Inspecting SQL for cache entries

6. Modifying the cache manager column display

7. Inspecting the cache reports

8. Purging cache entries manually using the Cache Manager

9. Purging cache entries automatically

10. Using event polling tables

11. Seeding the cache

1. Using FMW to Configure Caching

To use Enterprise Manager to enable or disable query caching, go to the Business Intelligence Overview page, click Performance tab of the Capacity Management page, select “Lock and Edit Configuration” and choose “Cache enabled”. To disable query caching, deselect “Cache enabled”. Apply and activate your changes.

You can also use Fusion Middleware control to set the maximum size for a single cache entry, as well as the maximum number of cache entries in the query cache.

Using NQSConfig.ini to edit manually

NQSConfig.ini is the initialization file used in OBIEE 10g and 11g to set parameters on start up. Each instance of Oracle BI Server has its own NQSConfig.ini file.

Fig 1

Cache parameters modified in Fusion Middleware Control are stored in [cache] section of NQSConfig.ini configuration file.

The additional parameters included in the NQSConfig.ini file are as follows:

• DATA_STORAGE_PATHS: Specifies one or more directories for query cache storage, as well as the maximum size to each storage directory. When the cache storage directories begin to fill up, the entries that are recently used (LRU) are discarded to make space for new entries.

• MAX_ROWS_PER_CACHE_ENTRY: Controls the maximum number of rows for any cache entry. If the number of rows that a query returns is greater than the value specified in the MAX_ROWS_PER_CACHE_ENTRY parameter, the query is not cached.

• POPULATE_AGGREGATE_ROLLUP_HITS: If a query gets a cache hit from a previously executed query, the new query is not added to the cache. When set to YES, this parameter overrides this default when the cache hit occurs by rolling up an aggregate from a previously executed query.

3. Setting Cache for Tables

Enable caching for a table so that any query involving the table is added to the cache.

The table can be made non-cacheable by deselecting the Cacheable check box. Defining tables as non-cacheable is only done when there are tables that change very frequently like real-time subject area.

The cache persistence time is set to indicate how long entries for this table should be kept in the cache. This is useful for data sources that are updated frequently.

Note: All tables are cacheable by default.

4. Using Cache Manager

The Cache Manager can be viewed in online mode only. Select Manage->Cache to open the Cache Manager. The dialog box is opened as below:

Fig 2

The Cache Manager provides Oracle BI Server administrators the ability to view information about the entire query cache, as well as information about individual entries in the query cache associated with open repository.

It also provides the ability to select specific cache entries and perform various operations on those entries such as viewing and saving the cached SQL Call or purge them.

Click the Cache tab at the bottom of the left pane to view cache entries for the current repository, business models, and users. The associated cache entries are reflected in the right pane, with the total number of entries shown in the view-only field at the top. Cache hits are shown in the Last used column.

5. Inspecting SQL for cache entries

The Cache Manager provides the ability to view the SQL for a cache entry in a separate window. To see the logical SQL used by the query, right-click the cache entry and select Show SQL or select the cache entry and select SQL->Show from the menu.

The Find button and Find Again button enable you to search and troubleshoot complex queries.

The SQL for a request can assist in evaluating cache statistics. For example, you realize that a cache entry has fulfilled 90 other requests, and you may want to know the SQL behind this entry to seed the cache requests that are just as effective.

6. Modify Cache Manager Column Display

You can alter how the Cache Manager displays information by following the steps below:

1. Select Edit->Options.

The Options window allows you to choose the columns that you want the Cache Manager to display by selecting or deselecting the check boxes for the columns.

2. Use the Up and Down buttons to change the order in which the columns are displayed.

7. Inspecting Cache Reports

In the Cache Manager, select Action->Show, or right-click in the right pane and Select Show to display global cache information. This includes information such as:

• Number of entries currently in the cache

• Number of queries satisfied by the cache

• Number of queries not satisfied

• The report also includes cache settings, such as:

• Maximum allowable number of entries in the cache

• Storage space information

• Maximum allowable number of rows per cache entry result set

Administrators can use this information to monitor cache performance. For example, if a large number of queries are not being satisfied by the cache, overall performance will be affected.

Fig 3

8. Purging Cache Entries Manually

Purging cache is the process of delete entries from the query cache. There are two methods for manually deleting cache entries:

1. Cache Mode: Purge one or more selected cache entries associated with the open repository, a specified business model, or a specified user within a business model.

2. Physical Mode: Purge all cache entries for tables associated with one or more selected databases, one or more selected catalogs, one or more selected schemas, or all cache entries associated with one or more selected tables.

To purge cache entries, select the entries and then right-click and select purge, or select Edit->Purge.

Purging cache entries manually gives the administrator the highest level of control over purging but is not necessarily the most efficient method.

9. Purging Cache Entries Automatically

Cache entries can be purged automatically by:

You can set cache persistence time to indicate how long entries for this table should be kept in cache. You can also use cache parameters in NQSConfig.ini, such as MAX_CACHE_ENTRIES, to limit the total number of cache entries.

When cache storage exceeds the specified number, entries that are least recently used are discarded, which essentially purges the cache.

10. Using Event Polling Tables

• Event polling table is a physical table that resides on a database accessible to Oracle BI Server.

• Oracle BI Server polls tables at set intervals and purges any stale cache entries that reference the updated tables.

• Regardless of where the table resides-in its own database or in a database with other tables, it requires a fixed schema.

Caution: Because there is a polling interval in which the cache is not completely up to date, there is always the potential for stale data in the cache.

11. Seeding the Cache

• Seeding is the process of pre-populating the cache with queries that are known to generate cache hits.

• One of the main advantages of seeding the cache is the improvement of query performance.

• The best queries for seeding the cache are queries that heavily consume database processing resources or that are likely to be reused.

• The cache seeding can be performed during off hours by running queries and caching their results or immediately after purging.

Caution:  Do not seed the cache with simple queries that return many rows and require very little database processing.

12. Cache Hit Conditions

 A cache hit occurs only when certain conditions are met. The conditions are as depicted below:

• The Query WHERE clause constraints need to be equivalent to the cached results or to the subset of results.

• All the columns in the SELECT list of a new query must exist in the cached query or they must be able to be calculated from the columns in the query.

• Join conditions must be equivalent.

• Queries that request an aggregated level of information can use cached results at a lower level of aggregation.

Selvi

Add comment