꿈꾸는 개발자, DBA 커뮤니티 구루비

#나쁜 트랜잭션 습관

Infomix, Sybase, SQL Server 는 각 문장 앞뒤로 BEGIN WORK / COMMIT(ROLLBACK) 를 삽입 한다, 왜냐면 락은 중요한 자원이며 읽기와 쓰기가 서로 블로킹 하기 때문에 동시성을 높이기 위해서 가능한 트랜잭션을 짧게 정의
bq. 오라클은 정반대 - 트랜잭션이 묵시적으로 시작되며, 자동커밋 안되며, 트랜잭션은 업무 단위에 데이터 무결성을 고려하여 커밋 하면됨, 읽기와 쓰기가 경합하지 않음
h3. (on) 루프에서 커밋하기
h6. (1) 성능 관련 이슈
bq. 자주 커밋한다고 해서 더 빨라지지 않는다
bq. 행 단위 / 벌크 방식 처리는 단일 SQL 문으로 처리 하는것 보다 느리다
bq. 행 단위 / 벌크 방식 처리는 시스템이 실패 했을 때를 고려 해야 한다, 단일 SQL 문은 그냥 다시 실행 하면 됨
bq. 쓰기 일관성에 의한 UPDATE 재시작 메커니즘에 대한 대안 으로서 분할 처리 하지 말고, LOCK TABLE 하자.
||Demo#1 (단일 SQL)||
|{code:sql|borderStyle=solid}
SQL> set serveroutput on;
SQL> drop table t;

Table dropped.

SQL> create table t as select * from all_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

SQL> variable n number
SQL> exec :n := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

SQL> update t set object_name = lower(object_name);

100926 rows updated.

SQL> exec dbms_output.put_line( (dbms_utility.get_cpu_time - :n) || ' cpu hsecs...' );
83 cpu hsecs...

PL/SQL procedure successfully completed.

|
||Demo#2 (행 단위 SQL)||
|{code:sql|borderStyle=solid}
-- 초기화(drop/create/dbms_stats)
SQL> exec :n := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

SQL> begin
  2     for x in ( select rowid rid, object_name, rownum r from t )
  3     loop
  4        update t
  5           set object_name = lower( x.object_name )
  6         where rowid = x.rid;
  7        if ( mod(x.r, 100) = 0 ) then
  8           commit;
  9        end if;
 10     end loop;
 11     commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line( (dbms_utility.get_cpu_time - :n) || ' cpu hsecs...' );
278 cpu hsecs...

PL/SQL procedure successfully completed.

|

Demo#3 (벌크 방식 SQL)
{code:sql	borderStyle=solid} -- 초기화(drop/create/dbms_stats) SQL> exec :n := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

SQL> declare
2 type ridArray is table of rowid;
3 type vcArray is table of t.object_name%type;
4
5 l_rids ridArray;
6 l_names vcArray;
7
8 cursor c is select rowid, object_name from t;
9 begin
10 open c;
11 loop
12 fetch c bulk collect into l_rids, l_names LIMIT 100;
13 forall i in 1 .. l_rids.count
14 update t
15 set object_name = lower(l_names(i))
16 where rowid = l_rids(i);
17 commit;
18 exit when c%notfound;
19 end loop;
20 close c;
21 end;
22 /

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line( (dbms_utility.get_cpu_time - :n) || ' cpu hsecs...' );
103 cpu hsecs...

PL/SQL procedure successfully completed.

|
h6. (2) snapshot too old
bq. 트랜잭션을 원자성에 맞는 크기로 설계할 수 있을 만큼 충분한 언두 공간을 구성 해야 함
bq. 작은 언두 공간으로 인해 구현된 루프 내 커밋은 ORA-01555 를 유발할 수 있음
||Demo#4 (ORA-01555)||
|{code:sql|borderStyle=solid}
SQL> create table t as select * from all_objects;

