AWR之对磁盘IO的诊断

     数据库性能的问题，有可能是磁盘IO的性能有瓶颈，需要结合几个参数看。

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
gg	AIX-Based Systems (64-bit)	8	4		61.75

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	18168	26-Apr-13 08:00:50	89	4.0
End Snap:	18169	26-Apr-13 08:20:58	90	4.4
Elapsed:		20.14 (mins)
DB Time:		672.73 (mins)

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
log file sync	173,762	17,765	102	44.01	Commit
db file scattered read	176,020	5,599	32	13.87	User I/O
db file sequential read	158,339	3,545	22	8.78	User I/O
DB CPU		2,386		5.91
db file parallel read	4,565	172	38	0.43	User I/O

Statistic	Total	per Second	per Trans

physical read total IO requests

354,391

293.21

2.05

physical write total IO requests

125,140

103.54

0.73

Tablespace	Filename	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
SYSAUX	/space/rdb/db_sys/tbs_rdb_sysaux_f01.dbf	3,473	3	32.65	1.36	941	1	0	0.00
SYSTEM	/space/rdb/db_sys/tbs_rdb_system_f01.dbf	4,454	4	28.14	3.69	63	0	0	0.00
SYSTEM	/space/rdb/db_sys/tbs_rdb_system_f02.dbf	266	0	30.15	8.52	2	0	0	0.00

      物理IO 读写的响应时间都不好, 每秒的 IOPS=293.21  +103 不到400 的IOPS下 就达到30ms的响应速度，可见 IO 存储非常差。就per transaction看 它的Io特点有所变化，  IO的度量 至少分成IOPS、吞吐量和响应时间三个维度来分析，平均单次IO 若大于20ms  即可认为 IO较差。

 

   再看一份报告，IO已经差到了极点，还有一个原因，rman备份和数据库放在一个磁盘上。      

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
log file sync	192,156	18,743	98	46.9	Commit
RMAN backup & recovery I/O	7,077	7,915	1,118	19.8	System I/O
db file sequential read	40,557	7,424	183	18.6	User I/O
read by other session	15,306	7,025	459	17.6	User I/O
SQL*Net more data to client	37,819	2,595	69	6.5	Network

Tablespace	Filename	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
EXAMPLE	/oradata/example01.dbf	257	0	1752.61	63.75	1	0	0	0.00
GG	/oradata/hyscdb/fwms08.dbf	24,480	3	157.29	1.09	1,249	0	13,931	485.40
GG	/oradata/hyscdb/fwms_01.dbf	1,633	0	2072.72	1.32	45	0	127	543.31
GG	/oradata/hyscdb/fwms_02.dbf	2,274	0	755.55	1.23	12	0	21	194.76
GG	/oradata/hyscdb/fwms_03.dbf	1,691	0	197.97	1.18	9	0	7	637.14
GG	/oradata/hyscdb/fwms_04.dbf	1,527	0	2328.81	1.18	11	0	2	830.00
GG	/oradata/hyscdb/fwms_05.dbf	2,228	0	785.39	1.29	6	0	0	0.00
GG	/oradata/hyscdb/fwms_06.dbf	6,900	1	1803.51	47.59	18	0	0	0.00
GG	/oradata/hyscdb/fwms_09.dbf	32,135	4	667.47	41.15	76	0	1,253	315.80
GG	/oradata/hyscdb/fwms_10.dbf	25,357	4	1126.37	63.71	27	0	0	0.00