latch: redo writing - 日本エクセム株式会社 Oracle待機イベント情報

SQL> select name, gets, misses, immediate_gets, immediate_misses,
   wait_time
from v$latch_parent
where name = 'redo writing';

NAME             GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES  WAIT_TIME
---------- ---------- ---------- -------------- ---------------- --------
redo writing    829715         20              0                0          0

SQL> ed redo_test.sql
- 以下のように数百回のupdateとcommitを実行しながら、REDO領域の負荷を増加させるSQL文を作成します。
update redo_test set id= id、name= name where id=＆1;
commit;
update redo_test set id= id、name= name where id=＆1;
commit;
update redo_test set id= id、name= name where id=＆1;
....

SQL> ed redo_test.bat
- 以下のように同時に30個のセッションでredo_test.sqlを実行します。
start sqlplus maxgauge/ maxgauge@ ora10gr2@redo_test1
start sqlplus maxgauge/ maxgauge@ ora10gr2@redo_test2
...
start sqlplus maxgauge/ maxgauge@ ora10gr2@redo_test30

cmd> redo_test.bat

SQL> select event, total_waits, time_waited
from v$session_event
where sid = (select sid from v$mystat where rownum = 1)
order by 3 desc;

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client            515        5458
buffer busy waits                              106         503
log file sync                                     129         231
latch: cache buffers chains                  86          84
latch: In memory undo latch               27          17
events in waitclass Other                    18          15
latch: library cache                            22          14
SQL*Net message to client                516           3
latch: redo copy                                             2               3
latch: library cache pin                         4           1
SQL*Net break/reset to client               2           0
cursor: mutex S                               193           0