oracle系统统计信息

系统统计信息主要描述了与系统硬件相关的某些特性，例如cpu和io系统的性能和利用率等。这些信息对于查询优化器来说是非常重要的，在选择执行计划的过程中，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官方文档给出了详细的解释：

Parameter Name	Description	Initialization	Options for Gathering or Setting Statistics	Unit
cpuspeedNW	Represents noworkload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU速率	At system startup	Set gathering_mode = NOWORKLOAD or set statistics manually.	Millions/sec.
ioseektim	I/O seek time equals seek time + latency time + operating system overhead time. 寻道时间	At system startup 10 (default)	Set gathering_mode = NOWORKLOAD or set statistics manually.	ms
iotfrspeed	I/O transfer speed is the rate at which an Oracle database can read data in the single read request. 数据传输速率	At system startup 4096 (default)	Set gathering_mode = NOWORKLOAD or set statistics manually.	Bytes/ms
cpuspeed	Represents workload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU速率	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART|STOP, or set statistics manually.	Millions/sec.
maxthr	Maximum I/O throughput is the maximum throughput that the I/O subsystem can deliver. 输入输出最大吞吐量	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART|STOP, or set statistics manually.	Bytes/sec.
slavethr	Slave I/O throughput is the average parallel slave I/O throughput. 从属进程的平均吞吐量	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	Bytes/sec.
sreadtim	Single block read time is the average time to read a single block randomly. 单块读时间	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	ms
mreadtim	Multiblock read is the average time to read a multiblock sequentially. 多块读时间	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	ms
mbrc	Multiblock count is the average multiblock read count sequentially. 一次多块读的数据库块数量	None	Set gathering_mode = INTERVAL or START|STOP, or set statistics manually.	blocks

有一点需要注意：在新版本的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，我会专门来介绍。