在Oracle 11g中,默认有3个自动任务,分别是:自动统计信息收集、SQL调优顾问、段空间调整顾问,查看方法如下:
col CLIENT_NAME for a40
col TASK_NAME for a40
col OPERATION_NAME for a40
SELECT CLIENT_NAME,TASK_NAME,OPERATION_NAME,STATUS FROM dba_autotask_task;
自动统计信息收集,使用的任务为gather_stats_prog。gather_stats_prog调用了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程
col PROGRAM_ACTION for a50
SELECT PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION FROM dba_scheduler_programs WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';
在Oracle 11g中,一共配置了7个自动维护窗口,每天一个窗口
SELECT * FROM dba_autotask_window_clients;
每个窗口运行时间等信息:
select t.window_name,
t.resource_plan,
t.schedule_type,
t.repeat_interval,
t.duration,
t.window_priority,
t.next_start_date,
t.last_start_date,
t.enabled,
t.active,
t.comments
from dba_scheduler_windows t
where ENABLED = 'TRUE';
在窗口任务启动时,自动任务GATHER_STATS_PROG每次运行时会先生成ORA$AT_OS_OPT_xxx的作业,然后再执行这个作业。
SELECT a.JOB_NAME,a.ACTUAL_START_DATE,a.RUN_DURATION,a.STATUS FROM dba_scheduler_job_run_details a WHERE a.JOB_NAME LIKE 'ORA$AT_OS_OPT%';
统计信息收集策略
每次自动收集统计信息,并不是对所有表都进行收集,Oracle只对那些已经统计信息失效的对象进行收集,那么Oracle如何判断哪些对象的统计信息失效了呢?
在Oracle 11g中,如果参数STATISTICS_LEVEL的值为TYPICAL(默认)或者ALL,则DBA_TAB_MODIFICATIONS会记录自上次自动统计信息收集完成之后对目标表的insert、update、delete的操作影响行数,并且还会记录自从上次自动收集统计信息之后是否发生过truncate。需要注意的是DBA_TAB_MODIFICATIONS并不会实时更新,如果需要查看最新信息,可以手动更新该表的信息:
EXEC dbms_stats.flush_database_monitoring_info();
Oracle收集失效的统计信息的策略:
自上次自动统计信息收集作业完成之后,如果DBA_TAB_MODIFICATIONS中记录的INSERT+UPDATE+DELETE所影响的行记录之和超过了DBA_TABLES中目标表记录数的10%,或者是自上次统计信息收集完成之后目标表执行过truncate操作,那么Oracle会认为目标表的统计信息已经失效,自动统计信息收集作业就会对目标表重新收集统计信息。
使用以下方法可以禁用/启用自动统计信息收集
SQL> EXEC dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);
查看是否已经关闭:
SELECT WINDOW_NAME,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR FROM DBA_AUTOTASK_WINDOW_CLIENTS
可以使用如下方法重新打开自动统计信息收集:
SQL> EXEC dbms_auto_task_admin.enable(client_name=> 'auto optimizer stats collection',operation=> NULL,window_name=> NULL);
使用DBMS_SCHEDULER.DISABLE可以禁用维护窗口,从而禁用统计信息收集
----禁掉周一的自动维护作业,包括统计信息收集、段顾问、sql调优顾问
EXEC dbms_scheduler.disable(NAME=> 'SYS.MONDAY_WINDOW',FORCE=> TRUE)
----启用周一的自动维护作业,包括统计信息收集、段顾问、sql调优顾问
EXEC dbms_scheduler.enable(NAME=>'SYS.MONDAY_WINDOW');
----禁掉周二的自动统计信息收集,段顾问、sql调优顾问保持开启
EXEC dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>'TUESDAY_WINDOW');
----再次开启:
EXEC dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>'TUESDAY_WINDOW');
----禁用窗口
EXEC dbms_scheduler.disable(NAME=> 'SYS.MONDAY_WINDOW',FORCE=> TRUE)
----修改启动时间为23点
EXEC dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW',attribute => 'REPEAT_INTERVAL',value => 'freq=daily;byday=TUE;byhour=23;byminute=0; bysecond=0');
----启用窗口
EXEC dbms_scheduler.enable(NAME=>'SYS.MONDAY_WINDOW');
在Oracle 11g中,默认统计信息的收集阈值为10%,即10%的行数据发生变化或者执行了truncate,才会再次收集统计信息。我们可以使用下面的方法针对单个表修改阈值。
----修改test01表的统计信息收集阈值为5%。
查看初始的阈值:
SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'TEST',tabname => 'KECHENG') FROM dual;
DBMS_STATS.GET_PREFS(PNAME=>'STALE_PERCENT',OWNNAME=>'TEST',TABNAME=>'KECHENG')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
SQL>
修改阈值为5:
SQL> EXEC dbms_stats.set_table_prefs(ownname => 'TEST',tabname => 'KECHENG',pname => 'STALE_PERCENT',pvalue => 5);
PL/SQL procedure successfully completed
确认修改后的阈值:
SQL> SELECT dbms_stats.get_prefs(pname => 'STALE_PERCENT',ownname => 'TEST',tabname => 'KECHENG') FROM dual;
DBMS_STATS.GET_PREFS(PNAME=>'STALE_PERCENT',OWNNAME=>'TEST',TABNAME=>'KECHENG')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5
注意:当阈值为0时,不管数据如何变化,每天都会自动收集统计信息。
标签:11g,NAME,收集,EXEC,dbms,信息,Oracle,统计 From: https://www.cnblogs.com/guaika/p/18375299