안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
이번주는 XpertMon 을 이용한 DB2 UDB OLTP 튜닝 마지막 시간으로 , 문제 쿼리를 찾아내어
쿼리를 튜닝하는 방법에 대해 설명드리도록 하겠습니다.

---

XpertMon 을 이용한 DB2 UDB OLTP Tuning III

Database Query Optimizer
DB2 UDB는 데이터에 접근하는데 있어서 최적의 방법을 찾아내는 비용기반의 강력한
쿼리 옵티마이저를 갖고 있습니다. 쿼리 옵티마이저는 원문 쿼리를 최적화된 형태의 쿼리로
재작성하고, 다양한 쿼리 실행 계획 생성 및 각각의 CPU, 메모리, I/O 모델링을 통해
최소의 비용을 갖는 엑세스 플랜을 선택합니다.

Catalog 통계 정보
쿼리 옵티마이저는 SYSSTAT 카탈로그 뷰를 통해 데이터베이스 오브젝트 통계정보를
사용하여 데이터에 엑세스하는 최적의 경로를 찾아냅니다. 만약 현재 통계정보가 없다면,
옵티마이저는 부정확한 통계정보를 가지고 비효율적인 엑세스 플랜을 선택할지도 모릅니다.

기본적으로 새로 데이터베이스가 생성되면 runstats 명령이 수행될 때까지 데이터베이스의
오브젝트에 대한 통계정보는 수집되지 않습니다. 카탈로그 통계정보가 수집되지 않았다면
SYSSTAT카탈로그 뷰의 CARD, NPAGES, FPAGES 같은 칼럼의 값은 -1 로 나타날 것입니다.

- Runstats 수행 여부 확인

마지막 runstats 명령이 수행된 이후, 많은 update 작업이 일어났거나, 새로운 인덱스가
생성된 경우 runstats 명령을 다시 수행하여 현재의 테이블 및 인덱스의 통계정보를
갱신해 주는 것이 좋습니다.
이는 옵티마이저로 하여금 정확한 통계정보를 가지고 최적의 엑세스 플랜을 선택할 수
있게 하기 때문입니다.
- Catalog Component를 이용한 Runstats 수행

때로는 데이터베이스 내의 모든 테이블에 대해 runstats 명령을 수행해야 할 때도 있습니다.
이럴 경우 가장 간편한 방법은 reorgchk 명령을 사용하는 것입니다.

% db2 reorgchk update statistics on table all

현재 테이블 및 인덱스의 통계정보가 최신의 것인지 확인하고 싶을 때는 아래의 명령을
수행하여 통계정보가 수집된 시간을 확인할 수 있습니다.
- Runstats 수행여부 확인

만약 runstats가 수행되지 않았다면 stats_time 칼럼의 값으로 ‘-‘ 가 나올 것입니다.
통계정보가 수집되었다면 runstats가 수행된 시간소인이 찍힙니다.

문제 쿼리 찾아내기
한 application안에서 수백 개의 다른 SQL이 돌아갈 수 있습니다. 이때 어느 한 SQL이
잘못 코딩되었거나, 제대로 튜닝이 되지 않았을 경우 전체 시스템의 성능에 영향을
줄 수 있습니다. 어떻게 이런 나쁜 쿼리를 찾아 낼 수 있을까요?
dynamic SQL statement 스냅샷이 application 에서 돌아가는 dynamic SQL 문장에 대한
정보를 제공합니다.
XpertMon 은 Report Component 를 이용하여 문제 쿼리를 찾아낼 수 있습니다.
아래와 같이 Report 에서 SQL Report 를 클릭하여 데이터를 수집합니다.
- Report 기능 ON 화면

Report 를 ON 하였으면 테스트에 사용되는 프로그램 performer를 아래와 같이 수행합니다.
$ java performer
$ java performer

- SQL Report 결과 화면

총 4개의 SQL 이 수행되었음을 볼 수 있습니다. 위의 화면은 Top CPU Time별 SQL 화면입니다.
총 4개의 SQL 은 다음과 같습니다.

• SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =46401
• SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =85593
• UPDATE ACCOUNT SET BALANCE = ? WHERE ACCT_ID = ?
• INSERT INTO AUDITLOG VALUES (?,?,?,?,?)

위의 쿼리 중 처음 2개는 where 절의 ACCT_ID 값을 제외한 나머지는 동일합니다.
PERFORMER 프로그램에서는 파라미터 마커- ‘?’ -를 사용하지 않았습니다.
따라서 이 두 select 문장은 옵티마이저가 다른 SQL로 간주하여 별도로 compile 되고
추가의 오버헤드가 발생하게 됩니다.
반면에 insert 문장과 UPDATE 문장은 파라미터 마커를 사용했습니다.
따라서 같은 문장으로 간주하여 STMT COUNT 에 보면 2번 수행한 것으로 나타나는 것을
볼 수 있습니다.
이제 문제 SQL 문장을 찾아내 보도록 하겠습니다.
수행시간이 많은 순서대로 Report 결과를 살펴보도록 하겠습니다.
- 수행시간별 Top SQL
가장 수행시간이 길었던 SQL 은 위의 보이는 SQL 이며, 1번 수행되었고, 수행시간은
0.24초입니다. 평균 수행시간이 0.24 초로 이 쿼리는 보기보다는 그리 나쁘게 보이지
않습니다만 이 쿼리를 좀 더 다듬어 볼 수 있을까요?
읽은 행수(100,000건) 인 것으로 보아 테이블 스캔이 일어났다는 것을 알 수 있습니다.

