Oracle 统计信息, 老话题中的新故事

概述

       在Oracle数据库中，查询优化器为每个待执行的SQL语句提供最快最有效的执行计划。为了达到这一点，查询优化器会首先搜索可用的访问路径（ACCESS PATH）,关联方式，和关联顺序(JOIN ORDEDR)等等，最终会产生几个可选的查询计划。接下来就是在这些可选的查询计划中，找出最优的那个，在这一步中，决定最优查询计划的关键信息就是优化器统计数据（Optimizer statistics），它提供的诸如数据的分布、表和索引的存储特征等信息决定最终的最优查询计划。这些统计信息包括

• 表统计信息，如表中的记录数量和每条记录的平均长度
• 列统计信息，如该列具有distinct value的数量,该列具有的null的数量，和数据分布等
• 索引统计信息，包括叶子节点块的数量，B-Tree索引的深度，Culstering factor 等等
• 系统统计信息，I/O 性能及利用率和CPU的性能及利用率。

         Oracle将上述的统计信息存储在数据字典中，我们知道诸如表的数据和索引信息是一直在变化的，为了获得最优的查询计划，我们就要保证这些统计信息得到及时的刷新，以不至于因为陈旧的统计信息导致错误、低效的查询计划被作为最优的执行计划执行。

 

       在Oracle 8i之前，DBA使用analyze table来为Oracle优化器收集统计信息。在 Oracle 8i里，我们有DBMS_STATS包，我们使用它来为优化器收集最新的统计信息，但要做到统计信息收集自动化，就要靠DBA自己写脚本来调度。从Oracle 10g 开始，我们可以把收集统计信息的任务完全交给Oracle来完成。最重要的是，Oracle实现了完全自动化。在10g中 Oracle使用DBMS_SCHEDULER包来调用一个名为 GATHER_STATS_JOB的 JOB来完成统计信息的收集。我们可以通过如下SQL查看这个Job

 

select job_name from dba_scheduler_jobs
where job_name like 'GATHER_STATS%';

在11g中，Oracle引入了Auto-Task来进行统计信息的自动化收集工作。下面的章节都是围绕11g来展开。

 

统计信息自动收集

         Oracle 11g中提供了一个auto task来完成对象上统计信息的自动收集工作。我们可以通过如下脚本来开、关这个自动统计信息收集的auto task.

SQL> begin dbms_auto_task_admin.enable(
  2 client_name=>'auto optimizer stats collection',
  3 operation=>NULL,
  4 window_name=>NULL);
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> begin
  2  dbms_auto_task_admin.disable(
  3  client_name=> 'auto optimizer stats collection',
  4  operation=> NULL,
  5  window_name=> NULL);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>

 

这个Auto task 的开关状态，我们可以通过执行如下脚本获得。

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

SQL>

这个Auto task 是在Oracle的维护窗口(Maintenance window)来执行的。我们可以看到所有预定义的Oracle 维护窗口时间。

 

SELECT * FROM dba_autotask_schedule order by start_time;

我们还可以通过OEM，点击Administration --> Oracle Scheduler --> home，来查看和编辑这些时间窗口。

另外，我们可以通过查询dba_optstat_operations视图来查看每一个执行过的自动统计信息收集的Task的实际开始和结束时间。

 

SQL> select operation,target,start_time,end_time from dba_optstat_operations
where operation='gather_database_stats(auto)';

OPERATION                          START_TIME                        END_TIME
---------------------    ----------------------------             ----------------------------
gather_database_stats    26-APR-11 10.00.02.970000 PM             26-APR-11 10.03.11.671000 PM
…
SQL>

 

       自动统计信息收集Task在运行时调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC ，它与DBMS_STATS.GATHER_DATABASE_STATS （GATHER AUTO）实现的功能基本是一样的。主要的不同之处是它只在维护窗口时间启动，而且会对所需要刷新统计信息的对象按照统计信息发生变更的多少来进行排序，以保证在窗口时间内完成最需要刷新统计信息的对象的处理，根本没有任何统计信息的对象会首先得到刷新。

 

