Home > Oracle > Disable SYS_AUTO_SQL_TUNING_TASK from DBMS_SCHEDULER and DBMS_JOBS

Disable SYS_AUTO_SQL_TUNING_TASK from DBMS_SCHEDULER and DBMS_JOBS

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;
/

Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: