scoreking的博客

statspack的若干使用技巧

-->

xzh2000 | 17 一月, 2005 19:59

一点整理的statspack内容信息
Instance Efficiency Percentages

Data Buffer Hit Ratio#<#90#数据块在数据缓冲区中的命中率，通常应该在90%以上，否则考虑加大 db_block_buffers(9i 以上可是db_cache_size)
Buffer Nowait Ratio#<#99#在缓冲区中获取buffer 的未等待比率
Library Hit Ratio#<#98#主要代表着sql在共享区的命中率，通常在98%以上
In Memory Sort Ratio#<#0#如果过低说明有大量的排序在临时表空间中进行，可尝试增加sort_area_size
Redo Nowait Ratio#<#98#写日志的不等待比率,太低可调整log_buffer(增加)和 _log_io_size(减小，默认为1/3*log_buffer/log_block_size,使得 _log_io_size 为合适的值，比如128k/log_block_size)
Soft Parse Ratio#<#90#近似当作sql在共享区的命中率，通常高代表使用了绑定变量,太低需要调整应用使用绑定变量，或者参考 cursor_sharing = force (9i 中增加了 similar )
Latch Hit Ratio#<#99#内部结构维护锁命中率，高于99%,通常低是因为shared_pool_size过大和没有使用绑定变量导致硬解析过多，可参考 _spin_count 参数设置
Percent Non-Parse CPU#<#95#查询实际运行时间/(查询实际运行时间+sql解析时间)，太低表示解析消耗时间过长
Percent Parse CPU to Parse Elapsed#<#90#解析实际所用时间/(解析实际所用时间+解析中等待资源时间),越高越好
Execute to Parse Percent#<#10#该值越高表示一次解析后被重复执行的次数越多，如果过低可以考虑设置 session_cached_cursors > 0
Memory Usage Percent#<#75#共享池的使用率，应该稳定在75%--90%之间，太小浪费内存，太大则显内存不足
Percent of SQLs with Execution>1#<#40#执行次数大于1的sql的比率（若太小可能是没有使用绑定变量）
Percent of Memory for SQl with Execution>1#<#0#执行次数大于1的sql消耗内存/(所有sql消耗内存)

Instance Load Profile

Redo Size/Sec#>#100000#每秒产生的日志大小（单位字节），可标志数据库任务的繁重与否
Redo Size/Tx#>#0#平均每个事务的日志生成量
Logical Reads/Sec(逻辑读)#>#0#平均每秒产生的逻辑读,单位是block
Logical Reads/Tx#>#0#平均每个事务产生的逻辑读,单位是block
Block Changes/Sec#>#100#每秒block变化数量，数据库事务带来改变的块数量
Block Changes/Tx#>#0#平均每个事务所导致的改变的块数
Physical Reads/Sec#>#100#平均每秒数据库从磁盘读取的block数
Physical Reads/Tx#>#0#平均每个事务从磁盘读取的block数
Physical Write/Sec#>#50#平均每秒写磁盘的block数
Physical Write/Tx#>#0#平均每个事务写磁盘的block数
User Calls/Sec#>#0#每秒用户call次数
User Calls/Tx#>#0#每事务用户call次数
Parses/Sec#>#100#每秒解析次数，近似反应了每秒语句的执行次数
Parses/Tx#>#0#每事务产生的解析次数
Hard Parses/Sec#>#10#每秒产生的硬解析次数
Hard Parses/Tx#>#0#每事务产生的硬解析次数
Sorts/Sec#>#20#每秒产生的排序次数
Sorts/Tx#>#5#每事务产生的排序次数
Transactions/Sec#>#0#每秒产生的事务数
Rows/Sort#>#0#每次排序所涉及到的行数
Percent of Block Changed/Read#>#0#发生变化的块数/读次数，变化的块需要从回滚段来数据
Recursive Call Percent#>#0#递归操作占所有操作的比率
Rollback/Tx Percent#>#5#事务回滚率（回滚开销很大）
Executes/Sec#>#0#每秒执行次数
Execute/Tx#>#0#每个事务执行次数
--45: Logons/Sec
--46: Logons/Tx

I/O Statistics(I/O统计数据)

Table Space I/O#>#0#表示各表空间在IO上的分布，若出现严重不均衡，则要重新考虑对象的存储规划和表空间中数据文件的磁盘规划
Datafile I/O#>#0#表示各数据文件的IO分布，若不均衡则需要重新考虑对象的存储规划
Table I/O（表I/O）#>#0#对这些IO很大的表，要考虑放置在高速磁盘上，并且尽可能的分布在不同的磁盘上

TOP SQL

Top SQL with High Buffer Gets#>#0#这类sql进行了大量的block的读，要检查该sql是否用到了索引，或者说表上是否存在合理的索引，对于必须全表扫描的大表可以考虑recycle buffer ,对于频繁进行全表扫描的小表可以考虑keep buffer，还有一种需要注意的情况就是如果通过索引获取数据比例占表数据比例过大，比如20%(举例数据)，就能导致buffer gets过大
Top SQL with High Physical Reads#>#0#这类sql导致了大量的从磁盘获取数据，可能因为数据缓冲区太小，也可能是过多的全表扫描，需要考察索引是否合理，是否用到索引
Top SQL with High Execution Count#>#0#这类sql是需要重点关注的，也许这些sql本身一次执行并没有消耗大量的时间或者空间，但由于频繁的执行对系统影响极大，所以只要有优化的可能到要对这些sql进行优化。还有另外一些情况，就是某些程序中可能大量频繁地使用dual表来获取一些信息(比如时间的计算等)，尽可能的使这类sql转化为应用本地能解决的函数，或者还存在一些由于设计上的缺陷导致不必要的查询，都要在设计的角度避免这些查询
Top SQL with High Shared Memory#>#0#这类sql使用了大量的内存，不一定执行的频繁，但是它可能把执行的频繁的sql涉及的一些数据挤出缓冲区，这同样将导致很多问题，所以也需要从尽可能的优化
Top SQL with High Version Count#>#20#表示多个用户的sql在字面上是一样的，或者sql虽然一样但是session的一些参数发生了改变（比如sort_area_size发生了变化）

Wait Events(等待事件)

