跟着吕大师（VAGE）揭密隐含参数：_db_writer_coalesce_area_size(二)

125 45323 log buffer space 0 00 0 00 0 00 3290255840 2 Configuration -1 0 WAITED SHORT TIME 4608 7446

gyj@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file parallel write';

no rows selected

gyj@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT like 'db file%';

EVENT TOTAL_WAITS TIME_WAITED_MICRO

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

db file sequential read 91162 37712572

db file scattered read 1830 8543678

db file single write 372 1934286

db file async I/O submit 553 84199251

db file parallel read 67 1863758

始终没看到db file parallel write等待。。。。

在吕大师的指点下：

select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';

MOS上有篇文章

'db file async I/O submit' when FILESYSTEMIO_OPTIONS=NONE [ID 1274737.1]

'db file async I/O submit' should be treated as 'db file parallel write' in previous releases.

**********************************************************************

在吕大的指点下，开始测试：

**********************************************************************

第一把：

参数：_db_writer_coalesce_area_size=1048576

sys@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';

EVENT TOTAL_WAITS TIME_WAITED_MICRO

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

db file async I/O submit 1944 88188121

gyj@OCM> set timing on;

gyj@OCM> update gyj100 set id=id+0 where rownum<=5000000;

5000000 rows updated.

Elapsed: 00:00:46.10

sys@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';

EVENT TOTAL_WAITS TIME_WAITED_MICRO

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

db file async I/O submit 2222 128007271

sys@OCM> select (2222-1944)/46||'次/秒' from dual;

(2222-1944)/46

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

6.04347826次/秒

第二把测试，修改参数：

sys@OCM> alter system set "_db_writer_coalesce_area_size"=8048576 scope=spfile;

System altered.

sys@OCM> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@OCM> startup