Oracle系统统计信息

我们可以通过DBMS_STATS.GATHER_SYSTEM_STATS过程来收集系统统计信息，收集的方式有两种：负载统计（WORKLOAD STATISTICS）在具有真实系统负载的数据库系统上，收集某一时间段内的系统信息；非负载统计（NOWORKLOAD STATISTICS）oracle数据库基于某种方式（如持续读磁盘）模拟系统负载来获取系统统计信息。

通各方资料和oracle官方文档中可以看出，我们最好采用系统负载的方式来收集系统统计信息，收集的时间间隔至少要30分钟，条件允许的情况下，最好多次收集，择优使用。处理使用DBMS_STATS.GATHER_SYSTEM_STATS来收集系统统计信息，我们也可以手工设定某些信息选项，这一点在后面会有示例。

oracle的系统统计信息最终是存储在aux_stats$数据基表中的，首先来看一下该表的结构：
SQL> desc aux_stats$
名称                是否为空? 类型
----------------------------- -------- --------------------
SNAME                 NOT NULL VARCHAR2(30)
PNAME                 NOT NULL VARCHAR2(30)
PVAL1                  NUMBER
PVAL2                  VARCHAR2(255)

aux_stats$表中存储的数据量是非常有限的，如

SQL> select * from aux_stats$;

SNAME            PNAME             PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_INFO        STATUS              COMPLETED
SYSSTATS_INFO        DSTART              09-17-2011 10:21
SYSSTATS_INFO        DSTOP               09-17-2011 10:21
SYSSTATS_INFO        FLAGS             1
SYSSTATS_MAIN        CPUSPEEDNW       1751.75879
SYSSTATS_MAIN        IOSEEKTIM            10
SYSSTATS_MAIN        IOTFRSPEED         4096
SYSSTATS_MAIN        SREADTIM
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED
SYSSTATS_MAIN        MBRC

SNAME            PNAME             PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR

已选择13行。

sname字段的取值有：SYSSTATS_INFO,SYSSTATS_MAIN,SYSSTATS_TEMP

SYSSTATS_INFO：系统统计信息的状态

SYSSTATS_MAIN：系统统计信息的内容

SYSSTATS_TEMP：在收集系统统计信息期间，用于临时存放中间数据

对于pname字段的含义，oracle官方文档给出了详细的解释：

align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">align="left" valign="top">

有一点需要注意：在新版本的oracle（10g)中，非工作量统计信息总是可用的，如果不存在非工作量统计信息，会在实例初始化时自动收集。

当我们更新系统统计信息后，oracle并不会讲已解析的sql语句置为无效，但是新的sql会使用信息系统统计信息。

我们先来看一下非工作量统计信息

非工作量统计信息包括IO TRASFER SPEED, IO SEEK TIME 和CPU SPEED，即上表中的蓝色标识区域。

非工作量统计信息是在系统空闲时段收集的，它通过随机读取所有的数据文件来测试系统IO性能并测试CPU的速度。在默认情况下，oracle使用非工作量统计信息，但是如果我们收集了工作量统计信息，oracle就会优先使用工作量统计信息。

oracle会在第一次启动时自动收集非工作量统计信息，如果我们需要手工收集非工作量统计信息，可以使用不带参数的DBMS_sTATS.GATHER_SYSTEM_STATS过程。收集非工作量统计信息会对系统产生负载，其运行时间的长短也依赖于数据库服务器的性能。由于oracle内部的检测机制，非工作量统计信息的收集可能不会覆盖默认值，这时我们可以多试几次或者手工来设定统计信息。

我们再来看一下工作量统计信息

工作量统计信息包括单块读时间和多块读时间，mbrc等，即上表中红色标识区域。

oracle在收集工作量统计信息时，不会对数据库系统造成额外的系统负载，因为oracle是通过查询统计时间段内的统计信息如v$sysstat v$sesstat等来得到工作量系统统计信息的。