alter system set mts_dispatcher#>#0#当会话决定执行"ALTER SYSTEM SET MTS_DISPATCHERS = <string> "的时候等待DISPATCHERS的启动
BFILE check if exists#>#0#检查外部的bfile文件是否存在
BFILE check if open#>#0#检查外部的bfile文件是否已经open
BFILE closure#>#0#等待关闭外部bfile文件
BFILE get length#>#0#获得外部bfile文件的大小
BFILE get name object#>#0#得到外部bfile文件的名字
BFILE get path object#>#0#得到外部bfile文件的路径
BFILE internal seek#>#0#
BFILE open#>#0#等待外部bfile被打开
BFILE read#>#0#等待外部bfile文件读取完毕
buffer busy due to global cache#>#0#
buffer busy waits#>#0#block正被读入缓冲区或者缓冲区正被其他session使用,出现此情况通常可能通过几种方式调整：增大data buffer,增加freelist，减小pctused,增加回滚段数目，增大initrans,考虑使用LMT
buffer deadlock#>#0#由于系统缓慢所产生而非应用产生了死锁
buffer latch#>#0#会话等待'buffer hash chain latch'
buffer read retry#>#0#OPS下读buffer的过程中内容发生了变化于是重新读取
Cache simulator heap#>#0#
checkpoint completed#>#0#等待检查点的完成，通常出现这个问题的原因是IO问题严重，可调整跟检查点相关参数log_checkpoint_interval,log_checkpoint_timeout,db_block_max_dirty_target,fast_start_io_target 等，可间接的增大日志文件大小和增加日志文件组数
Contacting SCN server or SCN lock master#>#0#
control file parallel write#>#0#等待写所有控制文件的完成，可将控制文件分散在不同的磁盘上
control file sequential read#>#0#读控制文件，在备份控制文件、OPS等情况下产生
control file single write#>#0#OPS下同一时刻只允许一个session将共享信息写入磁盘
conversion file read#>#0#
db file parallel read#>#0#做恢复的并行从数据文件获取数据
db file parallel write#>#0#当多个IO可以同时发生时(多磁盘)，DBWR可并行写入，DBWR等待最后一个IO的完成
db file scattered read#>#0#一次获取的block被分散在buffer的不连续空间中，通常表示全表扫描过多，可检查应用程序是否合理的使用了索引，数据库是否合理的创建了索引
db file sequential read#>#0#通常暗示着通过索引获取数据量比较大（比如通过索引进行范围扫描获取表数据百分比过大），多表连接的时候连接顺序不当，hash join时hash_area_size无法容纳hash table
db file single write#>#0#更新数据文件头出现等待
debugger command#>#0#
DFS db file lock#>#0#OPS下每个实例在数据文件上有一个共享的全局锁，当要offline数据文件的时候等候其他实例同步文件
DFS lock handle#>#0#会话等待一个全局锁请求
direct path read#>#0#通常发生在临时表空间排序、并行查询中
direct path read (lob) #>#0#
direct path write#>#0#direct方式导入数据(sqlldr,CTAS)、PDML、临时表空间排序
direct path write (lob)#>#0#
dispatcher listen timer#>#0#
dispatcher shutdown#>#0#
dispatcher timer#>#0#
DLM generic wait event#>#0#
dupl. cluster key#>#0#
enqueue#>#0#对共享资源的获取要求一种排队(FIFO)的机制以保护共享资源，ST enqueue表示空间分配或者释放导致的问题可采用LMT表空间来避免，TX enqueue主要产生于唯一索引重复、bitmap index 的频繁更新、initrans太小或者pctfree过小
file identify#>#0#
file open#>#0#
free buffer waits#>#0#在缓冲区中寻找可用buffer出现等待，可能数据缓冲区太小，也可能检查点间隔太长，也可能频繁的DML而IO成为瓶颈
free global transaction table entry#>#0#分布式数据库中会话等待一个全局事务槽
free process state object#>#0#
global cache bg acks#>#0#
global cache cr request#>#0#
global cache freelist wait#>#0#
global cache lock busy#>#0#会话等待将一个buffer从当前共享状态转换为当前独占状态
global cache lock cleanup#>#0#
global cache lock null to s#>#0#
global cache lock null to x#>#0#
global cache lock open s#>#0#
global cache lock open x#>#0#
global cache lock s to x#>#0#
global cache multiple locks#>#0#
global cache pending ast#>#0#
global cache pending asts#>#0#
global cache retry prepare#>#0#
global cache retry request#>#0#
imm op#>#0#
inactive session#>#0#
inactive transaction branch#>#0#
index block split#>#0#当在索引中查找一个key的时候如果发现该索引block正在裂变则等待裂变完成
io done#>#0#会话等待IO的完成
KSIM GDS request cancel#>#0#
latch activity#>#0#
latch free#>#0#latch是一种维护内存的锁，不采用排队机制，快速的获取然后很快释放，造成的原因通常有程序没有使用绑定变量、shared_pool_size设置过大（比如1G）、LRU竞争、某些块过热（访问太频繁）
LGWR wait for redo copy#>#0#表示等待redo allocation and redo copy latches,可增加 _log_simulteneous_copies(默认为 2*CPUs)，但同时也容易引入redo allocation latch contention,所以需要慎重
library cache load lock#>#0#
library cache lock#>#0#
library cache pin#>#0#
listen endpoint status#>#0#
LMON wait for LMD to inherit communication channels#>#0#
local write wait#>#0#
lock manager wait for dlmd to shutdown#>#0#
lock manager wait for remote message#>#0#
log buffer space#>#0#生成日志等待lgwr赶快写文件而腾出log buffer,可在init参数文件中增大 log_buffer,放置日志文件于高速磁盘上
log file parallel write#>#0#当lgwr写日志文件的过程中出现等待，这个等待通常会导致 log file sync事件,放置日志文件于高速磁盘上
log file sequential read#>#0#
log file single write#>#0#
log file switch (archiving needed)#>#0#当日志切换的时候由于日志组循环使用了一圈但日志归档还没有完成，通常是io有严重问题，可增大日志文件和增加日志组，调整log_archive_max_processes
log file switch (checkpoint incomplete)#>#0#当日志切换的时候由于日志组循环使用了一圈但将被使用的日志组中的checkpoint还没有完成造成，通常是io有严重问题，可增大日志文件和增加日志组
log file switch (clearing log file)#>#0#
log file switch completion#>#0#
log file sync#>#0#当用户commit的时候通知lgwr写日志但lwgr正忙，造成的可能原因是commit太频繁或者lgwr一次写日志时间太长（可能是因为一次log io size 太大），可调整 _log_io_size，结合log_buffer,使得 (_log_io_size*db_block_size)*n = log_buffer,这样可避免和增大log_buffer引起冲突;放置日志文件于高速磁盘上

write complete waits#>#0#用户等候buffer被写进文件，暗示着写数据文件等待

statspack分析 Load Profile ~~~~~~~~~~Per Second Per Transaction --------------- --------------- Redo size: 22,007.09 2,921.10 --很重要的参数，表示你数据变更频率 Logical reads: 22,890.62 3,038.38 Block changes: 95.88 12.73 Physical reads: 5,413.37 718.54 Physical writes: 5.67 0.75 User calls: 750.85 99.66 Parses: 183.20 24.32 ----软解析每秒超过300次意味着你的"应用程序"效率不高， 没有使用soft soft parse，调整session_cursor_cache Hard parses: 20.41 2.71 --每秒超过100次，就可能说明你绑定使用的不好 Sorts: 5.17 0.69 Logons: 0.03 0.00 Executes: 185.17 24.58 Transactions: 7.53 % Blocks changed per Read: 0.42 Recursive Call %: 21.95 --如果有很多PLSQL，那么他就会比较高 Rollback per transaction %: 0.01 Rows per Sort: 159.13 --看回滚率是不是很高，因为回滚很耗资源 Instance Efficiency Percentages (Target 100%) --这一部分通过可以提前找出ORACLE潜在将要发生的性能问题（所以很重要哦） ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.71 Redo NoWait %: 100.00 --Buffer Nowait<99%说明，有可能是有热块(查找x$bh的 tch和v$latch_children的cache buffers chains) Buffer Hit %: 76.54 In-memory Sort %: 100.00 --Buffer Hit<95%,重要的参数,小于95%可能是要加db_cache_size,但是大量的非选择的索引也会造成该值很高（大量的db file sequential read） Library Hit %: 97.07 Soft Parse %: 88.86 --Library Hit<95%,要考虑加大共享池，绑定变量，修改cursor_sharing等 Execute to Parse %: 1.06 Latch Hit %: 99.76 --Soft Parse<95%,需要考虑到绑定，如果低于80%，那么就可能sql基本没有被重用 Parse CPU to Parse Elapsd %: 89.28 % Non-Parse CPU: 91.37 --Latch Hit<99%,要确保>99%，否则存在严重的性能问题，比如绑定等会影响该参数 如果一个经常访问的列上的索引被删除，可能会造成buffer hit 显著的下降 如果增加了索引，但是他影响了ORACLE正确的选择表连接时的驱动顺序，那么可能会导致buffer hit 显著增高 如果你的命中率变化幅度很大，说明你要改变SQL模式 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 89.18 85.56 --共享内存使用情况 70%-98%都在正常范围 % SQL with executions>1: 36.31 36.10 -- % Memory for SQL w/exec>1: 38.86 38.33 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 2,913 32.01 db file sequential read 2,142,279 2,820 30.99 db file scattered read 1,724,832 1,183 13.00 buffer busy waits 198,624 1,042 11.44 log file sync 22,857 915 10.06 TIMED_STATISTICS = TRUE 那么等待事件按等待的时间排序 = FALSE那么事件按等待的数量排序 常见事件 LOG FILE SYNC: 在每次提交时都出现，如果这个等待事件影响到数据库性能，那么就需要修改应用程序的提交频率 db file sequential read: 在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用非选择性的索引 DB_CACHE_SIZE: 可以决定该事件出现的频率 db file scattered read : 意味着等待于全表扫描有关系,通常全表扫描表数据放入内存中,但是被申请到的内存高速缓冲的每个区可能不连续,该值过大说明缺少索引或者限制了索引的使用(也可以调整optimizer_index_cost_adj) ,如果经常必须进行全表扫描,而且表比较小, 把该表存人keep池.如果是大表经常进行全表扫描,那么应该是olap系统,而不是oltp的 buffer busy wait: 当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待.该值不应该大于1%,确认是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小) latch free: 常跟应用没有很好的应用绑定有关 Enqueue : 最有可能是多个用户同时修改同一个块,如果没有空闲的ITL空间,就会出现数据库块级锁 logfile switch: 通常是因为归档速度不够快,需要增大重做日志 log buffer space: 日志缓冲区写的速度快于LGWR写REDOFILE的速度,可以增大日志文件大小 TOP SQL 调整首要的25个缓冲区读操作和首要的25个磁盘读操作做的查询,将可对系统性能产生5%到5000%的增益. Instance Activity Stats for DB: CRMTEMP Instance: crmtemp Snaps: 3 -11 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 291,318 98.1 13.0 CPU used when call started 291,318 98.1 13.0 CR blocks created 1,784 0.6 0.1 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DBWR buffers scanned 985,112 331.6 44.0 DBWR checkpoint buffers written 948 0.3 0.0 DBWR checkpoints 0 0.0 0.0 dirty buffers inspected 483 0.2 0.0 --脏缓冲的个数 free buffer inspected 8,154 2.7 0.4 --如果数量很大,说明缓冲区过小 sorts (disk) 0 0.0 0.0 --不应当大于1-5% sorts (memory) 15,365 5.2 0.7 sorts (rows) 1,445,018 823.0 109.2 summed dirty queue length 24,667 8.3 1.1 ...... ...... 大家来补充，最好结合实例进行分析

