oracle增加db_writer_processes

orache出现db file async I/O submit，insert太多导致大量lock，关闭异步I/O 或者启动filesystemio_options两种方法来消除这个等待时间，无效。

alter system set disk_asynch_io =false scope=spfile;
or
alter system set filesystemio_options =asynch scope=spfile;

*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP)(disp=0)'
*.filesystemio_options='ASYNCH'

filesystemio_options有4个选项
ASYNCH enable asynchronous I/O on file system files, which has no timing requirement for transmission.
DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.
SETALL: enable both asynchronous and direct I/O on file system files.
NONE: disable both asynchronous and direct I/O on file system files.

重启oracle
su - oracle
cat /proc/slabinfo | grep kio
可以通过以上命令看是否启用了异步I/O 如果kiocb中有值，就证明启用了异步I/O

###########################
监测看到DBWR为1不断被锁。尝试修改

--0. 使用下面的需求修改dbwr进程，最大值小于等于服务器CPU的数量；使用的服务器为8核，那么修改如下

alter system set db_writer_processes=8 scope=spfile;

show parameter db_writer

alter system set dbwr_io_slaves=8 scope=spfile;

alter system set "_db_block_lru_latches"=16 scope=spfile;

-- 1. 多个DBWRs 进程和多个DBWR IO 从属进程不能并存。

如果同时启用，则会产生如下错误信息: ksdwra("Cannot start multiple dbwrs when using I/O slaves.\n";);而且，如果两个参数都启用，dbwr_io_slaves优先。
show parameter db_writer_processes
alter system set dbwr_io_slaves=0 scope=spfile;
alter system set db_writer_processes=4 scope=spfile;
alter system set "_db_block_lru_latches"=8 scope=spfile;
show parameter db_writer_processes

-- 2. DBWRs 进程的数目不能超过LRU latche sets的数(db_block_lru_latches或_db_block_lru_latches). 如果超过了，DBWRs 进程的数目将等于LRU latche sets的数(db_block_lru_latches或_db_block_lru_latches)并且启动期间在alert.log内生成下列消息：
("Cannot start more dbwrs than db_block_lru_latches.\n";);然而LRU latche sets的数(db_block_lru_latches或_db_block_lru_latches)可以超过DBWRs 进程的数目。

-- 3. dbwr_io_slaves 不受限于LRU latche sets的数(db_block_lru_latches或_db_block_lru_latches); 即可以dbwr_io_slaves >= db_block_lru_latches.

相关日志