一.11g中auto stats gather job被集成到了auto task中。
SQL> select client_name,status from DBA_AUTOTASK_CLIENT; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED SQl>select client_name,operation_name from dba_autotask_operation t; ------------------ --------------------------- CLIENT_NAME OPERATION_NAME auto optimizer stats collection auto optimizer stats job auto space advisor auto space advisor job sql tuning advisor automatic sql tuning task
可以通过以上视图进行名字和状态的查看。
禁用的时候，使用存储过程逐个禁用。
SQL> begin
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/
PL/SQL procedure successfully completed.
SQL> select client_name,status from DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
二.在10G版本中,自动收集的job名字为:GATHER_STATS_JOB,默认情况下该job每天晚上10点执行.
1.查看该job的状态以及执行情况
Select a.Enabled, Last_Start_Date, Next_Run_Date From Dba_Scheduler_Jobs a Where Job_Name = 'GATHER_STATS_JOB';
2.关闭和启动该job
方法一：
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;
三.其他建议
对于数据量很大的库，一般是建议关闭掉自动收集的任务，通过自己手工写存储过程，再把存储过程加入JOB中去收集统计信息，统计信息的收集是根据表的大小来自己定义收集的采样率以及其他的选项。
可以参考如下的存储过程：
DECLARE
CURSOR STALE_TABLE IS
SELECT OWNER,
SEGMENT_NAME,
CASE
WHEN SIZE_GB < 0.5 THEN
THEN
THEN
THEN
THEN
END AS PERCENT,
AS DEGREE
FROM (SELECT OWNER,
SEGMENT_NAME,
) SIZE_GB
FROM DBA_SEGMENTS
WHERE OWNER = 'SCOTT'
AND SEGMENT_NAME IN
(SELECT /*+ UNNEST */ DISTINCT TABLE_NAME
FROM DBA_TAB_STATISTICS
WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')
AND OWNER = 'SCOTT')
GROUP BY OWNER, SEGMENT_NAME);
BEGIN
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
FOR STALE IN STALE_TABLE LOOP
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => STALE.OWNER,
TABNAME => STALE.SEGMENT_NAME,
ESTIMATE_PERCENT => STALE.PERCENT,
METHOD_OPT => 'for all columns size skewonly',
DEGREE ,
GRANULARITY => 'ALL',
CASCADE => TRUE);
END LOOP;
END;
上述的存储过程，先定义了表的大小所对应的采样率，然后通过查询DBA_SEGMENTS得到表的大小，再通过DBMS_STATS.GATHER_TABLE_STATS去收集统计信息。