• 第一部分

数据库概要信息

DB Name DB Id Instance Inst Num Release Cluster Host ---------- ----------- ------------ -------- ----------- ------------ GLOB 188430914 glob 1 9.2.0.4.0 NO b02

• 第二部分

数据库采样时段，这一部分记录了数据库采样的时间，以及采样点数，这部分信息对于report来说是十分重要。

任何统计数据都需要通过时间纬度来衡量，离开了时间，任何数据都失去了意义。

我们在论坛上经常看到有人贴出Top 5等待事件寻求分析，我们的回答是:

无法分析，如果没有时间纬度!

Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 508 10-Nov-03 15:27:29 76 39.4 End Snap: 511 10-Nov-03 15:57:42 66 35.4 Elapsed: 30.22 (mins)

• 第三部分

主要性能指标说明:

• Execute to Parse %执行分析比率

Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.81 In-memory Sort %: 100.00 Library Hit %: 98.75 Soft Parse %: 97.05 Execute to Parse %: 44.21 Latch Hit %: 94.79 Parse CPU to Parse Elapsd %: 11.74 % Non-Parse CPU: 96.08

执行分析比率计算公式如下:

100 * (1 - Parses/Executions) = Execute to Parse

所以如果系统Parses > Executions,就可能出现该比率小于0的情况.

该参数计算来自以下部分:

Instance Activity Stats for DB: ORA9 Instance: ora91 Snaps: 30 -32 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ exchange deadlocks 481 0.2 0.0 execute count 4,873,158 1,968.2 94.4 …………… parse count (failures) 542 0.2 0.0 parse count (hard) 80,281 32.4 1.6 parse count (total) 2,718,643 1,098.0 52.6 parse time cpu 44,009 17.8 0.9 parse time elapsed 374,902 151.4 7.3 …………………….

通过公式及以上两个数值：

100 * (1 - Parses/Executions) = Execute to Parse 100 * (1 - 2,718,643/4,873,158) = 0.44211884777797067117462 * 100 = 44.21

该值<0通常说明shared pool设置或效率存在问题

造成反复解析，reparse可能较严重,或者可是同snapshot有关

如果该值为负值或者极低,通常说明数据库性能存在问题

• Parse CPU to Parse Elapsd %

来自parse time cpu和parse time elapsed

100*（parse time cpu / parse time elapsed）= Parse CPU to Parse Elapsd % 100*（44,009 / 374,902）= 11.7388010733471680599196590% = 11.74%

• Rollback per transaction 平均事务回滚率

% Blocks changed per Read: 0.37 Recursive Call %: 1.14 Rollback per transaction %: 38.22 Rows per Sort: 11.83 如果回滚率过高，可能说明你的数据库经历了太多的无效操作 过多的回滚可能还会带来Undo Block的竞争 该参数计算公式如下: Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% ……………. user commits 31,910 12.9 0.6 user rollbacks 19,740 8.0 0.4 ……………. 对于本例: Round(19740 / (31910 + 19740),4) = .3822

这一部分的内容还没有写完,在继续进行中...

:)

2004年6月25日 星期五

If you have any question,please mail to eygle@itpub.net .

statspack报告数据结果解释

本人将最近在学习性能调优时，所用笔记总结如下，欢迎批评指正
本文将不断更新，欢迎补充。（所列数据仅用于便于说明，没有实
际意义）

一、statspack 输出结果中必须查看的十项内容

1、负载间档(Load profile)
2、实例效率点击率(Instance efficiency hit ratios)
3、首要的5个等待事件(Top 5 wait events)
4、等待事件(Wait events)
5、闩锁等待
6、首要的SQL(Top sql)
7、实例活动(Instance activity)
8、文件I/O(File I/O)
9、内存分配(Memory allocation)
10、缓冲区等待(Buffer waits)

二、输出结果解释

1、报表头信息
数据库实例相关信息，包括数据库名称、ID、版本号及主机等信息

Quote:

STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ PORMALS 3874352951 pormals 1 9.2.0.4.0 NO NJLT-SERVER1 Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 36 18-7月 -04 20:41:02 29 19.2 End Snap: 37 19-7月 -04 08:18:27 24 15.7 Elapsed: 697.42 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 240M Std Block Size: 8K Shared Pool Size: 96M Log Buffer: 512K

2、负载间档
该部分提供每秒和每个事物的统计信息，是监控系统吞吐量和负载变化的重要部分

Quote:

Load Profile ~~~~~~~~~~~~ Per Second(秒) Per Transaction事物 --------------- --------------- Redo size: 148.46 3,702.15 Logical reads: 1,267.94 31,619.12 Block changes: 1.01 25.31 Physical reads: 4.04 100.66 Physical writes: 4.04 100.71 User calls: 13.95 347.77 Parses: 4.98 124.15 Hard parses: 0.02 0.54 Sorts: 1.33 33.25 Logons: 0.00 0.02 Executes: 2.46 61.37 Transactions: 0.04 % Blocks changed per Read: 0.08 Recursive Call %: 30.38 Rollback per transaction %: 0.42 Rows per Sort: 698.23

说明:
Redo size:每秒产生的日志大小(单位字节)，可标志数据库任务的繁重与否
Logical reads:平决每秒产生的逻辑读，单位是block
block changes:每秒block变化数量，数据库事物带来改变的块数量
Physical reads:平均每秒数据库从磁盘读取的block数
Physical writes:平均每秒数据库写磁盘的block数
User calls:每秒用户call次数
Parses:每秒解析次数，近似反应每秒语句的执行次数
软解析每秒超过300次意味着你的"应用程序"效
率不高，没有使用soft soft parse，调整session_cursor_cache
Hard parses:每秒产生的硬解析次数
Sorts:每秒产生的排序次数
Executes:每秒执行次数
Transactions:每秒产生的事务数，反映数据库任务繁重与否

3、实例命中率
该部分可以提前找出ORACLE潜在将要发生的性能问题，很重要

Quote:

Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.96 In-memory Sort %: 99.14 Library Hit %: 99.53 Soft Parse %: 99.57 Execute to Parse %: -102.31 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 81.47 % Non-Parse CPU: 96.46

