by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_리두 조사하기




  • 작업시 생성되는 리두가 얼마나 되는지 측정할 필요가 있다.
  • LGWR 은 오라클 인스턴스에서 하나만 존재한다.
  • LGWR 은 리두관리, 커밋요청 작업을 한다.
  • LGWR 이 수행하는 일이 많을수록 시스템은 느려진다.
리두 측정하기
  • conventional path INSERT (버퍼캐시 경유) VS direct-path INSERT
    생성되는 리두 의 차이점
    1. Conventional path INSERT
      SQLPLUS >set autot traceonly statistic
      SQLPLUS >truncate table t ;
      
      Table truncated.
      
      SQLPLUS >insert into t
        2  select * from big_table ;
      
      565568 rows created.
      
      
      Statistics
      ----------------------------------------------------------
      	654  recursive calls
            35161  db block gets
            14419  consistent gets
      	  3  physical reads
         59319436  redo size         <-------------- 59MB 정도의 리두가 생성됨.
      	837  bytes sent via SQL*Net to client
      	790  bytes received via SQL*Net from client
      	  3  SQL*Net roundtrips to/from client
      	  1  sorts (memory)
      	  0  sorts (disk)
           565568  rows processed
      
      
    2. direct-path INSERT
      • 필자는 NOARCHIVELOG 모드 데이터베이스에서 테스트하였다.
      • 테스트DB 가 ARCHIVELOG 모드여서 테이블만 nologging 으로 변경하고 실행했다.
        SQLPLUS >truncate table t ;
        
        Table truncated.
        
        SQLPLUS >alter table t nologging ;
        
        Table altered.
        
        SQLPLUS >insert /*+ APPEND */ into t
          2  select * from big_table ;
        
        565568 rows created.
        
        
        Statistics
        ----------------------------------------------------------
        	777  recursive calls
               5788  db block gets
               6116  consistent gets
        	  2  physical reads
             153404  redo size        <-------------- 리두가 153KB 생성되었다.
        	825  bytes sent via SQL*Net to client
        	804  bytes received via SQL*Net from client
        	  3  SQL*Net roundtrips to/from client
        	  7  sorts (memory)
        	  0  sorts (disk)
             565568  rows processed
        
        
        
        -- ====================================
        -- 추가테스트 
        -- ====================================
        
        SQLPLUS >truncate table t ;
        
        Table truncated.
        
        -- t 테이블은 logging 모드 테이블이다.
        -- 데이터베이스는 ARCHIVELOG 모드이다.
        
        SQLPLUS >insert /*+ APPEND */ into t
          2  select * from big_table ;
        
        565568 rows created.
        
        Statistics
        ----------------------------------------------------------
        	736  recursive calls
               5790  db block gets
               6068  consistent gets
        	  2  physical reads
           61532396  redo size       <---------------- 
        	823  bytes sent via SQL*Net to client
        	804  bytes received via SQL*Net from client
        	  3  SQL*Net roundtrips to/from client
        	  1  sorts (memory)
        	  0  sorts (disk)
             565568  rows processed
        
        SQLPLUS >truncate table t ;
        
        Table truncated.
        
        SQLPLUS >insert /*+ APPEND NOLOGGING */ into t
          2  select * from big_table ;
        
        565568 rows created.
        
        
        Statistics
        ----------------------------------------------------------
        	736  recursive calls
               5791  db block gets
               6068  consistent gets
        	  2  physical reads
           61532396  redo size          <-------- 
        	824  bytes sent via SQL*Net to client
        	814  bytes received via SQL*Net from client
        	  3  SQL*Net roundtrips to/from client
        	  1  sorts (memory)
        	  0  sorts (disk)
             565568  rows processed
        
        
