전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
- 코드작성 시 리두로그 이용의 영향에 대해 살펴본다.
커밋의 역할
- COMMIT은 트랜잭션 크기에 관계없이 일반적으로 매우 빠르게 처리.
- 대다수 개발자는 개별 로우마다 건건히 커밋하는 식으로 트랜잭션 크기를 인위적으로 제한한다. (논리적인 작업 단위를 완료하고 커밋해야 한다.)
- 잦은 커밋은 자원을 더 소모시킨다.
- 테이블 생성
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.
- 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);
}
}
- row 10,000 개 입력 / 커밋 row 단위를 다르게 하여 실행한 TKPROF 파일 실행결과
- 커밋을 자주할 수록 대기시간이 길어진다.
 |
- 데이터베이스 자원사용을 감소시키는데 효과가 있을 것이라는 것은 잘못된 생각.
- 커밋은 비지니스 요구에 기반하여 트랜잭션 크기를 조절해야 한다.
COMMIT 비용 증가의 원인
- 데이터베이스에 대한 왕복 횟수 증가로 더 많은 네트워크 트래픽이 발생.
- 커밋할 때 마다 리두로그 디스크 플러시로 대기상태를 유발시킴. (log file sync 이벤트)
트랜잭션 크기와 관계없이 COMMIT 응답 시간이 거의 일정할까 ?
- 커밋 전 이미 완료된 작업
- 언두 블록은 SGA에 생성
- 변경 데이터 블록은 SGA에 생성
- 언두/데이터 블록에 대한 리두는 버퍼링되어 SGA에 생성
- 위 세 항목의 일부 데이터 조합은 이미 디스크에 플러시 되었을 것이다.
- 모든 락은 획득되었다.
- 커밋 수행 시 남겨둔 작업
- 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에 생성
- 위 세 항목의 일부 데이터 조합은 이미 디스크에 플러시 되었을 것이다.
- 모든 락은 획득되었다.
- 롤백
- 모든 변경을 언두.
- 언두세그먼트의 데이터를 읽어 역순으로 작업.
- 언두 엔트리를 적용한 것으로 표기.
- 세션이 보유한 락 모두 해제. 큐에서 대기하던 모든 세션을 깨운다.
- 커밋은 리두로그 버퍼에 남은 데이터를 플러시 할 뿐. 롤백에 비해 할일이 없다.
- 커밋보다 롤백이 더 많은 비용이 든다.
- 확실히 커밋해야 한다는 확신이 들 때 작업하라.
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4027
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.