话聊Oracle统计信息的那点事

本文预计阅读需要15分钟

【引言】

在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小时收集统计信息；

手动收集统计信息的情