리두 로그를 생성하지 못하도록 막을 수 있을까?
  • 아니오.
    • 리두로깅은 데이터베이스의 핵심기능.
    • 9i 릴리즈 2 에서는 데이터베이스를 FORCE LOGGING 모드로 변경 가능. 모든작업이 로깅된다.
    • SELECT FORCE_LOGGING FROM V$DATABASE ; 로 확인가능.
    • Data Guard 를 지원한다.
  • SQL에서 NOLOGGING 설정
    • NOLOGGING : 리두를 아예 생성하지 않는다는 의미가 아님. 상당히 적은 양의 리두를 생성한다.
  • NOLOGGING 옵션 없이 CTAS로 테이블 생성시 리두 발생 확인
    
    SQLPLUS >select log_mode from v$database ;
    
    LOG_MODE
    ------------
    ARCHIVELOG
    
    SQLPLUS >drop table t purge ;
    
    Table dropped.
    
    SQLPLUS >variable redo number
    SQLPLUS >exec :redo := get_stat_val('redo size');
    
    PL/SQL procedure successfully completed.
    
    SQLPLUS >create table t
      2  as select * from all_objects ;
    
    Table created.
    
    SQLPLUS >exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated ...') ;
    1996548 bytes of redo generated ...
    
    PL/SQL procedure successfully completed.
    
    -- 1.9 MB 정도의 리두 생성됨.
    
  • NOLOGGING 옵션 사용하여 CTAS로 테이블 생성시 리두 발생 확인
    SQLPLUS >drop table t purge ;
    
    Table dropped.
    
    SQLPLUS >variable redo number
    SQLPLUS >exec :redo := get_stat_val ('redo size');
    
    PL/SQL procedure successfully completed.
    
    SQLPLUS >create table t
      2  NOLOGGING
      3  as
      4  select * from all_objects;
    
    Table created.
    
    SQLPLUS >set serveroutput on
    
    SQLPLUS >exec dbms_output.put_line( (get_stat_val('redo size')-:redo)|| ' bytes of redo generated..');
    78644 bytes of redo generated ..
    
    PL/SQL procedure successfully completed.
    
    -- 78KB 정도의 리두 생성됨.
    
  • NOARCHIVELOG 모드 데이터베이스에서였다면 차이가 없었을 것.
    (NOARCHIVELOG 모드일 경우 CREATE TABLE 은 데이터 딕셔너리 변경 이외에 나머지는 로깅되지 않는다.)
  • NOLOGGING 작업 주의사항
    • 백업 복구 담당자와 반드시 논의후 사용한다.
    • 어느정도 리두는 생성 된다. (데이터 딕셔너리를 보고하기 위한 것)
    • NOLOGGING 이후에 일어나는 DML작업은 리두로깅 된다.
      (direct-path load 로 sql loader를 사용 / insert append 문법으로 direct-path insert 만 로깅안함)
    • ARCHIVELOG 모드 데이터베이스에서는 NOLOGGING 작업으로 생성된 데이터에 대한 백업 기준을 정해야 한다.
      백업파일에 없을 경우, 리두로그파일에도 없어서 데이터 복구 방법이 없다.
  • 인덱스에 NOLOGGING 설정하기
    • 인덱스나 테이블을 NOLOGGING 모드로 변경한 이후 rebuild 시 로깅되지 않는다.
      
      
      -- 1. LOGGING 으로 INDEX Rebuild
      
      SQLPLUS >create index t_idx on t (object_name) ;
      
      Index created.
      
      SQLPLUS >variable redo number
      SQLPLUS >exec :redo := get_stat_val('redo size');
      
      PL/SQL procedure successfully completed.
      
      SQLPLUS >alter index t_idx rebuild ;
      
      Index altered.
      
      SQLPLUS >exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated ...') ;
      9733976 bytes of redo generated ...
      
      PL/SQL procedure successfully completed.
      
      -- 9MB 리두 생성됨
      
      
      -- 2. NOLOGGING 으로 변경 후 INDEX Rebuidl
      
      SQLPLUS >alter index t_idx nologging ;
      
      Index altered.
      
      SQLPLUS >exec :redo := get_stat_val('redo size');
      
      PL/SQL procedure successfully completed.
      
      SQLPLUS >alter index t_idx rebuild ;
      
      Index altered.
      
      SQLPLUS >exec dbms_output.put_line( (get_stat_val('redo size')-:redo) || ' bytes of redo generated ...') ;
      77800 bytes of redo generated ...
      
      PL/SQL procedure successfully completed.
      
      -- 77KB 리두 생성됨.
      -- 이 상태에서 백업 없이 장애발생 시, t_idx 인덱스는 데이터를 잃게된다.
      
  • NOLOGGING 요약 (NOLOGGING으로 할 수 있는 작업)
    • 인덱스 생성과 rebuild
    • /*+ APPEND */ 힌트 또는 SQL Loader direct-path INSERT 로 벌크INSERT.
      • 주의 : 테이블 데이터는 리두로깅 안되나, 인덱스 변경에 대한 리두는 생성된다.
    • LOB 작업 (대용량객체의 update)
    • CREATE TABLE AS SELECT 를 이용한 테이블 생성
    • MOVE, SPLIT 과 같은 ALTER TABLE 작업들
  • 적절한 NOLOGGING 작업은 리두로그양을 줄임으로서 작업을 극적으로 개선할 수 있다.
    • ex) 테이블을 다른 테이블스페이스로 옮길 때
    • 그러나 복구에 문제가 생길 수 있으므로 주의한다.
