监控数据库性能的SQL

在压力测试过程中往往不知道如何监控数据库的问题下面是在性能测试过程中对数据库性能问题诊断的语法提供参考：

监控数据库性能的SQL51Testing软件测试网lM\UTB
查看tablespace的空間使用情況51Testing软件测试网w"s)j9jb+b!W
selecttablespace_name,sum(bytes)/1024/1024fromdba_free_spacegroupbytablespace_name51Testing软件测试网;{ pRd}3[5z3S
1. 监控事例的等待
TW/A*TW8_'q+m0selectevent,sum(decode(wait_Time,0,0,1)) "Prev",51Testing软件测试网ee0j0n*g#Y
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"51Testing软件测试网!fx8CM+Es
{*x
fromv$session_Wait
b6U'Bh@;WX#g0groupbyeventorderby4;51Testing软件测试网8t!R/u!Tb6i|$]
2. 回滚段的争用情况51Testing软件测试网(loHW"m5w`n|;H
selectname, waits, gets, waits/gets "Ratio"51Testing软件测试网(I;f BO/F
fromv$rollstat a, v$rollname b51Testing软件测试网9V;Xz H.OEc
wherea.usn=b.usn;51Testing软件测试网a^:M vK Rysp
3. 监控表空间的 I/O 比例
S][a2K3v0selectdf.tablespace_name name,df.file_name"file",f.phyrds pyr,
8z!?&Z!T4^p'Ak0vC0f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw51Testing软件测试网[{DD3~#L
fromv$filestat f, dba_data_files df51Testing软件测试网m3xGo8H9Spq
wheref.file#=df.file_id
pLr`$c/C0orderbydf.tablespace_name;
|T~1O'iuh04. 监控文件系统的 I/O 比例
}r1kRGxY&F9ps0selectsubstr(a.file#,1,2) "#", substr(a.name,1,30) "Name",51Testing软件测试网E#A pNY9Ne
b/I.d
a.status, a.bytes, b.phyrds, b.phywrts
~i^}(C7F:hL*H0fromv$datafile a, v$filestat b
9Cc:h L M/r0wherea.file#=b.file#;
-K#i.EH%M0O"},I05.在某个用户下找所有的索引
TdZ tL(Q4d0selectuser_indexes.table_name, user_indexes.index_name,uniqueness, column_name
H/yD'T(Af0y0fromuser_ind_columns, user_indexes51Testing软件测试网VlK1dl(X
whereuser_ind_columns.index_name=user_indexes.index_name
q6M(d jM \Z0anduser_ind_columns.table_name=user_indexes.table_name
)g
k1a+] q^ aE0orderbyuser_indexes.table_type, user_indexes.table_name,51Testing软件测试网%y[zH;uC
D
user_indexes.index_name, column_position;
{Ysn$o1mD.w0R06. 监控 SGA 的命中率51Testing软件测试网1D(m/N&_SDe?b
selecta.value+b.value "logical_reads", c.value "phys_reads",51Testing软件测试网,cXC;B1mG6d%} pV
round(100*((a.value+b.value)-c.value)/(a.value+b.value)) "BUFFER HIT RATIO"
(Hf{9[J^0fromv$sysstat a, v$sysstat b, v$sysstat c51Testing软件测试网4s%Of#`|7KDk
wherea.statistic#=38andb.statistic#=3951Testing软件测试网8E F"D(~!sJ0tv
andc.statistic#=40;51Testing软件测试网_/qy_o#\;^X
7. 监控 SGA 中字典缓冲区的命中率51Testing软件测试网R^wI`0q5k
selectparameter, gets,Getmisses , getmisses/(gets+getmisses)*100"miss ratio",51Testing软件测试网rJ&oo$HX0?b6dt1b
(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hit ratio"
3iaavKg&a,^]e0fromv$rowcache
uaQ D8|#Z/Q,J}e0wheregets+getmisses<>0
"B2U Pu t|0groupbyparameter, gets, getmisses;
-G#|UO&~-Weo08. 监控 SGA 中共享缓存区的命中率，应该小于1%51Testing软件测试网1Z]"Jjl8h{
selectsum(pins) "Total Pins",sum(reloads) "Total Reloads",51Testing软件测试网d
y"d1]N#o0T
sum(reloads)/sum(pins)*100libcache
+CD~ PLej k0fromv$librarycache;
4tf4b6P$T"B:J k0selectsum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reloadpercent"
q"x#i!^n;ixjC8G0fromv$librarycache;51Testing软件测试网0u2BD)U0C\b
9. 显示所有数据库对象的类别和大小51Testing软件测试网#J:uQb.z6e`H&}~
selectcount(name) num_instances ,type ,sum(source_size) source_size ,51Testing软件测试网0_6o il L s Ft
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,51Testing软件测试网4PyH7mnn
sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size) size_required
Yk#@^wl"o&s:V0fromdba_object_size
/X[I J7o]\ \"g0groupbytypeorderby2;51Testing软件测试网)HR^?:p/nN5C-y;a
10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%
7]-{lu|%N\+?;_v0SELECTname, gets, misses, immediate_gets, immediate_misses,
@+xx,z F*M0Decode(gets,0,0,misses/gets*100) ratio1,
hh7x3Zj2dt(j@&c0Decode(immediate_gets+immediate_misses,0,0,51Testing软件测试网6Jjy9_
qa0m
immediate_misses/(immediate_gets+immediate_misses)*100) ratio251Testing软件测试网p3~[!d[}
FROMv$latchWHEREnameIN('redo allocation','redo copy');51Testing软件测试网.v&x8e ZMe0q+FY0pA
11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
9@v1j8?3AW0SELECTname, valueFROMv$sysstatWHEREnameIN('sorts (memory)','sorts (disk)');51Testing软件测试网wmO]pM.lC
12. 监控当前数据库谁在运行什么SQL语句51Testing软件测试网[v};MD#Q
SELECTosuser, username, sql_textfromv$session a, v$sqltext b
skK@8RL&s0wherea.sql_address=b.addressorderbyaddress, piece;51Testing软件测试网 i?5n6Y-E;G
51Testing软件测试网/_2EO_!M?#x4^q

其实对数据库来说数据库主要监控下等待事件event,和对应的sql,根据情况再做分析:51Testing软件测试网2u(w/q!{jME&}
SELECTS.SID, S.USERNAME, SW.EVENT, Q.SQL_TEXT51Testing软件测试网U;{nX*~I!y5@(k-?0rjM
 FROMV$SESSION S, V$SESSION_WAIT SW, V$SQL Q51Testing软件测试网^dA6A I
WHERES.USERNAME<>'SYS'51Testing软件测试网eG*\'j2o-B
  ANDQ.HASH_VALUE=S.SQL_HASH_VALUE51Testing软件测试网-\#c%sYHG6]7c
  ANDSW.SID=S.SID51Testing软件测试网){i|`8W]Y]d;d6du
  ANDS.STATUS='ACTIVE'
f;TIT Y l~.v4a2d0  ANDSW.EVENTNOTLIKE'%SQL*Net%'
AE)Lyi"p h8F0ORDERBYLAST_CALL_ETDESC;
0z7S}{2x+@d051Testing软件测试网V;e.[#p!S5z-K J