OBIEE Cache Issues
The OBIEE cache is enabled but the query is not being cached. There may be multiple reasons behind that; some of them is as follows;
Use of Non-cacheable SQL functions:
If any of the Non-cacheable functions are used in the request then the query will not be cached.
i.e CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE
Non-cacheable Table:
If the query for the request makes any connection to a non cacheable table then the query/results will not be cached even if all other tables are set to cacheable.
Query execution Terminated:
While running the request on Presentation Server if the execution is forcibly cancelled or time out occurred, cache will not create.
Cache Configuration not enabled:
There is a cache enable/disable entry in NQSConfig.ini file. Cache will be enabled only if it is set to YES.
Bad cache configuration:
If the MAX_CACHE_ENTRY_SIZE parameter is set to a bigger value as compared to the actual storage space (DATA_STORAGE_PATHS) available on the drive then nothing can possibly be added to the cache.
Result set to Large to Handle by Cache:
The max row limit for cache is set in a parameter in nqsconfig.ini, named MAX_ROWS_PER_CACHE_ENTRY (default 100,000 rows) and MAX_CACHE_ENTRY_SIZE (default 1 MB). If the rows retrieved in the request exceeds the MAX ROW LIMIT or the data retrieved by the request exceeds the MAX CACHE ENTRY SIZE then the cache will not be created.
Query got a cache hit:
In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.
Cheers!
