안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
현재 XpertMon 개발팀은 그동안 고객들이 요청하셨던 기능과 일반 다른 모니터링 툴에는
존재하지 않는 새로운 기능을 추가하는 업그레이드 작업을 진행중에 있습니다.
10월 초에 새로운 릴리스의 XpertMon 이 출시될 예정입니다.
많은 관심 부탁드립니다.
이번주에는 저번주에 이어 SQL Procedure 튜닝에 대해서 살펴보도록 하겠습니다.
많은 도움 되시길 바랍니다.
SQL Procedure 튜닝 II
SQL procedure VS SQL function
SQL procedure 와 SQL function 수행시간을 비교해보도록 하겠습니다.
- SQL procedure : get_dept_emps
- SQL function : get_dept_emps_f
프로그램을 작성하여, 위의 procedure 수행시간과 function 수행시간을 비교해보도록 하겠습니다.
XpertMon의 Report 기능을 이용하겠습니다.
- SQL procedure 호출 방식 : 프로그램안에서 procedure를 CALL 함 (test1)
- SQL function 방식 : 프로그램안에서 SQL function을 사용함 ( test2)
Report 기능을 이용한 수행시간 비교
- test1 : HJLEE.TEST1 은 SQL procedure 를 CALL 하는 프로그램
총 CPU 시간 : 0.57 , 소요시간 : 0.93
- test2 : HJLEE.TEST2 는 SQL function 을 호출하는 프로그램
총 CPU 시간 : 0.11 , 소요시간 : 0.54
위에서 보는것과 같이 SQL procedure로 CALL 하는 프로그램보다, inline SQL PL이
훨씬 속도도 빠르고, CPU도 적게 사용하는 것을 볼 수 있습니다.
따라서 SQL procedure를 SQL function으로 대체하는 것이 좋습니다.
V8.2에서는 SQL function 에서 update,delete,insert,merge 문장을 사용할 수 있습니다.
에러 핸들링 튜닝
SQL PL 로 프로시저를 작성하는 경우 에러를 체크하는 방법은 에러를 체크하는 핸들러를
작성하는 것입니다. 핸들러는 특정 에러조건이 발생하면 조건에 따라 처리하는 작업을 수행합니다.
핸들러가 작성되면, 모든 에러 정보는 로컬변수에 저장됩니다.
그리고, 프로시저안에 작성한 에러 핸들링 코드가 에러를 처리하는데 사용됩니다.
아래 예를 보겠습니다.
- 튜닝전 에러핸들링 procedure
- 튜닝후 에러핸들링 procedure
위의 튜닝전 procedure 에서는 핸들러 선언시 SQL Exception, SQL Warning, NOT FOUND 에
대한 SQLCODE와 SQLSTATE 값을 지역 변수에 저장하도록 하였습니다.
이것이 비용이 많이 소모됩니다.
NOT FOUND 나 SQL Warning에 대한 에러처리는 지역변수 할당없이 바로 체크하고,
그 외 에러들과 예상치 못한 경고들만 핸들링으로 처리하는 것이 CPU 시간과 수행시간을
줄이는 방법입니다.
효율적인 procedure 쿼리 작성
- 튜닝전 procedure
- 튜닝후 procedure
튜닝전 procedure에서는 다음과 같은 두가지 문제가 있습니다.
첫번째로, 특정 Row가 존재하는지를 점검하기 위해 Count로 row의 수를 세었는데,
이것은매우 비싼 옵션입니다.
두번째로, update하기 위한 Row가 존재하는지 여부를 판단하기 위해 count를 수행하는 것
역시 많은 비용이 드며, count 체크 후에도 update하기 위해서는 다시 데이터를 찾아야합니다.
따라서 튜닝 후의 쿼리처럼, count 는 1건 fetch로 변경하고, update전 데이터체크는
일단 update하고, update된 row의 수가 저장되는 ROW_COUNT 라는 변수를 체크하여 처리하는
로직으로 변경하는 것이 좋습니다.
PVM optimizer 의 특징
V7에서는 SQL로 Procedure를 작성하면 C와 Embedded SQL로 바뀌어서 컴파일되었는데,
V8에서는 PVM을 이용하여 자체적으로 컴파일되고 실행됩니다.
DB2 V8.2 PVM optimizer은 다음과 같이 SQL 을 optimizing 합니다.
- 대부분의 직접 변수 할당의 수행
- 루프 증가 같은 아주 기본적인 산술의 실행
- 여러 SET 문장을 단일 VALUES INTO 로 합침
- CASE 문장과 IF THEN ELSE 문장을 CASE 표현식으로 rewrite
- 기본적인 조건식 평가
- CASE 와 IF THEN ELSE 조건을 미리 계산하여 맞는 분기로 바로 이동
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
