</p> 从结果可以看出:
1 }) O G D' O' t% w! S 24并发,oracle使用了24个core,整体的CPU占用率在50%,load在24,非常准确,此时db time基本和db cpu一致,因为你干的所有事情,都是在CPU上7 j' x4 t: a: R6 j( u4 {
48并发,oracle使用了48个core,CPU使用率达到100%,oracle在多cpu环境下对资源的利用确实很高,此时db time,db cpu,cpu time一致了,CPU在满负荷运转
- Y% X* a$ m7 q- L 60并发,这个时候会发现,db cpu没变,因为CPU已经耗尽,没得涨了,db time现在已经远大于cpu time和db cpu,这说明我们有一部分的程序根本抢不到CPU,进入了1 s# ~4 f" s7 I
等待,load也超过了core数量,达到574 f' d" v% E# k8 g2 v6 q
通过db cpu/db time,我们可以看出这个数据库是否是CPU使用很重的应用,比如大量的运算,latch争用等,- j: [* R4 Z h
如果是一个IO很重的应用,会发现db cpu站的比例会很小
6 p( E* q! |$ h: c IO负载统计
2 B% H* C9 T9 C* J g3 R( x IO表现在两个方面,IOPS和吞吐量,我们OLTP系统,一般比较关心IOPS,及每个IO的响应时间,* W+ \8 r' }: P+ f7 \& z
以前针对IOPS统计,我们一直是按照statspack的physical reads+physical writes来统计,这个是很不准确的
+ h" N1 n9 c7 t/ U 按照文档的解释:
$ Z+ W g5 h6 g! ^, u6 b$ K physical reads:Total number of data blocks read from disk
$ N0 C# ?- M6 [5 b- l2 [* j. u1 F 这个是按照block读取的数量来统计的,oracle的IO种类有很多,如果是scatter read或者parallel read,一个IO会读取多块的,
) G6 ]# `+ p$ O/ _/ i: p; c 这样计算IOPS会偏大,重新调整后,发现统计出来的曲线和从存储段观察的比较吻合" V- `9 y# p; z& n9 I0 }* K
9I:- M7 X6 M+ q) r: d& m) R
–IOPS&MBPS
" _$ X: k+ s. i9 |8 Q+ m select sum(iops) as iops,sum(mbps) as mbps9 L0 j. Y; r% G& \. ^$ u% w6 k
from (
2 s& l) p) j' k6 v8 H- O% D# h select sum(phyrds + phywrts) as IOPS,3 ]- o4 A5 P' G
sum(phyblkrd + phyblkwrt) as MBPS+ B& i4 F6 b" A( v: ^, v7 h' V V
from (select a.phyrds,a.phywrts,a.phyblkrd * b.block_size / 1024 / 1024 as phyblkrd,a.phyblkwrt * b.BLOCK_SIZE / 1024 / 1024 as phyblkwrt: h- H0 a% e" v
from v$filestat a,v$datafile b# k; m3 M0 s% G6 y E1 } z
where a.file# = b.file#)
* U" }2 c% P3 J( q6 x3 L1 P union all( d0 M' o! i. n a
select sum(decode(name,’redo writes’,value,’0′)) as IOPS,
5 ?( @2 C4 E S) R8 [, v! ? sum(decode(name,’redo size’,value,’0′)) / 1024 / 1024 as MBPS" }9 c% ~4 V2 v, d+ ]9 U
from v$sysstat where name in( ‘redo writes’,'redo size’));/ a" N7 s/ b" v G
10G/11G
- _7 {% \5 G5 ^% B5 j& a# C* n –IOPS&MBPS. t2 I+ t7 n5 M4 K# c; V5 U
select sum(decode(name,’physical read IO requests’,value,’physical write IO requests’,value,0)) as iops,* \: g- L$ s2 A7 a
sum(decode(name,’physical read bytes’,value,’physical write bytes’,value,0)) / 1024 / 1024 as mbps
- U2 r: m }, S+ z0 G! f from v$sysstat; _4 _ p" a: H% p6 l) @5 N
where name in (’physical read IO requests’,'physical write IO requests’,
# s# h( s7 |- Z& K, B3 ` ‘physical read bytes’,'physical read total bytes’,
: T: Q/ u2 l4 g; G8 R# j- x" U ‘physical write bytes’,'physical write total bytes’,'physical read total IO requests’,'physical write total IO requests’! e, k$ i% e8 G
);
5 `: y1 i8 k3 b$ h3 v1 U+ S 最近将这些统计指标做进了监控系统,看看效果怎么样 |