My Oracle Support Banner

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 MARCH 20, 2024

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.

Symptoms

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:

ERROR
---------

[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
-----------------------

  1. Login to OBIEE
  2. Select Catalog, expand folder structure on left to locate analysis/report
  3. Select Open
  4. 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:

  1. Configure the Analysis cache logging:
    1. Login to OBIEE with an authoring role
    2. Browse in the catalog the location of your analysis
    3. Edit the Analysis
    4. Click the Advanced link
    5. Within the prefix field of the Advanced SQL Clauses section, add the following: Set Variable ENABLE_CACHE_DIAGNOSTICS=4,LOGLEVEL=5;
    6. Click Apply SQL
    7. Save the analysis
  2. Test the analysis
    1. Login to OBIEE as the user who will load the analysis
    2. Load the dashboard with the analysis
    3. 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.
    4. Alternatively, you can test realtime while an OBIEE administrator is logged in, and the query log can be viewed by the administrator real-time:
    5. After you've loaded the dashboard analysis, have the administrator browse to Administration > Manage Sessions
    6. 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)

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.