OBIEE includes 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. This post will concentrate on the former.
NQSConfig
Caching is enabled and configured in the NQSConfig.ini file located at\server\Config, look for this section:
###############################################################################
#
# Query Result Cache Section
#
###############################################################################
[ CACHE ]
ENABLE = YES;
//A comma separated list ofpair(s)
//DATA_STORAGE_PATHS = "C:\OracleBIData\nQSCache" 500 MB;(For ex)
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;
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 YESthen query caching will take place.
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
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 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 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 is whether to populate the cache with a new entry when it has aggregated data from a previous cache entry to fulfill a request. By default this is 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.
NQSConfig
Caching is enabled and configured in the NQSConfig.ini file located at
#
# Query Result Cache Section
#
###############################################################################
[ CACHE ]
ENABLE = YES;
//A comma separated list of
//DATA_STORAGE_PATHS = "C:\OracleBIData\nQSCache" 500 MB;(For ex)
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;
USE_ADVANCED_HIT_DETECTION = NO;
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 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 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 is whether to populate the cache with a new entry when it has aggregated data from a previous cache entry to fulfill a request. By default this is 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.
Posts
