成为MySQL DBA后，再看ORACLE数据库（十四、统计信息与执行计划）

一、前言

一条SQL到达数据库内核之后，会解析为一条逻辑执行计划，CBO优化器对逻辑计划进行改写和转换，生成多个物理执行计划。为SQL构造出搜索空间，根据数据的统计信息、基数估计、算子代价模型为搜索空间中的执行计划估算出执行所需要的代价（CPU、内存、网络、I/O 等资源消耗），最终选出代价最小的执行计划作为SQL的具体执行方式。因此，为了保证SQL的最佳执行性能，数据库需要找到一个最优的执行计划，生成执行计划的优化器必须要知道最新的统计信息，例如条数，block数量，某个字段的选择率等。

二、硬解析与软解析

一条SQL只有经过解析后才能生成执行计划，在ORACLE中，解析分为硬解析和软解析。硬解析（Hard Parse）是指Oracle在执行目标SQL时，在share pool的库缓存（Library Cache）中找不到可以重用的解析树和执行计划，而不得不从头开始解析目标SQL并生成相应的父游标（Parent Cursor）和子游标（Child Cursor）的过程。软解析（Soft Parse)是指Oracle在执行目标SQL时，在Library Cache中找到了匹配的父游标（Parent Cursor）和子游标（Child Cursor），并将存储在子游标中的解析树和执行计划直接拿过来重用而无须从头开始解析的过程。创建解析树、生成执行计划对于SQL的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。这就是在很多项目中，倡导开发设计人员对功能相同的代码要尽量保持代码的一致性，以及要在程序中多使用绑定变量的原因。

三、统计信息

通过前面硬解析和软解析的介绍可以知道ORACLE数据库中SQL的执行计划是可以缓存在share pool里面，执行计划是根据数据库对象的统计信息生成的，优化器为了产生最佳的执行计划，依赖于对象统计信息。统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等信息。统计信息包括表统计信息、索引统计信息、列统计信息、系统统计信息。借用Jieke Xu老师的这张图很清楚的描述了统计信息是什么：

那在ORACLE中统计信息又存在哪呢？或者说我们如何去查询ORACLE的统计信息呢？统计信息都是存放在数据字典中，通过dba_tables、dba_tab_statistics、dba_tab_columns、dba_tab_col_statistics、dba_indexes、dba_ind_statistics等数据字典视图可以查询ORACLE的表、列和索引的统计信息。而系统统计信息存放在系统表sys.aux_stats$中。

查询某个表的统计信息：
SELECT D.NUM_ROWS, --表中的记录数
      D.BLOCKS, --轰中数据所占的数据块数
      D.EMPTY_BLOCKS, --表中的空块数
      D.AVG_SPACE, --数据块中平均的，使用空间
      D.CHAIN_CNT, --表中行连接和行迁移的数量
      D.AVG_ROW_LEN, --每条记录的平均长度
      D.STALE_STATS, --统计信息是否过期
      D.LAST_ANALYZED --最近一次搜集统计信息的时间
 FROM DBA_TAB_STATISTICS D  --DBA_TAB_STATISTICS DBA_TABLES
WHERE D.TABLE_NAME = 'ATBWSL';

查询表上列的统计信息：
SELECT D.COLUMN_NAME,
      D.NUM_DISTINCT, --唯一值的个数
      D.LOW_VALUE, --列上的最小值
      D.HIGH_VALUE, --列上的最大值
      D.DENSITY, --若不存在柱状图的话,则表示选择率因子（密度）=1/(NDV)
      D.NUM_NULLS, --空值的个数
      D.NUM_BUCKETS, --直方图的BUCKETS个数
      D.HISTOGRAM, --直方图的类型
      D.LAST_ANALYZED --最近一次搜集统计信息的时间
 FROM DBA_TAB_COLUMNS D  --DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'ATBWSL';

四、自动收集统计信息

