合 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; / |