왜 새로운 로그를 할당할 수 없는가 ?
  • alert.log
    • DBWR이 리두로그에 의해 보호되는 데이터에 대한 체크포인트를 완료하지 않았거나
    • ARCH가 리두로그 파일을 아카이브 저장소에 복제 완료하지 않았을 때 발생되는 메시지.
  • 온라인 리두로그파일을 재사용하려고 하는데
    • 체크포인트가 완료되지 않았거나 / 아키이빙이 안되고 있을 경우
      리두 로그파일을 안전하게 사용할 수 있을 때까지 대기한다.
    • 리두 로그 파일 크기를 매우 작게 잡을 경우 발생할 수 있음.
  • 해결방안
    1. DBWR 속도를 빠르게 한다.
      • DBWR I/O 슬레이브 또는 DBWR 프로세스를 여러개 띄워 비동기I/O가 가능하도록 DBWR튜닝.
    2. 리두 로그 파일을 추가하라.
      • 'Checkpoint not complete' 발생 빈도를 최대로 줄일 수 있다.
      • 시스템 멈춤 현상을 제거한다.
    3. 로그 파일을 좀 더 크게 재생성하라.
      • 로그파일 채우는 시간과 재사용하는 시간 간격을 늘려준다.
      • 리두로그파일을 대량으로 소모하는 작업이 많을 경우 ARCH가 아카이빙하는 시간을 충분히 벌 수 있다.
      • 체크포인트 발생 시간 간격도 늦출 수 있다.
    4. 체크포인트가 좀 더 빈번하게 일어나도록 하라.
      • 블록 버퍼캐시를 작게 잡거나, DBWR 이 더티블록을 자주 플러시하도록 강제(파라미터 변경)하라.
      • 복구 시 적용하는 리두로그 양이 적어짐.
        단점은 버퍼캐시의 본래 효과를 발휘하지 못함.
