ORACLE 11g LOG FILE SYNC v1.0

ORACLE 11g LOG FILE SYNC

 

Date	Ver	Etc.
110829

 

 

1.    LOG FILE SYNC

User Session 이 commits ( or rollback) 을 할 때, 세션의 redo 정보는 LGWR 에 의해 반드시 redo logfile 로 쓰여져야 한다. Commit 이나 Rollback 을 수행하는 Server Process 는 redo log 가 다 쓰이는 동안 LOG FILE SYNC event 를 대기한다. (Ref. B 참고)

 

2.    LGWR 가 Redo Log 를 내려쓰는 주기

-      매 3초마다

-      redo log buffer 의 1/3 을 사용했을 때

-      DBWn 이 수정된 버퍼를 디스크로 내려쓸 때 필요한 경우

(Ref.C 참고)

 

3.    Log File Sync 가 나타날 수 있는 상황

1번에서 확인했듯이 Log File Sync 는 Server Process 가 LGWR 의 작업 (redo buffer -> redo log file) 하는 동안 나타나는 event 이다.

 

잦은 Log File Sync 는 주로 Application 에서의 불필요한 commit 으로 인해 발생된다.

또한 Log buffer 크기가 지나치게 큰 경우도 발생한다. Log Buffer 크기가 크다는 것은 1/3 의 사이즈도 크다는 것을 의미하며 이 데이터를 Log File 에 내려쓰는 시간도 증가한다는 것을 의미한다.

 

4.    Log File Sync 로 나타날 수 있는 문제

Log File Sync 를 대기하는 server process 는 다음 작업을 진행하지 못한다. Transaction 에 대한 종료 (commit) 가 되어야 하기 때문이다. 때문에 이 event 를 대기하는 세션은 행걸린 것 같은 상황이 지속된다.

 

보통은 이 event 는 매우 짧은 시간 나타나며 길게 지속되는 경우는 드물다.

 

5.    Log File Sync 의 Wait 의 확인

event 의 확인은 v$system_event 나 v$session_wait 로 확인한다.

v$session_wait 의 경우 말 그대로 session 에 대한 wait 정보를 조회하는데 여기서 문제는 실시간 정보(델타값) 를 보여준다는 것이다. 때문에 수행 구간에 대해 문제가 얼마나 발생했는지 그 강도와 빈도를 확인하기가 어렵다.

 

반면 v$system_event 의 경우 시스템 레벨의 누적값을 보여준다. 그렇기 때문에 다수의 세션이 활동하는 시스템에서 어떤 세션이 특정 event 에 대해 얼마나 큰 영향을 주는지 파악하기 어렵다. 이 view 를 이용해 정확한 event 를 확인한다는 것은 타 세션에 의한 접근이 제어되고 있는 상황 (이를 테면 테스트 장비) 을 의미한다.

 

6.    Log File Sync event 의 해소방안

<Average time waited 가 낮지만 waits 횟수가 높을 때>

Application 에서 빈번하게 commit 을 호출하는 경우

 

<Average time waited 가 높을 때>

*

LGWR 를 대기하는 세션들을 확인하고 무엇을 하는데 시간을 소모하고 무엇을 대기하는지 확인한다.

Slow I/O 로 확인되면 다음의 내용을 시도한다.

l  Ref.B 를 참고

-      redo logs 를 포함하는 디스크에 대해 다른 I/O 활동을 최소화한다. 혹은 물리적으로 독립된 디스크를 사용한다.

-      다중화 된 REDO logs 를 archiver 에 대한 부하를 최소화 하기 위해 물리적으로 분산한다.

-      Redo log 를 더 빠른 I/O 디바이스로 옮긴다.

-      raw 디바이스의 사용을 고려한다. (혹은 디스크 벤더가 제공하는 simulated raw device)

 

7.    Practice

ID (id1 number, id2 number, id3 number, id4 char(2000) default ‘Hello’) 라는 테이블을 만듦.

 

insert into id(id1, id2, id3) select level, level + 1, level + 2 from dual connect by level < 10000;

데이터를 증가시켰고 다음의 사이즈로 만들었다.

 

SQL> select bytes/1024/1024 mb from dba_segments where segment_name = 'ID';           MB ----------        112

 

세션을 하나 더 열어 다음의 스크립트를 수행했다.

declare  begin   for i in 1..9999 loop     update id set id2 = id2 + 1 where id1 = i;     commit write immediate;      end loop; end; /

 

위 프로시저의 경우는 Application 에서 빈번하게 commit 을 호출하는 경우에 해당된다. 단순히 commit 만 사용해서는 log file sync 를 볼 수 없으며 commit write immediate (즉시 처리) 를 사용해야 관측할 수 있다.

 

위 프로시저를 수행한 후 LGWR 와 위 프로시저를 호출한 SID 에 대해 event 를 조회해 보았다.

SQL> select event, p1, p2, p3, seconds_in_wait from v$session where sid in (11,44)   2  order by sid asc   3  /   EVENT                                  P1         P2         P3 SECONDS_IN_WAIT ------------------------------ ---------- ---------- ---------- --------------- log file parallel write                 1          4          1               0 log file sync                        6301    2473202          0               0

 

log file parallel write event 가 발생하는 SID 11 은 LGWR 이다. REDO BUFFER 의 LOG 가 REDO LOG FILE 로 내려 쓰여지는 과정은 LGWR 가 일하며 이 과정에서 log file parallel write event 가 발생한다. 동시에 LGWR 를 대기하는 세션들은 log file sync event 가 발생한다.

 

해당 event 의 P1, P2, P3 에 대한 값은 다음과 같이 확인 가능하다.

SQL> select event, p1text, p2text, p3text from v$session where sid in (11,44)   2  order by sid asc   3  /   EVENT                          P1TEXT               P2TEXT               P3TEXT ------------------------------ -------------------- -------------------- ------------------------------ log file parallel write        files                blocks               requests log file sync                  buffer#              sync scn

 

log file parallel write 를 예로 들자면 p1 이 files 에 해당한다. p2 는 blocks 에 해당한다. log file sync 의 경우 p1, p2, p3 의 내용이 또 다르다. 다시 말해 event 마다 p1, p2, p3 에 해당하는 값의 의미가 다르다.

 

위의 내용은 v$session 을 이용해 확인한 정보로 이 정보는 v$session_wait 로 조회한 내용과 동일하다.

위에서 확인한 내용은 5에서 이야기한대로 현재값만을 보여준다.

 

누적값을 이용해 구간에 대한 변화값(델타) 을 구하기 위해서는 v$system_event 를 사용해야 한다.

 

EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT ------------------------------ ----------- -------------- ----------- ------------ log file sync                        30525              0        2946           .1   (프로시저 수행)   EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT ------------------------------ ----------- -------------- ----------- ------------ log file sync                        30526              0        2948           .1

 

TOTAL_WAITS 는 이 event 를 대기한 전체 횟수이다.

TIME_WAITED 는 이 이벤트를 대기한 누적 시간이다. (1/100 초)

 

이 값으로 델타값을 만들면 발생량 / 추이그래프를 만들 수 있다.

 

8.    REFERENCES

A.     11g Release 1 (11.1) commit |

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4010.htm

B.     10 Instance Tuning Using Performance Views | log file sync |

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/instance_tune.htm#sthref819

C.     11g Release 1 (11.1) | 9 Process Architecture | LGWR |

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/process.htm