11g中统计信息自动收集任务的名称是auto optimizer stats collection。11g中自动任务默认的执行时间窗口为：

周一到周五是晚上10点开始到2点结束

周末是早上六点，持续20个小时。

1、查看自动收集任务及状态

select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';

2、停止自动收集任务

SQL> BEGIN

2 DBMS_AUTO_TASK_ADMIN.DISABLE(

3 client_name => 'auto optimizer stats collection',

4 operation => NULL, window_name => NULL);

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';

CLIENT_NAME STATUS

----------------------------------------------- -------------

auto optimizer stats collection DISABLED

但是此时再查询DBA_ATUOTASK_TASK视图时，显示该任务状态还是ENABLED

SQL> select client_name,status from dba_autotask_task

where client_name='auto optimizer stats collection';

CLIENT_NAME STATUS

---------------------------------------------- ------------

auto optimizer stats collection ENABLED

Oracle给出的解释是在现在的版本中（11.1 to 11.2）一个client对应一个task，

但是在将来的版本中会出现多个client会对应一个task，所以一个client被disabled了，不会改变task的状态。[ID 858852.1]

3、启动自动收集任务

SQL> BEGIN

2 DBMS_AUTO_TASK_ADMIN.ENABLE(

3 client_name => 'auto optimizer stats collection',

4 operation => NULL, window_name => NULL);

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';

CLIENT_NAME STATUS

-------------------------------------------------------- ---------

auto optimizer stats collection ENABLED

4、查看自动收集任务历史执行状态

SQL> SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed

2 FROM dba_autotask_client_history

3 WHERE client_name like '%stats%';

CLIENT_NAME WINDOW_NAME JOBS_CREATED JOBS_STARTED JOBS_COMPLETED

------------------------------- ----------------------------- --------------- ----------------- -----------------------

auto optimizer stats collection SATURDAY_WINDOW 5 5 5

auto optimizer stats collection SUNDAY_WINDOW 6 6 6

通过时间窗口名称可以看出是周几执行的，在时间窗口内创建了几次job，执行了几次job，当然可以加上window_start_time来查看具体执行的日期。

5、查看自动收集任务执行时间窗口

SQL> select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS

from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME;

WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE

------------------------------ --------------------------------------------------- ------------ --------

MONDAY_WINDOW 26-NOV-12 10.00.00.000000 PM PRC TRUE ENABLED

TUESDAY_WINDOW 27-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED

WEDNESDAY_WINDOW 28-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED

THURSDAY_WINDOW 29-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED

FRIDAY_WINDOW 30-NOV-12 10.00.00.000000 PM PRC FALSE ENABLED

SATURDAY_WINDOW 01-DEC-12 06.00.00.000000 AM PRC FALSE ENABLED

SUNDAY_WINDOW 02-DEC-12 06.00.00.000000 AM PRC FALSE ENABLED

我将系统日期改为11月26日22点以后，MONDAY_WINDOW执行时间窗口自动激活。

6、查询自动收集任务正在执行的JOB

select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB

where client_name='auto optimizer stats collection';

这个查询没有结果也很正常，只有job正在运行时，该查询才有结果。

7、与时间窗口相关的视图

查询自动收集任务所属时间窗口组

SQL> select client_name,window_group from dba_autotask_client

where client_name='auto optimizer stats collection';

CLIENT_NAME WINDOW_GROUP

---------------------------------------- ---------------------------------------------------

auto optimizer stats collection ORA$AT_WGRP_OS

查询自动收集任务所属时间窗口组详细信息

SQL> select * from dba_scheduler_window_groups where window_group_name='ORA$AT_WGRP_OS';

WINDOW_GROUP_NA ENABL NUMBER_OF_WINDOWS NEXT_START_DATE COMMENTS

------------------------- -------- --------------------------- ---------------------------------------- -------------------------------

ORA$AT_WGRP_OS TRUE 7 26-NOV-12 10.00.00.000000 PM PRC auto optimizer stats collection

查看自动收集任务所属时间窗口组包含的子时间窗口

SQL> select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';

WINDOW_GROUP_NA WINDOW_NAME

--------------- ------------------------------

ORA$AT_WGRP_OS MONDAY_WINDOW

ORA$AT_WGRP_OS TUESDAY_WINDOW

ORA$AT_WGRP_OS WEDNESDAY_WINDOW

ORA$AT_WGRP_OS THURSDAY_WINDOW

ORA$AT_WGRP_OS FRIDAY_WINDOW

ORA$AT_WGRP_OS SATURDAY_WINDOW ORA$AT_WGRP_OS SUNDAY_WINDOW

查看子时间窗口信息

SQL> select a.window_name,a.next_start_date,a.active from dba_scheduler_windows a

2 inner join dba_scheduler_wingroup_members b on a.window_name = b.window_name

