buffer busy waits, latch: cache buffers chains, enq: HW - contention, enq: TX - contention 등
다양한 Wait를 초래하는 작업을 생성하고, 해결 방법을 모색해 본다.
상황 : freelist로 관리되는 segment에 동시 insert를 수행하는 상황이다.
(1) Startup 직후의 wait 상황 확인
SQL> conn / as sysdba
SQL> startup force
SQL> conn system/oracle
SQL> ed system_event.sql
set pages 60
set lines 120col wait_class format a20
col event format a60select *
from (
select wait_class, event, total_waits, time_waited
from v$system_event
where wait_class <> 'Idle'
order by 1, 2, 3 desc
) where rownum <= 100;
SQL> @ system_event.sql
WAIT_CLASS EVENT TOTAL_WAITS TIME_WAITED
-------------------- ------------------------------------------------------------ ----------- -----------
Commit log file sync 5 11
Concurrency buffer busy waits 1 0
Concurrency latch: cache buffers chains 1 0
Concurrency latch: shared pool 2 0
Concurrency os thread startup 19 63
Configuration checkpoint completed 1 59
Network SQL*Net message to client 13 0
Other Streams AQ: qmn coordinator waiting for slave to start 2 489
Other control file heartbeat 1 391
Other enq: CF - contention 5 20
Other enq: CN - race with init 1 3
Other flashback buf free by RVWR 6 58
Other instance state change 1 0
Other latch free 1 0
Other latch: cache buffers lru chain 5 0
Other rdbms ipc reply 10 35
System I/O Log archive I/O 27 4
System I/O control file parallel write 200 129
System I/O control file sequential read 753 22
System I/O log file parallel write 32 18
System I/O log file sequential read 89 21
System I/O log file single write 16 2
System I/O recovery read 3 0
User I/O db file scattered read 109 104
User I/O db file sequential read 4439 2093
User I/O db file single write 7 1
User I/O direct path read 28 0
User I/O direct path read temp 4 0
User I/O direct path write 14 0
User I/O read by other session 3 3
30 rows selected.
(2) freelist로 관리하는 테이블을 생성하고, 동시 insert 작업을 수행한다.
SQL> drop tablespace ts1 including contents and datafiles;
SQL> create tablespace ts1 datafile '/u01/app/oracle/oradata/orcl/ts1.dbf' size 50m
extent management local
segment space management manual;
SQL> create table t_buffer_busy_waits1 (n number, name varchar2(50)) tablespace ts1;
SQL>
create or replace procedure up_buffer_busy_waits1
is
begin
for i in 1..100000 loop
insert into t_buffer_busy_waits1 values (i, 'BUFFER BUSY WAITS?');
end loop;
commit;
end;
/
SQL>
create or replace procedure do_bbw1
is
v_jobno number:=0;
begin
for i in 1..10 loop
dbms_job.submit(v_jobno,'up_buffer_busy_waits1;', sysdate);
end loop;
commit;
end;
/
SQL> exec do_bbw1
SQL> select count(*) from dba_jobs_running;
COUNT(*)
----------
10
(3) 10개의 Job이 모두 종료될 때까지 잠시 기다린 뒤 다양한 wait가 발생했음을 확인한다.
SQL> -- 다음 쿼리의 결과가 0이 될 때까지 잠시 기다린다.
SQL> select count(*) from dba_jobs_running;
COUNT(*)
----------
0
SQL> @ system_event.sql
WAIT_CLASS EVENT TOTAL_WAITS TIME_WAITED
-------------------- ------------------------------------------------------------ ----------- -----------
Commit log file sync 33 49
Concurrency buffer busy waits 5920 23834
Concurrency cursor: mutex S 1 141
Concurrency latch: cache buffers chains 50 39
Concurrency latch: library cache 3 5
Concurrency latch: library cache pin 37 32
Concurrency latch: shared pool 3 0
Concurrency library cache load lock 6 38
Concurrency library cache pin 6 3
Concurrency os thread startup 29 212
Concurrency row cache lock 2 2
Configuration checkpoint completed 1 59
Configuration enq: HW - contention 707 2536
Configuration log buffer space 62 249
Configuration log file switch (checkpoint incomplete) 6 249
Configuration log file switch completion 14 119
Configuration undo segment extension 3 18
Network SQL*Net message to client 38 0
Other LGWR wait for redo copy 7 3
Other SGA: allocation forcing component growth 1 1
Other Streams AQ: qmn coordinator waiting for slave to start 2 489
Other control file heartbeat 1 391
Other enq: CF - contention 29 708
Other enq: CN - race with init 1 3
Other enq: TC - contention 2 0
Other enq: TX - contention 64 103
Other flashback buf free by RVWR 19 267
Other instance state change 1 0
Other job scheduler coordinator slave wait 2 3125
Other latch free 3 0
Other latch: cache buffers lru chain 6 1
Other latch: redo allocation 2 2
Other rdbms ipc reply 43 82
System I/O Log archive I/O 297 256
System I/O RMAN backup & recovery I/O 24 8
System I/O control file parallel write 785 2656
System I/O control file sequential read 2235 38
System I/O control file single write 20 3
System I/O log file parallel write 324 3076
System I/O log file sequential read 374 751
System I/O log file single write 36 3
System I/O recovery read 3 0
User I/O Data file init write 64 8
User I/O db file scattered read 3244 245
User I/O db file sequential read 14463 2938
User I/O db file single write 11 1
User I/O direct path read 49 0
User I/O direct path read temp 4 0
User I/O direct path write 35 0
User I/O local write wait 8 1
User I/O read by other session 7 17
51 rows selected.
SQL> select STATISTIC_NAME, value
from v$segment_statistics
where object_name = 'T_BUFFER_BUSY_WAITS1';
STATISTIC_NAME VALUE
buffer busy waits 5915
---------------------------------------------------------------- ----------
logical reads 1057456
gc buffer busy 0
db block changes 1016416
physical reads 15228
physical writes 4020
physical reads direct 0
physical writes direct 0
gc cr blocks received 0
gc current blocks received 0
ITL waits 0
row lock waits 0
space used 0
space allocated 33554432
segment scans 0
15 rows selected.
(4) 다양한 Wait를 어떻게 해결할 것인가에 대해 연구하고 토의하도록 한다.
각자 교재(OWI Advanced Oracle Wait Interface in 10g)를 이용해서 해법을 연구해보도록 한다.
buffer busy waits 및
latch: cache buffers chains에 관한 해법은 필수적으로 연구해야 하며,
다른 wait에 관한 해법은 2가지 wait만 각자 선택해서 연구하도록 한다.
- buffer busy waits -> Analysis Case
- latch: cache buffers chains -> Analysis Case
* 주요 참고 자료 :
'오라클 > OWI' 카테고리의 다른 글
| 다양한 wait가 발생하는 사례 연구 3 (0) | 2010.08.05 |
|---|---|
| 다양한 wait가 발생하는 사례 연구 2 (0) | 2010.08.05 |
| [Oracle Wait Event 모니터링] 6. Top SQL 튜닝하기 (0) | 2010.07.10 |
| [Oracle Wait Event 모니터링] 5. Redo buffer 관련 Wait (0) | 2010.07.10 |
| [Oracle Wait Event 모니터링] 4. Buffer Cache 관련 Wait (0) | 2010.07.10 |