태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

CLOB Concatentaion 줄이기

오라클 2009.08.21 14:02
한 고객사에서 PL/SQL 내에서 300KB가 넘는 SQL 문장을 동적으로 생성해야하는 요구 사항이 생겼습니다.

최초에 사용한 방법은 다음과 같았습니다.
(원래의 소스 코드를 매우 간단한 버전으로 수정했음을 알려 드립니다)

UKJA@ukja1021> create or replace function make_long_sql(p_idx in number)
  2  return clob
  3  is
  4  	     v_clob	     clob;
  5  begin
  6  	     v_clob := 'select ';
  7  	     for idx in 1 .. p_idx loop
  8  		     v_clob := v_clob||to_char(p_idx)||', '||to_char(p_idx+1)||','||to_char(p_idx+2)||',';
  9  	     end loop;
 10  	     v_clob := v_clob || '1 from dual';
 11  
 12  	     return v_clob;
 13  end;
 14  /

Function created.
아무런 특이 사항을 발견할 수 없습니다. CLOB 변수를 Concatenation을 통해서 합치는 매우 간단한 작업입니다. 즉, 개선 사항이 전혀 없어 보입니다. 그런데 이 함수를 수행해보면 무려 46초가 걸립니다.
UKJA@ukja1021> exec :c := make_long_sql(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.00
실제 고객사에서는 150KB 정도의 문자열을 동적으로 생성하는데 5분 이상이 걸렸습니다. 그래서 도움 요청이 왔죠.

제가 내린 처방은 다음과 같이 임시로 VARCHAR2 변수를 사용해서 CLOB에 대한 Concatenation 회수를 줄이는 것이었습니다.

UKJA@ukja1021> create or replace function make_long_sql2(p_idx in number)
  2  return clob
  3  is
  4  	     v_clob	     clob;
  5  	     v_varchar	     varchar2(1000);
  6  begin
  7  	     v_clob := 'select ';
  8  	     for idx in 1 .. p_idx loop
  9  		     v_varchar := to_char(p_idx)||', '||to_char(p_idx+1)||','||to_char(p_idx+2)||',';
 10  		     v_clob := v_clob||v_varchar;
 11  	     end loop;
 12  	     v_clob := v_clob || '1 from dual';
 13  
 14  	     return v_clob;
 15  end;
 16  /

Function created.
결과는 매우 극적입니다. 수행 시간이 42초에서 0.5초로 줄었습니다.
UKJA@ukja1021> exec :c := make_long_sql2(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.50
실제 고객사에서는 5분 작업이 5초 정도로 줄었습니다.

어디서 이런 큰 차이가 발생하는 것일까요? V$SESSTAT 뷰와 V$LATCH 뷰를 통해서 각각의 경우에 어떤 차이가 있는지를 보면 힌트를 얻을 수 있습니다.

NAME                                           VALUE1       VALUE2         DIFF
---------------------------------------- ------------ ------------ ------------
session logical reads                      17,376,991      133,035  -17,243,956
db block gets from cache                   15,764,933      102,971  -15,661,962
db block gets                              15,764,933      102,971  -15,661,962
consistent changes                          6,024,802       17,697   -6,007,105
db block changes                            6,024,814       17,710   -6,007,104
consistent gets from cache                  1,612,058       30,064   -1,581,994
consistent gets                             1,612,058       30,064   -1,581,994
free buffer requested                       1,432,143           49   -1,432,094
calls to get snapshot scn: kcmgss             480,030       40,019     -440,011
lob writes                                    120,008       10,004     -110,004
lob writes unaligned                          119,962       10,004     -109,958
lob reads                                      60,001       10,001      -50,000
...

LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS
------------------------------ ---------- ---------- ---------- ----------
cache buffers chains            -45091211          0          0   -1399062
object queue header operation    -2864253          0          0          0
cache buffers lru chain          -2864138          0          0          0
simulator hash latch              -164417          0          0          0
simulator lru latch               -112459          0          0     -51952
...
CLOB만 사용하는 경우(첫번째)와 VARCHAR2를 임시로 사용해서 Concatenation 회수를 줄인 경우(두번째)에 Logical Reads, LOB reads, LOB writes 등에서 큰 차이가 발생하고, 그만큼 latch 획득에서도 차이가 발생합니다. 이것이 성능 차이로 나타난 것입니다.

그 이유는 아래 글에서 잘 설명되어 있습니다.
http://jonathanlewis.wordpress.com/2009/07/09/concatenating-lobs/

LOB은 Concatenation에 취약한 특징을 지니고 있기 때문에 Concatenation의 회수 및 위치가 성능에 큰 영향을 미칠 수 있습니다. LOB를 사용할 때는 이점에 유의할 필요가 있겠습니다.

저작자 표시
신고
tags :
Trackback 0 : Comments 2
  1. oveRock 2009.08.21 14:40 신고 Modify/Delete Reply

    흥미롭네요.... 저는 오라클이 워낙 젬병이라 잘은 모르겠지만....
    페인트공 알고리즘과 연관이 있는 게 아닐까요?

    • 욱짜 2009.08.21 15:06 신고 Modify/Delete

      본문의 링크에 설명이 잘 나와요.

      v_clob := v_clob || 'x' || 'y';

      이렇게 하면 'x'을 붙일 때 임시 Lob를 만들고, 'y'를 붙일 때 또 임시 Lob을 만들게 됩니다. 그만큼 불필요한 작업이 더 생기게 됩니다.

      러시아 페인트 공과는 조금 다른 문제입니다.

Write a comment

티스토리 툴바