【DB笔试面试629】在Oracle中，自动收集统计信息的机制有哪些？10g和11g在自动收集统计信息方面有哪些区别？

AiDBA宝典

发布于 2019-09-29 16:18:49

6780

发布于 2019-09-29 16:18:49

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

在Oracle中，自动收集统计信息的机制有哪些？10g和11g在自动收集统计信息方面有哪些区别？

♣

答案部分

对于Oracle而言，准确的统计信息对于CBO来说是非常重要的，因为这直接关系到CBO能否对目标SQL生成合适的、正确的执行计划。所以DBA应该使自己维护的数据库中的统计信息尽量准确。在Oracle 10g之前并没有自动收集统计信息的机制，从Oracle 10g开始引入了自动收集统计信息的功能，这个功能在Oracle 10g中被称为自动统计信息收集（Automatic Statistics Gathering），在Oracle 11g中被称为自动优化器统计信息收集（Automatic Optimizer Statistics Collection）。自动统计信息收集作业能够每天收集普通对象和数据字典的统计信息，但不会收集X$系列表的内部对象统计信息。Oracle的初始化参数STATISTICS_LEVEL控制收集统计信息的级别，有三个参数值：

l BASIC：收集基本的统计信息

l TYPICAL：收集大部分统计信息（数据库的默认设置）

l ALL：收集全部统计信息

当使用Oracle自动收集统计信息时，必须要确保Oracle的参数STATISTICS_LEVEL的值为TYPICAL或者ALL。默认值为TYPICAL，该值可以确保数据库自我管理功能所需求的所有主要统计信息的正确收集，及提供最好的综合性能。这个默认值可以能胜任大多数的环境，并且Oracle不推荐去修改该值。

DBA可以根据Oracle提供的脚本$ORACLE_HOME/rdbms/admin/catmwin.sql查看统计信息收集作业的整体搭建流程。有兴趣的读者可以研究下此脚本的内容。

Oracle 10g和11g的自动统计信息收集机制有所不同，详见下表：

自动收集统计信息的运行日志查询：

SELECT JRD.LOG_ID,
       JRD.JOB_NAME,
       N.JOB_CLASS,
       TO_CHAR(JRD.ACTUAL_START_DATE, 'YYYY-MM-DD HH24:MI:SS') ACTUAL_START_DATE,
       TO_CHAR(JRD.LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') LOG_DATE,
       JRD.STATUS,
       JRD.ERROR#,
       JRD.RUN_DURATION,
       JRD.ADDITIONAL_INFO
  FROM DBA_SCHEDULER_JOB_LOG N, DBA_SCHEDULER_JOB_RUN_DETAILS JRD
 WHERE N.LOG_ID = JRD.LOG_ID
   AND N.JOB_NAME LIKE 'ORA$AT_OS_OPT_%' --11g
-- AND N.JOB_NAME = 'GATHER_STATS_JOB' --10g
 ORDER BY JRD.LOG_ID DESC;

在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来对表进行设置的。

表级别的设定如下所示：

l 修改为5%（范围从1-100）：EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',5);

l 恢复为10%：EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',NULL);

l 查询表百分比：SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'TB_NAME') FROM DUAL;

l 查询全局百分比：SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT') FROM DUAL;

Oracle 10g的自动统计信息收集功能没有资源限制，但Oracle 11g的统计信息收集功能在资源管理上面限制了对系统资源使用，其对应的RESOURCE_PLAN的名称为DEFAULT_MAINTENANCE_PLAN。用户可以根据各自系统的业务场景来配置是否开启自动收集统计信息，也可以调整窗口调度的开始时间、持续时间和资源组限制等。

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的默认的维护窗口配置覆盖了下面的时间段：

l 每个工作日的晚上10点到第二天凌晨2点，持续4小时

l 每个周六上午6点到周日凌晨2点，周日上午6点到周一凌晨2点，都是持续20个小时

晚上10点到凌晨2点，这个时段往往是业务的高峰期，给本已紧张的系统带来更大的负担。所以，有必要把自动执行的时间改到空闲的时段。这个要根据各自的业务进行判断，如，系统可调整为：周一到周五，凌晨1点开始，持续5个小时；周六、周日，凌晨1点开始，持续10个小时。

用SYS用户执行如下语句即可：

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$中。

示例如下：

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