oracle计算工作量统计信息是通过某些计数器来实现的，这些计数器只有在oracle buffer cach完成某些操作时才会改变，因此这些计数器不仅反映了磁盘输入输出的延迟，同时也反应了与latch和任务转换相关的等待事件。由此我们可以推断出，如果一个系统的瓶颈在io系统，那么oracle在根据工作量统计信息生成执行计划时，会优先选择对io依赖较小的执行计划。

我们可以通过如下两种方式来收集工作量系统统计信息

Run the DBMS_STATS.GATHER_SYSTEM_STATS('start') procedure at the beginning of the workload window, then the DBMS_STATS.GATHER_SYSTEM_STATS('stop')procedure at the end of the workload window.

Run DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>N) where N is the number of minutes when statistics gathering is stopped automatically.

当我们收集工作量统计信息时，oracle会收集MBRC的值，但是如果在收集时间段内，oracle没有能过收集到mbrc和MREADTIM的值（如，在时间段内没有发生串行化的全表扫描操作），在这种情况下，oracle会使用初始化参数DB_FILE_MULTIBLOCK_READ_COUNT来评估全表扫描的成本，如果该初始化参数没有指定，那么oracle会使用8来代替。关于DB_FILE_MULTIBLOCK_READ_COUNT，我会专门来介绍。

1. 什么是系统统计信息？

我们知道在CBO环境中，Oracle依赖于对象的统计估算成本，以选择正确的SQL执行计划。从Oracle9i开始CBO计算成本(cost)的算法有了变化。Oracle9i以前更多考虑IO（多块读与单块读）成本;9i以后，强化了cpu速度对成本估算的影响。

Oracle提供了dbms_stats.gather_system_stats来收集系统统计信息。系统统计信息让优化器考虑服务器的IO与CPU性能及其利用率，作为计算成本的依据；为每一个可选的执行计划估算IO与CPU成本。因而对于CBO来说，获得准确的系统统计信息对于正确估计成本是非常重要的。Oracle收集的系统统计信息主要内容说明如下：

--cpuspeedNW 表示非负载情况下的cpu速度,在系统启动时自动搜集

--ioseektim IO查找时间，以毫秒表示；缺省为10ms,非负载模式或可以手动设置。

--iotfrspeed IO传输速度，表示Oracle数据库单次读数据的传输速率，单位为bytes/ms，在系统启动时自动收集；默认为4096 bytes/ms

--cpuspeed 表示负载情况下的cpu速度,以平均每秒可提供的cpu周期表示

--maxthr　　　最大IO吞吐量,单位为bytes/s

--slavethr 从属IO吞吐量，表示并行进程时，从属进程的IO吞吐量，单位为bytes/s

--sreadtim 单块读时间(如索引读取)，表示随机读一个Oracle数据块的时间，以ms计算

--mreadtim 多块读时间(主要是指全表扫描),表示连续读取多个Oracle数据库的平均时间，以ms计算　

--mbrc 多块读计数，表示一次多块读的读取的Oracle数据块数量

系统统计信息存储在sys.aux_stats$表中：

SQL> select * from sys.aux_stats$;

SNAME PNAME PVAL1 PVAL2

-------------------- -------------------- ---------- --------------------

SYSSTATS_INFO STATUS COMPLETED

SYSSTATS_INFO DSTART 01-24-2011 18:06

SYSSTATS_INFO DSTOP 01-24-2011 18:06

SYSSTATS_INFO FLAGS 1

SYSSTATS_MAIN CPUSPEEDNW 1970.048

SYSSTATS_MAIN IOSEEKTIM 11.132

SYSSTATS_MAIN IOTFRSPEED 4096

SYSSTATS_MAIN SREADTIM 6

SYSSTATS_MAIN MREADTIM 24

SYSSTATS_MAIN CPUSPEED 1800

SYSSTATS_MAIN MBRC 6

SNAME PNAME PVAL1 PVAL2

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

2. 系统统计信息的收集

Dbms_stats.gather_system_stats的参数如下：

SQL> desc dbms_stats.gather_system_stats;

Parameter Type Mode Default?

-------------- -------- ---- --------

GATHERING_MODE VARCHAR2 IN Y

INTERVAL NUMBER IN Y

STATTAB VARCHAR2 IN Y

