Oracle 自动收集统计信息机制

Automatic Optimizer Statistics Collection

   在Oracle 10g之前并没有自动收集统计信息的机制，从Oracle 10g开始引入了自动收集统计信息的功能，这个功能在Oracle 10g中被称为自动统计信息收集（Automatic Statistics Gathering）;

   在Oracle 11g中被称为自动优化器统计信息收集（Automatic Optimizer Statistics Collection）。自动统计信息收集作业能够每天收集普通对象和数据字典的统计信息，但不会收集X$系列表的内部对象统计信息。

  Oracle的初始化参数STATISTICS_LEVEL控制收集统计信息的级别，有三个参数值：
  1、 BASIC：收集基本的统计信息
  2、TYPICAL：收集大部分统计信息（数据库的默认设置）
  3、 ALL：收集全部统计信息
  当使用Oracle自动收集统计信息时，必须要确保Oracle的参数STATISTICS_LEVEL的值为TYPICAL或者ALL。默认值为TYPICAL，该值可以确保数据库自我管理功能所需求的所有主要统计信息的正确收集，及提供最好的综合性能。这个默认值可以能胜任大多数的环境，并且Oracle不推荐去修改该值。

自动收集统计信息机制

在Oracle 10g中，如果表中变更的行数（字典表SYS.MON_MODS_ALL$中记录的INSERT+UPDATE+DELETE的总数）超过表的总行数（SYS.TAB$中记录的目标表总记录数）的10%时或自上次自动统计信息收集作业完成之后目标表被执行过TRUNCATE操作，那么该表的统计信息就变为陈旧状态，Oracle就会在指定时间段自动收集统计信息。

– 查看表中变更的行数（insert,update,deletes）

select * from SYS.MON_MODS_ALL$ WHERE OBJ#='468923';

–DBA_TAB_MODIFICATIONS视图（基表为SYS.MON_MODS_ALL$）记录了从上次收集统计信息以来表中DML操作变化的数据量，包括执行INSERT、UPDATE和DELETE影响的行数(<font color=red>对于rollback操作的记录，仍然更新到DBA_TAB_MODIFICATIONS表，Oracle不会在rollback之后再去更新USER_TAB_MODIFICATIONS表</font>)，以及是否执行过TRUNCATE操作,<font color=green size=4>(此表信息默认情况是15分钟更新)</font>.另外，DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO可以将内存中的数据快速刷新到数据字典SYS.MON_MODS_ALL$中。

《DBA_TAB_MODIFICATIONS表的刷新策略测试》https://www.modb.pro/db/414692

smon会刷新统计结果到数据字典：DBA_TAB_MODIFICATIONS,oracle使用这些表的数据去判断表的统计数据是否过期,如果当表的数据改变超过10%或有truncate 操作,oracle就认为该表的统计数据已经过期.

-- 示例
08:25:00 SQL> create table t1 (id number,name varchar(10));
Table created.
08:28:55 SQL> select * from DBA_TAB_MODIFICATIONS where table_name='T1';
no rows selected
08:29:21 SQL> insert into t1 values(1,'a');
1 row created.
08:29:36 SQL> commit;
-- 如果需要查看最新信息，可以手动更新该表的信息：
08:29:38 SQL> EXEC dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
08:29:42 SQL> select * from DBA_TAB_MODIFICATIONS where table_name='T1';
TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS        UPDATES    DELETES TIMESTAMP          TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ --- -------------
SYS                            T1                                                                                                    1              0          0 11-JUN-22          NO              0

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

表级别的设定如下所示：
1、修改为5%（范围从1-100）：

EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',5);

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 自动收集统计信息任务测试》https://www.modb.pro/db/414757

维护窗口

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

Oracle 11g的默认的维护窗口配置覆盖了下面的时间段：
l 每个工作日的晚上10点到第二天凌晨2点，持续4小时
l 每个周六上午6点到周日凌晨2点，周日上午6点到周一凌晨2点，都是持续20个小时
*注：周六与周日的执行频率是：从6点开始每4小时执行维护一次，详细见下图执行日志：
（图中开始执行时间调整为每天凌晨3点开始执行）

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

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自动统计信息收集是通过每天执行自动任务gather_stats_prog来实现的，它每天会自动生成ORA$AT_OS_OPT_xxx的作业，然后执行作业来收集统计信息，其本质也是执行了DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。

查看维护窗口

SET line 9999  PAGESIZE 9999
 col WINDOW_NAME format a18
 col REPEAT_INTERVAL format a55
 col DURATION format a15
 col resource_plan format a25
 SELECT T1.WINDOW_NAME,
         T1.REPEAT_INTERVAL,
         T1.DURATION,
         T1.ENABLED,
         T1.RESOURCE_PLAN
    FROM DBA_SCHEDULER_WINDOWS T1, DBA_SCHEDULER_WINGROUP_MEMBERS T2
   WHERE T1.WINDOW_NAME = T2.WINDOW_NAME
     AND T2.WINDOW_GROUP_NAME IN
         ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');

