首页 » ORACLE [C]系列, ORACLE 9i-23c » 12C AWR show Tablespace And Datafiles Av Read (ms) high(高) BUG

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

Related Posts:

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：