在OEM 里，通过Administration --> Oracle Scheduler --> Automated Maintenance Task

 

统计信息收集的对象？

Oracle 在每次进行统计信息收集时，主要对如下两种情况的对象进行统计信息收集

• 没有任何统计信息的对象
• 存在过时统计信息的对象

       第一条很好理解。第二条就有点问题了。什么叫过时的统计信息呢？怎么定义？那Oracle的解释是一个对象上的记录数的10%发生了变化，Oracle就认为这个对象上的统计信息过时了。

       那Oracle又怎么去判断哪些对象上发生更改的记录数占总记录的比例呢？答案就是Oracle通过监测发生在表和索引上的变化来实现的。Oracle提供了一个监控进程，用于监控发生在表上的INSERT, UPDATE, DELETE的数量，是否这个表在上次的统计信息收集后被TRUNCATE过，这些信息都存在在一个叫 [USER|ALL|DBA]_TAB_MODIFICATIONS的系统表中。这个监控进程默认是启动的。由数据库初始化参数STATISTIC_LEVEL控制。默认参数值为 TYPICAL。如果这个参数被设定设定为 BASIC，那么这个监控工具将不再执行。从数据库的变化发生到反应到上述视图里，会有些时间延迟。我们可以通过调用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO将内存中的数据变化情况刷新到这个表里。

 

统计信息手工收集的场景

虽然从Oracle 10g开始，Oracle就推荐我们使用Oracle自带的自动收集统计信息功能。但在有些场合，我们仍然需要手工进行统计信息收集。

• 当使用外部表时(External Table)

通过外部表，我们可以把来自其他数据源的不同格式的数据(CSV,txt)作为Oracle数据库中的表来对待。

Oracle 数据库不会标记外部表的统计信息为陈旧过时的。如果外部表所基于的外部文件发生变化，我们需要把当前的统计信息删除掉，然后再重新生成。

• 当需要收集系统统计信息时

Oracle 不会自动收集系统统计信息。这些信息包括，系统的硬件特征，例如I/O和 CPU 性能和利用率。查询优化器在选择执行计划时会对每个查询使用系统I/O和CPU资源做出估算。所以，最优查询计划的产生也依赖于系统统计信息。

当收集Fixed  Ojbects上的统计信息时，如动态性能表（对于动态表，我们应该使用GATHER_FIXED_OBJECTS_STATS 来收集优化器统计信息）. Fixed Object是Oracle用来存储内部数据的内部结构，主要是一些以X$开头的表和他们的索引。他们是Oracle动态性能视图(V$SQL,V$ACCESS和许多的V$视图)的数据来源.当我们做一些数据库的升级或对数据库的配置做更改。例如，当我们视图更改SGA的大小时，所有包含Buffer Cache和Shared Pool信息的X$的表（在 v$buffer_pool 或 v$shared_pool_advice.视图中使用到的表）会受到影响。

• 执行了一个数据的 BULK LOAD

当你执行了一个数据的 BULK LOAD后，我们需要手工执行一下，统计信息收集。自动统计信息收集对于大部分的OLTP应用都很适用，但在warehouse的环境中，我们可能会有问题，因为自动统计信息收集一般都发生在晚上的数据库维护窗口。如果你在数据库维护时间窗口之后做一个大数据的BULK Load，那如果没有及时收集统计信息，你的SQL语句的执行计划可能不是最优的状态。

 

总结

        Oracle 10g之前，基于Cost的查询优化器不能找到最优的执行计划，最根本原因就是缺少及时更新的，全面的统计信息。10g后，Oracle对统计信息的收集做到了自动化，这很大程度上保证了统计信息的及时更新，从而提高了执行计划的质量。

       除了自动化收集统计信息外，Oracle还提供了Dynamic Sampling的技术,对应的配置参数为OPTIMIZER_DYNAMIC_SAMPLING。通过它，Oracle可以决定是否通过动态数据取样来决定SQL查询的最优执行计划,这在统计信息没有得到及时更新的情况下，会很有帮助。