Archive

Archive for December, 2012

Disable SYS_AUTO_SQL_TUNING_TASK from DBMS_SCHEDULER and DBMS_JOBS

December 12, 2012 Leave a comment

Why should you disable SYS_AUTO_SQL_TUNING_TASK on Production DB systems?
On Production DBs, SQL should have been thoroughly tested prior to release into Production. SYS_AUTO_SQL_TUNING_TASK may cause SQL plans to change and in doing so would affect the performance of your queries.

Here’s how to disable SYS_AUTO_SQL_TUNING_TASK from DBMS_SCHEDULER and DBMS_JOBS:
conn / as sysdba

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

If you are running this particular task, you may notice these errors:
“Process 0x%p appears to be hung in Auto SQL Tuning task”
“Current time = %u, process death time = %u”
“Attempting to kill process 0x%p with OS pid = %s”
“OSD kill skipped for process %p”
“OSD kill succeeded for process %p”
“OSD kill failed for process %p”

These errors usually indicate the job ran over the scheduled maintenance window. To extend the window to 21600s:
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(‘SYS_AUTO_SQL_TUNING_TASK’, ‘LOCAL_TIME_LIMIT’, 21600);
END;
/

Otherwise, you may choose to disable the tuning process, just like above:
conn / as sysdba

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

Advertisements
Categories: Oracle