After Upgrade to OAS 2024, [Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired.

User_9DRNE

We just upgraded to OAS version 2024. We have reports that are erroring out in Analytics due to the amount of time they take to run. We are getting error [Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired. (HY000).

Any idea how to up the query limit in 7.6 (2024) so some of our longer running reports can complete?

Sumanth V -Oracle

@User_9DRNE - Please refer the below note and increase the value of DEFAULT_DB_MAX_EXEC_TIME

After In-Place Upgrade To Oracle Analytics Server (OAS) 2022, Some Analysis Queries Fail With '[nQSError: 60009] The user request exceeded the maximum query governing execution time.' (Doc ID 2909923.1)

It determines the maximum time in seconds that a database query runs.

If the execution time of a database query exceeds the number of seconds set for this parameter, the error message [nQSError: 60009] The user request exceeded the maximum query governing execution time. is displayed, and the query is terminated. If query execution time is less than the number of seconds set for this parameter, then the query completes and no error message is displayed. The default value is 0, and the maximum value is unlimited.

Example: DEFAULT_DB_MAX_EXEC_TIME = 600;

Reference: https://docs.oracle.com/en/middleware/bi/analytics-server/administer-oas/configuration-file-settings.html#GUID-B5F3D217-D0AB-4E1D-BD90-38CAFDE34329

Mallikarjuna Kuppauru-Oracle

Hi @User_9DRNE

Can you check below?

RPD

Go to Manage-->Identity-->Application Roles

Select the ROLE and click on the permissions tab.

In the next wizard go to the “Query limits” tab.

Against the database row set the following values

Max Time Minutes=20

Status Max Time=Enable

Retest the same ?

Regards,

Arjun

Sumanth V -Oracle

@User_9DRNE-

Could you please let us know if the upgrade was performed as an in-place upgrade or out-of-place upgrade?

Additionally, to rule out any database limits causing the issue, kindly confirm whether the physical SQL executes successfully on the data source.

SteveF-Oracle

Hi @User_9DRNE ,
To add

Thanks for posting in the Community, please update your Display Name, so we all know whom we are communicating with.

It is difficult to say explicitly without some more contextual information.

Is this a classic analysis, a workbook, a Publisher report?

Are you seeing the error in the UI (which)?, or in a specific log (which)?

User_9DRNE

Hopefully I answer everyone's questions. Thank you for the quick responses.

I updated my profile so hopefully it will show my name.

@ Mallikarjuna Kuppauru-Oracle
I did check the RPD settings, and we attempted to update the “Query limits” with the out of the box role and this did not work. We do not have this same issue in other environments that are still version 7.0 and we do not have this setting done in them environments and never have. But we did try.

@ Sumanth V -Oracle
This was an in place upgrade from version 7.0. The SQL runs fine on the data source and in other environments that have not been upgraded.

@ SteveF-Oracle
This is a classic analysis/dashboard page in the analytics UI. We are seeing the error after the reports runs for about 1 min. We have some reports that run for several mins and this issue seems to be impacting all of them reports. Reports that render quickly are running as expected. Along with the error above I am also getting this one State: HY000. Code: 60009. [nQSError: 60009] The user request exceeded the maximum query governing execution time. (HY000).

I believe at one time we set our query time in the system somewhere to 15 mins max run time. We do have a few reports that run close to that time. I do not believe these are database issues because this is only an issue in our newly upgraded enviroment. I hope I answered most of the questions. Thanks in advance for your help!

Jenny Self

Harriet Huang-Oracle

Hi @User_9DRNE,

To narrow down the issue and make sure there is no issue in database side, you can run the analysis till timeout, and then check session log through Administration -> Manage Sessions to see if the physical query runs successfully or not. If not, copy the physical query and run in database directly, and check how long does it complete.