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

gyj@OCM> select * from v$version;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

gyj@OCM> show parameter mem

NAME TYPE VALUE

hi_shared_memory_address integer 0

memory_max_target big integer 0

memory_target big integer 0

shared_memory_address integer 0

gyj@OCM> show parameter sga

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 372M

sga_target big integer 0

gyj@OCM> show parameter db_cache

db_cache_advice string ON

db_cache_size big integer 100M

gyj@OCM> show parameter mttr

fast_start_mttr_target integer 0

_db_writer_coalesce_area_size Size of memory allocated to dbwriter for coalescin 1048576

gyj@OCM>create table gyj100 (id int,name varchar2(2000));

gyj@OCM> declare

2 c number :=0 ;

3 begin

4 for i in 1 .. 5000000 loop

5 insert into gyj100 values(i,'gyj'||i);

6 c := c+1;

7 if mod(c,5000)=0 then

8 commit;

9 end if;

10 end loop;

11 end;

12 /

PL/SQL procedure successfully completed.

gyj@OCM> select bytes/1024/1024 sz from user_segments where segment_name='GYJ100';

SZ           

120
             

gyj@OCM> select sid from v$mystat where rownum=1;

SID           

125     
             

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

5000000 rows updated.

Elapsed: 00:00:47.50

再开一个窗口观察等待事件：

gyj@OCM> select * from V$SESSION_wait where sid=125;

SID       SEQ# EVENT                                                            P1TEXT                                                 P1 P1RAW     P2TEXT                                                                   P2 P2RAW            P3TEXT                                 P3 P3RAW             WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                        WAIT_TIME SECONDS_IN_WAIT STATE              WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO           

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';

db file async I/O submit 1944 88188121

gyj@OCM> set timing on;

Elapsed: 00:00:46.10

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

ORACLE instance started.

Total System Global Area 388354048 bytes

Fixed Size 2228584 bytes

Variable Size 276827800 bytes

Database Buffers 104857600 bytes

Redo Buffers 4440064 bytes

Database mounted.

Database opened.

Elapsed: 00:00:16.34

db file async I/O submit 31 15256544

sys@OCM> select 31/16||'次/秒' from dual;

31/16           

1.9375次/秒
           

再测一把，把参数改回去：_db_writer_coalesce_area_size=1048576

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

sys@OCM> startup force;

Elapsed: 00:00:48.49

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

db file async I/O submit 282 40353435

sys@OCM> select 282/48||'次/秒' from dual;

282/48           

5.875次/秒
           

总结：

参数_db_writer_coalesce_area_size=1048576， 产生的等待6次/秒

参数：_db_writer_coalesce_area_size=8048576，产生的等待2次/秒