目前在学习Oracle的性能调优这一块，管理和合理的运用统计信息是很重要的，本章记录一下Oracle对于统计信息的管理和维护。

首先看一下Oracle对于统计信息的解释：

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:

Table statistics
- Number of rows
- Number of blocks
- Average row length
Column statistics
- Number of distinct values (NDV) in column
- Number of nulls in column
- Data distribution (histogram)
Index statistics
- Number of leaf blocks
- Levels
- Clustering factor
System statistics
- I/O performance and utilization
- CPU performance and utilization

从上面的注释可以看到，统计信息的范围是很广的，典型的包括表的总行数，存储所占用的数据块数以及平均行的长度。

我们最常用到的无外乎表，列以及索引的统计信息。

其中，对于红字标识部分也就是通常所说的直方图要深刻理解。

直方图又称为数据分布，其标明了该列数据的分布区间，如下图所示：

Consider a column C with values between 1 and 100 and a histogram with 10 buckets.

这个图示标明列C的值均匀分布在1-100的区间。

直方图是很重要的统计信息，能否生成高效率的执行计划与此密切相关。

关于统计信息各项指标的用途在后面再详细说明，今天主要是了解统计信息的概念以及如何对统计信息进行操作。

在Oracle 9i之前，只能通过analyze命令来更新对象的统计信息，例如：
ANALYZE TABLE T1 COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;
并且需要人手工干预来定时更新。

在9i之后，Oracle提供了一个系统包dbms_stats用于管理和维护统计信息，并且作为系统任务由Oracle自动调用，定期更新统计信息，当然也可以手工执行。

默认数据库在创建的时候会自动产生更新统计信息的job，可以通过以下语句查询系统的自动作业调度：

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

如果想将此作业停止，可执行以下命令：
BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

以下命令用于收集或更新一个表的统计信息：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS（'TEST','T1',10);
END;
/

以下命令用于删除一个表的统计信息：
BEGIN
  DBMS_STATS.DELETE_TABLE_STATS（'TEST','T1');
END;
/

以下命令用于锁定一个表的统计信息:
BEGIN
  DBMS_STATS.LOCK_TABLE_STATS（'TEST','T1');
END;
/

注：如果执行了锁定操作，那么这个表或对象的统计信息将无法被更新，直到执
行解锁命令。

注：更新以及删除模式或数据库的统计信息同上。



还原统计信息

默认情况下，数据库会保留最近31天的统计信息，可以通过以下语句查询：

SELECT * FROM DBA_OPTSTAT_OPERATIONS;

以上结果记录着Oracle自动创建的更新数据库统计信息的作业在最近31天的执行情况，
默认情况下可以将任意对象的统计信息还原至这段时间内。


一个典型的还原统计信息的例子如下：
begin
  dbms_stats.restore_table_stats(ownname => 'BOLAN',tabname => 'IEA_CWFXCPB',as_of_timestamp => to_date('2012-05-14 22:00:49','yyyy-mm-dd hh24:mi:ss'));
end; 


导入和导出统计信息

除了可以通过系统自动备份的统计信息来还原之前的记录，还可以手工来管理统计信息。

首先需要调用dbms_stats.create_stat_table来创建一张用来存放统计信息的表，如下：
begin
dbms_stats.create_stat_table(ownname=>'SCOTT',stattab=>'STATTAB');
end;

导出统计信息：

begin
dbms_stats.export_table_stats(ownname => 'SCOTT',tabname => 'TEST',stattab => 'STATTAB');
end;

以上语句将表TEST的统计信息导出到刚刚创建的表STATTAB中。

如果要将多张表的统计信息导入到一张表中进行存储，那么就必须要有一个标识用以区分每段统计信息，那么上面的语句就要考虑加一个参数STATID，如下：

begin
dbms_stats.export_table_stats(ownname => 'SCOTT',tabname => 'TEST',stattab => 'STATTAB',STATID=>'TEST_20120516');
end;

这样在查询或者导入统计信息的时候就有一个唯一区别一组统计信息的标记。

导入统计信息:
begin
dbms_stats.import_table_stats(ownname => 'SCOTT',tabname => 'TEST',stattab => 'STATTAB',STATID=>'TEST_20120516');
end;

注:ORACLE里面的频度直方图只能通过ANALYZE命令来生成，DBMS_STATS只能生成高度直方图。

此时就将上面所导出的统计信息重新导入。

导入和导出统计信息在性能调优的时候尤为重要，有时候更新一张表的统计信息会
导致好几个大的查询执行计划改变，反而会使得整体的执行效率降低，而此时如果没有保存相应的统计信息，那么想恢复到之前的状态要费很大的工夫。
所以以后在性能调优涉及到要更新对象统计信息的时候，一定要先保存统计信息再更新，切记切记！

ITPub博客

Oracle统计信息管理和维护

最新文章