监控数据库性能的SQL

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

监控数据库性能的SQL
7u6Y1A7H*}d W8E-yDf0查看tablespace的空間使用情況51Testing软件测试网D1Bg{TCG
selecttablespace_name,sum(bytes)/1024/1024fromdba_free_spacegroupbytablespace_name51Testing软件测试网H.R)H&t,Fd!M!S
1. 监控事例的等待
*b$D&v9MES~0selectevent,sum(decode(wait_Time,0,0,1)) "Prev",51Testing软件测试网uY/O,yr
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
L8Z7?O.?%E(Y0fromv$session_Wait
R[Nbt#ZV UM.t ~0groupbyeventorderby4;51Testing软件测试网7K8I"t!?8Q3uP)e P
2. 回滚段的争用情况51Testing软件测试网!X$~I8bY@#ky3H/aF
selectname, waits, gets, waits/gets "Ratio"
*V"|+`my%A'SB7EB2V0fromv$rollstat a, v$rollname b
(WN~hO6d,IBa0wherea.usn=b.usn;
k\?-PO(]f03. 监控表空间的 I/O 比例51Testing软件测试网*xk!p]!r`A
selectdf.tablespace_name name,df.file_name"file",f.phyrds pyr,
7w;bdyC3oG$\jW8a0f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
3xfk)XaM;n0fromv$filestat f, dba_data_files df51Testing软件测试网KVRF~^O7iB4r'B
wheref.file#=df.file_id
"G;R8\"W T-o0orderbydf.tablespace_name;
G3Af?iR04. 监控文件系统的 I/O 比例
cYw
Q+]|V7N
WR0selectsubstr(a.file#,1,2) "#", substr(a.name,1,30) "Name",51Testing软件测试网6J5L3\/U,Z@
a.status, a.bytes, b.phyrds, b.phywrts
1T_lE0v.}R|0fromv$datafile a, v$filestat b51Testing软件测试网 f$yV:o d9tvBK%ZB`9L
wherea.file#=b.file#;51Testing软件测试网xT+k'~*x6N ~F1i6I
5.在某个用户下找所有的索引
(DC7d:Y/O#B9H9Kl0selectuser_indexes.table_name, user_indexes.index_name,uniqueness, column_name
Gr5t@
?e|2z0fromuser_ind_columns, user_indexes51Testing软件测试网7Tn |)?Y+{,G
whereuser_ind_columns.index_name=user_indexes.index_name51Testing软件测试网(Jc{Y,v#ZIQKN9Y
anduser_ind_columns.table_name=user_indexes.table_name
%h j&Rq"Kh3_I-^0orderbyuser_indexes.table_type, user_indexes.table_name,
*bE \*?v&E3\*n~0user_indexes.index_name, column_position;
R[1u a(ws06. 监控 SGA 的命中率51Testing软件测试网&w wD;V"w
selecta.value+b.value "logical_reads", c.value "phys_reads",51Testing软件测试网P#s0[E9e w8kq j
round(100*((a.value+b.value)-c.value)/(a.value+b.value)) "BUFFER HIT RATIO"
V$yGW;Zs;xA8ud M0fromv$sysstat a, v$sysstat b, v$sysstat c51Testing软件测试网-x2H&|E`'KJK
wherea.statistic#=38andb.statistic#=39
6D+O&}'P"yb+?-q:C0andc.statistic#=40;
P
P @7U;g-W3O07. 监控 SGA 中字典缓冲区的命中率51Testing软件测试网m0P3v i*Txy6tS
selectparameter, gets,Getmisses , getmisses/(gets+getmisses)*100"miss ratio",
OMp`J[i!H0(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hit ratio"
J k l#yz)^w0fromv$rowcache51Testing软件测试网dxp%}$Npc!A
wheregets+getmisses<>051Testing软件测试网o U-bWS
groupbyparameter, gets, getmisses;
-B?/Zl pqI;Xx%A08. 监控 SGA 中共享缓存区的命中率，应该小于1%
bL3Z"p&a0selectsum(pins) "Total Pins",sum(reloads) "Total Reloads",51Testing软件测试网9}-j4O2u5P b7x
sum(reloads)/sum(pins)*100libcache51Testing软件测试网*[;wP.evW
fromv$librarycache;51Testing软件测试网YU,e*g]
selectsum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reloadpercent"
wG"wyq}'[G0fromv$librarycache;
2SZ Z4PJ09. 显示所有数据库对象的类别和大小
8fumQ!?3^Ze0selectcount(name) num_instances ,type ,sum(source_size) source_size ,51Testing软件测试网F8s5pueX
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,51Testing软件测试网#S}6A;|C
sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size) size_required51Testing软件测试网0s? z@%\
fromdba_object_size51Testing软件测试网5FR7^ r*O$?J:K"@
groupbytypeorderby2;
c;o!tLm"n
Pg010. 监控 SGA 中重做日志缓存区的命中率，应该小于1%51Testing软件测试网&rr(E9[sE
SELECTname, gets, misses, immediate_gets, immediate_misses,
z0Q~9E'[mcG8w-He+G0Decode(gets,0,0,misses/gets*100) ratio1,
yv7Bd}0G/J0Decode(immediate_gets+immediate_misses,0,0,
Q*}sR.Po*vD ?0immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
w-UXe0SFIv0FROMv$latchWHEREnameIN('redo allocation','redo copy');
)p{9f9kL_011. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size51Testing软件测试网 s.gVzS
SELECTname, valueFROMv$sysstatWHEREnameIN('sorts (memory)','sorts (disk)');
'LY
DFd)v D012. 监控当前数据库谁在运行什么SQL语句51Testing软件测试网 \4[I~(~ A#F ? n4Y2V
SELECTosuser, username, sql_textfromv$session a, v$sqltext b
,gmf4d9o5_"c/ZZ0wherea.sql_address=b.addressorderbyaddress, piece;
?y7DK|5aAZ051Testing软件测试网2@D Oj(t[

其实对数据库来说数据库主要监控下等待事件event,和对应的sql,根据情况再做分析:
/N }4C-i$x0SELECTS.SID, S.USERNAME, SW.EVENT, Q.SQL_TEXT51Testing软件测试网?C+z/b%PT$z.A
 FROMV$SESSION S, V$SESSION_WAIT SW, V$SQL Q51Testing软件测试网.y:I/q oXPn
WHERES.USERNAME<>'SYS'
7H? B/||"R7I0  ANDQ.HASH_VALUE=S.SQL_HASH_VALUE51Testing软件测试网)Q$j)D5K;]1y
  ANDSW.SID=S.SID51Testing软件测试网u#k8Z][1UB)|
  ANDS.STATUS='ACTIVE'
HC'lGK0  ANDSW.EVENTNOTLIKE'%SQL*Net%'
(E)k8Z Xq`@?0`-^0ORDERBYLAST_CALL_ETDESC;51Testing软件测试网OLN2qbC

%A.I"OuJ6R051Testing软件测试网/O$M5?b%`:WR