虚位以待(AD)
虚位以待(AD)
首页 > 数据库 > Oracle数据库 > 跟着吕大师(VAGE)揭密隐含参数:_db_writer_coalesce_area_size

跟着吕大师(VAGE)揭密隐含参数:_db_writer_coalesce_area_size
类别:Oracle数据库   作者:码皇   来源:blog.csdn.net/guoyjoe     点击:

跟着吕大师(VAGE)揭密隐含参数:_db_writer_coalesce_area_size 最近在看吕大师的大作《Oracle核心揭密》,马上要上市了,期待中。。。 这部大作可以与Jonathan Lewis大师的《Oracle Core_ Ess
跟着吕大师(VAGE)揭密隐含参数:_db_writer_coalesce_area_size           最近在看吕大师的大作《Oracle核心揭密》,马上要上市了,期待中。。。        这部大作可以与Jonathan Lewis大师的《Oracle Core_ Essential Internals for DBA》相提并论,看了几天收益颇多,哈哈美国有Lewis,中国有VAGE。      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     NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ 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     NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_cache_advice                      string      ON db_cache_size                        big integer 100M     gyj@OCM> show parameter mttr     NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ 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';     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 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.     sys@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';         no rows selected     gyj@OCM> update gyj100 set id=id+0 where rownum<=5000000;     5000000 rows updated.     Elapsed: 00:00:16.34     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                                                  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;     System altered.     sys@OCM> startup force; 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.     sys@OCM> select event,TOTAL_WAITS , TIME_WAITED_MICRO from V$SYSTEM_EVENT where EVENT='db file async I/O submit';         no rows selected     gyj@OCM> update gyj100 set id=id+0 where rownum<=5000000;     5000000 rows updated.     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';         EVENT                                                            TOTAL_WAITS TIME_WAITED_MICRO ---------------------------------------------------------------- ----------- ----------------- db file async I/O submit                                                 282          40353435         sys@OCM> select 282/48||'次/秒' from dual;         282/48 ----------      5.875次/秒              果然把参数db_writer_coalesce_area_size改大起作用了:  参数:_db_writer_coalesce_area_size=1048576, 产生db file async I/O submit等待6次/秒  参数:_db_writer_coalesce_area_size=8048576,产生db file async I/O submit等待2次/秒
相关热词搜索: 跟着 大师 VAGE