write complete waits - 日本エクセム株式会社 Oracle待機イベント情報

・P1 : File#
・P2 : Block#
・P3 : Reason ID

・64,000件のローを持つCBL_TEST1〜CBL_TEST20テーブルを生成します。
・同時に20個のセッションでCBL_TEST1〜CBL_TEST20テーブルをそれぞれ更新します。
・FAST_START_MTTR_TARGET値を1に与えた場合と600に与えた場合には、システムレベルで
　　write complete waits待機がどのように発生していることを確認します。

-- ダーティバッファを作成するプロシージャ
create or replace procedure wcw_update(p_idx in number) 
is
begin
	for idx in 1 .. 500 loop
		execute immediate 'update cbl_test'||p_idx || ' set id = id where id = :id' 
			using idx;
		commit;
	end loop;
end;
/

var job_no number;

-- 複数のセッションで同時にテーブルを更新
begin
	for idx in 1 .. 19 loop
		dbms_job.submit(:job_no, 'wcw_update('||idx||');');
		commit;
	end loop;
end;
/

exec wcw_update(20);

SQL> select * 
from (
select event, total_waits, time_waited 
from v$system_event
where wait_class <> 'Idle'
order by 3 desc
) where rownum <= 100;

・FAST_START_MTTR_TARGET=1の場合

SQL> select * 
from (
select event, total_waits, time_waited 
from v$system_event
where wait_class <> 'Idle'
order by 3 desc
) where rownum <= 100;

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
log buffer space                      2440       81186
log file switch (private stran         565       31701
d flush incomplete)
free buffer waits                     1367       14198
write complete waits                    56        5334
log file parallel write                524        4964
buffer busy waits                      133        2302
log file switch completion             120        2215
db file sequential read               8589        1761
os thread startup                       47        1759
...

SQL> select name, value from v$sysstat where name like '%checkpoint%;

NAME                                                    VALUE
-------------------------------------------------- ----------
physical writes non checkpoint                          16823
DBWR checkpoint buffers written                         18024
DBWR thread checkpoint buffers written                   4063
DBWR tablespace checkpoint buffers written                  0
DBWR parallel query checkpoint buffers written              0
DBWR checkpoints                                            7
background checkpoints started                              4
background checkpoints completed                            3

・FAST_START_MTTR_TARGET=600の場合

SQL> select * 
from (
select event, total_waits, time_waited 
from v$system_event
where wait_class <> 'Idle'
order by 3 desc
) where rownum <= 100;

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
log buffer space                      2532       77920
free buffer waits                     5208       11624
log file switch (checkpoint in          93        6159
complete)
log file switch completion             235        5915
log file parallel write                319        4698
os thread startup                       46        1658
write complete waits                    17        1623
log file sync                           41        1554
latch: cache buffers chains            187        1452
...

SQL> select name, value from v$sysstat where name like '%checkpoint%;

NAME                                                    VALUE
-------------------------------------------------- ----------
physical writes non checkpoint                           9661
DBWR checkpoint buffers written                          2089
DBWR thread checkpoint buffers written                   1989
DBWR tablespace checkpoint buffers written                  0
DBWR parallel query checkpoint buffers written              0
DBWR checkpoints                                            7
background checkpoints started                              4
background checkpoints completed                            2