Category Archives: Cache Optimization

OBIEE Tutorials

While browsing web, I’ve found the following question:

“The query logs do show that there is a cache hit but the report still takes more than 1.5 mins to display. Why ?”

Solution offered

Checking the nqquery.log when the cache is hit, it was found the time elapsed between running the query, and getting the results was only 34 seconds
2008/10/20 07:17:38
2008/10/20 07:18:12

There is a defect logged to address the fact that you might see differences in the following measures for the same query:
-Time (Analytics web > Administration > Manage Sessions > Time column
-TOTAL_TIME_SEC (Usage tracking parameter)
-Elapsed Time (in NqQuery.log)

Elapsed Time (in NqQuery.log):
This is the total clock time it takes from the point SAS receives request to
the moment it gets data from SAW or ODBC client, until the moment data leaves
Analytics Server. Response Time and Physical Query Response Time are both
included in this.
These time parameters in NQQuery.log file do not reflect the time data spent
travelling between Analytics Web and Analytics Server.

TOTAL_TIME_SEC (Usage tracking parameter):
The time in seconds that the Oracle BI Server spent working on the query
while the client waited for responses to its query requests.

Time (Analytics web > Administration > Manage Sessions > Time column

This defect is targeted to be fixed in the next main release

Also it was recommended to change the following NQSconfig.ini settings which were set very high to 100000.. The recommended values are as follows:

MAX_QUERY_PLAN_CACHE_ENTRIES = 1024; // default is 1024
MAX_DRILLDOWN_INFO_CACHE_ENTRIES = 1024; // default is 1024
MAX_DRILLDOWN_QUERY_CACHE_ENTRIES = 1024; // default is 1024

OBIEE Caching

There’s an excellent article by John Minkjan about OBIEE caching. He covers most of the important functions of OBIEE caching in detail as well as providing valuable information in regards to the NQConfig.ini cache settings. This is probably the best OBIEE caching tutorial available publicly. It has helped me a lot when preparing documentation for deployment of a new project.