话聊Oracle统计信息的那点事

【此为"一森咖记"公众号——第115篇文章】

【引言】

在Oracle的11g版本中，统计信息为自动收集功能，在部署安装11g Oracle软件过程中，其中有一个步骤便是提示是否启动这个功能(默认是启用这个功能)；且有时候在生产环境中，还会对一些对象做手动统计信息的搜集，如果新搜集的统计信息产生的执行计划在实际生产中不符和生产要求，则需继续使用原有的执行计划。

因平时接触此部分内容不是很频繁，但统计信息的搜集无疑又很重要，故本文对自动统计信息搜集及常规操作做下介绍。

【大纲】

1. 啥事统计信息

2. Oracle的统计信息自动搜集策略；

3. 手动搜集的常用命令；

一、 统计信息是个啥？

说统计信息前，先要说下Oracle的优化器

Oracle数据库中的优化器又叫查询优化器（Query Optimizer）。它是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。Oracle的优化器有两种，基于规则的优化器（RBO）与基于代价的优化器（CBO)

      RBO: Rule-Based Optimization 基于规则的优化器

      CBO: Cost-Based Optimization 基于代价的优化器

RBO自ORACLE 6以来被采用，一直沿用至ORACLE 9i. ORACLE 10g开始，ORACLE已经彻底丢弃了RBO，它有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说RBO对数据不“敏感”；它根据ORACLE指定的优先顺序规则，对指定的表进行执行计划的选择。比如在规则中，索引的优先级大于全表扫描；在RBO中，SQL的写法往往会影响执行计划，这就要求开发人员非常了解RBO的各项细则，菜鸟写出来的SQL脚本性能可能非常差。

CBO是一种比RBO更加合理、可靠的优化器，它是从ORACLE 8中开始引入，但到ORACLE 9i 中才逐渐成熟，在ORACLE 10g中完全取代RBO， CBO是计算各种可能“执行计划”的“代价”，即COST，从中选用COST最低的执行方案，作为实际运行方案。它依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择。

如果对一次执行SQL时发现涉及对象（表、索引等）没有被分析、统计过，那么ORACLE会采用一种叫做动态采样的技术，动态的收集表和索引上的一些数据信息。

好了，这里改说下优化器依赖哪些统计信息，优化器统计范围：

表统计：

--行数，块数，行平均长度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；

列统计：

--列中唯一值的数量（NDV），NULL值的数量，数据分布；--DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；

索引统计：

--叶块数量，等级，聚簇因子；--DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；

系统统计：

--I/O性能与使用率；--CPU性能与使用率；--存储在aux_stats$中，需要使用dbms_stats收集，I/O统计在X$KCFIO中；

上一篇推文讲述了直方图，《【精品篇】_Oracle的直方图是个啥？》点击文章链接

简述直方图作用：

“当在表上收集统计时，DBMS_STATS收集表中列的数据分布的信息，数据分布最基本的信息是最大值和最小值，但是如果数据分布是倾斜的，这种级别的统计对于优化器来说不够的，对于倾斜的数据分布，直方图通常用来作为列统计的一部分。”

接下来讲一讲统计信息收集那点事

统计信息可分为自动收集和手动收集。

自动收集统计信息的情况：

自动统计收集在夜间进行，对所有更改活动中等的对象自动统计应该足够；使用如下命令查看；

SELECT WINDOW_NAME,

       REPEAT_INTERVAL,

       ENABLED,

       ACTIVE,

       duration

  FROM dba_scheduler_windows;

可以看出每周一至周五22:00开始，历时4小时进行统计信息的自动收集；

每周六和周日早上06：00开始，历时20小时收集统计信息；

手动收集统计信息的情况：

1. 频繁变化的表在白天的活动期间被TRUNCATE/DROP并重建；2. 块加载超过本身总大小10%的对象；

3. 在创建了基于索引的统计后，应该在表上收集新的列统计，这可以通过调用过程设置METHOD_OPT的FOR ALL HIDDEN COLUMNS；

4. 对于分区表，如果仅仅是一个分区有了较大改动，只需要收集一个分区的统计，但是收集整个表的分区也是必要的；

5. 。。。。

统计信息收集常用命令：

自动收集功能启用与禁用：

1、查看自动收集统计信息的任务及状态：

SQL> select client_name,status,window_group from dba_autotask_client;

其中"auto optimizer stats collection"便是要寻找的自动收集统计信息的任务名称，它的状态目前是启用状态。

查看该任务所包含的执行窗口

SQL> SELECT *

  FROM DBA_SCHEDULER_WINGROUP_MEMBERS

 WHERE WINDOW_GROUP_NAME = 'ORA$AT_WGRP_OS';

Window_group_name window_name

ORA$AT_WGRP_OSMONDAY_WINDOW

ORA$AT_WGRP_OSTUESDAY_WINDOW

ORA$AT_WGRP_OSWEDNESDAY_WINDOW

ORA$AT_WGRP_OSTHURSDAY_WINDOW

ORA$AT_WGRP_OSFRIDAY_WINDOW

ORA$AT_WGRP_OSSATURDAY_WINDOW

ORA$AT_WGRP_OSSUNDAY_WINDOW

2、禁止自动收集统计信息的任务

禁用自动收集统计信息的任务可以使用DBMS_AUTO_TASK_ADMIN包完成：

SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

PL/SQL 过程已成功完成。

SQL> select client_name,status from dba_autotask_client;

此时"auto optimizer stats collection"任务已经被禁用。

3、启用自动收集统计信息的任务

SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