3 where b.window_group_name='ORA$AT_WGRP_OS';

WINDOW_NAME NEXT_START_DATE ACTIV

------------------------------ -------------------------------------------- -----

MONDAY_WINDOW 26-NOV-12 10.00.00.000000 PM PRC TRUE

TUESDAY_WINDOW 27-NOV-12 10.00.00.000000 PM PRC FALSE

WEDNESDAY_WINDOW 28-NOV-12 10.00.00.000000 PM PRC FALSE

THURSDAY_WINDOW 29-NOV-12 10.00.00.000000 PM PRC FALSE

FRIDAY_WINDOW 30-NOV-12 10.00.00.000000 PM PRC FALSE

SATURDAY_WINDOW 01-DEC-12 06.00.00.000000 AM PRC FALSE

SUNDAY_WINDOW 02-DEC-12 06.00.00.000000 AM PRC FALSE

通过这个查询可以看出DBA_AUTOTASK_WINDOW_CLIENTS视图的信息其实和上面的结果一样。

总结一下统计信息自动收集任务运行的步骤：

首先是dba_autotask_task-->dba_autotask_client建立自动执行任务

再根据时间窗口及资源组建立自动执行作业

dba_autotask_client-->dba_scheduler_window_groups-->dba_scheduler_windows-->dba_scheduler_jobs

dba_autotask_client-->dba_scheduler_job_classes

--查看哪些表的统计信息是旧的

Please create a test table in schema TBCS:

create table mytest(id number, name varchar2(100));

insert into mytest

select rownum,dbms_random.string('a',100)

from dual

connect by level <= 10000;

commit;

1. Pleas update SR with the following output:

$sqlplus / as sysdba

declare

mystaleobjs dbms_stats.objecttab;

begin

-- check whether there is any stale objects

dbms_stats.gather_schema_stats(ownname=>'TBCS',

options=>'LIST STALE',

objlist=>mystaleobjs);

for i in 1 .. mystaleobjs.count loop

dbms_output.put_line(mystaleobjs(i).objname);

end loop;

end;

/

declare

mystaleobjs dbms_stats.objecttab;

begin

-- check whether there is any stale objects

dbms_stats.gather_schema_stats(ownname=>'TBCS',

options=>'LIST EMPTY',

objlist=>mystaleobjs);

for i in 1 .. mystaleobjs.count loop

dbms_output.put_line(mystaleobjs(i).objname);

end loop;

end;

/

2. Please implement the following and provide relevant trace:

idle>exec DBMS_STATS.SET_GLOBAL_PREFS('trace', 4+8+16+64+128+512+2048);

PL/SQL procedure successfully completed.

idle>execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

idle>exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

PL/SQL procedure successfully completed.

To disable trace, you can :

idle>exec DBMS_STATS.SET_GLOBAL_PREFS('trace', 0);

--如果考虑表大考虑调整收集到的百分比

begin

DBMS_STATS.SET_TABLE_PREFS (

ownname => 'TBCS',

tabname => 'RECEPTION',

pname => 'ESTIMATE_PERCENT',

pvalue => '1'); -- you can adjust the percent to a smaller value(0.01 or 0.1 etc)

end;

/

---手动调用自动维护窗口的JOB

SQL> execute DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

This will prompt the Automated Maintenance Tasks subsystem into starting a job that will gather optimizer statistics,

unless such a job is already running (for example if a maintenance window is currently open).

If an immediate job is created it will be named ORA$_AT_OS_MANUAL_nnnnnn (nnnnn is one or more decimal digits).

Unlike regular Automated Maintenance jobs, the "MANUAL" job is not tied to a specific maintenance window.

--查看历史的维护窗口

select * from DBA_AUTOTASK_JOB_HISTORY WHERE client_name ='auto optimizer stats collection' order by window_start_time desc;

--批量检查

set lines 500

set long 9999

set pages 999

set serveroutput on size 1000000

set feedback off

SET MARKUP HTML ON SPOOL ON HEAD "SQL*Plus Report"

spool query_result.html

set echo off

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

alter session set nls_timestamp_tz_format='yyyy/mm/dd hh24:mi:ss tzh:tzm';

select to_char(systimestamp,'yyyy/mm/dd hh24:mi:ss tzh:tzm') from dual;

select * from dba_autotask_client;

select * from DBA_SCHEDULER_WINGROUP_MEMBERS order by window_group_name,window_name;

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

select * from DBA_AUTOTASK_SCHEDULE;

select * from DBA_AUTOTASK_WINDOW_HISTORY order by window_start_time desc;

select * from DBA_AUTOTASK_JOB_HISTORY WHERE client_name ='auto optimizer stats collection' order by window_start_time desc;

spool off

SET MARKUP HTML OFF

Oracle 11g 统计信息之自动收集任务