Ora-16957 Sql Analyze Time Limit Interrupt Opiodr Aborting Process

There are couple of notes published on Oracle Support which deal with these errors.

This blog post deals with the Solution , workaround and a tiny ray of hope :-).

The following error is reported in the alert.log when SQL Tuning Advisor job is run:

ORA-16957: SQL Analyze time limit interrupt

OR

ORA-13639: The current operation was interrupted because it timed out.

Oracle 11g onward, the SQL Tuning Advisor runs against high impact SQL statements during the maintenance window. The default job "Time Limit" is 3600 seconds.

If the Auto tuning task job cannot finish within a specific time period, it will terminate with an ORA-13639.

The ORA-16957 error is an internal error code used to indicate that SQL Tuning Task has reached the time limit for tuning a specific sql.

The Default values set in 11.2 and 12.1 :

SQL> COL parameter_name FOR A18
SQL> COL parameter_value FOR A15
SQL> COL description FOR A45
SQL> SET linesize 200
SQL> SELECT parameter_name, parameter_value, description
FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('TIME_LIMIT','LOCAL_TIME_LIMIT');

PARAMETER_NAME PARAMETER_VALUE DESCRIPTION
—————— ————— ———————————————
LOCAL_TIME_LIMIT 1200 Time limit per statement in a SQL Tuning Set
TIME_LIMIT 3600 The maximum time that an analysis can execute

LOCAL_TIME_LIMIT defines the time limit per statement which is in the Tuning Set and TIME_LIMIT defines the total time limit for the SQL Tuning Task job to complete.

At times, when we are running the SQL Tuning task on relatively new complex SQLs, it could take much longer than the Default limit and it could fail with the above errors.

To workaround the errors, we can increase the value for LOCAL_TIME_LIMIT and TIME_LIMIT.

BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', <value in seconds>);
END;
/

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(parameter => 'TIME_LIMIT', value => <value in seconds>);
END;
/

However, we need to note that if we increase the per-statement time limit (LOCAL_TIME_LIMIT) then we need to stay within the bounds of the time limit for the entire task (TIME_LIMIT). The duration of the TIME_LIMIT parameter must be at least equal or greater than the LOCAL_TIME_LIMIT. When the maintenance window closes the SQL Tuning Advisor is stopped.

How do we identify the SQL causing the issue in the Tuning task? You can find the solution here.

The next question, What if I set the values to the Maximum amount possible ( 4 hour window = 14400 seconds ) and it still fails?

We have two options, Disable the SQL Tuning Advisor or manually tune the SQL with DBMS_SQLTUNE by creating a SQL tuning task.

Oracle Support Document Doc ID 262687.1: Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor

To disable the Automatic Tuning Task:
connect / as sysdba

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

Here comes the good part:

We have identified the query and we aren't able to tune it , the only other plausible would be to disable the advisor. What if we don't want to disable the SQL Tuning Advisor , Is there an option to skip the queries from the Auto SQL Tuning Task?

Fortunately, there has been an enhancement request raise with Oracle Support to implement this feature in a future release, hopefully 12.2.

Fingers crossed! 🙂

About suntrupth

Technical Product Manager, Database SME, Database Performance & SQL Tuning Enthusiast

Ora-16957 Sql Analyze Time Limit Interrupt Opiodr Aborting Process

Source: https://suntrupth.wordpress.com/2016/06/23/automatic-sql-tuning-advisor-task-fails-with-ora-16957-or-ora-13639/

Share this post