12C AWR show Tablespace And Datafiles Av Read (ms) high(高) BUG

12C的AWR的数据比11G又丰富了许多,  增加了ASH, ADDM, OGG的信息在AWR中. 前不久有个朋友说他的AWR I/O"AV rd(ms)" 显示的值非常高,Oracle认为大于20毫秒的平均磁盘读取时间是不可接受的,如果是EMC ,HDS 这类存储应该在5毫秒左右. 但这套库使用的还是SSD, 开始确认以为是I/O出了问题,但是数据库似乎没有任何I/O性能问题. 下面有AWR部分数据.
 

High Av Rd(ms)
If you are seeing high Average Disk Read times in your Oracle AWR Report read this tip.
High Average Disk Read times can be seen under the Tablespace IO Stats section of the AWR Report. When you see Av Rd(ms) of greater than 7 ms you may have an issue that requires further investigating. This could mean your system is experiencing extremely slow Avg Read times from the disk subsystem.
Recommendation:
In addition to SQL tuning of high-impact statements, the disk subsystem may need tweaked. This following disk setup would help with scattered and sequential disks reads. Additionally it would allow log switches to occur more rapidly.
 

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Tablespace	Reads	Av Rds/s	Av Rd(ms)	Av Blks/Rd	1-bk Rds/s	Av 1-bk Rd(ms)	% Opt Reads	Writes	Writes avg/s	Buffer Waits	Av Buf Wt(ms)
SDATA_04	1,916,489	357	14,230.38	6.07	338	15,010.71	0.00	32,929	6	7,293	2.14
DATA	1,250,028	233	21,618.71	1.09	230	21,877.27	0.00	11,638	2	1,829	0.28
SDATA_01	692,202	129	26,150.73	2.85	88	38,141.13	0.00	110,639	21	190	2.95
SDATA_02	674,072	125	18,160.23	2.16	108	21,095.97	0.00	80,518	15	281,222	1.25
IDX_01	464,109	86	0.62	1.00	86	0.62	0.00	35,747	7	8,656	1.26
SDATA_05	422,292	79	7,068.61	10.03	73	7,623.35	0.00	2,926	1	2	0.00
DX_02	235,571	44	1.96	1.00	44	1.96	0.00	133,751	25	7,195	1.04

 

File IO Stats

• ordered by Tablespace, File

Convert to CSV

Tablespace	Filename	Reads	Av Rds/s	Av Rd(ms)	Av Blks/Rd
SDATA_01	+data/DATAFILE/css_sdata_01_13.dbf	18,654	3	52,153.62	2.85
SDATA_01	+data/DATAFILE/css_sdata_01_14.dbf	14,281	3	52,146.38	2.78
SDATA_01	+data/DATAFILE/css_sdata_01_15.dbf	4,876	1	35,059.11	3.08
SDATA_01	+data/DATAFILE/css_sdata_01_16.dbf	3,284	1	28,023.41	3.60
SDATA_01	+data/DATAFILE/css_sdata_01_17.dbf	527	0	24,749.01	3.80
SDATA_01	+data/DATAFILE/css_sdata_01_18.dbf	5,447	1	303.84	1.36
SDATA_01	+data/DATAFILE/css_sdata_01_19.dbf	1,519	0	49,383.09	3.58
SDATA_01	+data/DATAFILE/css_sdata_01_20.dbf	5,086	1	39,083.43	2.76
SDATA_01	+data/DATAFILE/css_sdata_01_21.dbf	7,602	1	45,819.42	2.85

Note:
注意上面的AWR av read(ms)列数据非常吓人, 如果是真实的数据库可能早hang死了, 后来从MOS 上确认了该问题是12C AWR上的一个BUG,显示计算的值是错误的, 可以忽略该指标,参考I/O其它相关的指标. 同样AWR中新增的I/O profile 是很不错的数据.
解决可以安装当前版本中对应的补丁,在201607月后出的12.1 的PSU中也修复了该BUG.
references:
AWR Tablespace And Datafiles Av Read (ms) Is Incorrect After Upgrade to 12c (Doc ID 2182080.1)
http://oraclegiants.com/High%20Av%20Rd%20ms.htm