说明:
Buffer Nowait %:在缓冲区中获取Buffer的未等待比率
Redo NoWait %:在Redo缓冲区获取Buffer的未等待比率
Buffer Hit %:数据块在数据缓冲区中得命中率，通常应在90%以上，否则，需要调整
In-memory Sort %:在内存中的排序率
Library Hit %:主要代表sql在共享区的命中率，通常在95%以上，否，需要要考虑加
大共享池，绑定变量，修改cursor_sharing等参数。
Soft Parse %:近似看作sql在共享区的命中率，小于<95%,需要考虑到绑定，如果低于80%，
那么就可能sql基本没有被重用
Execute to Parse %:sql语句解析后被重复执行的次数，如果过低，可以考虑设置
session_cached_cursors参数
Parse CPU to Parse Elapsd %:解析实际运行事件/(解析实际运行时间+解析中等待资源时间)
越高越好
% Non-Parse CPU:查询实际运行时间/(查询实际运行时间+sql解析时间)，太低表示解析消耗时间过多。

Quote:

Shared Pool Statistics Begin End ------ ------ Memory Usage %: 33.79 57.02 % SQL with executions>1: 62.62 73.24 % Memory for SQL w/exec>1: 64.55 78.72

Shared Pool相关统计数据

Memory Usage %:共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。

% SQL with executions>1:执行次数大于1的sql比率，若太小可能是没有使用bind variables。

% Memory for SQL w/exec>1:也即是memory for sql with execution > 1:执行次数大于1的sql
消耗内存/所有sql消耗的内存

4、首要等待事件

Quote:

Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 581 63.58 SQL*Net more data to client 223,918 257 28.14 control file parallel write 13,595 24 2.66 direct path read 4,411 17 1.86 db file sequential read 2,851 12 1.28

常见等待事件说明:
oracle等待事件是衡量oracle运行状况的重要依据及指示，主要有空闲等待事件和非空闲等待事件
；空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，
非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。
比较影响性能常见等待事件
db file scattered read
该事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，
通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。该指数的数量过大说明
缺少索引或者限制使用索引。这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。
当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。可以尝试将较小的表放入
缓存keep中，避免反复读取它们。
db file sequential read
该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选
择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确
保索引扫描是必须的，并确保多表连接的连接顺序来调整
buffer busy wait
当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待.该值不应该大于1%,确认
是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)
latch free
闩锁是底层的队列机制(更加准确的名称应该是互斥机制)，用于保护系统全局区(SGA)共享内存结构
。闩锁用于防止对内存结构的并行访问。如果闩锁不可用，就会记录一次闩锁丢失。绝大多数得闩锁问题
都与使用绑定变量失败(库缓存闩锁)、生成重作问题(重执行分配闩锁)、缓存的争用问题(缓存LRU链) 以
及缓存的热数据宽块(缓存链)有关。当闩锁丢失率高于0.5%时，需要调整这个问题。
log buffer space
日志缓冲区写的速度快于LGWR写REDOFILE的速度,可以增大日志文件大小，增加日志缓冲区的大小，或
者使用更快的磁盘来写数据。
logfile switch
通常是因为归档速度不够快,需要增大重做日志
log file sync
当一个用户提交或回滚数据时，LGWR将会话得重做操作从日志缓冲区填充到日志文件中，用户的进程
必须等待这个填充工作完成。为减少这个等待事件，须一次提交更多记录，或者将重做日志REDO LOG 文件
访在不同的物理磁盘上.

分析一下STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORA8 1365122498 ora8 1 9.2.0.1.0 NO IBM02

Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 245 04-8月 -04 14:00:03 318 6.7
End Snap: 246 04-8月 -04 15:00:03 340 8.8
Elapsed: 60.00 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,360M Std Block Size: 8K
Shared Pool Size: 208M Log Buffer: 2,000K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 11,294.73 11,934.55
Logical reads: 996.91 1,053.38
Block changes: 72.60 76.71
Physical reads: 12.86 13.59
Physical writes: 5.57 5.89
User calls: 63.52 67.12
Parses: 10.34 10.93
Hard parses: 3.64 3.84
Sorts: 2.63 2.78
Logons: 0.09 0.09
Executes: 28.06 29.65
Transactions: 0.95

% Blocks changed per Read: 7.28 Recursive Call %: 40.50
Rollback per transaction %: 13.50 Rows per Sort: 409.44

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.73 In-memory Sort %: 99.99
Library Hit %: 91.37 Soft Parse %: 64.85
Execute to Parse %: 63.14 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 85.16 % Non-Parse CPU: 92.19

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 96.25 96.60
% SQL with executions>1: 27.55 15.98
% Memory for SQL w/exec>1: 32.45 18.32

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 116 345 50.81
CPU time 288 42.45
db file scattered read 3,308 15 2.21
db file sequential read 3,526 14 1.99
log file sync 2,570 7 1.02
-------------------------------------------------------------
Wait Events for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
enqueue 116 114 345 2972 0.0
db file scattered read 3,308 0 15 5 1.0
db file sequential read 3,526 0 14 4 1.0
log file sync 2,570 1 7 3 0.8
db file parallel write 1,078 539 6 5 0.3
control file parallel write 1,179 0 2 2 0.3
SQL*Net more data to client 39,260 0 2 0 11.5
latch free 140 5 0 3 0.0
SQL*Net break/reset to clien 340 0 0 1 0.1
log file switch completion 3 0 0 48 0.0
process startup 3 0 0 23 0.0
control file sequential read 548 0 0 0 0.2
log file sequential read 4 0 0 5 0.0
buffer busy waits 4 0 0 4 0.0
direct path write 4 0 0 4 0.0
log file parallel write 3,397 3,395 0 0 1.0
LGWR wait for redo copy 5 0 0 2 0.0
log file single write 4 0 0 1 0.0
undo segment extension 348 348 0 0 0.1
direct path read 30 0 0 0 0.0
SQL*Net message from client 226,481 0 377,967 1669 66.5
wakeup time manager 113 113 3,389 29991 0.0
jobq slave wait 66 63 195 2956 0.0
SQL*Net more data from clien 6 0 0 60 0.0
SQL*Net message to client 226,503 0 0 0 66.5
-------------------------------------------------------------
Background Wait Events for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 1,078 539 6 5 0.3
control file parallel write 1,179 0 2 2 0.3
rdbms ipc reply 29 0 0 4 0.0
process startup 3 0 0 23 0.0
control file sequential read 481 0 0 0 0.1
log file sequential read 4 0 0 5 0.0
log file parallel write 3,397 3,395 0 0 1.0
buffer busy waits 3 0 0 3 0.0
LGWR wait for redo copy 5 0 0 2 0.0
log file single write 4 0 0 1 0.0
latch free 4 0 0 1 0.0
rdbms ipc message 14,195 11,021 16,541 1165 4.2
smon timer 14 9 3,344 ###### 0.0
-------------------------------------------------------------

分析一下SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
681,196 90 7,568.8 19.0 22.70 26.61 1188822893
SELECT /*+ first_rows */ 0 FROM CLDE WHERE ADD_JOB_NUM=:1

591,129 853 693.0 16.5 32.33 34.33 391637996
SELECT COUNT(*) FROM WG WHERE WG = '1' AND TC = :b1 AND DH
= :b2 AND XH = :b3 AND CJ = :b4

202,544 14 14,467.4 5.6 0.76 0.83 3957953059
select yuangh,xingm,bumdm from archives where (bumdm in (select
site from users_dept1 where userid=:Usersite)) --SYF order by bu
mdm,yuangh

165,066 2,662 62.0 4.6 8.15 8.45 336027820
SELECT count(*) from face_rk where job_num = :b1

104,996 484 216.9 2.9 15.35 16.06 1556371957
select nianf,yuef,gongzlx,bumdm,kedh,banzdm,yuangh,xingm,xingb,y
uanglx,gongznx, slr_003,slr_009,slr_010,slr_007,slr_023,s
lr_029,slr_024,slr_028, slr_022,shiyj,gongjj,yilj,yanglj,
slr_030,slr_045,slr_042,kouc,buf,slr_043,slr_044, tesjs,s
hifjs,gongzzh, slr_016,slr_017,slr_019,slr_018,slr_026,

98,031 3 32,677.0 2.7 1.73 2.06 459237807
SELECT "TOOL_MX"."JOB_NUM" , "TOOL_MX"."NAME" ,
"TOOL_MX"."USE_UNIT" , "TOOL_MX"."GYBZ_MAN" ,
"TOOL_MX"."BZ_DATE" , "TOOL_MX"."JH_NUM" ,
"TOOL_MX"."DH_DATE" , "TOOL_MX"."LT_DATE" ,
"TOOL_MX"."SC_NUM" , "TOOL_MX"."RK_DATE" ,

