oracle-statspack
如何读懂statspack报告 (zt)
===========================================================
作者: tolywang(https://www.wendangku.net/doc/2a2102149.html,)
发表于:2008.07.05 10:57
分类: Oracle数据库管理
出处：https://www.wendangku.net/doc/2a2102149.html,/post/48/465825
---------------------------------------------------------------
产生一个statspack报告是比较简单的，但是如何读懂statspack报告却不是那么容易，需要对Oracle的体系架构、内存结构、等待事件以及应用系统有充分的了解，加上不断的实践，才能基本读懂statspack报告并且从报告中找到调整优化Oracle的途径。
下面接合一个实际的statspack报告，大致分析一下。
1．基本信息分析
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- --------- ---
RES 2749170756 res 1 8.1.7.0.0 NO res
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 2 26-Jul-03 16:37:08 38
End Snap: 3 26-Jul-03 17:03:23 38
Elapsed: 26.25 (mins)
Statspack报告首先描述了数据库的基本情况，比如数据库名、实例名、实例个数、oracle版本号等等；然后是该报告的开始快照和结束快照的信息，包括 snap id , snap time 等等；最后是该报告经过的时间跨度，单位是分钟(mins)。
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 200M Std Block Size: 8K
Shared Pool Size: 48M Log Buffer: 512K
然后描述了Oracle内存结构中几个重要的参数。
2．内存信息分析
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 2,055.42 616,282.67
Logical reads: 2,317.78 694,948.08
Block changes: 17.58 5,269.92
Physical reads: 565.04 169,416.67
Physical writes: 13.47 4,037.42
User calls: 2.22 666.75
Parses: 1.22 367.08
Hard parses: 0.38 114.92 1
如何读懂 statspack——turner 整理
Sorts: 0.64 192.25
Logons: 0.00 1.17
Executes: 2.57 771.92
Transactions: 0.00
% Blocks changed per Read: 0.76 Recursive Call %: 86.78
Rollback per transaction %: 0.00 Rows per Sort: 34.84
.. Redo size: 是日志的生成量，分为每秒和每事务所产生的，通常在很繁忙的系统中日志生成量可能达到上百k，甚至几百k；
.. Logical reads: 逻辑读实际上就是logical IO=buffer gets表示的含义，我们可以这样认为，block在内存中，我们每一次读一块内存，就相当于一次逻辑读；
.. Parses 和 Hard parses: Parse 和 hard parse通常是很容易出问题的部分，80%的系统的慢都是由于这个原因所导致的。所谓parse分soft parse 和hard parse，soft parse是当一条sql传进来后，需要在shared pool中找是否有相同的sql，如果找到了，那就是soft parse，如果没有找着，那就开始hard parse，实际上hard parse主要是检查该sql所涉及到的所有的对象是否有效以及权限等关系，hard parse之后才根据rule/cost模式生成执行计
划，再执行sql。而hard parse的根源，基本都是由于不使用bind var所导致的，不使用bind var违背了oracle的shared pool的设计的原则，违背了这个设计用来共享的思想，这样导致shared_pool_size里面命中率下降。因此不使用bind var，将导致cpu使用率的问题，极有使得性能急剧下降。还有就是为了维护internal structure，需要使用latch，latch是一种Oracle低级结构,用于保护内存资源，是一种内部生命周期很短的lock，大量使用latch将消耗大量的cpu资源。
.. Sorts: 表示排序的数量；
.. Executes: 表示执行次数；
.. Transactions: 表示事务数量；
.. Rollback per transaction %: 表示数据库中事务的回退率。如果不是因为业务本身的原因，通常应该小于10%为好，回退是一个很消耗资源的操作。
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.98
Buffer Hit %: 65.82 In-memory Sort %: 99.65
Library Hit %: 91.32 Soft Parse %: 88.18
Execute to Parse %: 9.28 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 94.61 % Non-Parse CPU: 99.90
.. Buffer Hit %: 数据缓冲区命中率，最好应该大于90%。如果过小可考虑增加DB_CACHE_SIZE或者DB_BLOCK_BUFFERS
.. Library Hit %: libaray cache的命中率，最好应该大于98%。如果过小可考虑增加SHARED_POOL_SIZE。
.. In-memory Sort %: 排序在内存的比例，如果这个比例过小，可以考虑增大sort_area_size，在Oracle9i中可以考虑调整pga_aggregate_target，使得排序在内存中进行而不是在temp表空间中进行；
.. Soft Parse %: 软解析的百分比，这个百分比也应该很大才好，因为我们要尽量减少hard parse。 soft parse 百分比=soft/(soft+hard)； 2
如何读懂 statspack——turner 整理
.. Execute to Parse %: 这个数字也应该是越大越好，接近100%最好。有些报告中这个值是负的，看上去很奇怪。事实上这表示一个问题，sql如果被age out的话就可能出现这种情况，也就是sql老化，或执行alter system flush shared_pool等。
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.63 87.19
% SQL with executions>1: 71.53 75.39
% Memory for SQL w/exec>1: 59.45 65.17
.. % SQL with executions>1: 这个表示SQL被执行次数多于一次的比率，也应该大为好，小则表示很多sql只被执行了一次，说明没有使用绑定变量。
3．等待事件分析
等待事件（Wait Events）是Oracle中比较复杂难懂的概念。Oracle 的等待事件是衡量Oracle 运行状况的重要依据及指标。等待事件的概念是在Oracle7.0.1.2 中引入的，大致有100 个等待事件。在Oracle 8.0 中这个数目增加到了大约150 个，在Oracle8i 中大约有200 个事件,在Oracle9i 中大约有360 个等待事件。主要有两种类别的等待事件，即空闲（idle）等待事件和非空
闲（non-idle）等待事件。空闲事件指Oracle 正等待某种工作,在诊断和优化数据库的时候,我们不用过多注意这部分事件。常见的空闲事件有:
.. dispatcher timer
.. lock element cleanup
.. Null event
.. parallel query dequeue wait
.. parallel query idle wait - Slaves
.. pipe get
.. PL/SQL lock timer
.. pmon timer- pmon
.. rdbms ipc message
.. slave wait
.. smon timer
.. SQL*Net break/reset to client
.. SQL*Net message from client
.. SQL*Net message to client
.. SQL*Net more data to client
.. virtual circuit status
.. client message
非空闲等待事件专门针对Oracle 的活动,指数据库任务或应用运行过程中发生的等待，这些等待事件是我们在调整数据库的时候应该关注与研究的。
一些常见的非空闲等待事件有:
.. db file scattered read
.. db file sequential read
.. buffer busy waits
.. free buffer waits
.. enqueue
.. latch free
3
如何读懂 statspack——turner 整理
.. log file parallel write
.. log file sync
下面接合statspack中的一些等待事件进行讲述。
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file scattered read 26,877 12,850 52.94
db file parallel write 472 3,674 15.13
log file parallel write 975 1,560 6.43
direct path write 1,571 1,543 6.36
control file parallel write 652 1,290 5.31
-------------------------------------------------------------
.. db file scattered read: DB文件分散读取。
这个等待事件很常见，经常在top5中出现，这表示，一次从磁盘读数据进来的时候读了多于一个block的数据，而这些数据又被分散的放在不连续的内存块中，因为一次读进来的是多于一个block的。
通常来说我们可以认为是全表扫描类型的读，因为根据索引读表数据的话一次只读一个block，如果这个数字过大，就表明该表找不到索引，或者只能找到有限的索引，可能是全表扫描过多，需要检查sql是否合理的利用了索引，或者是否需要建立合理的索引。
当全表扫描被限制在内存时，它们很少会进入连续的缓冲区内，而是分散于整个缓冲存储器中。尽管在特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要,是否可以通过建立合适的索引来减少对于大表全表扫描所产生的大规模数据读取。对于经常使用的小表，应该尽量把他们pin 在内存中，避免不必要的老化清除及重复读取。
.. db file sequential read: DB文件连续读取。通常显示单个块的读取(通常指索引读取)，表示的是读进磁盘的block被放在连续的内存块中。事实上大部分基本代表着单个block的读入，可以说象征着 IO 或者说通过索引读入的比较多。因为一
次IO若读进多个的block，放入连续的内存块的几率是很小的，分布在不同block的大量记录被读入就会遇到此事件。因为根据索引读数据的话，假设100条记录，根据索引，不算索引本身的读，而根据索引每个值去读一下表数据，理论上最多可能产生100 buffer gets，而如果是full table scan，则100条数据完全可能在一个block里面，则几乎一次就读过这个block了，就会产生这么大的差异。这种等待的数目很多时，可能显示表的连接顺序不佳，或者不加选择地进行索引。
对于高级事务处理（high-transaction）、调整良好（welltuned）的系统，这一数值很大是很正常的，但在某些情况下，它可能暗示着系统中存在问题。你应当将这一等待统计量与Statspack 报告中的已知问题（如效率较低的SQL）联系起来。检查索引扫描，以保证每个扫描都是必要的，并检查多表连接的连接顺序。DB_CACHE_SIZE 也是这些等待出现频率的决定因素。有问题的散列区域（Hash-area）连接应当出现在PGA 内存中，但它们也会消耗大量内存，从而在
4
如何读懂 statspack——turner 整理
顺序读取时导致大量等待。它们也可能以直接路径读／写等待的形式出现。
.. Free Buffer Wait: 释放缓冲区。
这种等待表明系统正在等待内存中的缓冲，因为内存中已经没有可用的缓冲空间了。如果所有SQL 都得到了调优，这种等待可能表示你需要增大DB_BUFFER_CACHE。释放缓冲区等待也可能表示不加选择的SQL 导致数据溢出了带有索引块的缓冲存储器，没有为等待系统处理的特定语句留有缓冲区。这种情况通常表示正在执行相当多数量的DML（插入／更新／删除），并且可能说明DBWR 写的速度不够快，缓冲存储器可能充满了相同缓冲器的多个版本，从而导致效率非常低。为了解决这个问题，可能需要考虑增加检查点、利用更多的DBWR 进程，或者增加物理磁盘的数量。
.. Buffer Busy Wait: 缓冲区忙。
该等待事件表示正在等待一个以unshareable方式使用的缓冲区，或者表示当前正在被读入buffer cache。也就是当进程想获取或者操作某个block的时候却发现被别的进程在使用而出现等待。一般来说Buffer Busy Wait不应大于1%。检查缓冲等待统计部分（或V$WAITSTAT），看一下等待是否位于段头。如果是，可以考虑增加自由列表（freelist，对于Oracle8i DMT）或者增加freelist groups.其修改语法为：
SQL> alter table sp_item storage (freelists 2);
对于Oracle8i而言，增加freelist参数，在很多时候可以明显缓解等待，如果使用LMT，也就是 Local Manangement Tablespace，区段的管理就相对简单。还可以考虑修改数据块的pctusedpctfree值，比如增大pctfree可以扩大数据的分布，在某种程度上
就可以减少热点块的竞争。如果这一等待位于undo header，可以通过增加回滚段（rollback segment）来解决缓冲区的问题。如果等待位于undo block上，我们可能需要检查相关应用，适当减少大规模的一致性读取，或者降低一致性读取(consistent read)的表中的数据密度或者增大DB_CACHE_SIZE。如果等待处于data block，可以考虑将频繁并发访问的表或数据移到另一数据块或者进行更大范围的分布（可以增加pctfree 值，扩大数据分布，减少竞争），以避开这个"热点"数据块，或者可以考虑增加表中的自由列表或使用本地化管理的表空间（Locally Managed Tablespaces）。如果等待处于索引块，应该考虑重建索引、分割索引或使用反向键索引。反向键索引在很多情况下，可以极大地缓解竞争，其原理有点类似于hash分区的功效。反向键索引（reverse key index）常建在一些值是连续增长的列上，例如列中的值是由sequence产生的。为了防止与数据块相关的缓冲忙等待，也可以使用较小的块：在这种情况下，单个块中的记录就较少，所以这个块就不是那么"繁忙"；或者可以设置更大的pctfree,使数据扩大物理分布，减少记录间的热点竞争。在执行DML (insert/update/ delete)时，Oracle向数据块中写入信息，对于多事务并发访问的数据表，关于ITL的竞争和等待可能出现，为了减少这个等待，可以增加initrans，使用多个ITL槽。
.. latch free: latch释放
latch 是一种低级排队机制，用于保护SGA 中共享内存结构。
latch就像是一种快速地被获取和释放的内存锁。latch用于防止共享内存结构被多个用户同时访问。如果latch不可用，就会记录latch释放失败(latch free miss)。
有两种与闩有关的类型：立刻和可以等待。
假如一个进程试图在立刻模式下获得闩，而该闩已经被另外一个进程所持有，如果该闩不能立刻可用的话，那么该进程就不会为获得该闩而等待。它将继续执行
5
如何读懂 statspack——turner 整理
另一个操作。
大多数latch 问题都与以下操作相关：
没有很好的是用绑定变量（library cache latch）、重作生成问题（redo allocation latch）、缓冲存储器竞争问题（cache buffers LRU chain），以及buffer cache中的存在"热点"块（cache buffers chain）。通常我们说，如果想设计一个失败的系统，不考虑绑定变量，这一个条件就够了，对于异构性极强的系统，不使用绑定变量的后果是极其严重的。另外也有一些latch 等待与bug 有关，应当关注Metalink 相关bug 的公布及补丁的发布。当latch miss ratios大于0.5%时，就应当研究这一问题。Oracle 的 latch 机制是竞争，其处理类似于网络里的CSMA/CD，所有用户进程争夺latch，对于愿意等待类型(willing-to-w
ait)的latch,如果一个进程在第一次尝试中没有获得latch,那么它会等待并且再尝试一次,如果经过_spin_count 次争夺不能获得latch, 然后该进程转入睡眠状态，持续一段指定长度的时间，然后再次醒来，按顺序重复以前的步骤.在8i/9i 中默认值是 _spin_count=2000。如果SQL语句不能调整，在8.1.6版本以上，Oracle提供了一个新的初始化参数: CURSOR_SHARING，可以通过设置CURSOR_SHARING = force 在服务器端强制绑定变量。设置该参数可能会带来一定的副作用，对于Java的程序，有相关的bug，具体应用应该关注Metalink的bug公告。
.. enqueue
enqueue 是一种保护共享资源的锁定机制。该锁定机制保护共享资源，如记录中的数据，以避免两个人在同一时间更新同一数据。enqueue 包括一个排队机制，即FIFO（先进先出）排队机制。Enqueue 等待常见的有ST、HW 、TX 、TM 等。ST enqueue 用于空间管理和字典管理的表空间(DMT)的分配。对于支持LMT 的版本，可以考虑使用本地管理表空间，对于Oracle8i，因为相关bug 不要把临时表空间设置为LMT. 或者考虑预分配一定数量的区。HW enqueue 指段的高水位标记相关等待；手动分配适当区段可以避免这一等待。TX 是最常见的enqueue 等待。TX enqueue 等待通常是以下三个问题之一产生的结果。第一个问题是唯一索引中的重复索引，你需要执行提交（commit）/回滚（rollback）操作来释放enqueue。第二个问题是对同一位图索引段的多次更新。因为单个位图段可能包含多个行地址（rowid），所以当多个用户试图更新同一段时，等待出现。直到提交或回滚， enqueue 释放。第三个问题，也是最可能发生的问题是多个用户同时更新同一个块。如果没有自由的ITL 槽，就会发生块级锁定。通过增大initrans 和/或maxtrans 以允许使用多个ITL 槽，或者增大表上的pctfree值，就可以很轻松地避免这种情况。TM enqueue 在DML 期间产生，以避免对受影响的对象使用DDL。如果有外键，一定要对它们进行索引，以避免这种常见的锁定问题。
.. Log Buffer Space: 日志缓冲空间
当你将日志缓冲（log buffer）产生重做日志的速度比LGWR 的写出速度快，或者是当日志转换（log switch）太慢时，就会发生这种等待。为解决这个问题，可以增大日志文件的大小，或者增加日志缓冲器的大小。另外一个可能的原因是磁盘I/O 存在瓶颈，可以考虑使用写入速度更快的磁盘。
.. log file switch (archiving needed)
这个等待事件出现时通常是因为日志组循环写满以后，第一个日志归档尚未完成，出现该等待可能是 IO 存在问题。
解决办法：
.. 可以考虑增大日志文件和增加日志组 6
如何读懂 statspack——turner 整理
.. 移动归档文件到快速磁
盘
.. 调整log_archive_max_processes .
.. log file switch (checkpoint incomplete): 日志切换（检查点未完成）
当你的日志组都写完以后，LGWR 试图写第一个log file，如果这时数据库没有完成写出记录在第一个log file 中的dirty 块时（例如第一个检查点未完成），该等待事件出现。该等待事件说明你的日志组过少或者日志文件过小。你可能需要增加你的日志组或日志文件大小。
.. Log File Switch: 日志文件转换
所有的提交请求都需要等待"日志文件转换（必要的归档）"或"日志文件转换（chkpt.不完全）"。确保归档磁盘未满，并且速度不太慢。 DBWR 可能会因为输入/输出（I／O）操作而变得很慢。你可能需要增加更多或更大的重做日志，而且如果DBWxR 是问题症结所在的话，可能需要增加数据库书写器。
.. log file sync: 日志文件同步
当一个用户提交或回滚数据时，LGWR 将session 会话的重做由redo buffer 写入到重做日志中。log file sync 必须等待这一过程成功完成(Oracle 通过写redo log file 保证commit 成功的数据不丢失)，这个事件说明提交可能过于频繁，批量提交可以最大化LGWR 的效率，过分频繁的提交会引起LGWR频繁的激活，扩大了LGWR 的写代价。为了减少这种等待事件，可以尝试每次提交更多的记录。将重做日志置于较快的磁盘上，或者交替使用不同物理磁盘上的重做日志，以降低归档对LGWR的影响。对于软RAID，一般来说不要使用RAID 5，RAID5 对于频繁写入得系统会带来较大的性能损失，可以考虑使用文件系统直接输入/输出，或者使用裸设备（raw device），这样可以获得写入的性能提高。
.. log file single write
该事件仅与写日志文件头块相关，通常发生在增加新的组成员和增进序列号时。头块写单个进行，因为头块的部分信息是文件号，每个文件不同。更新日志文件头这个操作在后台完成，一般很少出现等待，无需太多关注。
.. log file parallel write
从log buffer 写redo 记录到redo log 文件，主要指常规写操作(相对于log file sync)。
如果你的Log group 存在多个组成员，当flush log buffer 时，写操作是并行的，这时候此等待事件可能出现。尽管这个写操作并行处理，直到所有I/O 操作完成该写操作才会完成(如果你的磁盘支持异步IO或者使用IO SLAVE，那么即使只有一个redo log file member,也有可能出现此等待)。这个参数和log file sync 时间相比较可以用来衡量log file 的写入成本。通常称为同步成本率。
.. control file parallel write: 控制文件并行写
当server 进程更新所有控制文件时，这个事件可能出现。
如果等待很短，可以不用考虑。如果等待时间较长，检查存放控制文件的物理磁盘I/O 是否存在瓶颈。多
个控制文件是完全相同的拷贝，用于镜像以提高安全性。对于业务系统，多个控制文件应该存放在不同的磁盘上，一般来说三个是足够的，如果只有两个物理硬盘，那么两个控制文件也是可以接受的。在同一个磁盘上保存多个控制文件是不具备实际意义的。减少这个等待，可以考虑如下方法：
.. 减少控制文件的个数(在确保安全的前提下)
.. 如果系统支持，使用异步IO
.. 转移控制文件到IO 负担轻的物理磁盘 7
如何读懂 statspack——turner 整理
.. control file sequential read/ control file single write
控制文件连续读/控制文件单个写。对单个控制文件I/O 存在问题时，这两个事件会出现。
如果等待比较明显，检查单个控制文件，看存放位置是否存在I/O 瓶颈。使用查询获得控制文件访问状态：
select P1 from V$SESSION_WAIT where EVENT like 'control file%' and STATE='WAITING';
解决办法：
.. 移动有问题的控制文件到快速磁盘
.. 如果系统支持，启用异步I/O
.. direct path write: 直接路径写
该等待发生在，等待确认所有未完成的异步I/O 都已写入磁盘。你应该找到I/O 操作频繁的数据文件，调整其性能。也有可能存在较多的磁盘排序，临时表空间操作频繁，可以考虑使用Local 管理表空间，分成多个小文件，写入不同磁盘或者裸设备。
.. SQL*Net message from dblink
该等待通常指与分布式处理（从其他数据库中SELECT）有关的等待。这个事件在通过DBLINKS 联机访问其他数据库时产生。如果查找的数据多数是静态的，可以考虑移动这些数据到本地表并根据需要刷新，通过快照或者物化视图来减少跨数据库的访问，会在性能上得到很大的提高。
.. slave wait: 从属进程等
Slave Wait 是Slave I/O 进程等待请求，是一个空闲参数，一般不说明问题。
4．High Load SQL 分析
对于一个特定的应用程序或者系统来讲，要调整优化其性能，最好的方法是检查程序的代码和用户使用的SQL语句。
如果使用了 level 5 级别的 snapshot ，那么statspack生成的报告中就会显示系统中高负荷SQL语句（High Load SQL）的信息，而其详细信息可以在 stats$sql_summary 表中查到。缺省情况下 snapshot 的级别是 level 5。
按照 buffer gets, physical reads, executions, memory usage and version count 等参数的降序排列顺序，把SQL语句分为几个部分罗列在报告中。在其它部分都调整好的情况下，这些top语句就会非常有用，可以对之进行优化来获得更好的性能。
5．报告的其他部分
statspack 报告的其他部分包括了 Instance Activity Stats，Tablespace IO Stats，Buffer Pool Statistics，Buffer wait Statistics，Rollback Segment Stats，Latch Activity，Dictionary Cache Stats，Library Cache Activity，SGA breakdown differen
ce 以及数据库初始化参数等等。本文不对这些内容进行详细讨论，请参加其他详细文档。
tolywang 发表于:2008.07.05 10:57 ::分类: ( Oracle数据库管理 ) ::阅读:(1238次) :: 评论 (2) :: 引用 (0)
re: 如何读懂statspack报告 (zt) [回复]
在statspack的报告分析中呢，我们首先要关注的就是基于elapse time的收集时间间隔，任何的统计数据在statspack中都是要通过时间的这个纬度里度量的，离开了时间的纬度，那么任何其他的数据也就失去了本身的意义。
我截取了在做bulk update时候的一个statspack，性能上来讲瓶颈不在数据库这端，只是想针对statspack来说明报告的不同部分。
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 6 21-Jan-08 11:02:25 26 9.7
End Snap: 7 21-Jan-08 12:14:03 26 11.2
Elapsed: 71.63 (mins)
这里列出了我的测试经历的时间：71.63 mins
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 608M Std Block Size: 8K
Shared Pool Size: 256M Log Buffer: 13,920K
同时也列出了我目前各个SGA关键组件的大小。
在分析statspack报告之前呢，Load profile（负载概要信息），这一部分是一定要读的。包括了Per Second和Per Transaction这两个指标的状态。那么所有的这些都是通过收集v$sysstat视图的信息来获得的。
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 5,466.50 1,623.37
(redo size这部分的单位是bytes, 表示在71分钟左右的时间里，每秒生成的redo的大小，通过还标识了每个事务产生的redo大小)
Logical reads: 8,682.95 2,578.55
(这里logical reads=db block gets+consistent gets)
Block changes: 23.17 6.88
Physical reads: 0.52 0.16
Physical writes: 1.76 0.52
User calls: 43.10 12.80
Parses: 14.23 4.23
Hard parses: 0.01 0.00
Sorts: 105.55 31.34
Logons: 0.02 0.01
Executes: 139.63 41.47
Transactions: 3.37
那么这里Transactions=(user commits + user rollbacks)/(71.63*60)
(其实这里用transactions rollbacks来代替user rollbacks更恰当)
% Blocks changed per Read(这里主要表示的是Block changes/Logical reads的比率): 0.27 Recursive Call %: 80.50
Rollback per transaction %: 0.00 Rows per Sort: 1.23
(这里也表示我们transaction的平均回滚率,user rollbacks/(user commits+user rollbacks))
主要注意一个细节：
transactions rollbacks和user rollbacks的区别，通常用户如果执行了一个rollback,即使没有事务需要回滚，user rollbacks也还是要+1,但是transaction rollbacks只有在确实有事务回滚的时候才+1
以上诸如user commits信息都可以从Statspack报告中的Instance Activity Stats部分来观察到：
那么来看下一个部分
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Re
do NoWait %: 100.00
Buffer Hit %: 99.99 In-memory Sort %: 100.00
Library Hit %: 99.98 Soft Parse %: 99.90
Execute to Parse %: 89.81 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 13.77 % Non-Parse CPU: 99.99
这里列出了数据库的一些性能指标。需要注意的是Buffer hit的计算公式在10g中有了改变
Buffer hit% = 1 - (physical reads-physical reads direct-physical reads direct(lob))/session logical reads-(physical reads direct+physical reads direct(lob))
其中我们的session logical reads=db block gets+consistent gets
在很多情况下buffer hit越高不全都反映，我数据库的状态越好，在有的情况下，buffer hit%高会往往饶过我逻辑读比较高这个问题。
In-memory Sort = sort memory / (sort memory + sort disk) * 100%
通过这个指标我们可以观察一下我们的内存排序量
Soft Parse % = 1 - parse count(hard)/parse count(total)
除了这里对于软解析我们还要关注Load profile中的Hard parses的per seconds单位来联合观察
Execute to Parse % = 1-parses/executions
这里主要介绍的是解析和执行之间的比率。那么也就是说有多少次执行我们不需要解析。这个指标越高越好
Parse CPU to Parse Elapsd % = （parse time cpu / parse time elapsed）* 100%
parse time cpu表示的是oracle在进行SQL解析的时候耗用的CPU时间,而parse time elapsed指在进行sql解析的时候总耗费CPU的时间
那么所有这些参与计算的指标信息我们都是可以从Instance Activity Stats中的total列得到的
最后呢，来总结一下statspack报告中比较重要的一块:
响应时间的判断，response time是我们衡量数据库响应时间的一个重要指标，
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 3,088 94.4
log file parallel write 14,363 69 5 2.1
log file sync 14,263 68 5 2.1
control file parallel write 1,466 32 22 1.0
db file parallel write 1,734 11 6 .3
这里我们看到了top 5的等待事件：
其中的CPU TIME是Oracle 9i以后引入的，这里其实需要指出的是，调优的第一手资料其实还是从statspack中的这个部分来分析，那么结合我们的top 5的等待事件，我们可以来衡量不同等级的top sql:
1.消耗最多CPU的(逻辑IO比较多的)
2.导致过多物理I/O的(物理IO比较多的)
3.执行次数较频繁的(Execution次数比较多的)
4.执行时间较长的(Elapse time比较长的)
其实在Oracle世界里有一套关于resposne time的分析方法：
Response time=Service time + Wait time
因此在上面的top 5 timed events表格里面我们看见了有Waits和Time (s)两列，分别罗列了我们response time的两大部分，一部分是服务时间由time (s)表示，另外一个部分就是等待时间了由Waits表示。而Time (s)是指花在CPU上的时间，而W
aits是花 在等待事件上的总的时间
那么什么是Service time呢？
其实Service time就是CPU为执行该语句花费的时间。
Service Time=CPU Parse + CPU Recursive + CPU Other 三部分组成
CPU Parse是CPU用于分析语句的时间，CPU Recursive是CPU用于语句的递归SQL的时间，CPU Other则就是CPU用于执行语句的真正时间了。
在Statpacks中Instance Activity Stats中就有部分的时间统计信息：
Service time=CPU used by this session
CPU Parse=parse time cpu
CPU Resursive=recursive cpu usage
所以CPU ther=CPU used by this session - parse time cpu - recursive cpu usage
如果执行时间(CPU Other)在整个Service time中占较大的比例，那么下一步就是找出那些造成了最多逻辑IO的SQL语句，可以从statspack报告的SQL ordered by Gets部分找到。
如果分析时间(CPU Parse=parse time cpu)在整个响应时间中占较大的比例，那么下一步就是查找哪些SQL分析过多，这在statspack报告中在SQL ordered by Parse Calls中列出。
如果等待时间(Wait time)在整个响应时间中占较大的比例，并且主要是块读取相关的等待时，下一步就是找出哪些SQL造成了过多的物理读，可以查看statspack报告中的SQL ordered by Reads部分。
比如我们现在从Instance Activity Stats查出一些数据：
Instance Activity Stats DB/Inst: IRMDB/irmdb Snaps: 6-7
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 308,760 71.8 21.3
......
parse time cpu 23 0.0 0.0
parse time elapsed 167 0.0 0.0
......
recursive cpu usage 307,328 71.5 21.2
......
我们知道CPU Time的total call time%是94.4%
我们就可以看一下：
CPU Time=94%
CPU ther=308,760 - 23 - 307,328 = 1409
CPU ther=1409 / 308,760 * 94% = 0.43%
CPU Parse=23 / 308,760 * 94% = 忽略不计
CPU Recursive = 307,328 / 308,760 * 94% = 93.56%
可以看到原来我们的Recursive call占用了我们大量的CPU时间
除了上面的SQL ordered by Gets(逻辑IO最多)，SQL ordered by Parse Calls(软解析过多)，SQL ordered by Reads(物理IO过多)，statspack还按照其他的一些方式列出了Top SQL，这些Top SQL在某些情况下都是需要给予特别关注的。比如：
SQL ordered by Executions 执行次数超过100的
SQL ordered by Sharable Memory 占用library cache超过1M的
SQL ordered by Version Count 子cursor超过20的
如果没有statspack，那么根据v$sysstat/v$sesstat中的统计信息，结合v$sql/v$sqlarea，一样可以得到相关的SQL。
v$sql对于每一个子cursor都有一行统计记录，而v$sqlarea则对同一个父cursor只有一行统计记录，也就是v$sqlarea是对v$sql按照父cursor进行group by后的一个结果。这两个视图中都有诸如buffer_gets,parse_calls,disk_reads,,executions,sharable_mem等列，和上面提到
的statspack中列出Top SQL的条件对应。
tolywang 评论于：2008.07.05 11:01
re: 如何读懂statspack报告 (zt) [回复]
Wait Events for DB: his Instance: his Snaps: 1 -2
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
latch free 459,303 9,690 5,636 12 38.5
db file sequential read 859,956 0 1,894 2 72.1
db file scattered read 267,639 0 384 1 22.5
log file sync 11,529 1 109 9 1.0
log file parallel write 12,718 0 96 8 1.1
enqueue 45 13 62 1383 0.0
wait list latch free 234 0 6 25 0.0
buffer busy waits 149 0 4 30 0.0
SQL*Net more data to client 115,913 0 4 0 9.7
log file sequential read 112 0 4 34 0.0
LGWR wait for redo copy 398 147 3 8 0.0
control file parallel write 796 0 2 3 0.1
SQL*Net break/reset to clien 512 0 0 1 0.0
control file sequential read 691 0 0 0 0.1
log file switch completion 8 0 0 6 0.0
direct path read 625 0 0 0 0.1
log file single write 8 0 0 0 0.0
direct path write 103 0 0 0 0.0
async disk IO 26 0 0 0 0.0
SQL*Net message from client 3,295,298 0 646,563 196 276.5
wakeup time manager 74 74 2,205 29804 0.0
SQL*Net message to client 3,295,303 0 9 0 276.5
SQL*Net more data from clien 6,646 0 1 0 0.6
-------------------------------------------------------------
这部分内容是列出数据库中所有的等待事件。
Background Wait Events for DB: his Instance: his Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 12,717 0 96 8 1.1
log file sequential read 112 0 4 34 0.0
LGWR wait for redo copy 398 147 3 8 0.0
control file parallel write 796 0 2 3 0.1
db file scattered read 43 0 1 29 0.0
db file sequential read 79 0 1 7 0.0
latch free 37 6 0 12 0.0
control file sequential read 637 0 0 0 0.1
log file single write 8 0 0 0 0.0
direct path write 99 0 0 0 0.0
direct path read 99 0 0 0 0.0
async disk IO 26 0 0 0 0.0
rdbms ipc message 15,705 2,225 11,603 739 1.3
pmon timer 833 762 2,288 2746 0.1
smon timer 8 7 2,085 ###### 0.0
-------------------------------------------------------------
这部分是数据库后台进程发生的事件，这些信息对于我们在确认后台进程是在等待什么是有用处的。
随后的程序应该是这个报告的核心，应该说80％的问题是SQL问题，只有优化SQL才能从根本上解决问题的根源。
SQL ordered by Gets for DB: his Instance: his Snaps: 1 -2
-> End Buffer Gets Threshold: 10000
-> Note
that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
35,289,726 1 35,289,726.0 18.6 203.90 787.99 1761327529
Module: HIS.exe
Select A.ID,Decode(P.险类,NULL,NULL,'√') as 医保,A.NO as 单据号
...
这部分是按照一致读排序收集的SQL文。阀值为：10000
SQL ordered by Reads for DB: his Instance: his Snaps: 1 -2
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
466,867 119 3,923.3 18.6 125.23 322.94 954397685
Module: HIS.exe
SELECT Decode(SUM(Decode(Z.记录状态,1,1,0)),0,0,1) AS 选择,A.相
。。。
这部分是按照磁盘读排序收集的SQL文。阀值为：1000
SQL ordered by Executions for DB: his Instance: his Snaps: 1 -2
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
33,972 33,972 1.0 0.00 0.01 2293415029
Module: HIS.exe
SELECT SYSDATE FROM DUAL
这部分是按照执行次数排序收集的SQL文。阀值为：100
SQL ordered by Parse Calls for DB: his Instance: his Snaps: 1 -2
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
33,972 33,972 5.62 2293415029
Module: HIS.exe
SELECT SYSDATE FROM DUAL
这部分是按照分析次数排序收集的SQL文。阀值为：1000
SQL ordered by Sharable Memory for DB: his Instance: his Snaps: 1 -2
-> End Sharable Memory Threshold: 1048576
Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
2,384,856 82 0.2 3772824490
Module: HIS.exe
这部分是按照占用共享内存排序收集的SQL文。阀值为：1048576
SQL ordered by Version Count for DB: his Instance: his Snaps: 1 -2
-> End Version Count Threshold: 20
Version
Count Executions Hash Value
-------- ------------ ------------
104 82 3772824490
这部分是按照版本次数排序收集的SQL文。阀值为：1048576
n 出现在共享池中的SQL版本数多于一个的原因：
n 不同的用户提交同样的版本，但是实际访问的表不同。
n 在一个不同环境中执行的同一个查询，例如优化器的模式不同；
n 正在使用FIND DRAINED ACCESS CONTROL重写这个查询，在共享池中的每一个版本实际上是不同的查询。
n 客户程序对绑定变量
使用不同的数据类型长度；
Instance Activity Stats for DB: his Instance: his Snaps: 1 -2
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 375,743 160.3 31.5
CPU used when call started 375,933 160.4 31.5
CR blocks created 22,146 9.5 1.9
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DBWR buffers scanned 555,692 237.1 46.6
DBWR checkpoint buffers written 18,682 8.0 1.6
DBWR checkpoints 2 0.0 0.0
DBWR free buffers found 535,358 228.4 44.9