STATID VARCHAR2 IN Y

STATOWN VARCHAR2 IN Y

STATTAB、STATID、STATOWN与其他收集统计信息的参数一样，不多做说明。系统统计信息有工作负载与无工作负载两种类型; ioseektim、iotrfspeed、cpuspeednw是无负载的统计信息，也就是说不需要系统有工作负载，可以系统空闲时进行收集。Oracle为在系统启动时间重新设置，或重置为默认值。要手动收集非工作负载统计信息，使用dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD')。当使用dbms_stats.delete_system_stats()删除系统统计信息时间，将只保留非负载时的统计信息:

SQL> exec dbms_stats.delete_system_stats();

PL/SQL 过程已成功完成。

SQL> select * from sys.aux_stats$;

SNAME PNAME PVAL1 PVAL2

-------------------- -------------------- ---------- --------------------

SYSSTATS_INFO STATUS COMPLETED

SYSSTATS_INFO DSTART 01-25-2011 11:37

SYSSTATS_INFO DSTOP 01-25-2011 11:37

SYSSTATS_INFO FLAGS 0

SYSSTATS_MAIN CPUSPEEDNW 2030.679

SYSSTATS_MAIN IOSEEKTIM 10

SYSSTATS_MAIN IOTFRSPEED 4096

SYSSTATS_MAIN SREADTIM

SYSSTATS_MAIN MREADTIM

SYSSTATS_MAIN CPUSPEED

SYSSTATS_MAIN MBRC

SNAME PNAME PVAL1 PVAL2

-------------------- -------------------- ---------- --------------------

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

已选择13行。

不同压力与不同类型的应用，甚至同一系统的不同时间，cpu与io的能力都是不一样的。比如ZLHIS在８点到11点的压力，明显于大于下午的压力；这时候包括多块读时间、单块读时间的效率都会有差异。理想的情况是，收集不同系统负载下的系统统计信息，存放到特定的统计信息表中，然后在负载发生变化的时候导入到Oracle中，但在类似ZLHIS这种要求高可用的系统，频繁的变更系统统计信息不太现实。大多数情况下，只需要采集系统高峰时段或典型时段的系统统计信息即可。

收集负载情况下的统计信息有两种方式，一种是手工指定收集时段的开始与结束：

--启动收集

exec dbms_stats.gather_system_stats(gathering_mode => 'START');

.............

--等待系统运行一段时间，等待时间长短根据情况做调整

.............

--停止收集

exec dbms_stats.gather_system_stats(gathering_mode => 'STOP');

另一种方式就是使用间隔模式，指定一个间隔时段，Oracle自动开始与结束信息收集：

--以未来10分钟的系统负载，收集系统统计信息。

exec dbms_stats.gather_system_stats(gathering_mode => 'INTERVAL',interval => 10);

需要说明的是收集系统统计信息，并不影响已经缓存的sql语句，只会影响新解析的SQL语句，如果要已经缓存的SQL语句也按新的系统统计信息生成执行计划，只有清空共享池，但这在生产系统上是比较危险的操作。另外需要注意的就是，如果在收集时段内没有相应操作，将不会收集对应的系统统计信息；例如，如果收集时段内没有产生全表扫描的多块读，mbrc(多块读计数)将不会收集。

2. 系统统计信息对CBO成本计算的影响

虽然CBO计算的成本只是对生成何种执行计划有关，并不对真正执行SQL语句的真实代价产生什么影响，但作为CBO估算成本的基础要素，系统统计信息要尽量保证准确。我们通过实验来说明系统统计信息对sql语句成本估算的影响：

　　 使用dba_objects视图创建一个测试表:

SQL> --创建测试表

SQL> create table test as select * from dba_objects;

表已创建。

SQL> insert into test select * from test;

已创建10212行。

SQL> --收集测试表的统计信息

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'test',cascade => true);

PL/SQL 过程已成功完成。

然后删除删除工作负载的统计信息，只保留非工作负载的统计信息:

exec dbms_stats.delete_system_stats();

我们来看此时的对test全表扫描估算的成本：

