oracle 11gr2 统计信息

  • 宋祖强

    2015-12-04 09:50:37
  • Oracle
  • 原创

 

一．任务说明

在Oracle的11g版本中提供了统计数据自动收集的功能。在部署安装11g Oracle软件过程中，其中有一个步骤便是提示是否启动这个功能(默认是启用这个功能)。

oracle 11g的自动收集统计信息为oracle自动去启用auto optimizer stats collection的client name，该任务有7个window name的窗口来调度，分别：

MONDAY_WINDOW

TUESDAY_WINDOW

WEDNESDAY_WINDOW

THURSDAY_WINDOW

FRIDAY_WINDOW

SATURDAY_WINDOW

SUNDAY_WINDOW

并且在资源管理上面限制了自动收集统计信息任务对系统资源使用，用户可以根据各自系统的业务场景来配置是否开启自动收集统计信息，也可以调整窗口调度的开始时间、持续时间和资源组限制等。

 

oracle 11g的自动统计分析任务执行时间默认是：

周一到周五   22：00 – 02：00 （持续4小时）

周六和周天   06：00 -- 04：00 （持续22小时）

二．启用与禁用

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

select client_name,status

from Dba_Autotask_Client where client_name='auto optimizer stats collection';

 

2、停止自动收集任务

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 where client_name='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 where client_name='auto optimizer stats collection';


4、获得当前自动收集统计信息的执行时间：

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 ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

 

或者：

select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ;

或者：

select window_name,duration,next_start_date from dba_scheduler_windows; 

 

其中：
     WINDOW_NAME：任务名，一周七天

     REPEAT_INTERVAL：任务重复间隔时间

其中：req=daily;

byday=SUN;周天

byhour=6 ;开始时间

      DURATION：持续时间

WINDOW_NEXT_TIME：下次执行时间

OPTIMIZER_STATS：是否开启 

 

三．修改统计信息的收集时间和持续时间

每个公司的业务情况都不一样，有的公司在晚上10点的时候，还属于业务的高峰期，那么默认的统计信息的收集就不符合业务需求了，可以根据业务需求进行相应的修改。

例如，修改为：

周一到周五，凌晨2点开始，持续4个小时；

 周六、周日，凌晨2点开始，持续8个小时；

 

begin

sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0');

sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

begin

sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=2;byminute=0; bysecond=0');

sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

begin

sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=2;byminute=0; bysecond=0');

sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

begin

sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=2;byminute=0; bysecond=0');

sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

begin

sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=2;byminute=0; bysecond=0');

sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');

end;

/

begin

sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=2;byminute=0; bysecond=0');

sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 08:00:00');

end;

/

begin

sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=2;byminute=0; bysecond=0');

sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 08:00:00');

end;

/

 

再次查看执行间隔；

 

SQL> 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 ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

 

也可以单独修改开始时间、持续时间或者单独开启关闭等

 

1.       修改任务的持续时间，单位是分钟：

 

SQL> BEGIN

     DBMS_SCHEDULER.SET_ATTRIBUTE(

     name => '"SYS"."FRIDAY_WINDOW"',

     attribute => 'DURATION',

     value => numtodsinterval(180,'minute'));

     END; 

     /

 

2.       修改开始执行时间，BYHOUR=2，表示凌晨2点开始执行：

 

SQL>BEGIN

      DBMS_SCHEDULER.SET_ATTRIBUTE(

      name => '"SYS"."FRIDAY_WINDOW"',

      attribute => 'REPEAT_INTERVAL',

      value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');

    END;

/

 

3.       开启任务

 

 SQL> BEGIN

      DBMS_SCHEDULER.ENABLE(

      name => '"SYS"."FRIDAY_WINDOW"');

     END;

/

 

4.       停止任务

 

SQL> BEGIN

      DBMS_SCHEDULER.DISABLE(

      name => '"SYS"."FRIDAY_WINDOW"',

      force => TRUE);

    END;

    /

 

修改之后再次查看，上面要注意要先禁掉维护窗口，然后才修改相关的属性。

 

SQL> 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 ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

 

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

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

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%'

 

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

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

select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection';

 

7. 如何来查看这个调度任务是否执行完毕了，在oracle 11g后自动统计分析的任务每次运行都会生成一个ORA$AT_OS_OPT_XXX的作业，我们可以通过查看dba_scheduler_job_run_details视图来查看执行状态

col job_name for a25
col status for a10
SQL> col error# for 9
SQL> col ACTUAL_START_DATE for a20
SQL> col RUN_DURATION for a10
SQL> col cpu_used for a10
SQL> select job_name,status,error#,actual_start_date,run_duration,cpu_used

from  dba_scheduler_job_run_details where job_name like 'ORA$AT_OS_OPT%';

JOB_NAME                  STATUS     ERROR# ACTUAL_START_DATE    RUN_DURATI CPU_USED
------------------------- ---------- ------ -------------------- ---------- ----------
ORA$AT_OS_OPT_SY_1        STOPPED         0 14-JAN-15 10.00.00.5 +000 04:00 +000 01:41
                                            57309 PM +08:00      :00        :49.34

 

得知从这个status字段为stopped得知，恢复测试库该收集统计信息的任务在4个小时之内并没有运行完毕，任务没有执行完毕就停止了。

job_name和job_status在这dba_scheduler_job_run_details和dba_autotask_job_history两个视图中的数据是完全吻合的。

 

四．手动收集统计信息

--收集当前数据库下所有用户的统计信息
exec dbms_stats.gather_database_stats();


--收集当前数据库用户下所有对象的统计信息
exec dbms_stats.gather_schema_stats(user);


--收集数据字典的统计信息
exec dbms_stats.gather_dictionary_stats(); 


--当系统有很大的分区表时，如果总是全部收集则会比较慢，11g之后可以设置INCREMENTAL只对数据有变动的分区做收集

exec dbms_stats.set_table_prefs(user,'table_name','INCREMENTAL','TRUE');--只收集数据变动的分区

exec dbms_stats.set_table_prefs(user,'table_name','INCREMENTAL','FALSE');--都要收集

select dbms_stats.get_prefs('INCREMENTAL',null,'table_name') from dual; --查看分区表INCREMENTAL的值


--获取global的统计信息收集设置选项
select dbms_stats.get_prefs('method_opt') from dual;
select dbms_stats.get_prefs('concurrent') from dual; 
select dbms_stats.get_prefs('GRANULARITY') from dual;

select dbms_stats.get_prefs('INCREMENTAL') from dual;


--设置global的统计信息收集选项
exec DBMS_STATS.SET_PARAM('DEGREE',4);

exec DBMS_STATS.SET_PARAM('INCREMENTAL','TRUE');

 

五．相关视图：


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

 

 

 

请使用浏览器的分享功能分享到微信等