"오라클 리두 로그 튜닝"의 두 판 사이의 차이

1 리두 로그 버퍼(Redo Log Buffer)[편집]

1. INSERT, DELETE, UPDATE 작업을 실행하면 트랜젝션에 의해 만들어진 모든 변경정보(체인지 벡터) 기록
2. UPDATE문이 실행되면 변경 전/후 데이터 복구에 대한 설명서(체인지 벡터) 가 메모리상의 로그버퍼에 저장 됨
   1. 메모리상의 로그버퍼는 LGWR 프로세스에 의해 온라인 리두 로그 파일에 기록됨.
   2. 온라인 리두 로그는 라운드 로빈 방식으로 순차적으로 아키이브 로그에 저장됨(아카이빙 로그 모드일 경우)
3. 리두 로그 버퍼 영역은 여러 명의 사용자가 같이 사용하는 공간이기 때문에 너무 많은 서버 프로세스가 동시에 많은 변경된 데이타를 저장 하면 서버 프로세서 간에 결합(Contention)이 발생.
4. 사용자의 변경된 정보를 저장할 충분한 리두 로그 버퍼 공간이 없다면 미처 변경된 정보를 저장하지 못하고 Waiting 발생.
5. 이러한 문제 발생시 결국 UPDATE, DELETE, INSERT 시에 성능 문제 발생 됨
6. 관련된 파라메터 LOG_BUFFER 에 의해 로그 버퍼 영역의 크기가 결정됨. 이 파라메터를 미설정 시 DB_BLOCK_SIZE 의 4배 값을 기본 로그버퍼 영역의 크기로 할당.

2 리두 로그 튜닝[편집]

• Redo 로그는 커질수록 성능에 유리함(최근 Redo 크기를 수백 메가에서 수 십 기가바이트까지 설정하는 경우가 많음)

1. Redo가 지나치게 작으면 log file switch completion 등 대기 이벤트가 문제 발생.
2. 로그 스위치가 너무 자주 일어나면 아카이빙 및 체크포인트가 자주 발생하게 되므로 archiver 프로세스와 DBWR 프로세스가 바빠짐.
3. v$log_history 뷰를 조회 하면 로그 스위치가 얼마나 자주 일어나는지 확인.

• 오라클의 권고안은 20분에 한번씩 일어나도록 할것.

1. 반드시 준수할 필요는 없지만 적어도 5~10분 사이에 한번씩 로그 스위치가 일어나도록 설정해주는 것을 권장함.

• Redo 로그가 커져서 불리한 점은 instance recovery 시간이 느려진다는것.

1. instance recovery time이 늦어 지면 fast_start_mttr_target 파라미터를 활용하여 복구 시간을 빠르게 하는것을 권고.

• 오라클 공식 매뉴얼에서도 다음과 같이 권고하고 있습니다.

https://docs.oracle.com/cd/E11882_01/server.112/e41573/build_db.htm#PFGRF013

4.1.3 Sizing Redo Log Files

The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. 
Generally, larger redo log files provide better performance. Undersized log files increase checkpoint activity and reduce performance.
Although the size of the redo log files does not affect LGWR performance, it can affect DBWR and checkpoint behavior. 
Checkpoint frequency is affected by several factors, including log file size and the setting of the FAST_START_MTTR_TARGET initialization parameter. 
If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time, Oracle Database automatically tries to checkpoint as frequently as necessary. 
Under this condition, the size of the log files should be large enough to avoid additional checkpointing due to under sized log files. 
The optimal size can be obtained by querying the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY view. 
You can also obtain sizing advice on the Redo Log Groups page of Oracle Enterprise Manager.

It may not always be possible to provide a specific size recommendation for redo log files, but redo log files in the range of 100 MB to a few gigabytes are considered reasonable. 
Size online redo log files according to the amount of redo your system generates. 
A rough guide is to switch log files at most once every 20 minutes.

3 로그 버퍼 캐시 영역 확장하기[편집]

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

SQL>select sid , event , seconds_in_wait , state 
      from v$session_wait 
     where event like ‘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 파라메터 값을 더 높게 설정한다.

4 인덱스 생성시 NOLOGGING 절을 적용[편집]

1. INSERT, DELETE, UPDATE 시 트랜젝션에 의해 발생 된 모든 변경전, 후 정보가 로그 버퍼 영역에 저장되는데 이를 Logging Mode 라고 함 .
2. TABLE, INDEX 를 생성할 때 NOLOGGING 로 생성 하면 모든 트랜젝션의 로그 정보가 로그 버퍼 영역에 저장되지 않음.
3. 만약 해당 테이블의 데이터가 다른 시스템 또는 다른 데이터베이스로 부터 이동되었거나 언제든지 복구 가능한 테이블이라면 LOGGING 모드로 데이터베이스를 사용하는 것보다 NOLOGGING 모드로 사용하는 것이 로그 버퍼 영역을 적게 사용하기 때문에 로그 버퍼 영역에 대한 경합 현상을 최소화 함.
4. 인덱스는 테이블을 통해 언제든지 만들 수 있기 때문에 NOLOGGING 모드로 만드는 것이 성능향상에 좋음

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

SQL>alter table emp logging ;

SQL>create index ix_emp on tb_emp(no) nologging;