91,861 10 9,186.1 2.6 10.81 12.08 2355361288
UPDATE DSJ.LIST SET NANUM_LINE=:b1 WHERE JOB_NUM1 = :b2

86,808 6 14,468.0 2.4 0.45 0.51 1190886821
select t.* ,t.rowid from archives t --WHERE BUMDM=:Usersite wh
ere (bumdm in (select site from users_dept1 where userid=:Usersi
te)) --SYF order by bumdm,yuangh

86,474 385 224.6 2.4 24.42 26.85 3809895136
begin pack_salary.p_salary_singlecalc(:nianf,:yuef,:gongzlx,:yua
ngh); end;

85,257 6 14,209.5 2.4 0.30 0.29 2516101932
select count(*) from ( select t.* ,t.rowid from archives t whe
re (bumdm in (select site from users_dept1 where userid=:Usersit
e)))

77,345 22 3,515.7 2.2 99.53 106.46 3462562281
INSERT into package_contents(job_num1,case_no,job_num,goods_name
,JOB_NUM11,unit) select :b1,:b2,job_num,str_check(str_check(name
)),JOB_NUM1,'件' from dsj.list where job_num LIKE '%'||:b1||'%'
SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------

73,946 117 632.0 2.1 3.21 3.30 2564771303
UPDATE OLD_LIST SET TOTAL_P=:b1 WHERE JOB_NUM = :b2

71,855 117 614.1 2.0 2.84 3.02 2395855240
SELECT COUNT(*) FROM OLD_LIST_TEMP WHERE PART_NUM = :b1

64,938 2 32,469.0 1.8 1.28 2.67 3582756360
UPDATE OLD_LIST_TEMP SET N_UNIT_W=0,N_TOTAL_W=0,SPARE_P=0

54,269 614 88.4 1.5 5.04 6.52 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8

42,728 3 14,242.7 1.2 0.75 1.01 3169921088
UPDATE FACE SET DES_P=:1,MX_DONE_DATE=:2,LINE_WRITER=:3,LINE_DON
E_DATE=:4,LINE_CHECK=:5,NANUM_LINE=:6 WHERE ROWID=:7

41,690 102 408.7 1.2 0.33 0.35 709258517
select constraint_name, column_name from sys.all_cons_columns wh
ere owner = :owner and table_name = :table_name and constraint_n
ame not like 'SYS_C%' order by constraint_name, position

39,804 2 19,902.0 1.1 1.73 3.23 1492226261
select list.line LX, list.job_num1 glh, list.job_num dh, list.na
me mc, list.piece sl, list.mat cz, list.unit_w dz, list.des_size
fm, list.des_p zs, list.des_work a1, list.bj_code gzj , list.ym
d rq, list.m cs, list.man xgr, list.type clzb, list.type_code zb
, list_bak1.line, list_bak1.name, list_bak1.piece, list_bak1.mat

30,243 4,971 6.1 0.8 0.53 0.52 1404744636
SELECT * from salaryformula where gongsh = :b1 order by gongsh

28,399 6 4,733.2 0.8 1.21 4.31 877854086
SELECT * FROM salaryresult WHERE nianf=:b6 and yuef>=:
b5 AND yuef<=:b4 and gongzlx=:b3 and (BUMDM>=:b2 or :b
2 is null)and (BUMDM<=:b1 or :b1 is null)

21,549 5 4,309.8 0.6 0.29 0.39 803228315
begin pack_salary.p_update_work_Age(:bumdm,:yuangh1,:yuangh2,:yu
anglx1,:yuanglx2); end;

20,049 717 28.0 0.6 0.26 0.31 601639400
UPDATE Archives set gongznx = PACK_SALARY.F_WORK_AGE(:b3
,:b2) where yuangh= :b1

17,170 34 505.0 0.5 0.53 0.52 2722690622
SELECT NVL(MAX(XH),0) + 1 FROM WELD_RKTZ WHERE :b1 = JOB_NUM


SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
16,182 261 62.0 0.5 0.15 0.19 2128744839
SELECT Key_ From Pdm_Temp_Tree Where Job_Num = :b1

15,566 79 197.0 0.4 0.88 1.48 3980150586
SELECT SCJH_04_QJ.JOB_NUM1, SCJH_04_QJ.PIECE, SCJH_04_QJ.NAME,
SCJH_04_QJ.MAT, SCJH_04_QJ.PIECE0, SCJH_04_QJ.UNIT_W, SCJH_04_
QJ.TC_NUM, SCJH_04_QJ.PRE_SHOP, SCJH_04_QJ.NEXT_SHOP, SCJH_04_Q
J.H1, SCJH_04_QJ.H2, SCJH_04_QJ.H3, SCJH_04_QJ.H4, SCJH_04_QJ.H5
, SCJH_04_QJ.H6, SCJH_04_QJ.TECH_REQ, SCJH_04_QJ.CLASS, SCJH_04