Table created.

SQL> create index t_idx on t ( object_name );

Index created.

SQL> exec dbms_stats.gather_table_stats( user, 'T', cascade => true );

PL/SQL procedure successfully completed.

-- UNDOTBS1
SQL> create undo tablespace undo_small
  2     datafile '/oradata1/undo_small.dbf'
  3     size 10m reuse
  4     autoextend off
  5     /

Tablespace created.

SQL> alter system set undo_tablespace = undo_small;

System altered.

SQL> begin
  2  for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
  3               from t where object_name > ' ' )
  4     loop
  5        update t
  6           set object_name = lower(x.object_name)
  7         where rowid = x.rid;
  8        if ( mod(x.r, 100) = 0 ) then
  9           commit;
 10        end if;
 11     end loop;
 12     commit;
 13  end;
 14  /
begin
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-06512: at line 2

SQL> begin
  2  for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
  3               from t where object_name > ' ' )
  4     loop
  5        update t
  6           set object_name = lower(x.object_name)
  7         where rowid = x.rid;
  8        if ( mod(x.r, 100) = 0 ) then
  9           null;
 10        end if;
 11     end loop;
 12     commit;
 13  end;
 14  /
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_SMALL'
ORA-06512: at line 5

-- ORA-01555 : UPDATE 작업의 일부는 처리되었지만, 나머지 작업은 처리되지 않음 (미지의 상태) / 루프내 커밋 환경에서는 ORA-01555 를 피할 수 없다
-- ORA-30036 : 작업이 ROLLBACK 된다 (일관적 상태) / 언두 공간이 충분 했다면 ORA-30036 오류를 피할 수 있다

-- V$UNDOSTAT 를 활용하여 적정한 언두 공간을 구성 하자

|

(3) 재시작 가능한 프로세스는 복잡한 로직을 요구한다

논리적 트랜잭션이 끝나기 전에 커밋(루프내 커밋)의 제일 큰 문제는 작업이 도중에 실패하면 트랜잭션이 미지의 상태가 되는것
bq. 이를 해결하기 위해 복잡한 로직(처리 대상의 분할)을 구현 하더라도 여러가지 문제가 있음 (균등한 분할이 안되는 문제, 반복적인 스캔으로 인한 비효율 문제, t.object_name 이 변경되는 레코드의 처리 불확실성 등)
||Demo#5 (재시작 가능한 프로세스)||
|{code:sql|borderStyle=solid}
SQL> create table to_do
2 as
3 select distinct substr ( object_name, 1, 1 ) first_char
4 from T
5 /

Table created.

SQL> begin
2 for x in ( select * from to_do )
3 loop
4 update t set last_ddl_time = last_ddl_time + 1
5 where object_name like x.first_char || '%';
6
7 dbms_output.put_line( sql%rowcount || ' rows updated' );
8
9 delete from to_do where first_char = x.first_char;
10
11 commit;
12 end loop;
13 end;
14 /
35745 rows updated
26 rows updated
7 rows updated
18 rows updated
100926 rows updated
2269 rows updated
1 rows updated
1 rows updated

PL/SQL procedure successfully completed.

-- 단순하게 접근하라
– SQL로 할 수 있다면 SQL로, SQL로 할 수 없는 것은 PL/SQL로...
-- 코드량 최소화, 충분한 자원 할당, 오류 상황 고려

|
h3. (on) 자동커밋 사용하기
bq. ODBC, JDBC 는 DML 후 조용히 COMMIT 함, 아래와 같이 설정할 필요가 있음
||Demo#6 (AUTOCOMMIT)||
|{code:java|borderStyle=solid}
Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@database", "scott", "tiger");
conn.setAutoCommit(false);

|

나쁜 트랜잭션 습관 (by openlsw) [2012.10.17]

#나쁜 트랜잭션 습관

(3) 재시작 가능한 프로세스는 복잡한 로직을 요구한다