Error: "Query Result Cache: The query for user '$username' exceeded the maximum limit of 20971520 bytes. The query was not inserted into the query result cache." When Attempting to Run an Analysis In OBIEE Resulting In Slow Analysis Loading
(Doc ID 2423188.1)
Last updated on JULY 16, 2018
Applies to:Primavera Analytics Cloud Service - Version 17.5 and later
Primavera Analytics - Version 17.7 and later
Information in this document applies to any platform.
When attempting to run an analysis in OBIEE 12c (via Primavera Analytics), there is a delay in performance and the following error occurs in the log files when the prefix field of the Advanced SQL Clauses section setting ENABLE_CACHE_DIAGNOSTICS=4 and LOGLEVEL=5:
[TIMESTAMP] [OBIS] [TRACE:5]   [ecid: XXX] [sik: ssi] [tid: XX] [messageid: XX] [requestid: XX] [sessionid: XX] [username: <username>] -------------------- Query Result Cache: [XX] The query for user '<username>' exceeded the maximum limit of 20971520 bytes. The query was not inserted into the query result cache.
STEPS TO DUPLICATE
- Login to OBIEE
- Select Catalog, expand folder structure on left to locate analysis/report
- Select Open
- Observe error
Note: If a dashboard analysis is not reading a cache hit as you would expect it, you can diagnose the cache hit behavior of the analysis by executing the following steps:
- Configure the Analysis cache logging:
- Login to OBIEE with an authoring role
- Browse in the catalog the location of your analysis
- Edit the Analysis
- Click the Advanced link
- Within the prefix field of the Advanced SQL Clauses section, add the following: Set Variable ENABLE_CACHE_DIAGNOSTICS=4,LOGLEVEL=5;
- Click Apply SQL
- Save the analysis
- Test the analysis
- Login to OBIEE as the user who will load the analysis
- Load the dashboard with the analysis
- Review the OBIS Query log for detail ($OBIEE_Domain/servers/obis1/logs/obis1-query.log) Note, Cloud Customers will require this log to be provided by the operations team through a service request.
- Alternatively, you can test realtime while an OBIEE administrator is logged in, and the query log can be viewed by the administrator real-time:
- After you've loaded the dashboard analysis, have the administrator browse to Administration > Manage Sessions
- Click the View Log link for your analysis
For additional information on BI Server Cache settings with Primavera Analytics please refer to the following KM document:
Oracle Business Intelligence Enterprise Edition (OBIEE) BI Server Cache And Presentation Server Cache Default Settings With Primavera Analytics (Doc ID 2349232.1)
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!