禁用任务

BEGIN
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',operation   => NULL,window_name => NULL);
end;

启用任务：

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection',operation   => NULL,window_name => NULL);
end;

查看状态：

SQL>  select CLIENT_NAME, STATUS   from DBA_AUTOTASK_CLIENT;
	CLIENT_NAME                         STATUS
	----------------------------------- --------
	auto optimizer stats collection     ENABLED
	auto space advisor                  ENABLED
	sql tuning advisor                  ENABLED

将自动任务执行时间改为早上3点：

exec dbms_scheduler.disable( name => 'MONDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=MON;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'MONDAY_WINDOW');

exec dbms_scheduler.disable( name => 'TUESDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=TUE;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'TUESDAY_WINDOW');

exec dbms_scheduler.disable( name => 'WEDNESDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=WED;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'WEDNESDAY_WINDOW');

exec dbms_scheduler.disable( name => 'THURSDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'THURSDAY_WINDOW');

exec dbms_scheduler.disable( name => 'FRIDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=FRI;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'FRIDAY_WINDOW');

exec dbms_scheduler.disable( name => 'SATURDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=SAT;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'SATURDAY_WINDOW');

exec dbms_scheduler.disable( name => 'SUNDAY_WINDOW', force => TRUE);
exec dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW', attribute => 'repeat_interval',value => 'freq=daily;byday=SUN;byhour=3;byminute=0;bysecond=0');
exec dbms_scheduler.enable( name => 'SUNDAY_WINDOW');

解锁并重新收集统计信息

--解锁某个表的统计信息
EXEC dbms_stats.unlock_table_stats(ownname => '***',tabname => '***'); 
--重新收集某个表的统计信息
EXEC dbms_stats.gather_table_stats(ownname => '***',tabname => '***',cascade => true);
-- 数据库中哪些表的统计信息被锁定：
SELECT owner,table_name,stattype_locked FROM DBA_TAB_STATISTICS a WHERE a.stattype_locked IN ('ALL','DATA','CACHE')
-- 备注：stattype_locked为空代表统计信息未锁定。

                         文章推荐

|PostgreSQL|URL|
|-|-|-|
|《课程笔记：PostgreSQL深入浅出》之 初识PostgreSQL(一)|https://www.modb.pro/db/475817|
|《课程笔记:PostgreSQL深入浅出》之 PostgreSQL源码安装(二)|https://www.modb.pro/db/475933|
|《课程笔记:PostgreSQL深入浅出》之初始化PostgreSQL(三)|https://www.modb.pro/db/479524|
|《课程笔记:PostgreSQL深入浅出》之PSQL管理工具-常用(四)|https://www.modb.pro/db/479560|
|《课程笔记:PostgreSQL深入浅出》之PSQL管理工具-高级命令（四)|https://www.modb.pro/db/479559|
|《课程笔记:PostgreSQL深入浅出》之内存与进程(五)|https://www.modb.pro/db/489936|
|《课程笔记:PostgreSQL深入浅出》之外存&永久存储(六)|https://www.modb.pro/db/502267|
|Oracle：|URL|
|《Oracle 自动收集统计信息机制》|https://www.modb.pro/db/403670|
|《Oracle_索引重建—优化索引碎片》|https://www.modb.pro/db/399543|
|《DBA_TAB_MODIFICATIONS表的刷新策略测试》|https://www.modb.pro/db/414692|
|《FY_Recover_Data.dbf》|https://www.modb.pro/doc/74682|
|《Oracle RAC 集群迁移文件操作.pdf》|https://www.modb.pro/doc/72985|
|《Oracle Date 字段索引使用测试.dbf》|https://www.modb.pro/doc/72521|
|《Oracle 诊断案例 ：因应用死循环导致的CPU过高》|https://www.modb.pro/db/483047|
|《Oracle 慢SQL监控脚本》|https://www.modb.pro/db/479620|
|《Oracle 慢SQL监控测试及监控脚本.pdf》|https://www.modb.pro/doc/76068|
|《Oracle 脚本实现简单的审计功能》|https://www.modb.pro/db/450052|
|《记录一起索引rebuild与收集统计信息的事故》|https://www.modb.pro/db/408934|
|Greenplum:|URL|
|《PL/Java.pdf》|https://www.modb.pro/doc/70867|
|《GP的资源队列.pdf》|https://www.modb.pro/doc/67644|
|《Greenplum psql客户端免交互执行SQL.pdf》|https://www.modb.pro/doc/69806|

                       欢迎赞赏支持或留言指正