#커밋과 롤백 처리

(on) 커밋의 역할

COMMIT 응답속도는 트랜잭션 크기와 상관없이 일정하다 (하는일이 별로 없다 ㅋㅋ)
||Demo#1 (커밋속도)||
|{code:sql|borderStyle=solid}
SQL> 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.

|
|{code:java|borderStyle=solid}
# PL/SQL 의 커밋 시간 최적화(비동기 커밋) 기능 때문에 Java로 테스트
# 분산 트랜잭션 / 데이터 가드 / COMMIT WORK WRITE WAIT (11gR1 이후) 환경에서는 PL/SQL 내 비동기 커밋이 동작하지 않음
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]);
    Integer commitCnt = new Integer(arr[1]);

    con.setAutoCommit(false);

    doInserts( con, 1, 1 );
    
    Statement stmt = con.createStatement();
    stmt.execute( "begin dbms_monitor.session_trace_enable(waits=>true); end;" );
    
    doInserts( con, iters.intValue(), commitCnt.intValue() );
    
    con.close();
  }

  static void doInserts( 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.setInt(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 + " / " + committed );
  }
}

|

Demo#1 결과

• COMMIT을 자주 할수록 대기 시간도 길어짐
• 데이터베이스에 대한 왕복 횟수 증가 (네트워크 트래픽 증가)
• 커밋시 리두 로그가 디스크에 기록되기를 기다림 (LGWR / log file sync)
• 트랜잭션 크기와 관계없이 COMMIT 응답 시간이 일정 하다

Table created.

SQL> alter table big_table nologging;

Table altered.

SQL> declare
l_cnt number; 2
3 l_rows number := &1;
4 begin
5 insert /*+ append */
6 into big_table
7 select rownum, a.*
8 from all_objects a
9 where rownum <= &1;
10
11 l_cnt := sql%rowcount;
12
13 commit;
14
15 while (l_cnt < l_rows)
16 loop
17 insert /*+ APPEND */ into big_table
18 select rownum + l_cnt,
19 OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
20 OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
21 TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
22 from big_table
23 where rownum <= l_rows - l_cnt;
24 l_cnt := l_cnt + sql%rowcount;
25 commit;
26 end loop;
27 end;
28 /
Enter value for 1: 1000000
old 3: l_rows number := &1;
new 3: l_rows number := 1000000;
Enter value for 1: 1000000
old 9: where rownum <= &1;
new 9: where rownum <= 1000000;

PL/SQL procedure successfully completed.

SQL> select count(*) from big_table;

COUNT(*)

SQL> alter table big_table add constraint big_table_pk primary key (id);

Table altered.

SQL> exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent => 1);

PL/SQL procedure successfully completed.

|
||Demo#3 (일정한 응답 시간)||
|{code:sql|borderStyle=solid}
SQL> create or replace function get_stat_val ( p_name in varchar2 ) return number
  2  as
  3     l_val number;
  4  begin
  5     select b.value
  6       into l_val
  7       from v$statname a, v$mystat b
  8      where a.statistic# = b.statistic#
  9        and a.name = p_name;
 10
 11     return l_val;
 12  end;
 13  /

Function created.

SQL> set serveroutput on;
SQL> create table t as select * from big_table where 1 = 0;

Table created.

SQL> declare
  2     l_redo number;
  3     l_cpu  number;
  4     l_ela  number;
  5  begin
  6     dbms_output.put_line( '-' || '      Rows' || '        Redo' || '     CPU' || ' Elapsed' );
  7     for i in 1 .. 6
  8     loop
  9        l_redo := get_stat_val ( 'redo size' );
 10        insert into t select * from big_table where rownum <= power( 10, i );
 11        l_cpu := dbms_utility.get_cpu_time;
 12        l_ela := dbms_utility.get_time;
 13        commit work write wait;
 14        dbms_output.put_line
 15        ( '-' ||
 16          to_char( power( 10, i ), '9,999,999') ||
 17          to_char( ( get_stat_val( 'redo size' ) - l_redo ), '999,999,999' ) ||
 18          to_char( ( dbms_utility.get_cpu_time - l_cpu ), '999,999' ) ||
 19          to_char( ( dbms_utility.get_time - l_ela ), '999,999' ) );
 20     end loop;
 21  end;
 22  /
-      Rows        Redo     CPU Elapsed
-        10       7,472       0       1
-       100      11,356       0       0
-     1,000     117,288       0       2
-    10,000   1,201,208       0       8
-   100,000  12,253,736       0      30
- 1,000,000 122,109,428       0      26

PL/SQL procedure successfully completed.

|

(on) 롤백의 역할

PL/SQL procedure successfully completed.

|
h6. Demo#3 결과 (트랜잭션 크기와 ROLLBACK 응답 시간이 관계가 있다)

||롤백전작업 (커밋전작업과 같음)||롤백후작업||
|{code:none|borderStyle=solid}
 > 언두 블록, 변경된 데이터 블록 생성(SGA)
 > 위 두 항목에 대한 리두 생성(SGA)
 > 위의 블록 중 일부는 디스크에 플러시 되었음
 > 모든 락은 획득 되었음

|

 > 트랜잭션의 모든 변경을 언두 한다
 > V$LOCK 에서 모든 락 해제, 락 대기 트랜잭션 재개

|