合 Oracle中的自动收集统计信息
Tags： Oracle统计信息收集统计信息统计信息自动收集
简介
对于Oracle而言，准确的统计信息对于CBO来说是非常重要的，因为这直接关系到CBO能否对目标SQL生成合适的、正确的执行计划。所以DBA应该使自己维护的数据库中的统计信息尽量准确。在Oracle 10g之前并没有自动收集统计信息的机制，从Oracle 10g开始引入了自动收集统计信息的功能，这个功能在Oracle 10g中被称为自动统计信息收集（Automatic Statistics Gathering），在Oracle 11g中被称为自动优化器统计信息收集（Automatic Optimizer Statistics Collection）。自动统计信息收集作业能够每天收集普通对象和数据字典的统计信息，但不会收集X$系列表的内部对象统计信息。Oracle的初始化参数STATISTICS_LEVEL控制收集统计信息的级别，有三个参数值：
BASIC：收集基本的统计信息
TYPICAL：收集大部分统计信息（数据库的默认设置）
ALL：收集全部统计信息
当使用Oracle自动收集统计信息时，必须要确保Oracle的参数STATISTICS_LEVEL的值为TYPICAL或者ALL。默认值为TYPICAL，该值可以确保数据库自我管理功能所需求的所有主要统计信息的正确收集，及提供最好的综合性能。这个默认值可以能胜任大多数的环境，并且Oracle不推荐去修改该值。
DBA可以根据Oracle提供的脚本$ORACLE_HOME/rdbms/admin/catmwin.sql查看统计信息收集作业的整体搭建流程。有兴趣的读者可以研究下此脚本的内容。
在Oracle 11g中对统计信息自动收集的功能进行了加强。在Oracle 10g中，如果表中变更的行数（字典表SYS.MON_MODS_ALL$中记录的INSERT+UPDATE+DELETE的总数）超过表的总行数（SYS.TAB$中记录的目标表总记录数）的10%时或自上次自动统计信息收集作业完成之后目标表被执行过TRUNCATE操作，那么该表的统计信息就变为陈旧状态，Oracle就会在指定时间段自动收集统计信息。在Oracle 10g中，这个10%（STALE_PERCENT）是无法修改的，如果表非常大，那么10%其实是非常多的数据，这就造成统计信息不准确。在Oracle 11g中，这个10%（STALE_PERCENT）是可以修改的，分为全局（DBMS_STATS.SET_GLOBAL_PREFS）、数据库级别（DBMS_STATS.SET_DATABASE_PREFS）、用户级别（DBMS_STATS.SET_SCHEMA_PREFS）和表级别（DBMS_STATS.SET_TABLE_PREFS）。其中，数据库级别和用户级别都是调用表级别的存储过程DBMS_STATS.SET_TABLE_PREFS来对表进行设置的。
表级别的设定如下所示：
- 修改为5%（范围从1-100）：EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',5);
- 恢复为10%：EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',NULL);
- 查询表百分比：SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'TB_NAME') FROM DUAL;
- 查询全局百分比：SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT') FROM DUAL;
Oracle 10g的自动统计信息收集功能没有资源限制，但Oracle 11g的统计信息收集功能在资源管理上面限制了对系统资源使用，其对应的RESOURCE_PLAN的名称为DEFAULT_MAINTENANCE_PLAN。用户可以根据各自系统的业务场景来配置是否开启自动收集统计信息，也可以调整窗口调度的开始时间、持续时间和资源组限制等。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SQL> SET line 9999 PAGESIZE 9999 SQL> col WINDOW_NAME format a18 SQL> col REPEAT_INTERVAL format a55 SQL> col DURATION format a15 SQL> col resource_plan format a25 SQL> SELECT T1.WINDOW_NAME, 2 T1.REPEAT_INTERVAL, 3 T1.DURATION, 4 T1.ENABLED, 5 T1.RESOURCE_PLAN 6 FROM DBA_SCHEDULER_WINDOWS T1, DBA_SCHEDULER_WINGROUP_MEMBERS T2 7 WHERE T1.WINDOW_NAME = T2.WINDOW_NAME 8 AND T2.WINDOW_GROUP_NAME IN 9 ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED'); WINDOW_NAME REPEAT_INTERVAL DURATION ENABL RESOURCE_PLAN ------------------ ------------------------------------------------------- --------------- ----- ------------------------- SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE DEFAULT_MAINTENANCE_PLAN FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE DEFAULT_MAINTENANCE_PLAN WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE DEFAULT_MAINTENANCE_PLAN TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE DEFAULT_MAINTENANCE_PLAN SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE DEFAULT_MAINTENANCE_PLAN THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE DEFAULT_MAINTENANCE_PLAN MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE DEFAULT_MAINTENANCE_PLAN |
Oracle 11g的默认的维护窗口配置覆盖了下面的时间段：
- 每个工作日的晚上10点到第二天凌晨2点，持续4小时
- 每个周六上午6点到周日凌晨2点，周日上午6点到周一凌晨2点，都是持续20个小时
晚上10点到凌晨2点，这个时段往往是业务的高峰期，给本已紧张的系统带来更大的负担。所以，有必要把自动执行的时间改到空闲的时段。这个要根据各自的业务进行判断，如，系统可调整为：周一到周五，凌晨1点开始，持续5个小时；周六、周日，凌晨1点开始，持续10个小时。
用SYS用户执行如下语句即可：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | begin sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 05:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 10:00:00'); end; / begin sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0'); sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 10:00:00'); end; / |
DBA_TAB_MODIFICATIONS视图（基表为SYS.MON_MODS_ALL$）记录了从上次收集统计信息以来表中DML操作变化的数据量，包括执行INSERT、UPDATE和DELETE影响的行数，以及是否执行过TRUNCATE操作。另外，DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO可以将内存中的数据快速刷新到数据字典SYS.MON_MODS_ALL$中。
示例如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SYS@orclasm > CREATE TABLE T_MON_20170602_LHR AS SELECT * FROM DBA_OBJECTS; Table created. SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR'; no rows selected SYS@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_MON_20170602_LHR'); PL/SQL procedure successfully completed. SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR'; no rows selected SYS@orclasm > DELETE FROM T_MON_20170602_LHR WHERE ROWNUM <=10000; 10000 rows deleted. SYS@orclasm > COMMIT; Commit complete. SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR'; no rows selected SYS@orclasm > EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; PL/SQL procedure successfully completed. SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR'; TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP ------------------------------ ---------- ---------- ---------- ------------------- T_MON_20170602_LHR 0 0 10000 2017-06-02 19:26:03 |
Oracle 10g和11g的自动统计信息收集机制对比
Oracle 10g和11g的自动统计信息收集机制有所不同，详见下表：
| Oracle 10g | Oracle 11g | |
|---|---|---|
| JOB名称 | GATHER_STATS_JOB | auto optimizer stats collection |
| 调用 | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC | DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC |
| 工作机制 | 在Oracle 10g中，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。该JOB调用的存储过程是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC。该存储过程首先检测统计信息缺失（Missing Statistics）和陈旧（Stale Statistics）的对象，然后确定优先级，再开始进行统计信息的收集。收集的规则是当表中变更的行数超过表的总行数的10%时就会自动收集，表的变更行数可以通过视图DBA_TAB_MODIFICATIONS来查询。 | Oracle 11g中的自动统计信息收集作业每天自动运行任务GATHER_STATS_PROG来实现，每次运行的时候都会先生成名为ORA$AT_OS_OPT_XXX的作业，然后再执行这个作业来自动收集统计信息，其本质也是执行DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC。可以手动调用DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS来执行收集统计信息的JOB。 |
| 默认时间窗口 | 可维护窗口有两个，WEEKNIGHT_WINDOW和WEEKEND_WINDOW，时间为： ● 每个工作日的晚上10点到第二天凌晨6点，持续8小时 ● 所有的周末时间即周末全天收集（周五晚上10点到周一凌晨6点） Oracle并没有对上述两个维护窗口施加资源控制，这也就意味着Oracle 10g中的自动统计信息收集作业在运行时可以无限制地消耗系统资源 | 自动维护窗口增加到7个：MONDAY_WINDOW、TUESDAY_WINDOW、WEDNESDAY_WINDOW、THURSDAY_WINDOW、FRIDAY_WINDOW、SATURDAY_WINDOW、SUNDAY_WINDOW，时间为： ● 每个工作日的晚上10点到第二天凌晨2点，持续4小时 ● 每个周六上午6点到周日凌晨2点，周日上午6点到周一凌晨2点，都是持续20个小时 晚上10点到凌晨2点，这个时段往往是业务的高峰期，给本已紧张的系统带来更大的负担。所以，有必要把自动执行的时间改到空闲的时段。这个要根据各自的业务进行判断，如，系统可调整为：周一到周五，凌晨1点开始，持续5个小时；周六、周日，凌晨1点开始，持续10个小时 |
启用自动收集统计信息
10g
1 2 | EXEC DBMS_SCHEDULER.ENABLE('SYS.GATHER_STATS_JOB'); ALTER SYSTEM SET "_OPTIMIZER_AUTOSTATS_JOB"=TRUE SCOPE=SPFILE; |
需要重启库。
11g
1 2 | EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(); EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL); |
关闭自动收集统计信息
10g
1 2 | EXEC DBMS_SCHEDULER.DISABLE('SYS.GATHER_STATS_JOB'); ALTER SYSTEM SET "_OPTIMIZER_AUTOSTATS_JOB"=FALSE SCOPE=SPFILE; |
11g
1 2 | EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(); EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL); |
查询是否启用自动收集统计信息
注意：首先需要确认是否将参数
job_queue_processes配置为大于0的参数，若等于0，则禁用了自动收集统计信息的任务。
通过如下SQL修改参数：
10g
1 2 3 | SELECT D.ENABLED FROM DBA_SCHEDULER_JOBS D WHERE JOB_NAME = 'GATHER_STATS_JOB'; |
