Cache Management - Part 1

Hello All,

Today I thought to explain most usefully topic in obiee  i.e. CACHE MANAGEMENT.

Here in below post you will see, what is the use of cache management, how to configure the cache file..etc.

What is Cache Management?

Whenever a saved request or any dashboard is accessed by one user it can be saved as a file to cache. So if any other user accessing the same saved request, instead of waiting for the information to be fetched from the database it can be reproduced from the cache for a performance improvement

OBIEE has two levels of caching, one level works at the OBIEE server level and caches the actual data for a query returned from the database in a file, the other level is at the OBIEE Presentation Server. 

OBIEE 11g Cache Management:

You can enable cache in EM.

Go to EM -> Business Intelligence Folder -> Coreapplication -> Capacity Management -> Performance (See Below Screen Shots). Bydefault cache is enable in 11g. You can disable or enable by click on Active changes.

Once you enable cache it will automatically reflect into “NQSConfig” (See below Screen Shots).

Path NQSConfig file:

D:\obieerakeshpatil\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1

Explanations of cache management

ENABLE=YES

The first setting here ENABLE controls whether caching is enabled on the OBIEE server or not.  If this is set to NO then the OBIEE server will not cache any queries, if it is set to YES then query caching will take place.

DATA_STORAGE_PATHS ="C:\OracleBIData\cache" 500 MB

DATA_STORAGE_PATHS is a comma delimited list of paths and data limits used for storing the cache files.  This can include multiple locations for storage i.e.  C:\OracleBIData\cache" 500 MB,C:\OracleBIData\cache2" 200 MB.   Note that although the docs say that the maximum value is 4GB the actual max value is 4294967295.

MAX_ROWS_PER_CACHE_ENTRY = 100000

MAX_ROWS_PER_CACHE_ENTRY is the limit for the number of rows in a single query cache entry, this is designed to stop huge queries consuming all the cache space.

MAX_CACHE_ENTRY_SIZE = 1 MB

MAX_CACHE_ENTRY_SIZE is the maximum physical size for a single entry in the query cache.  If there are queries you want to enter the cache which are not being stored consider increasing this value to capture those results, Oracle suggest not setting this to more than 10% of the max storage for the query cache

MAX_CACHE_ENTRIES = 1000

MAX_CACHE_ENTRIES is  the maximum number of cache entries that can be stored in the query cache, when this limit is reached the entries are replaced using a Least Recently Used (LRU) algorithm.

POPULATE_AGGREGATE_ROLLUP_HITS = NO

POPULATE_AGGREGATE_ROLLUP_HITS is whether to populate the cache with a new entry when it has aggregated data from a previous cache entry to fulfil a request.  By default this is NO.

USE_ADVANCED_HIT_DETECTION = NO

USE_ADVANCED_HIT_DETECTION if you set this to YES then an advanced two-pass algorithm will be used to search for cache entries rather than the normal one-pass.  This means you may get more returns from the cache, but the two-pass algorithm will be computationally more expensive.

In next post i will update the how to manage cache in Administration tool.

- Thanks 

- Rakesh Patil

- rakeshpatil002@gmail.com