Oracle提供了统计数据自动收集功能。在部署安装软件过程中，其中有一个步骤便是提示是否启用这个功能(默认是启用这个功能)。这个功能貌似带来了统计数据采集上的便捷，但是其中却隐藏着性能隐患。在7*24小时的系统中这种自动运行的任务很有可能对系统性能带来冲击。因此，我们酌情需要修改或是禁掉这项功能。10g和11g的处理方法有所不同，以下会分别阐述。
一、Oracle 10g的统计信息收集
1、自动统计作业的查询
Oracle 10g版本中，自动统计的job名为GATHER_STATS_JOB，该任务默认在工作日晚上22：00-6：00和周末全天为运行窗口期，在运行窗口期内，该作业都会运行，根据stop_on_window_close属性来决定如果在窗口期结束以后该作业还没有运行完毕，是继续运行还是结束运行。
该作业调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息，该过程与另一个过程DBMS_STATS.GATHER_DATABASE_STATS功能相近，但有优先处理顺序的考虑，首先检测统计信息缺失和陈旧的对象，然后确定优先级，更新越多的表将越优先统计。
收集统计信息的表对象是之前从来没有收集过的或者更新记录数超过当前总记录数10%的表。记录数的更改量由Oracle数据库自动监控，在初始化参数statistics_level设置为TYPICAL或者ALL时，自动监控即会生效。在表user_tab_modifications中记录了所有被监控的表的数据量更改信息，该信息的更新会稍微滞后于真实的修改，可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO过程立刻将更改的信息更新到USER_TAB_MODIFICATIONS表中。对于更新之后再rollback的记录，仍然算为已经受影响的记录，Oracle不会在rollback之后再去更新USER_TAB_MODIFICATIONS表。
下面是在10g中查看自动收集统计作业的方法：
col program_name for a30
col schedule_name for a30
col last_run_duration for a30
select owner, job_name, program_name, schedule_name, job_class, enabled, auto_drop, run_count, to_char(last_start_date, 'yyyy-mm-dd hh24:mi:ss') last_start_time, last_run_duration, state from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
OWNER JOB_NAME PROGRAM_NAME SCHEDULE_NAME JOB_CLASS ENABL AUTO_ RUN_COUNT LAST_START_TIME LAST_RUN_DURATION STATE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----- ----- ---------- ------------------- ------------------------------ ---------------
SYS GATHER_STATS_JOB GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP AUTO_TASKS_JOB_CLASS TRUE FALSE 29 2017-01-28 06:00:05 +000000000 00:00:38.535000 SCHEDULED
以下查看自动收集统计作业的历次运行明细，该表会保留最近一个月的记录：
col owner for a10
col job_name for a20
col status for a10
col run_duration for a20
col session_id for a10
col cpu_used for a20
select owner, job_name, status, to_char(actual_start_date, 'yyyy-mm-dd hh24:mi:ss') actual_start_time, run_duration, session_id, cpu_used
from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB' order by actual_start_date desc;
即便作业被删除，我们仍可以通过log记录查看作业的历史运行情况：
col owner for a10
col job_name for a30
col operation for a20
col status for a10
select to_char(log_date, 'yyyy-mm-dd hh24:mi:ss') log_time, owner, job_name, job_class, operation, status from dba_scheduler_job_log
where job_name = 'GATHER_STATS_JOB' order by log_date desc;
如果这个作业正在运行中，可以通过以下查询看到：
col elapsed_time for a30
select job_name, session_id, elapsed_time, cpu_used from dba_scheduler_running_jobs;
2、自动统计作业的关闭和启用
方法一：
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
方法二：
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;
3、修改自动统计作业的执行时间
1)查看自动统计作业的时间窗口信息：
col window_name for a20
col repeat_interval for a80
col duration for a20
select t1.window_name, t1.repeat_interval, t1.duration
from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
where t1.window_name = t2.window_name
and t2.window_group_name in
(select window_group_name
from dba_scheduler_wingroup_members
where window_group_name in
(select schedule_name
from dba_scheduler_jobs
where job_name = 'GATHER_STATS_JOB'));
2)根据工厂实际开班时间修改这两个窗口，例如可以利用中午吃饭时间进行自动统计，以便避开对生产的干扰。在SYS下执行：
begin
dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW',
'REPEAT_INTERVAL',
'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=11;byminute=0; bysecond=0');
dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW',
'DURATION',
'+000 00:30:00');
dbms_scheduler.set_attribute('WEEKEND_WINDOW',
'REPEAT_INTERVAL',
'freq=daily;byday=SAT,SUN;byhour=11;byminute=0;bysecond=0');
dbms_scheduler.set_attribute('WEEKEND_WINDOW',
'DURATION',
'+000 00:30:00');
end;
/
3)修改成功后再次查看窗口时间表信息已达到目的：
二、针对Oracle 11g的处理方法
1、查看自动统计任务及其状态
select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
-------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
其中“auto optimizer stats collection”便是我们要寻找的自动收集统计信息的任务名称，它的状态目前是启用状态。
在11g之前，当表的数据量修改超过总数据量的10%，就会自动收集统计信息，这个STALE_PERCENT=10%是无法修改的，如果表非常大，10%是非常多的数据，有可能造成统计信息不准确。在11g之后，STALE_PERCENT=10%是可以修改的，分为全局(DBMS_STATS.SET_GLOBAL_PREFS)和表级别(DBMS_STATS.SET_TABLE_PREFS)。表级别的设定：
修改为5%(范围从1-100)：
exec dbms_stats.set_table_prefs(null,'table_name','STALE_PERCENT',5);
恢复为10%：
exec dbms_stats.set_table_prefs(null,'table_name','STALE_PERCENT',null);
查询百分比：
select dbms_stats.get_prefs('STALE_PERCENT',null,'table_name') from dual;
2、禁止自动统计任务
我们可以使用DBMS_AUTO_TASK_ADMIN包完成这个任务。
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);
select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
-------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
此时“auto optimizer stats collection”任务已经被禁用。
3、启用自动统计任务
exec dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => null, window_name => null);
select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
-------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
4、修改自动统计任务的执行时间
1)查询自动统计任务的执行窗口时间表：
select t1.window_name, t1.repeat_interval, t1.duration
from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
where t1.window_name = t2.window_name
and t2.window_group_name in
(select window_group
from dba_autotask_client t3
where t3.client_name = 'auto optimizer stats collection');
2)根据需要修改这些窗口的时间表，方法同10g中调用dbms_scheduler.set_attribute过程。
3)实际上，还有另外两个自动任务：
select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
-------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
利用上面的查询可以验证，它们与自动统计任务实际上是默认被安排在同一时间表上的，也可以根据需要修改。
三、手动收集统计信息
对于数据量很大的库，一般是建议关闭掉自动收集的任务，通过自己手工写存储过程，再把存储过程加入JOB中去收集统计信息。以下是一个手动收集统计信息的例子：
create or replace procedure manual_gather_stats is /** * 表的手动统计信息收集 */ my_username varchar2(20); cursor stale_table is select segment_name from user_segments where segment_name in (select table_name from user_tab_statistics where last_analyzed is null or stale_stats = 'YES') group by segment_name; stale stale_table%rowtype; begin dbms_stats.flush_database_monitoring_info; select username into my_username from user_users; for stale in stale_table loop dbms_stats.gather_table_stats(ownname => my_username, tabname => stale.segment_name, estimate_percent => null, degree => null, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => true); end loop; end; /
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28974745/viewspace-2148664/ ，如需转载，请注明出处，否则将追究法律责任。