在ORACLE 11g中会默认开启统计信息的自动收集功能，每天会通过定时任务收集普通对象和数据字典的统计信息。通过dba_autotask_client视图可以查看自动收集统计信息的任务及状态，其中"auto optimizer stats collection"便是要寻找的自动收集统计信息的任务名称，它的状态目前是启用状态。

通过dba_scheduler_windows视图可以查看统计信息的自动收集情况。可以看到Oracle 11g的默认的维护窗口配置覆盖了以下时间段：每个工作日的晚上10点到第二天凌晨2点，持续4小时；每个周六上午6点到周日凌晨2点，周日上午6点到周一凌晨2点，都是持续20个小时。实际应用中可以根据业务情况把自动收集统计信息的任务调整到业务低峰期，比如通过以下语句将周一的自动收集统计信息的任务时间调整到1点开始，持续5个小时：

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;

那么自动收集统计信息任务运行时，所有的对象都会被收集吗？答案是否定的，在自动收集统计信息时，优先收集缺失统计信息的对象，然后收集陈旧统计信息的对象。可以从DBA_TAB_STATISTICS和DBA_IND_STATISTICS这两个视图中查询缺失或陈旧统计信息的对象。LAST_ANALYZED IS NULL表示统计信息缺失，STALE_STATS='YES'表示统计信息陈旧。当表或分区的数据变化量超过10%或者执行过truncate操作，该对象的统计信息则变为陈旧。在ORACLE 11g中，这个10%可以根据表的大小在表级别修改。

禁用自动收集统计信息的任务可以使用DBMS_AUTO_TASK_ADMIN包完成：
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
启用自动收集统计信息的任务：
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

五、手动收集统计信息

手动收集统计信息常用于对表做了大批量的DML，或者数据库刚迁移完、还没到自动统计信息搜集的时候。在ORACLE中一般通过调用dbms_stats包手动收集统计信息。

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TB_NAME',CASCADE=>TRUE,DEGREE=>8);  #收集用户下面某个表的统计信息，cascace表示是否收集列和索引的统计信息，DEGREE设置收集统计信息的并行性
EXEC DBMS_STATS.GATHER_DATABASE_STATS();  #收集当前数据库下所有用户的统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);  #收集用户下所有对象的统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(USER,'INDEX_NAME');  #单独收集某个索引的统计信息

而系统统计信息收集则是通过调用DBMS_STATS.GATHER_SYSTEM_STATS包，一般有以下两种方法：

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('start');
系统正常运行负载一段时间
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('stop');
或
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE=>INTERVAL,INTERVAL=>10)  #INTERVAL为间隔时长，单位为分钟

 六、固定执行计划

通过前面的总结可以知道，优化器会根据优化器统计信息来生成“它认为最好的”执行计划，但是并不能够保证每一次都会选择最优的哪个执行计划。如果遇到了sql语句在某段时间内执行性能变差，并且发现它的执行计划发生了变化，那么可以考虑固定那个“执行性能好的”时候的执行计划。在ORALE中推荐使用SQL Plan Management（SQL计划管理，简称SPM）把执行计划绑定到某个sql_id。

当启用SPM之后每一个SQL都会存在对应的SQL plan baseline，可以从DBA_SQL_PLAN_BASELINES视图中查看目标SQL的所有SQL plan baseline。只有DBA_SQL_PLAN_BASELINES中列ENABLED和ACCEPTED两个列都是“YES”，SQL plan baseline对应的执行计划才会被Oracle启用。通过执行DBMS_SPM.ALTER_SQL_PLAN_BASELINE可以固定SQL的执行计划，当DBA_SQL_PLAN_BASELINES中FIX列已经变成了YES，则说明该计划已经被固定。

参数OPTIMIZER_CAPTURE_SQL_PALN_BASELINES用于控制是否启用自动捕获SQL plan baseline，默认值是FALSE，不开启自动捕获SQL plan baseline。参数OPTIMIZER_USE_SQL_PALN_BASELINES用于控制是否使用SQL plan baseline，默认为TRUE，默认Oracle在生成执行计划就会生成SPM，使用以后的SQL plan baseline。