日志文件和数据文件读写竞争的处理

通过AWR捕捉到日志和数据文件读写竞争的问题，给出处理建议。

感谢Jessie提供实际生产问题，感谢盖哥的解决方法。

关键字：log file sync db file sequential read 写redo log的触发条件 IO竞争

[@more@]

Jessie公司的系统昨天又出问题了。

问题

业务操作都很慢。

分析

取昨天工作时间（12小时）的AWR来分析一下。慢就是在等，还是从等待事件入手。

top event

依次为：log file sync、db file sequential read、log file parallel write、db file scattered read。

排除IO故障则直接指向了redo log和数据文件的读写竞争。

查询redo block size

方法1：

SQL> select max(lebsz) from x$kccle;

MAX(LEBSZ)

----------

1024

方法2：

redo block size =(redo size+redo wastage)/redo blocks written+16bytes

select name, value

from v$sysstat

where name in ('redo size', 'redo wastage', 'redo blocks written');

NAME VALUE

---------------------------------------------------

redo size 27784510352

redo wastage 607356656

redo blocks written 28174620

SQL> select ceil(16 + ( 27784510352+ 607356656)/28174620) redo_block_size from dual;

REDO_BLOCK_SIZE

---------------

1024

计算写redo log的平均大小

avg.redo write size=(Redo Block Written/Redo Writes)Xredo block size

select name, value

from v$sysstat

where name in ('redo writes' , 'redo blocks written');

NAME VALUE

---------------------------------------------------

redo writes 1314549

redo blocks written 28174620

select 1024*(28174620/1314549) avg_redo_write_size from dual;

AVG_REDO_WRITE_SIZE

-------------------

21947.3073122417

平均每次写log只写了20K数据，但是写了1314549次，这样的写redo log太频繁。

redo log写的触发条件

复习一下redo log写的触发条件：

1.3秒超时。

LGWR休眠3秒后醒来检查发现有redo要写出，将执行写操作。

2.阀值达到。

redo log buffer 1/3满或redo log buffer有1M脏数据时，LGWR将被通知写redo log。阀值受到隐含参数_log_io_size的影响，该隐含参数缺省是1/3redo log buffer大小，上限值为1M。隐含参数可以查询底层表X$KSPPSV（[K]ernel [S]ervice [P]arameter Component [S]ystem [V]alues）获得，SQL如下：

select x.ksppinm name,

y.ksppstvl value,

y.ksppstdf isdefault,

decode(bitand(y.ksppstvf, 7),

1,

'MODIFIED',

4,

'SYSTEM_MOD',

'FALSE') ismod,

decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj

from sys.x$ksppi x, sys.x$ksppcv y

where x.inst_id = userenv('Instance')

and y.inst_id = userenv('Instance')

and x.indx = y.indx

and x.ksppinm like '%_&par%'

order by translate(x.ksppinm, ' _', ' ');

3.用户提交

当一个事务提交时，在Redo Stream中将记录一个提交标志。在这些Redo被写到磁盘上之前，这个事务是不可恢复的。所以，在事务返回成功标志给用户前，必须等待LGWR写完成。

4.在DBWn写之前

如果DBWR将要写出的数据的高RBA超过LGWR的on-Disk RBA，则DBWR将通知LGWR去执行写出。

写redo log太频繁的原因

1.3秒超时。显然不可能，3秒写一次，10几个小时不可能写100W+次。

2.阀值到达。隐含参数_log_io_size使用的缺省值，log_buffer为1.5M

SQL> show parameter log_buffer

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_buffer integer 15316992

这样是500K写一次redo log。此原因排除。

3.用户提交。AWR中显示user commits平均每秒29.82次。我想这是redo log写频繁的原因。

处理意见

用户提交频繁最大可能的原因有二：

1.程序编写问题，提交太频繁。建议检查主要业务程序，优化提交批量。

2.业务量大。建议分散IO，将redo log文件同数据文件分开存储在不同的物理硬盘上；使用磁盘阵列分散redo log的IO。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21129591/viewspace-1054616/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21129591/viewspace-1054616/