[성능] SQL 성능 분석

DB2에서는 수행된 SQL을 분석할 수 있는 db2 벤치마크 도구를 제공한다.

db2batch 라는 도구를 통하여 SQL이 수행된 시간, SQL이 수행되면서 사용된 bufferpool 정보들도 확인이 가능하다.

 

다양한 옵션이 많지만 장황하지 않는 선에서 다음과 같이 명령어를 정리해 본다.

db2batch -d 데이터베이스 -f SQL_파일명 -a ID/PWD -m 변수값 -z 출력_파일명 -iso cs -car cc -o r 1 p 2 e yes

 

가장 간단한 옵션은 –f 옵션까지만 주어서 SQL문을 실행시키면 된다.

원격에서 실행하는 경우 –a 옵션을 주어 접속 계정 정보를 명시적으로 지정하고, –m 변수를 통하여 동적 SQL을 수행할 수도 있다.

 

jennifer 등을 통하여 실행된 동적SQL의 변수 값이 추출 가능한데, SQL문과 변수 값 리스트가 별도로 나오기 때문에 “정적SQL”로 만드는 작업이 번거로울 수 있다.  이런 경우 –m 옵션을 통하여 동적으로 SQL 성능 분석을 편하게 할 수 있을 것 같다.

 

-z 옵션은 결과 출력 파일 지정하는 옵션이며, –iso 는 격리수준(isolation level)을 의미하며 V9.7 이후 currently committed 이 지원되므로 –car cc 옵션을 주어도 무방할 듯 하다.

 

-o 옵션에는 세부 옵션들이 다양하게 존재한다. r 옵션은 실행된 SQL의 return 값을 몇 개 반환하게 할 지를 결정하는 경우로 50건 이상 넘어가는 결과 값의 경우 샘플로 몇 개의 데이터를 확인하고자 할 때 지정하면 될 듯 하다.

p 옵션은 성능 분석을 위한 데이터 수집을 하도록 하는 옵션으로 2 인 경우는 실행된 SQL 수행시간 이외 application snapshot 정보도 수집해 준다. 숫자가 커질수록 수집되는 snapshot 정보는 많아 진다. 해당 SQL이 어떤 자원을 어떻게 사용했는지 확인하고자 한다면 (app. snapshot 정보로도 충분하다고 생각이 들지만 더욱 더 상세하게 수집하고자 하는 경우) 숫자를 조정하면 될 듯 하다.

 

e 옵션은 explain 정보를 수집하는 옵션인데, 테스트를 해 본 바 수집되지는 않았다.

 

1. Static SQL 테스트

   sample 데이터베이스의 employee 테이블을 대상으로한 select 문으로 수행하였다.

 

SQL 문

cat > sql.db2 <<EOF select * from emp where empno > 10 ; EOF

 

실행

db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,CURRENT SCHEMA)" db2batch -d sample -f sql.db2 -a db2inst/db2inst -z sql1.out -iso cs -car cc -o r 1 p 2 e yes

 

결과

* Timestamp: Tue Aug 27 2013 00:43:11 KORST --------------------------------------------- * SQL Statement Number 1: select * from emp where empno > 10 ; (생략) 데이터 1건 결과 * 41 row(s) fetched, 1 row(s) output. * Elapsed Time is:       0.011069 seconds             Monitoring Information Instance name                                  = db2inst (생략)             Application Snapshot Application handle                         = 49923 Application status                         = UOW Waiting (생략) * Summary Table: Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output --------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- ------------- Statement           1           1       0.011069       0.011069       0.011069        0.011069       0.011069             41             1 * Total Entries:              1 * Total Time:                 0.011069 seconds * Minimum Time:               0.011069 seconds * Maximum Time:               0.011069 seconds * Arithmetic Mean Time:       0.011069 seconds * Geometric Mean Time:        0.011069 seconds ---------------------------------------------

 

 

2. 동적SQL 예제(1)

SQL 문

cat > par.db2 <<EOF --#PARAM 1000 --#PARAM 'M' select * from emp where salary > ? and sex = ?; EOF

 

실행

db2batch -d sample -f par.db2 -z par.out -iso cs -car cc -o r 5 p 2

 

 

3. 동적SQL 예제(2)

SQL 문

cat > par.ins <<EOF 1000 'M' EOF cat > par.db2 <<EOF select * from emp where salary > ? and sex = ?; EOF

 

실행

db2batch -d sample -f par.db2 -m par.ins -z par.out -iso cs -car cc -o r 5 p 2