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


♣

题目部分

在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的自动统计信息收集机制有所不同，详见下表：

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

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

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


 1SELECT JRD.LOG_ID,
 2       JRD.JOB_NAME,
 3       N.JOB_CLASS,
 4       TO_CHAR(JRD.ACTUAL_START_DATE, 'YYYY-MM-DD HH24:MI:SS') ACTUAL_START_DATE,
 5       TO_CHAR(JRD.LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') LOG_DATE,
 6       JRD.STATUS,
 7       JRD.ERROR#,
 8       JRD.RUN_DURATION,
 9       JRD.ADDITIONAL_INFO
10  FROM DBA_SCHEDULER_JOB_LOG N, DBA_SCHEDULER_JOB_RUN_DETAILS JRD
11 WHERE N.LOG_ID = JRD.LOG_ID
12   AND N.JOB_NAME LIKE 'ORA$AT_OS_OPT_%' --11g
13-- AND N.JOB_NAME = 'GATHER_STATS_JOB' --10g
14 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。用户可以根据各自系统的业务场景来配置是否开启自动收集统计信息，也可以调整窗口调度的开始时间、持续时间和资源组限制等。


 1SQL> SET line 9999  PAGESIZE 9999
 2SQL> col WINDOW_NAME format a18
 3SQL> col REPEAT_INTERVAL format a55
 4SQL> col DURATION format a15
 5SQL> col resource_plan format a25
 6SQL> SELECT T1.WINDOW_NAME,
 7  2         T1.REPEAT_INTERVAL,
 8  3         T1.DURATION,
 9  4         T1.ENABLED,
10  5         T1.RESOURCE_PLAN
11  6    FROM DBA_SCHEDULER_WINDOWS T1, DBA_SCHEDULER_WINGROUP_MEMBERS T2
12  7   WHERE T1.WINDOW_NAME = T2.WINDOW_NAME
13  8     AND T2.WINDOW_GROUP_NAME IN
14  9         ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');
15WINDOW_NAME        REPEAT_INTERVAL                                         DURATION        ENABL RESOURCE_PLAN
16------------------ ------------------------------------------------------- --------------- ----- -------------------------
17SUNDAY_WINDOW      freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN
18FRIDAY_WINDOW      freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN
19WEDNESDAY_WINDOW   freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN
20TUESDAY_WINDOW     freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN
21SATURDAY_WINDOW    freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN
22THURSDAY_WINDOW    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE  DEFAULT_MAINTENANCE_PLAN
23MONDAY_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用户执行如下语句即可：


 1begin
 2  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0');
 3  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');
 4end;
 5/
 6begin
 7  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0');
 8  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');
 9end;
10/
11begin
12  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0');
13  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');
14end;
15/
16begin
17  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0');
18  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');
19end;
20/
21begin
22  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0');
23  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');
24end;
25/
26begin
27  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0');
28  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 10:00:00');
29end;
30/
31begin
32  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0');
33  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 10:00:00');
34end;
35/

DBA_TAB_MODIFICATIONS视图（基表为SYS.MON_MODS_ALL$）记录了从上次收集统计信息以来表中DML操作变化的数据量，包括执行INSERT、UPDATE和DELETE影响的行数，以及是否执行过TRUNCATE操作。另外，DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO可以将内存中的数据快速刷新到数据字典SYS.MON_MODS_ALL$中。

示例如下：


 1SYS@orclasm > CREATE TABLE T_MON_20170602_LHR AS SELECT * FROM DBA_OBJECTS;
 2Table created.
 3SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';
 4no rows selected
 5SYS@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_MON_20170602_LHR');
 6PL/SQL procedure successfully completed.
 7SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';
 8no rows selected
 9SYS@orclasm > DELETE FROM T_MON_20170602_LHR WHERE ROWNUM <=10000;
1010000 rows deleted.
11SYS@orclasm > COMMIT;
12Commit complete.
13SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';
14no rows selected
15SYS@orclasm > EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
16PL/SQL procedure successfully completed.
17SYS@orclasm > SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP  FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';
18TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP
19------------------------------ ---------- ---------- ---------- -------------------
20T_MON_20170602_LHR                      0          0      10000 2017-06-02 19:26:03