statspack安装使用和report 分析(7)

cked

442 1,142 2.6 0.00 0.00 1749333492

select position#,sequence#,level#,argument,type#,charsetid,chars

etform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale,

0),nvl(radix, 0), type_owner,type_name,type_subname,type_linknam

e,pls_type from argument$ where obj#=:1 and procedure#=:2 order

by sequence# desc  

这部分告诉我们在这段时间中执行最多的SQL语句。为了隔离某些频繁执行的查询，以观察是否有某些更改逻辑的方法以避免必须如此频繁的执行这些查询，这可能是很有用的。或许一个查询正在一个循环的内部执行，而且它可能在循环的外部执行一次，可以设计简单的算法更改以减少必须执行这个查询的次数。即使它运行的飞快，任何被执行几百万次的操作都将开始耗尽大量的时间。

9.实例活动

Instance Activity Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5

Statistic Total per Second per Trans

CPU used by this session 4,870 4.5 194.8

CPU used when call started 4,870 4.5 194.8

CR blocks created 45 0.0 1.8

DBWR buffers scanned 24,589 22.8 983.6

DBWR checkpoint buffers written 14,013 13.0 560.5

DBWR checkpoints 5 0.0 0.2

……

dirty buffers inspected 38,834 36.0 1,553.4 --脏缓冲的个数

free buffer inspected 40,463 37.5 1,618.5 --如果数量很大,说明缓冲区过小

……  

10.I/O

下面两个报表是面向I/O的。通常，在这里期望在各设备上的读取和写入操作是均匀分布的。要找出什么文件可能非常“热”。一旦DBA了解了如何读取和写入这些数据，他们也许能够通过磁盘间更均匀的分配I/O而得到某些性能提升。

Tablespace IO Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5

->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)

BLISS_DATA

17,649 16 12.3 1.2 44,134 41 0 0.0

UNDOTBS1

4,484 4 9.6 1.0 29,228 27 0 0.0

SYSTEM

340 0 31.0 1.1 36 0 0 0.0  

File IO Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5

->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)

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

BLISS_DATA D:ORACLEORADATABLISSDBBLISS01.DBF

5,779 5 12.0 1.2 14,454 13 0

D:ORACLEORADATABLISSDBBLISS02.DBF

5,889 5 12.1 1.2 14,772 14 0

D:ORACLEORADATABLISSDBBLISS03.DBF

5,981 6 12.6 1.2 14,908 14 0  

11.缓冲池

Buffer Pool Statistics for DB: BLISSDB Instance: blissdb Snaps: 4 -5

-> Standard block size Pools D: default, K: keep, R: recycle

-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Free Write Buffer

Number of Cache Buffer Physical Physical Buffer Complete Busy