15,132 2 7,566.0 0.4 5.32 5.38 1269080801
select count(job_num1),mc,cz,ltrim(zg),sum(zs) zs1,sum(jz) jz1,b
a, sum(mz) mz1,sum(de) de1,lm from ybx.clde group by lb,job_nu
m1,mc,cz,zg,ba,lm,bj having (lm='c' or lm='C') and bj='jj' and (
lb='1'or lb='4') and job_num1=:job1 order by mc,cz,ba,decode(ins

-------------------------------------------------------------
SQL ordered by Reads for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Disk Reads Threshold: 1000

CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
8,984 2 4,492.0 19.4 1.66 3.70 1933892864
SELECT "COOL_ZZP"."UNIT" , "COOL_ZZP"."SR_DATE" ,
"COOL_ZZP"."JOB_NUM" , "COOL_ZZP"."SD_ZGS" ,
"COOL_ZZP"."JG_CL" , "COOL_ZZP"."WT_JG" ,
"COOL_ZZP"."BZ_J" , "COOL_ZZP"."BZ_F" ,
"COOL_ZZP"."ZY_GJ" , "COOL_ZZP"."FP_SS" ,

7,556 90 84.0 16.3 22.70 26.61 1188822893
SELECT /*+ first_rows */ 0 FROM CLDE WHERE ADD_JOB_NUM=:1

6,407 1 6,407.0 13.8 1.31 2.62 2933674388
SELECT SUM(PLAN_TIME1) FROM WELDPR.NEW_PROC_REL,DSJ.LIST WHER
E NEW_PROC_REL.JOB_NUM1 = :b1 AND NEW_PROC_REL.JOB_NUM = LIST.J
OB_NUM AND LIST.LINE NOT LIKE '3000%'

5,319 1 5,319.0 11.5 0.61 3.02 2934925061
SELECT DISTINCT JSDW FROM dsj.tzd WHERE HDR='MX' and JSR IS NULL


3,902 2 1,951.0 8.4 1.73 3.23 1492226261
select list.line LX, list.job_num1 glh, list.job_num dh, list.na
me mc, list.piece sl, list.mat cz, list.unit_w dz, list.des_size
fm, list.des_p zs, list.des_work a1, list.bj_code gzj , list.ym
d rq, list.m cs, list.man xgr, list.type clzb, list.type_code zb
, list_bak1.line, list_bak1.name, list_bak1.piece, list_bak1.mat

1,562 2 781.0 3.4 0.22 0.76 132186308
SELECT COUNT(*) FROM LIST_TEMP_INDEX WHERE JOB_NUM1 = :b1

1,189 6 198.2 2.6 1.21 4.31 877854086
SELECT * FROM salaryresult WHERE nianf=:b6 and yuef>=:
b5 AND yuef<=:b4 and gongzlx=:b3 and (BUMDM>=:b2 or :b
2 is null)and (BUMDM<=:b1 or :b1 is null)

675 773 0.9 1.5 0.27 0.94 2650523022
SELECT SUM(DES_WORK) FROM DSJ.LIST WHERE JOB_NUM1 = :b1

161 614 0.3 0.3 5.04 6.52 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8

152 79 1.9 0.3 0.88 1.48 3980150586
SELECT SCJH_04_QJ.JOB_NUM1, SCJH_04_QJ.PIECE, SCJH_04_QJ.NAME,
SCJH_04_QJ.MAT, SCJH_04_QJ.PIECE0, SCJH_04_QJ.UNIT_W, SCJH_04_
QJ.TC_NUM, SCJH_04_QJ.PRE_SHOP, SCJH_04_QJ.NEXT_SHOP, SCJH_04_Q
J.H1, SCJH_04_QJ.H2, SCJH_04_QJ.H3, SCJH_04_QJ.H4, SCJH_04_QJ.H5
, SCJH_04_QJ.H6, SCJH_04_QJ.TECH_REQ, SCJH_04_QJ.CLASS, SCJH_04

109 111 1.0 0.2 0.04 1.05 4289400484
SELECT ROWID,JOB_NUM,JOB_NUM1,TABLE_N,PROMPTED,WELD_CODE,START_D
ATE,END_DATE,REJECT_MARK,INPUT_DATE,PROCESS,OPMH,BJ,MODIGY_OPM,T
F,ZP_DATE,CH_IP,CH_PROM,RK_DATE,WG_DATE,FL_DATE,TE_SPEC,OPM FROM
NEW_TECH_REL WHERE (JOB_NUM=:1)

SQL ordered by Reads for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Disk Reads Threshold: 1000

CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
96 484 0.2 0.2 15.35 16.06 1556371957
select nianf,yuef,gongzlx,bumdm,kedh,banzdm,yuangh,xingm,xingb,y
uanglx,gongznx, slr_003,slr_009,slr_010,slr_007,slr_023,s
lr_029,slr_024,slr_028, slr_022,shiyj,gongjj,yilj,yanglj,
slr_030,slr_045,slr_042,kouc,buf,slr_043,slr_044, tesjs,s
hifjs,gongzzh, slr_016,slr_017,slr_019,slr_018,slr_026,

77 22 3.5 0.2 99.53 106.46 3462562281
INSERT into package_contents(job_num1,case_no,job_num,goods_name
,JOB_NUM11,unit) select :b1,:b2,job_num,str_check(str_check(name
)),JOB_NUM1,'件' from dsj.list where job_num LIKE '%'||:b1||'%'

75 94 0.8 0.2 0.24 0.91 1011885834
select job_num,job_num1,part_num,part_num1,des_num,des_size,des_
p,name,piece,mat,unit_w,line,type,bj_code,tz_order,M,LINE_M from
list where job_num1=upper(:job_num111) order by REPLACE(job_num
,'M')

74 20 3.7 0.2 0.16 1.05 4191266530
SELECT ALL LIST.NAME,LIST.MAT,list.m, LIST.PIECE,LIST
.UNIT_W, LIST.job_NUM,list.bj_code, substr
(list.job_num,1,9), substr(list.job_num,10,11),
substr(list.job_num,12,13), substr(list.job_num
,14,16), list.line, LIST.PART_NUM1,LIST.DE

67 107 0.6 0.1 0.06 0.89 2302688342
INSERT INTO CLDE(BJ,JOB_NUM1,RQ,JOB_NUM,ADD_JOB_NUM,CZ,ZS,JZ,DE,
MC,DG,ZG,LYL,LM,MK,TX,LB,MZ,BZ,BA,GG,AA,BB) VALUES (:BJ,:JOB_NUM
1,:RQ,:JOB_NUM,:ADD_JOB_NUM,:CZ,:ZS,:JZ,:DE,:MC,:DG,:ZG,:LYL,:LM
,:MK,:TX,:LB,:MZ,:BZ,:BA,:GG,:AA,:BB)

45 362 0.1 0.1 0.11 0.35 3190524044
SELECT ROWID,PLAN_TIME,WORK_SHOP,JOB_NUM,JOB_NUM1,TABLE_N,PROCES
S_COST,PROCESS_GROUP,PLAN_TIME1,PLAN_TIME_MODI_OPH,TABLE_TIME,TF
,BEGIN_DATE,FINISH_TIME,FINISH_PIECE,FINISH_DATE,FINISH_MARK,INP
UT_DATE,PROCESS_CODE FROM NEW_PROC_REL WHERE (JOB_NUM=:1)

41 36 1.1 0.1 0.02 0.24 1367683388
SELECT COUNT(*) FROM DSJ.LIST WHERE JOB_NUM1 = UPPER(:b1) AN
D (LINE LIKE '%01%' OR LINE LIKE '%02%' OR LINE LIKE '%03%'
OR LINE LIKE '%26%' OR LINE LIKE '%29%' OR LINE LIKE '%40
W%' OR LINE LIKE '%40B%' OR LINE LIKE '%230%' OR LINE LIKE
'%240%' OR LINE LIKE '%43W%' OR LINE LIKE '%42W%' OR LINE

36 372 0.1 0.1 0.33 1.10 1984204482
begin pack_salary.p_salaryresult_insert(:nianf,:yuef,:gongzlx,
:bumdm,:kedh,:banzdm,:yuangh,:xingm,:xingb,:yuanglx,:gongznx,:sl

-------------------------------------------------------------
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
4,971 12,856 2.6 0.00 0.00 1404744636
SELECT * from salaryformula where gongsh = :b1 order by gongsh

4,725 4,718 1.0 0.00 0.00 4080986794
SELECT PROC_GROUP,PLAN_PRICE FROM ASSETS.V_ASSETS WHERE PROC_
GROUP = :b1

2,662 2,662 1.0 0.00 0.00 336027820
SELECT count(*) from face_rk where job_num = :b1

2,440 2,440 1.0 0.00 0.00 4121249898
INSERT into bbb(job_num,JOB_NUM_1,JOB_NUM1,JOB_NUM1_1,in_date,us
ee,machine,comm) select :b4 ,:b3 ,:b2 ,:
b1 ,sysdate,userNAME,machine,program from v$session
WHERE SID = (select sid from v$mystat where rownum = 1)

1,572 1,572 1.0 0.00 0.00 2163629200
SELECT COUNT(*) FROM DSJ.FACE WHERE JOB_NUM = :b1

1,570 1,570 1.0 0.00 0.00 2704824524
SELECT MX_WRITER,LINE_WRITER FROM DSJ.FACE WHERE JOB_NUM = :b
1

1,250 1,250 1.0 0.00 0.00 1560113101
select * from HR_C1 WHERE NIANF=:NIANF AND YUEF=:YUEF and BUMDM
=:BUMDM

1,128 0 0.0 0.00 0.00 278968606
SAVEPOINT FM_1

1,072 1,072 1.0 0.00 0.00 3784857994
SELECT Count(*) From Pdm.Protree Where Symbol = :b1

962 274 0.3 0.00 0.00 272842946
select YUANGH,BUF from salaryresult WHERE NIANF=:NIANF AND YUEF
=:YUEF AND BUMDM=:BUMDM AND YUANGH=:YUANGH

946 901 1.0 0.00 0.00 633456687
select yuangh,xingm,xingb,bumdm,kedh,banzdm,yuanglx,gongznx,rugs
r,zhengssy,zhic, jib,fengxxs,jindj,jijf,ganbbz,cjgg from
archives where bumdm=:usersite and yuangh=:yuangh

912 0 0.0 0.00 0.00 3615375148
COMMIT

853 853 1.0 0.04 0.04 391637996
SELECT COUNT(*) FROM WG WHERE WG = '1' AND TC = :b1 AND DH
= :b2 AND XH = :b3 AND CJ = :b4

800 800 1.0 0.00 0.00 3115407350
begin pack_salary.p_salaryresult_fetch(:lock,:nianf,:yuef,:gon
gzlx,:yuangh,:xingm); end;

793 793 1.0 0.00 0.00 4246749948
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
INSERT into SALARY_APPEDX( nianf,
yuef, bumdm,
yuangh, xingm,
SLR_030, SL
R_100, SLR_101,

786 0 0.0 0.00 0.00 3758061761
savepoint DOA__LOCKPOINT

773 773 1.0 0.00 0.00 2650523022
SELECT SUM(DES_WORK) FROM DSJ.LIST WHERE JOB_NUM1 = :b1

725 725 1.0 0.00 0.00 1281731196
SELECT xingm from salaryresult where nianf=:b4
and yuef=:b3 and gongzlx=:b2 and yuangh=
:b1

717 715 1.0 0.00 0.00 601639400
UPDATE Archives set gongznx = PACK_SALARY.F_WORK_AGE(:b3
,:b2) where yuangh= :b1

715 715 1.0 0.00 0.00 3193665429
SELECT round(trunc(sysdate),'dd') from dual

715 715 1.0 0.00 0.00 3263940320
SELECT pyc_005 from PAYROLL_C

702 702 1.0 0.00 0.00 2964743345
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))

691 1 0.0 0.00 0.00 2963598673
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job

654 654 1.0 0.00 0.00 1754174331
SELECT COUNT(*) FROM WELDPR.REL_TABL WHERE BJ = '工艺' AND J
OB_NUM = :b1

644 0 0.0 0.00 0.00 502510949
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0

644 644 1.0 0.00 0.00 1498920852
select max(nvl(option$,0)) from sysauth$ where privilege#=:1 con
nect by grantee#=prior privilege# and privilege#>0 start with (g
rantee#=:2 or grantee#=1) and privilege#>0 group by privilege#

614 614 1.0 0.01 0.01 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------

613 613 1.0 0.00 0.00 2285186583
SELECT MAT,PIECE,UNIT_W,TYPE_CODE,TYPE,LINE,NANUM_LINE FROM LIST
WHERE ROWID=:1 FOR UPDATE OF MAT NOWAIT

601 595 1.0 0.00 0.00 3693280073
SELECT PROC_GROUP FROM ASSETS.V_ASSETS WHERE PROC_GROUP = '13
H' || :b1

597 597 1.0 0.00 0.00 2962403619
SELECT NVL(MAX(PROCESS_CODE),0) + 1 FROM NEW_PROC_REL WHERE
:b1 = JOB_NUM

593 593 1.0 0.00 0.00 1192975401
INSERT INTO NEW_PROC_REL(WORK_SHOP,PLAN_TIME,JOB_NUM,JOB_NUM1,TA
BLE_N,TF,PROCESS_COST,PROCESS_GROUP,PLAN_TIME1,TABLE_TIME,BEGIN_
DATE,FINISH_TIME,FINISH_PIECE,FINISH_DATE,FINISH_MARK,INPUT_DATE

-------------------------------------------------------------
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
4,725 4,725 12.69 4080986794
SELECT PROC_GROUP,PLAN_PRICE FROM ASSETS.V_ASSETS WHERE PROC_
GROUP = :b1

1,128 1,128 3.03 278968606
SAVEPOINT FM_1

800 800 2.15 3115407350
begin pack_salary.p_salaryresult_fetch(:lock,:nianf,:yuef,:gon
gzlx,:yuangh,:xingm); end;

786 786 2.11 3758061761
savepoint DOA__LOCKPOINT

644 644 1.73 502510949
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0

644 644 1.73 1498920852
select max(nvl(option$,0)) from sysauth$ where privilege#=:1 con
nect by grantee#=prior privilege# and privilege#>0 start with (g
rantee#=:2 or grantee#=1) and privilege#>0 group by privilege#

601 601 1.61 3693280073
SELECT PROC_GROUP FROM ASSETS.V_ASSETS WHERE PROC_GROUP = '13
H' || :b1

597 597 1.60 2962403619
SELECT NVL(MAX(PROCESS_CODE),0) + 1 FROM NEW_PROC_REL WHERE
:b1 = JOB_NUM

407 407 1.09 595939768
begin pack_salary.P_SALARY_APPEDX_FETCH(:lock,:nianf,:yuef,:yu
angh,:bumdm,:xingm); end;

388 388 1.04 1882717930
select * from SALARYBASE where rowid = :doa__rowid

385 385 1.03 3809895136
begin pack_salary.p_salary_singlecalc(:nianf,:yuef,:gongzlx,:yua
ngh); end;

372 372 1.00 1984204482
begin pack_salary.p_salaryresult_insert(:nianf,:yuef,:gongzlx,
:bumdm,:kedh,:banzdm,:yuangh,:xingm,:xingb,:yuanglx,:gongznx,:sl
r_003,:slr_009,:slr_010,:slr_007,:slr_023,:slr_029,:slr_024,:slr
_028,:slr_022,:shiyj,:gongjj,:yilj,:yanglj,:slr_030,:slr_045,:sl
r_042,:kouc,:buf,:slr_043,:slr_044,:tesjs,:shifjs,:gongzzh,:slr_

247 247 0.66 2959273380
set role CONNECT,RES

241 241 0.65 2467811771
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
COMMIT work comment ''

204 204 0.55 1010299517
begin pack_salary.P_SALARY_APPEDX_update(:nianf,:yuef,:gongzlx
,:bumdm,:yuangh,:xingm,:SLR_030,:SLR_100,:SLR_101,:SLR_102,:SLR_
103); end;

200 200 0.54 1323314622
UPDATE NEW_TECH_REL SET OPMH=:b1 WHERE JOB_NUM = :b2

196 196 0.53 3013728279
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0

158 158 0.42 1470906206
ROLLBACK

151 151 0.41 1656643811
ROLLBACK TO FM_1

147 147 0.39 319793062
alter session set nls_language= 'SIMPLIFIED CHINESE' nls_territo
ry= 'CHINA' nls_currency= '$' nls_iso_currency= 'CHINA' nls_nume
ric_characters= '.,' nls_date_format= 'DD-MON-YY' nls_date_langu
age= 'SIMPLIFIED CHINESE' nls_sort= 'BINARY' nls_calendar= 'GREG
ORIAN'

146 146 0.39 3397948939
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE=SIGNATURE

142 142 0.38 3357351478
SELECT DEVICE_NAME,TOTAL_W FROM DSJ.FACE WHERE JOB_NUM = :b1

119 119 0.32 1141046533
SELECT * FROM DSJ.FACE where job_num=upper(:job_num111)

118 119 0.32 3809432628
SELECT COUNT(*) FROM DSJ.FACE_RK WHERE JOB_NUM = :b1

115 115 0.31 2576475893
select * from SALARYBASE where rowid = :doa__rowid for update no
wait

114 114 0.31 1259305139
UPDATE NEW_REL_TECH SET NAME_BZ=:b1 WHERE JOB_NUM = :b2

114 114 0.31 3744481045
UPDATE NEW_REL_TECH SET MATERIAL_SOURCE=:b1 WHERE JOB_NUM = :b2

113 113 0.30 238087931
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft

113 113 0.30 2780709284
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1

113 113 0.30 2979542350
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time

-------------------------------------------------------------
分析一下Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 28,801 8.0 8.5
CPU used when call started 28,798 8.0 8.5
CR blocks created 803 0.2 0.2
DBWR buffers scanned 15,823 4.4 4.6
DBWR checkpoint buffers written 8,552 2.4 2.5
DBWR checkpoints 1 0.0 0.0
DBWR free buffers found 15,727 4.4 4.6
DBWR lru scans 9 0.0 0.0
DBWR make free requests 9 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 15,823 4.4 4.6
DBWR transaction table writes 28 0.0 0.0
DBWR undo block writes 2,644 0.7 0.8
SQL*Net roundtrips to/from client 225,575 62.7 66.2
active txn count during cleanout 975 0.3 0.3
background checkpoints completed 0 0.0 0.0
background checkpoints started 1 0.0 0.0
background timeouts 4,234 1.2 1.2
branch node splits 2 0.0 0.0
buffer is not pinned count 2,650,098 736.1 777.8
buffer is pinned count 1,204,540 334.6 353.6
bytes received via SQL*Net from c 11,612,130 3,225.6 3,408.3
bytes sent via SQL*Net to client 120,585,036 33,495.8 35,393.3
calls to get snapshot scn: kcmgss 132,881 36.9 39.0
calls to kcmgas 7,656 2.1 2.3
calls to kcmgcs 587 0.2 0.2
change write time 196 0.1 0.1
cleanout - number of ktugct calls 1,084 0.3 0.3
cluster key scan block gets 34,529 9.6 10.1
cluster key scans 14,590 4.1 4.3
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 3 0.0 0.0
commit cleanouts 15,685 4.4 4.6
commit cleanouts successfully com 15,682 4.4 4.6
commit txn count during cleanout 337 0.1 0.1
consistent changes 1,790 0.5 0.5
consistent gets 3,403,246 945.4 998.9
consistent gets - examination 632,808 175.8 185.7
cursor authentications 598 0.2 0.2
data blocks consistent reads - un 1,790 0.5 0.5
db block changes 261,365 72.6 76.7
db block gets 185,627 51.6 54.5
deferred (CURRENT) block cleanout 9,025 2.5 2.7
dirty buffers inspected 8 0.0 0.0
enqueue conversions 1,017 0.3 0.3
enqueue releases 13,488 3.8 4.0
enqueue requests 13,496 3.8 4.0
enqueue timeouts 0 0.0 0.0
enqueue waits 3 0.0 0.0
execute count 101,030 28.1 29.7
free buffer inspected 8 0.0 0.0
free buffer requested 49,399 13.7 14.5
hot buffers moved to head of LRU 1,955 0.5 0.6
immediate (CR) block cleanout app 562 0.2 0.2
immediate (CURRENT) block cleanou 1,883 0.5 0.6
index fast full scans (full) 11 0.0 0.0
Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
index fetch by key 467,102 129.8 137.1
index scans kdiixs1 86,292 24.0 25.3
leaf node 90-10 splits 3 0.0 0.0
leaf node splits 193 0.1 0.1
logons cumulative 320 0.1 0.1
messages received 3,788 1.1 1.1
messages sent 3,788 1.1 1.1
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 2,677,804 743.8 786.0
opened cursors cumulative 33,252 9.2 9.8
parse count (failures) 13 0.0 0.0
parse count (hard) 13,088 3.6 3.8
parse count (total) 37,236 10.3 10.9
parse time cpu 2,250 0.6 0.7
parse time elapsed 2,642 0.7 0.8
physical reads 46,291 12.9 13.6
physical reads direct 808 0.2 0.2
physical writes 20,067 5.6 5.9
physical writes direct 11,411 3.2 3.4
physical writes non checkpoint 13,846 3.9 4.1
prefetched blocks 38,649 10.7 11.3
process last non-idle time 213,953,086,549 59,431,412.9 ############
recovery blocks read 0 0.0 0.0
recursive calls 155,692 43.3 45.7
recursive cpu usage 15,041 4.2 4.4
redo blocks written 83,857 23.3 24.6
redo buffer allocation retries 3 0.0 0.0
redo entries 132,052 36.7 38.8
redo log space requests 3 0.0 0.0
redo log space wait time 14 0.0 0.0
redo size 40,661,028 11,294.7 11,934.6
redo synch time 695 0.2 0.2
redo synch writes 2,573 0.7 0.8
redo wastage 854,288 237.3 250.7
redo write time 907 0.3 0.3
redo writer latching time 0 0.0 0.0
redo writes 3,397 0.9 1.0
rollback changes - undo records a 415 0.1 0.1
rows fetched via callback 64,996 18.1 19.1
serializable aborts 3 0.0 0.0
session connect time 213,953,086,549 59,431,412.9 ############
session logical reads 3,588,873 996.9 1,053.4
session pga memory 7,049,728 1,958.3 2,069.2
session pga memory max 61,570,320 17,102.9 18,071.7
session uga memory 5,276,896 1,465.8 1,548.8
session uga memory max 128,351,464 35,653.2 37,672.9
shared hash latch upgrades - no w 87,961 24.4 25.8
shared hash latch upgrades - wait 0 0.0 0.0
sorts (disk) 1 0.0 0.0
sorts (memory) 9,466 2.6 2.8
sorts (rows) 3,876,175 1,076.7 1,137.7
summed dirty queue length 104 0.0 0.0
switch current to new buffer 2,482 0.7 0.7
table fetch by rowid 531,127 147.5 155.9
table fetch continued row 271 0.1 0.1
table scan blocks gotten 2,264,360 629.0 664.6
Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scan rows gotten 129,149,869 35,875.0 37,907.2
table scans (long tables) 124 0.0 0.0
table scans (short tables) 9,096 2.5 2.7
transaction rollbacks 20 0.0 0.0
user calls 228,687 63.5 67.1
user commits 2,947 0.8 0.9
user rollbacks 460 0.1 0.1
workarea executions - onepass 1 0.0 0.0
workarea executions - optimal 12,459 3.5 3.7
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MRP_3_DATA
1,866 1 1.7 3.6 3,340 1 0 0.0
MRP_3_IDX
2,207 1 5.1 7.9 887 0 0 0.0
UNDOTBS1
0 0 0.0 2,672 1 4 5.0
MRP_6_DATA
485 0 5.7 3.0 837 0 0 0.0
MRP_2_DATA
563 0 4.2 4.3 110 0 0 0.0
MRP_1_DATA
626 0 6.4 12.3 37 0 0 0.0
MRP_4_DATA
608 0 3.7 15.3 36 0 0 0.0
TEMP
30 0 172.0 26.9 404 0 0 0.0
MRP_2_IDX
234 0 7.4 1.0 138 0 0 0.0
MRP_1_IDX
143 0 8.0 1.0 120 0 0 0.0
MRP_5_IDX
64 0 5.6 1.0 90 0 0 0.0
PERFSTAT
0 0 0.0 151 0 0 0.0
TOOLS
17 0 9.4 1.0 121 0 0 0.0
YHY_PDM
14 0 7.9 1.0 46 0 0 0.0
MRP_5_DATA
2 0 10.0 1.0 33 0 0 0.0
SYSTEM
0 0 0.0 29 0 0 0.0
MRP_4_IDX
5 0 10.0 1.0 7 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MRP_1_DATA /database/mrp_1_data01.dbf
292 0 7.3 13.1 15 0 0
/database/mrp_1_data02.dbf
334 0 5.7 11.6 22 0 0

MRP_1_IDX /database/mrp_1_idx01.dbf
143 0 8.0 1.0 120 0 0

MRP_2_DATA /database/mrp_2_data01.dbf
305 0 3.4 3.6 69 0 0
/database/mrp_2_data02.dbf
258 0 5.0 5.1 41 0 0

MRP_2_IDX /database/mrp_2_idx01.dbf
234 0 7.4 1.0 138 0 0

MRP_3_DATA /database/mrp_3_data01.dbf
815 0 1.7 3.8 1,524 0 0
/database/mrp_3_data02.dbf
1,051 0 1.6 3.4 1,816 1 0

MRP_3_IDX /database/mrp_3_idx01.dbf
2,207 1 5.1 7.9 887 0 0

MRP_4_DATA /database/mrp_4_data01.dbf
294 0 3.2 15.2 34 0 0
/database/mrp_4_data02.dbf
304 0 3.9 15.5 2 0 0
/database/mrp_4_data03.dbf
10 0 10.0 11.9 0 0 0

MRP_4_IDX /database/mrp_4_idx01.dbf
5 0 10.0 1.0 7 0 0

MRP_5_DATA /database/mrp_5_data01.dbf
0 0 31 0 0
/database/mrp_5_data02.dbf
2 0 10.0 1.0 0 0 0
/database/mrp_5_data03.dbf
0 0 2 0 0

MRP_5_IDX /database/mrp_5_idx01.dbf
64 0 5.6 1.0 90 0 0

MRP_6_DATA /database/mrp_6_data01.dbf
485 0 5.7 3.0 837 0 0

PERFSTAT /database1/perfstat.dbf
0 0 151 0 0

SYSTEM /database/system01.dbf
0 0 29 0 0
File IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------

TEMP /database/temp01.dbf
30 0 172.0 26.9 404 0 0

TOOLS /database/tools01.dbf
17 0 9.4 1.0 121 0 0

UNDOTBS1 /database/undotbs01.dbf
0 0 2,672 1 4 5.0

YHY_PDM /database/YHY_PDM01.dbf
14 0 7.9 1.0 46 0 0

-------------------------------------------------------------
Buffer Pool Statistics for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default