행위

오라클 로그버퍼 튜닝

DB CAFE

1 로그버퍼(Log Buffer) 튜닝

  • 사용자들이 DB 접속한 후 INSERT, DELETE, UPDATE 작업을 실행하면 트랜젝션에 의해 만들어진 모든 변경 정보(UPDATE문이 실행면

변경 전, 변경 후 데이타가 로그 버퍼에 저장 ==>>> 로그버퍼 영역에서 LGWR(로그라이터)에 의해 로그 파일에 기록됨.

  • 리두 로그 버퍼 영역은 여러 명의 사용자가 같이 사용하는 공간이기 때문에 너무 많은 서버 프로세스가 동시에 많은 변경된 데이타를 저장 하면

서버 프로세서 간에 결합(Contention)이 발생.

  • 또한 사용자의 변경된 정보를 저장할 충분한 리두 로그 버퍼 공간이 없다면 미처 변경된 정보를 저장하지 못하고 Waiting 해야 하는 경우도 발생.

이러한 문제 발생시 결국 UPDATE, DELETE, INSERT 시에 성능 문제 발생.

관련 파라메터 LOG_BUFFER 에 의해 로그 버퍼 영역의 크기가 결정됨. 이 파라메터를 미설정 시 DB_BLOCK_SIZE 의 4배 값을 기본 로그버퍼 영역의 크기로 할당.


1.1 로그 버퍼 캐시 영역 확장하기

1) V$SESSION_WAIT 참조하여 튜닝여부를 확인.

SQL>select sid , event , seconds_in_wait , state from v$session_wait where event = ‘log buffer space%’ ;

SID EVENT SECOND_IN_WAIT STATE
—- ——————– —————– ——–
5 log buffer space 110 WAIT <= SECOND_IN_WAIT 값이 계속 증가되는 값을 나타내고 STATE 값이 ‘WAIT’을 나타내면
로그버퍼 영역이 작아서 서로 프로세스 간에 경합이 발생하고 있음을 의미.

2) V$SYSSTAT 에서 서버 프로세스가 로그 정보를 저장했던 로그버퍼의 블록수 (Redo Entries) 와 로그 버퍼의 경합으로 인해 발생한 대기상태에서 다시 로그 버퍼 공간을 할당 받았던 블록수(Redo Buffer Allocation Entries) 확인.

SQL> select name,value from v$sysstat where name in (‘redo buffer allocation retries’,’redo entries’) ;

NAME VALUE
—————————————
redo entries 2010
redo buffer allocation retries 15 <= redo buffer allocation retries의 값이 0 이어야 하고 redo entries 값의 1% 미만일 때 좋은 성능 기대
<= 기준치에 적합하지 않은 결과가 나오면 리두 로그 영역이 작아서 발생한 문제이므로 LOG_BUFFER 파라메터 값을 더 높게 설정한다.

1.2 인덱스 생성시 NOLOGGING 절을 적용

사용자가 테이블을 생성하고 데이터를 INSERT, DELETE, UPDATE 하면 트랜젝션에 의해 발생 된 모든 변경전, 변경 후 정보가 로그 버퍼 영역에 저장되는데 이를 Logging Mode 라고 함 .

TABLE, INDEX 를 생성할 때 NOLOGGING 키워드를 사용하면 생성 후 발생하는 모든 트랜젝션의 로그 정보가 로그 버퍼 영역에 저장되지 않음.

– 만약 해당 테이블의 데이터가 다른 시스템 또는 다른 데이터베이스로 부터 이동되었거나 언제든지 복구 가능한 테이블이라면 LOGGING 모드로 데이터베이스를 사용하는 것보다

 NOLOGGING 모드로 사용하는 것이 로그 버퍼 영역을 적게 사용하기 때문에 로그 버퍼 영역에 대한 경합 현상을 최소화 할 수 있다.

– 인덱스는 테이블을 통해 언제든지 만들 수 있기 때문에 NOLOGGING 모드로 만드는 것이 성능에 도움이 될 수 있다

SQL>create table emp (
   no number(5)
 , name varchar2(10) )

NOLOGGING ;

SQL>alter table emp logging ;

SQL>create index iㅌ_emp on emp10(no) nologging;