SQL> explain plan for select count(*) from test;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 71 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 71 (0)| 00:00:01 |

可以看到Oracle在没有负载情况下的系统统计信息时，估算的成本为71。接下为，我们使用导入一些系统统计信息。由于测试环境，没有什么负载，我们使用dbms_stats.set_system_stats过程来手工修改统计信息：

SQL> --创建统计信息表

SQL> exec dbms_stats.create_stat_table(ownname => user,stattab => 'SYSTEM_STATS');

PL/SQL 过程已成功完成。

SQL> --设置相关的统计信息值

SQL> exec dbms_stats.set_system_stats(pname => 'SREADTIM',pvalue => '6' ,stattab => 'system_stats');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.set_system_stats(pname => 'MREADTIM',pvalue => '12',stattab => 'system_stats');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.set_system_stats(pname => 'CPUSPEED',pvalue => '1800' ,stattab => 'system_stats');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.set_system_stats(pname => 'MBRC',pvalue => '16',stattab => 'system_stats');

PL/SQL 过程已成功完成。

SQL> --导入相应统计信息

SQL> exec dbms_stats.import_system_stats(stattab => 'system_stats',statown => user);

PL/SQL 过程已成功完成。

现在来重新查看sql语句的估算成本：

SQL> explain plan for select count(*) from test;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 33 (0)| 00:00:01 |

这次估算的成本(cost)为33,在收集了系统信息后，CBO估算的成本发生了变化。我们知道Oracle提供了db_file_multiblock_read_count参数，来控制Oracle一次多块读的Oracle数据块数量，也将影响Oracle对全表扫描成本的估算。Oracle既然收集了多块读IO速度(mreadtim)、多块读计数(mbrc)等信息，那db_file_multiblock_read_count的设置与这些统计信息是什么关系呢？答案是：如果存在负载情况下的多块读的相关统计信息，将会忽略db_file_multiblock_read_count的设置，如果不存在相应的系统统计信息，将使用db_file_multiblock_read_count的值对全表扫描成本进行估算。

首先，我们测试一下，不存在相关系统统计信息时，全表扫描的成本：

SQL> explain plan for select count(*) from test;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 71 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 71 (0)| 00:00:01 |

-------------------------------------------------------------------

已选择9行。

可以看成本(cost)又回到了未收集系统统计信息时的71,而不是收集后的33,这个时候，我们修改db_file_multiblock_read_count参数，来看看相应的成本cost是否会起变化：

SQL> show parameter db_file_multiblock_read_count;

NAME TYPE VALUE

------------------------------------ ----------- ---------------------

db_file_multiblock_read_count integer 8

SQL> alter session set db_file_multiblock_read_count=16;

会话已更改。

SQL> explain plan for select count(*) from test;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------

--------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 57 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 57 (0)| 00:00:01 |

-------------------------------------------------------------------

已选择9行。

可以看到在没有系统统计信息的情况下，设置db_file_multiblock_read_count，可以影响SQL的成本估算，现在变成了57。现在重新导入系统统计信息，看设置db_file_multiblock_read_count能否影响执行计划：

--重新导入系统统计信息

SQL> exec dbms_stats.import_system_stats(stattab => 'system_stats',statown => user);

PL/SQL 过程已成功完成。

SQL> --还原db_file_multiblock_read_count

SQL> alter session set db_file_multiblock_read_count=8;

会话已更改。

SQL> explain plan for select count(*) from test;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 20424 | 33 (0)| 00:00:01 |

-------------------------------------------------------------------

已选择9行。

可以看到，导入系统统计信息后，成本又变成了33，我们再设置db_file_multiblock_read_count，再重新解析sql语句：

SQL> --再设置db_file_multiblock_read_count值

SQL> alter session set db_file_multiblock_read_count=16;

会话已更改。

SQL> explain plan for select count(*) from test;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------

Plan hash value: 1950795681

-------------------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 33 (0)| 00:00:01 |

| 1 |

About Me

............................................................................................................................... ● 本文整理自网络 ● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 联系我请加QQ好友(646634621)，注明添加缘由 ● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................... 拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。