Exalytics: OBIEE 11.1.1.7.1 Query Fails with Error Codes: "OAMP2OPY:GIQKCQ7V Too Many Running Queries. Server Is Too Busy To Process Any More Queries at this Time."

Asset ID:	1-72-1911202.1
Update Date:	2017-09-29
Keywords:

Solution Type Problem Resolution Sure

Solution 1911202.1 : Exalytics: OBIEE 11.1.1.7.1 Query Fails with Error Codes: "OAMP2OPY:GIQKCQ7V Too Many Running Queries. Server Is Too Busy To Process Any More Queries at this Time."

Applies to:

Oracle Exalytics Software - Version 1.0.0.3.0 and later
Business Intelligence Suite Enterprise Edition - Version 11.1.1.7.1 and later
Exalytics In-Memory Machine X3-4 - Version All Versions and later
Exalytics In-Memory Machine X2-4 - Version All Versions and later
Information in this document applies to any platform.

Symptoms

Users are encountering errors when using Exalytics and running Oracle Business Intelligence Enterprise Edition (OBIEE) 11.1.1.7.1 reports and dashboards against tables that contain millions of rows. The error shown in the web browser is:

Error Codes:
OAMP2OPY:GIQKCQ7V
- Too many running queries. Server is too busy to process any more queries at this time

The NQQuery.log does not show a matching error, but there are many errors related to missed cache entries near the same time that the browser error was reported:

... The logical query disqualifies the plan cache [[plan exceeds maximum size

...The logical query block fail to hits or seed the cache in subrequest level due to [[only one subrequest]]

Cause

Maximum threads on the OBIEE server is set too low to handle query load. Current NQSConfig.ini file shows thread settings as:

SERVER_THREAD_RANGE = 50-520;
DB_GATEWAY_THREAD_RANGE = 50-520;

These NQSConfig.ini values correlate to the maximum number of concurrent client connections (sessions) to the OBI server:

Each client request to the OBI Server uses one SERVER_THREAD
Each SERVER_THREAD uses 0 or more DB_GATEWAY_THREADs depending upon the number of DB queries executed.

These values are set too low for an Exalytics system with queries against tables that have millions of rows.

Solution

To prevent this error, you will need to modify the settings in the NSQConfig.ini file.

1. First back up the current NQSConfig.ini file on the Exalytics server.
2. Then make the changes indicated below:

SERVER_THREAD_RANGE = 500 - 1000;
DB_GATEWAY_THREAD_RANGE = 500 - 1000;

3. After saving these changes, you will need to stop and restart the WLS and OBIEE servers for changes to take effect.

Reference: OracleFusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1)
Part Number E10541-07

NOTE: Typically Exalytics X2-3 and X3-4 servers have 1 - 2 Terabytes of RAM and can handle higher load. Using these same thread values on non-Exalytics servers could lead to errors and performance degradation and is not recommended. Exalytics Product Management best practices recommendation is to increase these values as shown on Exalytics servers only.

If you are encountering this error on a standalone OBIEE server, please refer to the recommendations in <Note 1333049.1> OBIEE 11g Infrastructure Performance Tuning Guide.

References

<BUG:17068257> - "OAMP2OPY:GIQKCQ7 TOO MANY RUNNING QUERIES. SERVER IS TOO BUSY TO PROCESS ANY MO
<NOTE:1462776.1> - Oracle Business Intelligence Enterprise Edition (OBIEE) Gives Error: "OAMP2OPY:GIQKCQ7 Too many running queries. Server is too busy to process any more queries at this time."
<NOTE:1333049.1> - OBIEE 11g Infrastructure Performance Tuning Guide

Attachments

This solution has no attachment