自动统计信息收集（Automatic Optimizer Statistics Collection）---TeacherWhat

概述

本文主要介绍自动统计信息收集（Automatic Optimizer Statistics Collection）任务。

自动统计信息收集（Automatic Optimizer Statistics Collection）：10g

从Oracle 10g版本开始，在默认设定的情况下，Oracle会通过自动维护作业，自动地收集优化器统计信息（Optimizer Statistics）。

自动统计信息收集相关设定

10g的自动统计信息收集主要和以下的3个设定相关：

1.调度作业 GATHER_STATS_JOB（默认：有效'SCHEDULED'） 如前一章介绍，10g数据库做成时会自动地做成调度作业【GATHER_STATS_JOB】，通过预定义的维护窗口（Maintenance Windows）进行执行。 WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ～ 次日06:00结束。 WEEKEND_WINDOW :周六和周日的全天2.数据库对象的MONITORING属性（默认：有效 statistics_level=TYPICAL） 从Oracle 8i开始，Oracle针对数据库对象推出了MONITORING属性，通过这个属性可以监视表（及其分区）的DML更新操作（如insert,update,delete,direct load）并记录更新数。10g以后MONITORING属性主要依赖于初始化参数statistics_level的设定，当初始化参数statistics_level为默认值（TYPICAL）或ALL时，会针对一时表以外的所有表启用MONITORING属性，监视DML更新操作并记录更新数。。3.统计信息的Lock状况（默认：统计信息不锁定） 如果对象的统计信息上进行了锁定（lock Statistics），统计信息不会被更新。所以，统计信息上进行了锁定的对象不会作为自动统计信息收集的对象。关于统计信息的锁定可以参考DBMS_STATS包的LOCK_SCHEMA_STATS、LOCK_TABLE_STATS等程序。自动统计信息收集基本流程

自动统计信息收集基本流程如下：

1.当调度作业【GATHER_STATS_JOB】有效时，会在在预定义的维护窗口执行DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC程序进行统计信息收集。2.统计信息收集的对象为满足以下条件的表： 1）统计信息不锁定 2）没有统计信息 或者 上一次统计信息收集后，表中10%的行被更新。3.统计信息收集的顺序如下： 1）没有统计信息的对象 2）上一次统计信息收集后变更量较多的对象 3）1）2）相同的，先收集大小比较小的对象自动统计信息收集的动作确认

自动统计信息收集的相关动作可以通过以下视图进行查看：

1.GATHERSTATSJOB 的状态和设定内容

SQL> set linesize 200 SQL> col job_name format a20 SQL> col program_name format a20 SQL> col schedule_name format a25 SQL> col state format a20 SQL> SQL> SELECT job_name, program_name, schedule_name, state, stop_on_window_close FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB'; 2 3 4 5 6 7 JOB_NAME PROGRAM_NAME SCHEDULE_NAME STATE STOP_ON_WINDOW_ -------------------- -------------------- ------------------------- -------------------- --------------- GATHER_STATS_JOB GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP SCHEDULED TRUE

从上面的输出我们可以看到，GATHERSTATSJOB作业运行在MAINTENANCEWINDOWGROUP窗口组中，并且状态为‘SCHEDULED’即有效状态。 并且其中STOPONWINDOW_CLOSE为True代表当窗口关闭时，作业会被终止。即，当超过窗口的持续时间时，即使统计信息收集作业没有完成，也会被取消。

2.运行窗口内容

--窗口组信息 SQL> SELECT * FROM dba_scheduler_wingroup_members WHERE window_group_name = 'MAINTENANCE_WINDOW_GROUP'; 2 3 WINDOW_GROUP_NAME WINDOW_NAME ------------------------- -------------------- MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW--窗口信息 SQL> col repeat_interval format a60 SQL> col duration format a20 SQL> SELECT window_name, repeat_interval, duration FROM dba_scheduler_windows WHERE window_name IN ( 'WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW' ); 2 3 4 5 WINDOW_NAME REPEAT_INTERVAL DURATION -------------------- ------------------------------------------------------------ -------------------- WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; b +000 08:00:00 ysecond=0WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00

通过上面我们可以看到，窗口的运行时间为如下

WEEKNIGHT_WINDOW : 周一到周五 每天22:00开始 ～ 次日06:00结束。 WEEKEND_WINDOW :周六0点开始，并且持续2天（到周日的23:59）。

其中，由于周五的窗口持续到周六06:00结束；由于Oracle一次只能打开一个窗口， 并且WEEKNIGHTWINDOW和WEEKENDWINDOW的优先度相同，所以WEEKEND_WINDOW的实际开始时间为周六06:00开始。

3.执行的程序内容

可以通过dbaschedulerprograms视图查询到GATHERSTATSPROG作业实际运行的程序。

SQL> col program_action format a50 SQL> SELECT program_action FROM dba_scheduler_programs WHERE program_name = 'GATHER_STATS_PROG'; 2 3 PROGRAM_ACTION -------------------------------------------------- dbms_stats.gather_database_stats_job_proc自动统计信息收集的注意事项1.通常情况下，gather_database_stats_job_proc程序执行时，系统字典表也作为统计信息收集对象。2.对于数据变化很大的表，建议通过锁定统计信息等方法，来避免作为自动统计信息收集的对象。 另外，针对一时表统计信息收集后也不会产生有效的信息，所以建议针对一时表采取动态统计的方法。例如： EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('SCOTT', 'EMP');3.可能由于统计信息收集时，可能会消耗大量的I/O、CPU等资源，所以可以根据业务内容调整自动统计信息收集的时间。

参考： Database Performance Tuning Guide

14.2 Automatic Statistics Gathering http://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i41282

11g & 12c

从11g开始，导入了周一到周日的日次窗口，并且定义了自动维护任务功能。 10g中的GATHERSTATSJOB作业不再存在，而被变更为自动维护任务的一部分，所以JOB作业的状态不在通过 DBASCHEDULERJOBS视图确认，而是通过DBAAUTOTASKCLIENT 视图来查看。 而且对于自动维护任务的维护等会通过DBMSAUTOTASK_ADMIN程序包进行维护。 其他的动作基本和10g相同。

例如：

--查看自动统计收集任务。 SQL> col client_name format a20 SQL> col window_group format a20 SQL> SELECT client_name, status, window_group FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection'; 2 3 4 5 CLIENT_NAME STATUS WINDOW_GROUP -------------------- ---------------------------- auto optimizer stats ENABLED ORA$AT_WGRP_OS collection --禁用自动统计收集任务。 BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END;

参考： Database SQL Tuning Guide

>Configuring Automatic Optimizer Statistics Collection

---来自腾讯云社区的---TeacherWhat