블록 클린아웃
  • 블록 클린아웃 : '락킹' 관련 정보를 제거.
    (트랜잭션에 의해 설정된 Lock을 해제하고 블록 헤더에 커밋 정보를 기록)
  • 버퍼캐시의 10% 를 초과하는 블록은 다음 번 액세스 시 클린아웃(트랜잭션 정보 제거) 될 수 있다.
  • 블록 클린아웃 시 리두를 생성, dirty 상태가 아닌 블록을 dirty 상태로 만든다.
  • direct-path load 작업 수행 / load 작업 후 테이블 분석으로 DBMS_STAT 수행하면 일반적으로 블록이 클린된다.
  • 커밋 클린아웃 : 블록이 SGA에 있고 액세스 가능할 경우 해당 블록을 재방문하여 클린아웃 하는 것.
    SELECT문이 클린아웃할 필요없이 커밋 시점에 클린아웃하는 최적의 방법.
  • 클린아웃 동작 테스트 : SELECT 시 리두가 생성되는 것을 확인하는 테스트.
    • 조건
      1. DB_CACHE_SIZE 16MB ( 8KB 블록 * 2048개 )
      2. 한 블록에 정확히 한 로우가 들어가도록 테이블을 생성
      3. 테이블에 로우를 10000개 채우고 커밋
      4. 블록 10000개는 2048개의 10% 를 초과하므로 커밋 시 dirty블록 모두 클린아웃할 수 없다.
      5. 커밋완료시점까지 생성된 리두 양을 측정, 각 블록을 방문하는 SELECT 가 생성하는 리두 양을 측정한다.
      6. SGA 자동 메모리 관리 비활성화. (버퍼캐시 크기가 임의로 증가될 수 있으므로)
  • 테스트 결과로 예상하는 것
    • SELECT 만 해도 리두를 생성할 것이다.
    • dirty 블록을 방문하는 순간 DBWR 가 블록을 다시 디스크에 기록하게 한다. (블록 클린아웃에 기인)
  1. 테이블 생성
  2. 쿼리시 하드파싱되지 않도록 DBMS_STAT 수행. 하드파싱 동안 통계정보를 갖지않는 객체를 스캔한다.
    (결과는 아직 데이터가 없으니 실패함)

  3. 데이터 적재 및 커밋
  4. 리두양 측정

    722KB 리두 생성됨. 기본키 인덱스를 읽고 테이블 T를 읽는동안 변경된 블록헤더의 리두이다.
  5. 다시한번 쿼리 실행 (블록클린 확인)
  • 버퍼 캐시 블록을 100,000개 보다 많이 수용하도록 설정 후 테스트하면
    SELECT 문 실행 시 리두를 거의 또는 아예 생성하지 않음을 확인할 수 있다. (dirty 블록을 클린아웃 할 필요 없기 때문)
  • 이런 클린아웃 매커니즘은 대용량 INSERT/UPDATE/DELETE 후 많은 데이터베이스 블록에 영향을 미친다.
    (캐시의 10%를 넘어가는 블록은 명백히 커밋 클린아웃 되지 않는다.)
  • 대량DML 이후 블록을 조회하는 첫 번째 쿼리가 리두를 생성, 그 블록을 diry 상태로 바꾼다.
  • OLTP 환경에서는 지연된 블록클린아웃 현상을 보기 힘들다. 작고 간단한 트랜잭션이 대부분이기 때문.
  • CTAS, direct-path 로 적재된 데이터는 모두 클린 블록을 만든다.
  • 대량 데이터 적재 -> 적재한 데이터에 UPDATE 실행 -> 클린아웃이 필요한 블록이 생산됨.
    => 통계정보를 수집하는 DBMS_STAT 유틸리티 실행 시 모든 블록을 클린아웃한다.
로그 경합
  • 'log file sync' / 'log file parallel write' 이벤트에 대한 대기시간으로
    로그 경합을 확인할 수 있다.
  • 로그 경합의 원인
    • 어플리케이션에서 잦은 커밋
    • 느린 디바이스에 리두 저장
    • 자주 액세스 되는 다른 파일과 같은 디바이스에 리두 저장
    • 로그 디바이스를 버퍼링 방식으로 마운트 (OS버퍼링 + DB리두로그 버퍼 = 중복된 버퍼링 시 시스템 지연)
    • RAID-5 처럼 느린 RAID 기술로 리두로그 저장 (RAID5 쓰기성능 최악)
  • 디스크 배치 권고
    : 디스크 쓰기 경합을 최소화 한다.
    • 리두 로그 그룹 1 : 디스크 1 과 3
    • 리두 로그 그룹 2 : 디스크 2 와 4
    • 아카이브 : 디스크 5와 선택적 디스크 6(용량 큰 디스크)
    • 멤버 A, B (리두로그 그룹1) : 디스크 1,3
    • 멤버 C, D (리두로그 그룹2) : 디스크 2,4
  • ARCH 와 LGWR 간 경합이 존재하지 않도록 한다.
