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

14S_커밋과 롤백 처리




코드작성 시 리두로그 이용의 영향에 대해 살펴본다.

커밋의 역할

  • COMMIT은 트랜잭션 크기에 관계없이 일반적으로 매우 빠르게 처리.
  • 대다수 개발자는 개별 로우마다 건건히 커밋하는 식으로 트랜잭션 크기를 인위적으로 제한한다.
    (논리적인 작업 단위를 완료하고 커밋해야 한다.)
  • 잦은 커밋은 자원을 더 소모시킨다.
  1. 테이블 생성
    SQLPLUS> create table test
    2 ( id           number,
    3  code         varchar2(20),
    4  descr        varchar2(20),
    5  insert_user  varchar2(30),
    6  insert_date  date
    7 )
    8 /
    
    Table created.
    
  2. Java로 수행
    import java.sql.*; 
    
    public class perftest { 
        public static void main (String arr[]) throws Exception 
        {
            DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); 
            Connection con = DriverManager.getConnection 
    ("jdbc:oracle:thin:@localhost:1521:ora11gr2","scott","tiger"); 
    
            Integer iters = new Integer(arr[0] );  -- insert 할 전체 row 수
            Integer commitCnt = new Integer(arr[1]); -- 몇 개의 row 단위로 커밋할지
    
            con.setAutoCommit (false) ; 
            dolnserts( con, 1, 1 ); 
    
            Statement stmt = con.createStatement (); 
            stmt.execute( "begin dbms_monitor.session_trace_enable(waits=>true); end ;" );
            dolnserts( con, iters.intValue() , commitCnt.intValue() ); 
    
            con.close();
    }
    
    static void dolnserts(Connection con, int count, int commitCount ) 
    throws Exception 
    { 
        PreparedStatement ps = con.prepareStatement 
        ("insert into test " + "(id, code, descr,insert_user,insert_date)" + " values (?,?,?, user, sysdate)" );
    
        int rowcnt = 0; int committed =0;
        for (int i =0; i < count; i++ {
            ps.setlnt(1 ,i); 
            ps.setString(2,"PS . code"+i); 
            ps.setString(3,"PS . desc"+i); 
            ps.executeUpdate();
            rowcnt++;
            if ( rowcnt == commitCount )
            {
                con.commit() ; 
                rowcnt =0; 
                committed++;
            }
        }
        con.commit () ; 
    
        System.out.println
        ("pstatement rows/commitcnt = " + count + " I " + committed);
      }
    }
    
  3. row 10,000 개 입력 / 커밋 row 단위를 다르게 하여 실행한 TKPROF 파일 실행결과
    • 커밋을 자주할 수록 대기시간이 길어진다.
  • 데이터베이스 자원사용을 감소시키는데 효과가 있을 것이라는 것은 잘못된 생각.
  • 커밋은 비지니스 요구에 기반하여 트랜잭션 크기를 조절해야 한다.
  • COMMIT 비용 증가의 원인
    • 데이터베이스에 대한 왕복 횟수 증가로 더 많은 네트워크 트래픽이 발생.
    • 커밋할 때 마다 리두로그 디스크 플러시로 대기상태를 유발시킴. (log file sync 이벤트)
  • 트랜잭션 크기와 관계없이 COMMIT 응답 시간이 거의 일정할까 ?
    • 커밋 전 이미 완료된 작업
      1. 언두 블록은 SGA에 생성
      2. 변경 데이터 블록은 SGA에 생성
      3. 언두/데이터 블록에 대한 리두는 버퍼링되어 SGA에 생성
      4. 위 세 항목의 일부 데이터 조합은 이미 디스크에 플러시 되었을 것이다.
      5. 모든 락은 획득되었다.
    • 커밋 수행 시 남겨둔 작업
      1. SCN 생성. Commit 시 마다 SCN은 1씩 증가.
      • 실제 COMMIT 단계 : LGWR이 리두로그 버퍼에 남은 리두로그 엔트리를 디스크에 쓰고, SCN을 온라인 리두로그 파일에 기록.
      • V$TRANSACTION 에서 트랜잭션 엔트리가 제거됨. (커밋 완료 확인)
      • V$LOCK 에 기록된 모든 락은 해제.
      • 버퍼캐시에 남아있는 변경된 블록중 일부는 커밋 순간 FAST모드로 클린아웃됨.
        (블록 클린아웃 : 데이터베이스 블록 헤더에 저장된 락 관련 정보)
  • COMMIT 시 LGWR 이 수행하는 물리적 디스크I/O 작업이 가장 긴 작업이나, 주기적으로 리두로그버퍼의 내용을 플러시.
  • 장시간 수행되는 트랜잭션이어도 버퍼에 생성된 리두로그의 많은 부분이 커밋을 하기전에 이미 디스크에 플러시된다.
  • COMMIT 시 디스크에 기록되지 않은, 버퍼에 남아있는 리두로그가 디스크로 안전하게 저장될 때 까지 대기.
  • PL/SQL 커밋시간 최적화 기능
    • 11gR2 이전 버전에서 PL/SQL을 제외한 프로그래밍 언어에서
      LGWR 호출이 동기적으로 동작하여 LGWR이 쓰기완료할때 까지 대기한다.
    • PL/SQL 은 비동기방식으로 커밋을 수행하며, LGWR이 완료하기를 대기하지 않고
      커밋 호출하자마자 즉시 리턴한다.
    • PL/SQL 에서 커밋을 100번 후 클라이언트로 리턴하면, 최적화로 LGWR을 한번 대기한다.
    • 최적화가 되지만, PL/SQL 에서도 논리적 작업단위 종료 시 커밋하는 것이 바람직하다.
  • 가변적으로 리두 양을 생성하고, 이에 따른 INSERT, COMMIT 작업시간 측정 테스트.
    
    SQLPLUS >grant select on v_$statname to hong ;
    
    Grant succeeded.
    
    SQLPLUS >grant select on v_$mystat to hong ;
    
    Grant succeeded.
    
    
    SQLPLUS> create or replace function get_stat_val( p_name in varchar2 ) return number 
    as
    l_val number; 
    begin
    select b.value
       into l_val
     from v$statname a, v$mystat b
     where a.statistic# = b.statistic# 
       and a.name = p_name;
    
      return l_val ;
     end ;
     /
    
    Function created.
    
  • 작업 부하량 및 CPU 경과시간 측정을 위한 PL/SQL
    
    SQLPLUS >create table t as select * from all_objects ;
    
    Table created.
    
    SQLPLUS >create table big_table as select * from all_objects ;
    
    Table created.
    
    SQLPLUS> set serveroutput on
    
    SQLPLUS> declare
    l_redo number;
    l_cpu  number;
    l_ela  number;
    
    begin
        dbms_output.put_line
        ( '-' || '     Rows' || '      Redo' ||
          '      CPU' || ' Elapsed') ;
        for i in 1 .. 6
        loop
            l_redo := get_stat_val ('redo size');
            insert into t select * from big_table where rownum <= power(10,i) ;
            l_cpu := dbms_utility.get_cpu_time;
            l_ela := dbms_utility.get_time;
            commit work write wait ;
            dbms_output.put_line
            ( '-' ||
               to_char( power(10, i), '9,999,999') ||
               to_char( (get_stat_val('redo size')-l_redo), '999,999,999') ||
               to_char( (dbms_utility.get_cpu_time-l_cpu), '999,999') ||
               to_char( (dbms_utility.get_time-l_ela), '999,999') );
            end loop;
    end;
    
    /
    
    -      Rows	   Redo	    CPU Elapsed
    -	 10	  1,420       0       2
    -	100	  9,220       1       2
    -     1,000	 99,916       0       1
    -    10,000   1,012,712       0       3
    -   100,000  10,441,412       0       8
    - 1,000,000  59,240,536       0       7
    
    PL/SQL procedure successfully completed.
    
    * log buffer 4.6 MB / 300MB 온라인 로그파일 6개  (2개 멤버씩 1개의 그룹 = 3개 그룹)
    * 시간단위는 1/100초
    
  • 리두 양이 1,420 바이트부터 59MB 까지 생성
  • 롤백의 역할
    • 커밋을 롤백으로 바꾸면 전혀 다른 결과를 얻게된다.
      
      SQLPLUS> declare
      l_redo number;
      l_cpu  number;
      l_ela  number;
      
      begin
          dbms_output.put_line
          ( '-' || '     Rows' || '      Redo' ||
            '      CPU' || ' Elapsed') ;
          for i in 1 .. 6
          loop
              l_redo := get_stat_val ('redo size');
              insert into t select * from big_table where rownum <= power(10,i) ;
              l_cpu := dbms_utility.get_cpu_time;
              l_ela := dbms_utility.get_time;
              -- commit work write wait ;
              rollback ;
              dbms_output.put_line
              ( '-' ||
                 to_char( power(10, i), '9,999,999') ||
                 to_char( (get_stat_val('redo size')-l_redo), '999,999,999') ||
                 to_char( (dbms_utility.get_cpu_time-l_cpu), '999,999') ||
                 to_char( (dbms_utility.get_time-l_ela), '999,999') );
              end loop;
      end;
      
      /
      
      -     Rows	Redo	  CPU Elapsed
      -	 10	  1,528       1       0
      -	100	 10,072       0       0
      -     1,000	102,908       1       1
      -    10,000   1,047,952       0       1
      -   100,000  10,901,836       3       3
      - 1,000,000  61,741,852      19      40   <--
      
      PL/SQL procedure successfully completed.
      
      
  • 롤백을 시작하기 전에 데이터베이스는 이미 많은 작업을 수행.
    • 커밋 전 이미 완료된 작업
      • 언두 블록은 SGA에 생성
      • 변경 데이터 블록은 SGA에 생성
      • 언두/데이터 블록에 대한 리두는 버퍼링되어 SGA에 생성
      • 위 세 항목의 일부 데이터 조합은 이미 디스크에 플러시 되었을 것이다.
      • 모든 락은 획득되었다.
    • 롤백
      • 모든 변경을 언두.
      • 언두세그먼트의 데이터를 읽어 역순으로 작업.
      • 언두 엔트리를 적용한 것으로 표기.
      • 세션이 보유한 락 모두 해제. 큐에서 대기하던 모든 세션을 깨운다.
  • 커밋은 리두로그 버퍼에 남은 데이터를 플러시 할 뿐. 롤백에 비해 할일이 없다.
  • 커밋보다 롤백이 더 많은 비용이 든다.
  • 확실히 커밋해야 한다는 확신이 들 때 작업하라.

문서정보

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