Sys_auto_sql_tuning_task Status Interrupted
In 11.2.0.3 database , we get alert in EM related to metric “Generic Operational Error” or 'Generic Operational Error Status '. Occasionally when running Automatic SQL Tuning the following messages may appear in the alert log:
Error-
These messages indicate that an auto kill of a 'hung'/long running tuning task has taken place.
This is a protective measure purely to avoid the task from over-running its time limit because of a single task and protects a the system from harm caused by such over-running.
Since this is an expected activity to prevent over-running there is no fix as such.
Instead as a workaround, you could:
FIX 1 :Give the task more time to complete (the following example would set the per statement timeout to 6 hours (21600 seconds)):
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 21600);
END;
/
Note
If you increase the per-statement time limit (LOCAL_TIME_LIMIT) then you 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.
FIX 2 :
Disable the automatic tuning process and the messages will not appear anymore (though obviously the auto tuning will also no longer occur - you could manually execute the job as desired later).
To disable the job:
connect / as sysdba
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
To re-enable in future:
connect / as sysdba
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
Source :
How to Avoid or Prevent 'Process 0x%p appears to be hung in Auto SQL Tuning task' Messages (Doc ID 1344499.1)
Sys_auto_sql_tuning_task Status Interrupted 2017
Mar 30, 2005 Automatic SQL Tuning using SQL Tuning Advisor. By DatabaseJournal.com Staff. By Sreeram Surapaneni Overview: SQL tuning is one of the challenging tasks faced by Database Administrators. It is an interesting and creative, but at the same time, daunting task. SELECT taskname, status FROM dbaadvisorlog WHERE owner = 'SCOTT'; TASKNAME. Automatic SQL Tuning in Oracle Database 11g Release 1; Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2) EXECUTEAUTOTUNINGTASK The EXECUTEAUTOTUNINGTASK procedure and function manually initiate the SYSAUTOSQLTUNINGTASK task. The only difference between the two is the function. Inside the dbmssqltune Package tasks. SQL tuning work is one of the most time consuming and challenging tasks faced by Oracle DBAs and application developers and the Oracle SQL Tuning Advisor is intended to facilitate SQL tuning tasks. Install windows using boot camp.