Access Plan 에 대한 이해
엑세스 플랜 자체를 이해하고 느린 SQL의 근원을 밝혀 문제를 해결하는 방법에 대해
알아보겠습니다.앞에서 찾아낸 문제 쿼리문장의 엑세스 플랜을 통해 엑세스 플랜의 기본에
대해 설명 드리겠습니다.
Report 화면에서 오른쪽 마우스를 누르면 Explain 을 바로 수행할 수 있습니다.
Explain 메뉴를 누르면 Explain Component 로 이동하면서 해당 SQL 의 Access Plan 을
볼 수 있습니다.
- SQL Report 결과에서 바로 Explain 을 수행하는 화면

- Explain Component ? Access Plan 기본 화면

위의 화면이 Explain 의 기본 화면입니다.
화면 상단은 Access Plan 을 보여주며, 화면 하단은 Access Plan 을 분석하는데 필요한
상세정보를 나타냅니다.
원 쿼리 문장과 옵티마이저에 의해 다시 쓰여진 쿼리문장도 보여줍니다.
- Rewrite Query 화면
Access Plan 화면에서 나타나는 Operator에 대해서 설명하도록 하겠습니다.

앞의 0] 1]은 순서입니다. 아래부터 위의 순서로 수행된 것입니다.
[3482.516] 은 cost로서 이전 단계의 비용에 CPU 코스트를 추가한 누적치를 보여줍니다.
cost 의 단위는 timeron 입니다. timeron은 실제 소요시간과는 직접적인 비교대상이
아니지만, 데이터베이스 관리 프로그램이 필요로 하는 자원의 추정치와 대략적인 연관이
있습니다.
여기서 살펴볼 엑세스 플랜 operator 의 간략한 설명입니다.

• Data Operation Complete : 쿼리에서 사용자에게 데이터가 반환됨을 의미합니다.
• Fetch Table Data: 특정 레코드 ID를 이용해 테이블로부터 칼럼을 전달하는 것을 의미합니다.
• Table Scan : 데이터 페이지로부터 필요한 모든 데이터를 직접 추출하는 것을 의미합니다.
• Index Scan : 최적화된 시작/종료 조건을 통해 테이블의 인덱스를 조회해서, 정렬된 데이터 흐름을 생성합니다.

Hint 기능을 이용하면 모든 Operator 에 대한 설명을 보실 수 있습니다.
- Operator 에 대한 Hint 화면

Table Scan VS. Index Scan
옵티마이저는 적절한 인덱스가 없거나 인덱스 스캔이 비용이 더 많을 경우 일반적으로
테이블 스캔을 선택합니다. 테이블의 크기가 작거나 인덱스 클러스터 비율이 낮은 경우,
또는 쿼리가 테이블내의 거의 모든 데이터를 요구할 경우 인덱스 스캔의 비용이
더 높을 수 있습니다.
이전의 엑세스 플랜에서 select 문장의 총 비용은 3482.516 timeron 이었습니다.
단 한건의 데이터만을 원하는 select 문장에 있어서 테이블 스캔은 비용이 크다고 판단됩니다.
테이블 스캔대신 인덱스 스캔을 사용한다면 성능이 좀 더 나아질 수 있을 것입니다.
인덱스를 생성하고 Performer 프로그램을 수행해서 수행시간을 비교해보도록 하겠습니다.
이제 ACCOUNT 테이블에 인덱스를 생성하겠습니다.
아래와 같이 Index Information 에서 오른쪽 마우스를 누르면 “Create Index” 메뉴를
통해서 인덱스를 작성할 수 있습니다.

- SQL Runner 를 이용하여 Index 작성

옵티마이저가 새롭게 생성된 인덱스를 인식하도록 runstats를 수행합니다.
Catalog Component 를 이용하여 수행하도록 하겠습니다.
- Runstats 수행

Explain Component를 이용하여 다시 Access Plan을 수행해보도록 하겠습니다.
새로이 생성된 Access Plan 입니다.
- 새로운 Access Plan
acct_id 칼럼에 인덱스를 생성한 뒤 비용이 3482.516 에서 38.643 timeron 으로 떨어진
것을 확인할 수 있습니다.
- SQL Report 결과
인덱스 생성후 PERFORMER 프로그램을 수행한 결과에서도 Elapsed time 이 0.24초에서
0.01초로 줄어들었으며, Row Read 건수도 100,000건에서 1건으로 줄었음을 볼 수 있습니다.

---

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