Automatic tuning advisor task fails with ORA errors
Automatic tuning advisor task fails with ORA-16957 or ORA-13639
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;
/
Once the problematic query is identified and is not possible to to tune it , then other way to solve this would be to disable the advisor tasks.
----
Comments
Post a Comment