目录
一、自动优化任务（SYS_AUTO_SQL_TUNING_TASK）
任务包含了三项内容：
自动优化器统计收集（auto optimizer stats collection）
自动分段顾问：（auto space advisor）
自动SQL调整顾问：（sql tuning advisor）
1、查看自动优化任务的参数值
select * from dba_advisor_parameters where task_name like 'SYS_AUTO_SQL_TUNING%' and (parameter_name like '%TIME_LIMIT%' or parameter_name like '%TIME_LIMIT%');
默认自动优化任务是1200秒（20分钟）。如果超时了，就会自动停止。
2、修改优化执行时间
（1）每条sql最大时间--Time limit per statement in a SQL Tuning Set
设置10800s=3小时。
begin
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 10800);
end;
（2）整个分析任务执行时间--The maximum time that an analysis can execute
设置36000s=10小时。
begin
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 36000);
end;
3、查看是否已经完成
status是状态。可以查看开始运行时间和结束时间。
select * from dba_advisor_tasks where task_name = 'SYS_AUTO_SQL_TUNING_TASK';
4、手动执行sql_tuning任务
sqlplus执行；
exec dbms_sqltune.execute_tuning_task(task_name=>'SYS_AUTO_SQL_TUNING_TASK');
（2）pl/sql developer执行
begin exec dbms_sqltune.execute_tuning_task(task_name=>'SYS_AUTO_SQL_TUNING_TASK'); end; /
5、取消正在进行的sql_tuning任务
（1）sqlplus执行；
exec dbms_sqltune.cancel_tuning_task('SYS_AUTO_SQL_TUNING_TASK');
（2）pl/sql developer执行
begin exec dbms_sqltune.cancel_tuning_task('SYS_AUTO_SQL_TUNING_TASK'); end; /
二、计划维护任务
自动维护任务是一种按规则自动启动的数据库维护操作任务。
比如自动收集为查询优化器使用的统计信息。
自动维护任务按维护窗口自动运行。
1、Oracle 11g 三种预定义的自动维护任务：
任务查看：
SQL>select * from dba_autotask_client; SQL> select client_name,status,window_group from dba_autotask_client;
（1） 自动优化器统计收集（auto optimizer stats collection）
为所有方案对象收集陈旧的或缺少的统计数据，所收集的统计信息将被用来提高sql的执行的性能，任务名是“auto optimizer stats collection”
（2）自动分段顾问：（auto space advisor）
标识数据库中的段是否有可以回收的空间，并以此信息统计为基础做出怎样整理段的碎片以节约空间。
你也可以手动的执行此job来获取最新的建议信息，或者获取自动段advisor 不检测的但又可以回收的段的信息，任务名是“auto space advisor”
（3）自动SQL调整顾问：（sql tuning advisor）
自动标识并尝试调整高负载的SQL，任务名是“sql tuning advisor”
缺省情况下，这三个任务配置为在所有维护窗口运行。
2、什么是维护窗口
所谓自动维护窗口是按照预定义的间隔时间窗口。
维护窗口是一个连续的时间间隔，用于管理自动维护任务所用。
维护窗口是oracle 调度窗口，属于窗口组MAINTENANCE_WINDOW_GROUP。
3、维护窗口查看
select window_name,resource_plan,start_date,repeat_interval,duration,next_start_date,enabled,active from dba_scheduler_windows;
三、启用/禁用自动维护任务
1、启用或禁用所有自动维护任务的所有窗口
（1）禁用
begin EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE; end; /
（2）启用
begin EXECUTE DBMS_AUTO_TASK_ADMIN.enable; end; /
2、启用或禁止某个自动维护任务的所有窗口（默认有3个任务）：
使用DBMS_AUTO_ADMIN pl/sql包来启用或禁用任务：
（1）如：禁用所有的sql tuning advisor任务
BEGIN dbms_auto_task_admin.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END;
（2）启用所有的sql tuning advisor任务（如：sql tuning advisor）
BEGIN dbms_auto_task_admin.enable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END;
这里client_name参数可以通过数据字典视图DBA_AUTOTASK_CLIENT来查询。
sys@ORCL11> select client_name from dba_autotask_client; CLIENT_NAME ------------------------------------------------------------ auto optimizer stats collection auto space advisor sql tuning advisor
3、启用或禁用某个自动维护任务的某个维护窗
（1）禁用（如：sql tuning advisor的周一窗口）
BEGIN dbms_auto_task_admin.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => 'MONDAY_WINDOW'); END;
（2）启用（如：sql tuning advisor的周一窗口）
BEGIN dbms_auto_task_admin.enable( client_name => 'sql tuning advisor', operation => NULL, window_name => 'MONDAY_WINDOW'); END;
四、维护窗口属性：
1、修改窗口的属性步骤
（1）首先禁用维护窗口
BEGIN
dbms_scheduler.disable
(
name => 'SATURDAY_WINDOW'
);
END;
/
（2）修改维护窗口属性：
--修改周六的窗口属性（修改value,间隔4小时进行一次）
begin
dbms_scheduler.set_attribute
(
name => 'SATURDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(4, 'hour')
);
end;
/
--修改周五的窗口属性（修改value值，每周五11点40开始）
begin
dbms_scheduler.set_attribute
(
name => 'FRIDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=FRI;byhour=11;byminute=40; bysecond=0'
);
end;
/
（3）启用维护窗口：
BEGIN
dbms_scheduler.enable
(
name => 'SATURDAY_WINDOW'
);
END;
/
2、创建新窗口：
BEGIN
dbms_scheduler.create_window(
window_name => 'EARLY_MORNING_WINDOW',
duration => numtodsinterval(1, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_window_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
3、删除窗口：
BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
五、立即停止正在运行的任务
1、查看当前正在运行的任务
（1）JOBS：
jobs是oracle数据库的对象，由dbms_scheduler.create_job这个包创建，即使用program,scheduler来配置job
通过视图：
SQL>SELECT * FROM USER_SCHEDULER_RUNNING_JOBS;
（2）DBMS_JOBS：
dbms_jobs只是jobs对象的一个实例， 就像对于tables， emp和dept都是表的实例。
可以通过视图查询：
SELECT * FROM DBA_JOBS_RUNNING;
停止运行job：
dbms_job.broken(jobid,true);
2、立即停止任务
（1）sqlplus执行
SQL>exec dbms_scheduler.stop_job('job_name名称'，force=>true);
（2）pl/sql执行
begin
dbms_scheduler.stop_job('job_name名称'，force=>true);
end;
/
六、跟踪job运行历史情况：
SQL> select client_name,job_name,job_start_time from dba_autotask_job_history;
CLIENT_NAME JOB_NAME JOB_START_TIME -------------------- -------------------- ------------------------------------ auto optimizer stats ORA$AT_OS_OPT_SY_1 09-APR-12 10.00.02.039000 PM +08:00 auto space advisor ORA$AT_SA_SPC_SY_2 09-APR-12 10.00.02.050000 PM +08:00 sql tuning advisor ORA$AT_SQ_SQL_SW_3 09-APR-12 10.00.02.015000 PM +08:0
七、跟踪job运行历史情况：
数据库默认sql tuning advisor，导致大量library cache lock
1、问题现象：
客户反映周六周日固定十点钟，一个程序会特别慢（大概10分钟），平时1到2秒。
（1）查看当时的日志发现：
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file. Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_j002_51847.trc: ORA-04021: timeout occurred while waiting to lock object
（2）查看trc文件发现：
ORA-04021: timeout occurred while waiting to lock object
*** 2015-09-20 10:17:51.129
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"CUSTOMERUSER"','"CUSTOM_TABLE"','"CUSTOMER_201509"', ...)
DBMS_STATS: ORA-04021: timeout occurred while waiting to lock object
2、分析
根据经验判断是锁导致，一下是解决方法和分析过程：
经过分析，该问题的原因是Automatic SQL Tuning Advisor Job在尝试调优一个较为复杂的SQL "6ru8vx9kjbv1k" 时，花了太多的时间在CPU上，并且它以S模式持有了
CUSTOMERUSER.CUSTOM_TABLE上的library cache lock，而另外一个收集统计信息的job需要以X模式来
获取CUSTOMERUSER.CUSTOM_TABLE上的library cache lock，S和X不兼容导致阻塞，直到收集统计信息的job超时 。
在此期间，所有的需要在CUSTOMERUSER.CUSTOM_TABLE的hard parse由于都需要以S模式申请表上的library cache lock 而被收集统计信息的那个job阻塞，因此您的数据库会出现大量的library cache lock而拖慢了应用。
3、解决思路：
解决办法是关闭Automatic SQL Tuning Advisor Job，这不会对您的数据库产生任何性能影响，因为 Automatic SQL Tuning Advisor Job只是给出优化建议，并没有执行任何实质性的操作，除非您接受优化建议。
4、解决方式：
根据上述二.2.(1)进行禁用，如：禁用所有的sql tuning advisor任务
BEGIN dbms_auto_task_admin.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END;
如果要开启，则enable即可
BEGIN dbms_auto_task_admin.enable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END;
