안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
이번주와 다음주에는 SQL Procedure 튜닝에 대해 살펴보도록 하겠습니다.
많은 도움 되시길 바랍니다.
SQL Procedure 튜닝 I
기본사항
아시는대로, V7에서 처음으로 Procedure 작성용 SQL(스칼라 변수, IF문과 WHILE 루프 같은) 이 소개되었습니다.
그전에는 C와 자바 언어로 stored procedure를 작성하였는데, V7에서부터 SQL 을 가지고 stored procedure를
작성할 수 있게 된 것입니다.
SQL 로 procedure가 생성되면, DB2 는 성능을 최대화하기 위하여 전체 procedure에서
SQL 쿼리부분만을 떼어내어, 컴파일하여 패키지의 섹션형태로 저장합니다.
나머지 procedure 로직은 컴파일하여 DLL(동적 링크 라이브러리) 로 만듭니다.
procedure가 수행되면, 계속적으로 DLL 과 SQL 문장 사이로 제어권이 왔다갔다 하게 되는
“context switch” 가 일어납니다.
DB2 V8에서, SQL Procedure는 “Unfenced Mode”로 실행됩니다. 바꿔 말하면, DB2 엔진과같은
번지 주소를 사용합니다. 그러므로 여기서 말하는 context switch라는 것은 운영체제에서
일어나는 것과 같은 full context switch가 아니라, DB2 안에서 layer의 변화정도로 보면됩니다.
Procedure내에서 context switch의 수를 줄이게 되면, procedure 수행빈도가 많거나,
procedure가 처리하는 데이터의 양이 많은 경우, 성능향상을 볼 수 있게 됩니다.
SQL Procedure 언어(SQL PL)는 처음에는 SQL Procedure 내에서만 사용할 수 있었는데,
V7.2가 되면서, SQL function 이나 trigger에서도 사용할 수 있게 되었습니다.
이러한 SQL PL을 inline SQL PL이라고 합니다.
SQL PL Procedure는 각각의 SQL 이 컴파일되어 패키지의 섹션으로 저장되는 것인 반면에
이름에서 추측하듯이 inline SQL PL 함수는 실행할 때마다 그때 그때 컴파일이 수행되면서
실행됩니다.
팁1 : 한번에 수행할 수 있다면 여러 문장보다는 한 개의 문장으로
- INSERT 문장 1
- INSERT 문장 2
INSERT 문장 2는 INSERT 문장 1의 수행시 필요한 시간의 대략 1/3이 소요됩니다.
문장이몇개 되지 않으면 사소할 수 있으나, 예를 들어 루프나 트리거에서 계속 반복되는
부분이라면 INSERT 문장 2와 같이 작성하는 것이 성능에 좋습니다.
이와 유사하게, 아래 SET 문장도 한 개의 VALUE 문장으로 바꾸는 것이 좋습니다.
- SET 문장 1
- VALUE 문장 1
다만 아래와 같이 어떠한 두 개의 문장이 서로 의존성이 있는 경우에는 아래와 같이 변경할 수 없습니다.
- SET 문장 2
- VALUE 문장 2
INTO 왼쪽의 부분은 병렬적으로 평가됩니다. 따라서 B에 할당된 값은 원시 문장에서
의도한것처럼 A에 할당된 값에 기본을 두고 작성되지 않습니다.
팁2 : 여러 SQL 문장을 한 개의 SQL 표현식으로
SQL 언어는 2가지의 조건 체크 방법을 제공하는데, 하나는 순차적 (IF와 CASE문) 방법이고,
또 하나는 함수적 (CASE 표현식) 방법입니다.
이중에서 CASE 표현식을 사용한 로직이 CASE문이나 IF문을 이용해 쓰여진 로직보다
더 간단하며, 효과적입니다.
아래 예를 살펴봅시다.
- IF 문장 1
IF 절은 단지 어떤값을 val 컬럼에 삽입해야 하는지 판단하기 위해서만 사용되었습니다.
procedure 로직과 data flow 사이의 context switch를 피하기 위해 아래처럼 하나의
CASE 표현식으로 처리하는 것이 더 효과적입니다.
- CASE 표현식 1
CASE 표현식은 scalar 값이 쓰여져야 되는 곳이면 어느든지 사용할 수 있습니다.
주로, 할당 문장의 오른쪽에 쓰이게 됩니다.
- IF 문장 2
- CASE 표현식 2
사실 더 좋은 방법은 아래와 같습니다.
- COALESCE 함수 사용
팁3 : SET-AT-A-TIME 문장법 사용
SQL DML 문장 만으로 표현할 수 없을 때 loop나 할당, 커서 사용과 같은 프로그램 로직을
이용하여 처리하게 됩니다. 그러나, 이런 처리시에 위에서 설명한 것과 같이, DML로
처리할 때보다 성능이 저하될 수 있는 위험요소가 존재할 수 있습니다.
- 변경 전
루프 부분만으로 아래와 같이 변경하며, 성능이 향상됩니다.
- 변경 후
- 최적의 변경 1
좀더 면밀히 보게 되면, 모든 코드의 블록은 sub-SELECT를 이용한 INSERT로 작성 될 수 있습니다.
변경 전에는 context switch 가 존재하지만, 변경 후에는 어떠한 context switch 도
존재하지 않아서 옵티마이저가 모든 계산을 전체적으로 최적화할 수 있었습니다.
최적의 변경 1은 가장 좋은 방법이긴 하지만, 각각의 INSERT 문이 다른 테이블에 대해
발생하게 되면 이렇게 SQL을 작성할 수 없습니다. 또한 아래와 같이 작성할 수도 있습니다.
- 최적의 변경 2
최적의 변경 2는 변경 전에서의 커서와 루프에 소비되는 시간이 없게 되므로, 성능을 향상할 수 있습니다.
팁4 : 커서 성능 향상
커서 성능을 향상시키려면 아래와 같이 두가지를 주의하여야 합니다.
첫째, 필요이상의 isolation을 사용하지 않습니다.
isolation이 높게 되면 DB2가 잠금을 많이 실행하고, 따라서 같은 자원에 대하여
응용프로그램들 사이에서 경쟁을 일으켜 동시성을 저하시킵니다.
SQL Procedure의 isolation은 DB2_SQLROUTINE_PREPOPTS 레지스트리 변수를 이용해서 설정하면 됩니다.
다음과 같이 실행합니다.
db2set DB2_SQLROUTINE_PREPOPTS=”ISOLATION UR”
위의 기본값은 CS이며, 변경후에는 db2를 재시작해야 적용됩니다.
또한 아래와 같이 특정 SQL에 대해서 isolation을 별도로 설정할 수 있습니다.
위의 쿼리는 DB2_SQLROUTINE_PREPOPTS에 설정된 분리 레벨을 무시하고 분리 레벨을 UR로 실행할 것입니다.
두번째로는 커서를 사용할 때 row blocking 을 사용할 수 있도록 해야 한다는 것입니다.
row blocking을 사용하면, 한번의 조작으로 여러 row를 검색할 수 있게 되므로 DBM 의
오버헤드를 줄일 수 있습니다. 특히 대용량의 데이터를 검색하는 경우 성능에 매우
중요한 영향을 미치게 됩니다. DB2 row blocking 은 갱신가능 커서에서는 일어나지 않으며,
읽기만 가능한 커서에서 일어납니다.
따라서 읽기 가능 커서인 경우에는 FOR READ ONLY 라는 문장을 적어주십시오.
아니면 BLOCKING ALL 옵션을 주어서 바인드를 하십시오.
바인드옵션 역시 위의 레지스트리변수를 이용하여 설정할 수 있습니다.
db2set DB2_SQLROUTINE_PREPOPTS=”BLOCKING ALL”
팁5 : 데이터 추출시 SQL procedure 보다는 SQL function 사용
앞에서도 잠깐 언급했듯이 SQL procedure와 SQL function 은 수행되는 방법이 다릅니다.
SQL procedure는 생성될 때, 각각의 SQL이 컴파일되어 패키지의 섹션으로 저장됩니다.
따라서, procedure가 재생성되거나, 관련 패키지가 리바인드 되지 않는한은 재 컴파일 되지 않습니다.
반면에, SQL function 은 쿼리가 따로 컴파일되지 않고, function 몸체와 함께 하나로 컴파일됩니다.
따라서 함수를 수행할때마다 컴파일이 일어납니다.
또한 SQL function에서는 SQL procedure와는 다르게, context switch가 일어나지 않습니다.
이런 차이로, 프로그램 로직부분을 수행하는 경우에는 SQL function 이 더 빠르게 수행됩니다.
그러나 SQL function 에서 사용할 수 있는 SQL 은 제약이 있습니다.
데이터를 변경하는 SQL 문장 (INSERT, UPDATE,DELETE..) 을 사용할 수 없고, select 문장에서도
CALL문이나, 커서, 조건 핸들링 등의 문장은 사용할 수 없습니다.
이런 제약에도 불구하고, 대부분의 SQL Procedure들은 부작용 없이 SQL function로의
전환이 가능합니다. 아래 예를 보겠습니다.
- SQL procedure 예
- SQL function으로 변경 예
작성후 procedure는 call 문을 이용하고, function은 values 문장이나 Select 문장을 이용하여 호출합니다.
- SQL function 사용방법 1
- SQL function 사용방법 2
팁6 : 임시 테이블을 사용
임시 테이블을 사용합니다. 임시 테이블은 일반테이블보다 사용시 성능이 좋습니다.
이유는 다음과 같습니다.
- 임시 테이블은 카탈로그에 등록되지 않기 때문입니다.
- 임시 테이블은 그것을 생성한 응용프로그램에서만 접근이 가능하기 때문에, 조작에 대해서 생성되는 잠금 현상이 없습니다.
- NOT LOGGED 옵션을 주는 경우 로깅을 하지 않기 때문입니다.
SQL procedure에서 임시 테이블을 사용하려면, 아래 예처럼 먼저 그 세션에서 테이블을 작성해야 합니다.
임시 테이블은 실행시간 중에만 존재하고, connect reset 이 수행되면 더 이상 존재하지 않습니다.
임시 테이블을 참조하는 쿼리는 static SQL 로 작성되었다 하더라도, dynamic 하게 컴파일됩니다.
즉 다른 dynamic SQL 처럼 컴파일시 패키지 캐쉬에 저장되고, 패키지 캐쉬에서 찾을 수 없는
경우에만 재컴파일이 일어나게 됩니다.
만약 여러 개의 거대한 임시테이블을 작성하고, 그것에 대해 쿼리를 수행할 계획이라면,
인덱스를 생성하고, runstats 를 수행하는 것이 좋습니다.
만약, 임시테이블을 작성하고, 해당 테이블에 대해 결과 값을 리턴받아야한다면, 아래의
예처럼 반드시 nested compound SQL로 작성해야 합니다.
- 임시 테이블 사용 예
팁7 : 최신 통계정보를 유지
SQL procedure가 생성될 때, 각각의 SQL 쿼리는 컴파일되어 패키지내의 섹션으로 저장된다고
하였습니다. DB2 optimizer는 컴파일시에 테이블의 통계정보, 사용가능한 인덱스정보등을
기본으로 하여 procedure의 access plan을 결정합니다.
만약 통계정보가 변하게 되면, procedure와 연관된 패키지를 리바인드하는 것이 좋습니다.
SQL Procedure과 관련된 패키지를 리바인드 하려면 REBIND_ROUTINE_PACKAGE라는 고유의
Procedure를 사용할 수 있습니다. 다음과 같이 할 수 있습니다.
XpertMon for DB2 UDB V2
DB 사업부
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail :
hjlee@iteg.co.kr
URL :
http://iteg.co.kr