PL/SQL 过程已成功完成。

SQL> select client_name,status from dba_autotask_client;

这个功能貌似带来了统计数据采集上的便捷，但是Oracle 11g自动收集统计信息的时间是22:00--2:00

这个时间段往往是业务的高峰期，给本已紧张的系统带来更大的负担。所以，应该把自动执行的时间改到空闲的时段。

4、获得当前自动收集统计信息的执行时间：

SQL> SELECT t1.window_name, t1.repeat_interval, t1.duration

  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');

其中：

      WINDOW_NAME：任务名

      REPEAT_INTERVAL：任务重复间隔时间

      DURATION：持续时间

修改步骤如下：

1.停止任务： SQL> BEGIN 

2 DBMS_SCHEDULER.DISABLE(

3 name => '"SYS"."FRIDAY_WINDOW"',

4 force => TRUE);

5 END;

6 / 

PL/SQL 过程已成功完成。

2.修改任务的持续时间，单位是分钟：

SQL> BEGIN 

2 DBMS_SCHEDULER.SET_ATTRIBUTE(

3 name => '"SYS"."FRIDAY_WINDOW"',

4 attribute => 'DURATION',

5 value => numtodsinterval(180,'minute'));

6 END;

7 / 

PL/SQL 过程已成功完成。

3.开始执行时间，BYHOUR=2，表示2点开始执行：

SQL> BEGIN 

2 DBMS_SCHEDULER.SET_ATTRIBUTE(

3 name => '"SYS"."FRIDAY_WINDOW"', 4 attribute => 'REPEAT_INTERVAL',

5 value => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');

6 END;

7 / 

PL/SQL 过程已成功完成。

4.开启任务：

SQL> BEGIN 

2 DBMS_SCHEDULER.ENABLE(

3 name => '"SYS"."FRIDAY_WINDOW"');

4 END;

5 / 

PL/SQL 过程已成功完成。

5.查看修改后的情况：

SQL> select t1.window_name,t1.repeat_interval,t1.duration 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');

WINDOW_NAME REPEAT_INTERVAL DURATION ------------------------------ --------------------------------------------------------- WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 FRIDAY_WINDOW FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0 +000 03:00:00 SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 

7 rows selected

接下来，再介绍下手动如何收集统计信息，此列操作常用于对表做了大批量的DML，或者数据库刚迁移完，还没到自动统计信息搜集的时候。

使用DBMS_STATS包导出与导入统计信息

1.首先创建保存统计信息的信息表stat_table

begin

dbms_stats.create_stat_table(ownname => 'USER',stattab => 'STAT_TABLE'); 

end

2.导出统计信息到stat_table里

begin

dbms_stats.export_table_stats(ownname => 'USER',tabname => ' the_operated_table_name ',stattab => 'STAT_TABLE');

end

#保存原有统计信息到STAT_TABLE中，目的是3过程不成功，执行步骤4恢复原表的统计信息

3.收集统计信息

begin

dbms_stats.gather_table_stats(ownname => 'USER',tabname => ' the_operated_table_name ');

end

4.导入统计信息到原表里

begin

dbms_stats.import_table_stats(ownname => 'USER',tabname => 'the_operated_table_name',stattab => 'STAT_TABLE');

end

其他dbms常用包，这里不做详细介绍，各位可查看Oracle官方手册。

EXPORT_COLUMN_STATS：导出列的分析信息 

EXPORT_INDEX_STATS：导出索引分析信息 

EXPORT_SYSTEM_STATS：导出系统分析信息 

EXPORT_TABLE_STATS：导出表分析信息 

EXPORT_SCHEMA_STATS：导出方案分析信息 

EXPORT_DATABASE_STATS：导出数据库分析信息 

IMPORT_COLUMN_STATS：导入列分析信息 

IMPORT_INDEX_STATS：导入索引分析信息 

IMPORT_SYSTEM_STATS：导入系统分析信息 

IMPORT_SCHEMA_STATS：导入方案分析信息 

IMPORT_DATABASE_STATS：导入数据库分析信息 

GATHER_INDEX_STATS：分析索引信息 

GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息 

GATHER_SCHEMA_STATS：分析方案信息 

GATHER_DATABASE_STATS：分析数据库信息 

GATHER_SYSTEM_STATS：分析系统信息 

【总结】

1. 本文重点介绍了Oradcle的统计信息是个嘛，同时，对统计信息的自动收集和如何手动收集进行了演示。

1. Oracle ADG同步技术，DBA必备的一种“后悔药”
2. Oracle 11g 异机rman恢复报错ORA-27302:failure occurred at: sskgpcreates
3. 年末总结_聊一聊数据库行业的“继往开来”
4. Materialized view物化视图的一个简单应用场景
   
5. 干货：RHEL7.2生产环境下双节点12c RAC搭建实操
6. 【干货篇】在国内外数据库百家争鸣的时代，DBA们该何去何从？
7. LINUX环境：MySQL和Oracle开机自启动，咋搞？
8. Logminer：oracle人为误操作之恢复神器
   
9. What：ASM自动脱落了
   
10. 实操：12C RAC环境下的ADG同步库搭建
11. “神器”：Oracle日志采集分析工具——TFA
    
12. Oracle Rac：关闭透明大页的原因及方法
    
13. 实操篇：Oracle 19c的安装部署
14. MySQL：主从同步延迟Seconds_Behind_Master越来越大，什么鬼？
15. 浅谈MySQL三种锁：全局锁、表锁和行锁
16. Oracle如何访问MySql：透明网关