ITPub博客

首页 > 数据库 > Oracle > Oracle11g 统计信息-----统计信息自动收集任务

Oracle11g 统计信息-----统计信息自动收集任务

Oracle 作者:royevictory 时间:2015-11-16 15:27:49 0 删除 编辑

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 "<TITLE>SQL*Plus Report</title><STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
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 



相关视图:
dba_autotask_task
dba_autotask_client
dba_autotask_client_job
dba_autotask_window_clients
dba_autotask_client_history

dba_scheduler_jobs
dba_scheduler_job_classes
dba_scheduler_window_groups
dba_scheduler_windows
dba_scheduler_wingroup_members

自定义自动收集任务时间窗口:
How to use an own Maintenance Window for the Statistics Collection in 11g [ID 1300313.1]

MOS相关文档: FAQ: Automatic Statistics Collection [ID 1233203.1]
How to Gather Optimizer Statistics on 11g ( Doc ID 749227.1

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29302187/viewspace-1839609/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-08-06

  • 博文量
    195
  • 访问量
    527800