임시 테이블과 리두/언두
  • 이번 절에서는 "로깅과 관련해서 임시테이블은 어떻게 동작하는가" 에 대해 알아본다.
  • 10장 데이터베이스 테이블에서 임시테이블의 모든것을 다룰 것이다.
  • 임시테이블의 데이터 블록에 대해서는 리두를 생성하지 않는다. -> 복구 불가.
  • 임시테이블의 데이터 블록을 변경하면 리두를 생성하지 않는다. 그러나 언두를 생성한다. -> 언두에 대한 리두는 어느정도 발생.
  • 언두 발생 이유 : 트랜잭션 내 savepoint 까지 롤백할 수 있기 때문에 필요하다.
  • 임시테이블에서 발생되는 언두에 대한 리두로깅을 측정하는 테스트.
    SQLPLUS >create table perm
      2  ( x char(2000),
      3    y char(2000),
      4    z char(2000) )
      5  /
    
    Table created.
    
    SQLPLUS >create global temporary table temp
      2  ( x char(2000),
      3    y char(2000),
      4    z char(2000) )
      5  on commit preserve rows
      6  /
    
    Table created.
    
  • 리두 양 리포팅하는 프로시저
    create or replace procedure do_sql (p_sql in varchar2 )
    as
        l_start_redo    number;
        l_redo          number;
    begin
    
        l_start_redo := get_stat_val('redo size');
    
        execute immediate p_sql;
        commit ;
    
        l_redo := get_stat_val('redo size') - l_start_redo ;
    
        dbms_output.put_line
        ( to_char(l_redo,'99,999,999') || ' bytes of redo generated for "' ||
          substr(replace(p_sql, chr(10), ' ' ), 1, 25) || '" ... ') ;
    end;
    /
    
    Procedure created.
    
    
  • PERM, TEMP 테이블 대상으로 동일INSERT/UPDATE/DELETE 실행
    SQLPLUS > set serveroutput on format wrapped
    
    SQLPLUS > begin
    do_sql ( 'insert into perm
              select 1, 1, 1
                from all_objects
              where rownum <= 500' );
    do_sql ( 'insert into temp
              select 1, 1, 1
                from all_objects
              where rownum <= 500' );
    dbms_output.new_line ;
    
    do_sql('update perm set x = 2');
    do_sql('update temp set x = 2');
    dbms_output.new_line ;
    
    do_sql('delete from perm');
    do_sql('delete from temp');
    dbms_output.new_line ;
    
    end;
    /
    
    
    -- 결과
    
      3,120,244 bytes of redo generated for "insert into perm	  " ...
         41,332 bytes of redo generated for "insert into temp	  " ...
    
      2,126,084 bytes of redo generated for "update perm set x = 2" ...
      1,067,252 bytes of redo generated for "update temp set x = 2" ...
    
      3,200,608 bytes of redo generated for "delete from perm" ...
      3,156,716 bytes of redo generated for "delete from temp" ...
    
    
  • 결과정리
    • INSERT
      • 일반테이블 : 많은 리두 생성 / 임시테이블 : 리두 거의 생성안함.
        임시테이블에서는 언두만 로깅되며, INSERT 는 언두 데이터가 거의 일반,임시테이블에 모두 거의 없다.
    • UPDATE
      • 일반테이블 : 리두양이 임시테이블보다 약 두개 정도 생성됨. / 임시테이블 : after image(리두) 가 저장안되므로 두배차이.
    • DELETE
      • 일반테이블/임시테이블 모두 비슷하다. DELETE 에 대한 언두는 크기 때문에 일반,임시테이블 대상으로 비슷한 양이 생성된다.
  • INSERT 는 언두와 리두를 거의 또는 아예 만들지 않는다.
  • DELETE 는 일반 테이블과 동일한 양의 리두를 생성한다.
  • UPDATE 는 일반 테이블 리두 양의 반 정도를 생성한다.
  • 임시테이블에 인덱스가 있을 경우, 인덱스 변경에 대한 언두(언두에 대한 리두 까지) 생성된다.
    SQLPLUS >create index perm_idx on perm(x) ;
    
    Index created.
    
    SQLPLUS >create index temp_idx on temp(x);
    
    Index created.
    
  • 결과
    
    ..
      8,613,908 bytes of redo generated for "insert into perm	  " ...
      3,029,920 bytes of redo generated for "insert into temp	  " ...
    
      7,632,424 bytes of redo generated for "update perm set x = 2" ...
      5,210,624 bytes of redo generated for "update temp set x = 2" ...
    
      4,310,508 bytes of redo generated for "delete from perm" ...
      4,252,840 bytes of redo generated for "delete from temp" ...
    
    
  • 임시테이블에 인덱스 추가 시 생성되는 언두(리두) 양이 이전 결과보다 많아짐을 확인할 수 있다.
  • INSERT 를 언두 하는 것은 쉽지만 (0 byte로 되돌림)
    DELETE 를 언두하는 것은 기존 데이터의 bytes 수 만큼의 데이터를 INSERT 하는 것이므로 이 리두는 중요하다.
  • 임시테이블의 DELETE 는 피하자.
  • 커밋하거나 세션이 끝났을 때 임시테이블이 자동으로 비워지는 옵션을 선택하여 언두를 전혀 생성하지 않도록 하자.
  • 임시테이블을 수정하는 것은 가급적 피하자. INSERT, SELECT 작업으로만 사용해야 한다.
    리두를 생성하지 않는 임시테이블만의 장점을 최대로 활용할 수 있다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.