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)
Auto Sql Tuning Task
Apr 03, 2016 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. The TYPE column of the DBASQLPROFILES view indicates if SQL profiles are created manually ( MANUAL) or automatically ( AUTO-TUNE ). The ENABLE and DISABLE procedures of the DBMSAUTOTASKADMIN package control whether automatic SQL tuning is included in the automated maintenance tasks. Auto sql tuning advisor keeps failing with ORA:7445 while it's tries to tune a particular SQL. Is there any way to skip this sql. Is it possible to skip the particular SQL statement from auto sql tuning advisor job. Ask Question Asked 2 years. This is one of the reasons why I usually disable the Automatic SQL Tuning Advisor. Stop/Enable/Disable Oracle Auto Task Jobs Using SQL – Space advisor, Optimiser stats collection, SQL tuning advisor Oracle 11g onwards there are three different automatic maintenance tasks: 1. Enabling Automated SQL Tuning in Oracle 11g. Tuning auto show rijeka. You will get the last execution date-time and status of Automatic SQL Tuning Task (SYSAUTOTUNINGTASK) or you will get the message No rows selected. To DISABLE Automatic SQL Tuning, use the DISABLE procedure as shown below.
+1 for using a debugger to find out where it's crashing and what the state of your memory is at that point.One thing to bear in mind is that a vector of bool doesn't behave exactly as a vector of any other type does, but uses a specialised implementation:I'm no expert in exactly what those differences are, but it may be that you're doing something with that vector that would be appropriate for a normal vector, but not for vector.EDIT: Ninja'd by shadow fiend, while I was interrupted in writing my reply.