안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
저번주에 말씀드린대로 현재 XpertMon 업그레이드 작업이 진행중입니다.
그동안 고객여러분이 요청하셨던 많은 사항들이 추가될 예정입니다. 기대해 주십시오.
이번주와 다음주에는 V8.2 에서 새로이 변화된 Runstats 에 대해 다뤄보도록 하겠습니다.
Runstats 의 중요성은 모두 아시죠?
그럼 많은 도움이 되시길 바라며 한주간 건강하십시오.
V8.2 에서의 Runstats I
Runstats 의 중요성
DB2 optimizer는 최선의 access path를 결정하기 위해 catalog 통계를 사용합니다.
그리고 catalog통계를 갱신하는 주된 방법은 Runstats utility를 실행하는 것입니다.
User 테이블의 변화가 있을 때 catalog 통계 테이블은 자동적으로 수정되지 않습니다.
Runstats 명령어를 수행하면 테이블에 대한 가장 마지막 정보를 Catalog 테이블에 업데이트합니다.
Runstats 가 수행되었는지 여부는 아래 컬럼을 조회해보면 알 수 있습니다.
수행되지 않은 경우는,
- syscat.tables 의 CARD= -1 혹은 STATS_TIME is NULL ( 테이블에 대해 미실행)
- syscat.indexes 의 NLEAF=-1 혹은 NLEVELS= -1 혹은 FULLKEYCARD = -1 혹은 STATS_TIME is NULL ( 인덱스에 대해 미실행)
Runstats 수행 시점
다음과 같은 경우에는 테이블과 인덱스에 대한 Runstats 를 수행해야 합니다.
- 테이블에 데이터가 LOAD되고 적합한 인덱스가 생성된 후
- LOAD하기 전에 인덱스를 생성하고 LOAD하는 동안 통계를 수집하는 것이 더 나음
- 새로운 인덱스를 생성한 후
- REORG Utility로 테이블을 재구성한 후
- 테이블과 인덱스에 대규모의 INSERT, DELETE, UPDATE가 발생한 후
- Prefetch Size가 변경된 후
- Redistribute Database Partition Group Utility가 실행된 후
Runstats 전후 변경사항은 Query Explain 의 결과값을 비교하면서 알 수 있습니다.
Runstats 수행후에는 Statistics SQL을 포함한 Package를 REBIND 해주어야 합니다.
db2rbind 명령어를 수행하면 데이터베이스 내의 모든 패키지를 Rebind 할 수 있습니다.
또한 현재 Package Cache내에 있는 모든 저장된 Dynamic SQL문을 삭제하기 위해
FLUSH PACKAGE 명령어를 사용해야 합니다.
기본 Runstats 예제
- 특정 컬럼에 통계 수집
|
RUNSTATS ON TABLE db2admin.department ON COLUMNS (deptno, deptname)
|
- Key 컬럼에 통계 수집 : index 를 구성하고 있는 컬럼에 대한 통계만 수집
|
RUNSTATS ON TABLE db2admin.department ON KEY COLUMNS
|
- 모든 인덱스 Catalog 통계 수집
|
RUNSTATS ON TABLE db2admin.department AND INDEXES ALL
|
- 특정 인덱스 3개만 Catalog 통계 수집
|
RUNSTATS ON TABLE db2admin.department FOR INDEXES db2admin.INX1, db2admin.INX2, db2admin.INX3
|
- 인덱스만 통계 수집
|
RUNSTATS ON TABLE db2admin.department FOR INDEXES ALL
|
With distribution 옵션
데이터의 분포가 일정하지 않을 때 with distribution 옵션을 주고 Runstats 를 실행합니다.
기본적으로 Catalog 통계 테이블은 데이터의 가장 큰 값과 작은 값에 대한 정보를
가지고 있고, optimizer는 데이터 값이 이 사이에 분포한다고 추정하고 Access path를
결정합니다. 만약 중복된 데이터 값이 많고, 일정하지 않으면 데이터별 분포자료 정보를
주어 optimizer의 판단을 돕는 것이 좋습니다.
- 테이블에 대한 distribution 통계 수집과 인덱스 통계 수집
|
RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND INDEXES ALL
|
- 특정 컬럼은 기본정보를, 특정 컬럼은 distribution 통계 정보를 수집
|
RUNSTATS ON TABLE db2admin.department ON COLUMNS (deptno, deptname) WITH DISTRIBUTION ON COLUMNS (mgrno, admrdept)
|
frequency, quantile 정보
with distribution 옵션으로 runstats 를 수행하면 frequency와 quantile 통계정보를
수집하게 됩니다. 어느정도 데이터를 수집할 것인지는 DB CFG 의 num_freqvalues과
num_quantiles 값에 의해 결정됩니다.
위의 CFG component에서 볼 수 있는 것처럼 num_freqvalues의 default 값은 10이며,
num_quantiles 값은 20입니다. freqvalues 값은 가장 중복값이 많은 데이터 값과 컬럼에
대한 정보를 저장하는 것으로, 컬럼당 10개의 데이터가 저장된다는 의미입니다.
quantiles 값은 데이터 값이 다른 값과 비교하여 어떻게 분산되어 있는지 정보를 저장하는
것으로 컬럼당 20개의 데이터가 기본적으로 저장됩니다.
runstats 수행시 frequency의 개수와 quantile의 개수를 컬럼 그룹별로 지정하여 줄 수 있습니다.
분산 정보를 수집하면 Catalog 공간이 늘어나며, runstats 수행시 CPU와 메모리를
더 사용하게 됩니다. 따라서 중요한 컬럼에 대해서만 분산 정보를 수집하는 것이 좋습니다.
다음 같은 경우에는 분산 통계 옵션을 주지 않는 것이 좋습니다.
Runstats는 아래의 상태일 경우 분산 통계 정보를 수집하지 않습니다.
- num_freqvalues = 0 이고, num_quantiles = 0 혹은 1 인 경우
- 각 데이터 값이 unique할 때
- 컬럼타입이 LONG, LOB, 구조화컬럼일 경우
- 컬럼에 NOT-NULL 값이 한 개만 있을 경우
- extended index 이거나 declared temporary table 일 경우
Ex)
deptname 은 frequency=50, quantile = 100으로 , deptno는 frequency = 5와 quantile = 10 으로, 인덱스 idx1,idx2 에 대한 통계정보 수집
|
RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION ON COLUMNS (deptno, deptname NUM_FREQVALUES 50 NUM_QUANTILES 100) DEFAULT NUM_FREQVALUES 5 NUM_QUANTILES 10 AND INDEXES db2admin.IDX1, db2admin.IDX2
|
컬럼 그룹 통계 수집
컬럼 그룹 통계를 수행하면, 해당 컬럼 그룹에 대한 distinct 조합 값의 정보를 저장합니다.
기본 정보로는 db2 optimizer 가 데이터 상관관계를 알 수 없는데, 컬럼 그룹 통계 정보를
통해 좀 더 정확하게 db2 optimizer 가 multi predicate 에 대한 선택을 수행하도록 하여줍니다.
아직까지 컬럼 그룹 통계는 데이터가 균등하다는 가정하에 수집되며, 분산 통계 정보는
지원되지 않습니다.
- 컬럼 그룹 통계 수집 : 두개 그룹 ( deptno,deptname) 과 (admrdept,location)
|
RUNSTATS ON TABLE db2admin.department ON COLUMNS ((deptno, deptname), deptname, mrgno, (admrdept, location)
|
LIKE Statistics
Runstats 시에 Like 옵션을 주면 SYSIBM.SYSCOLUMNS 테이블의 SUB_COUNT 컬럼과
SUB_DELIM_LENGTH 컬럼에 추가적인 정보가 저장됩니다.
현재까지는 string 컬럼에 대한 정보만 수집되며, “column LIKE ‘%abc’ 와
column LIKE ‘%abc%’ “ 과 같은 predicate 시 optimizer가 보다 정확한 판단을 수행할 수
있도록 하여줍니다.
- 모든 컬럼 정보 수집.deptname 에 대해서는 LIKE 정보 수집
|
RUNSTATS ON TABLE db2admin.department ON ALL COLUMNS and COLUMNS (deptname LIKE STATISTICS)
|
V8.2 새로운 기능 - 통계 프로파일을 이용
V8.2에 추가된 기능으로, 특정 테이블에 대해서 어떤 식으로 통계정보를 수집할 것인지를
profile로 작성합니다. SET PROFILE 옵션을 주어 runstats 수행하면 catalog 테이블에
등록되거나 저장됩니다. statistics profile 을 update하려면 UPDATE PROFILE 명령어를
수행하면 됩니다. 단 DELETE PROFILE 은 없습니다.
- 통계 정보 수집하지 않고 statistics profile에만 등록
|
RUNSTATS ON TABLE db2admin.department AND INDEXES ALL SET PROFILE ONLY
|
- statistics profile을 등록하고 등록된 profile을 이용하여 통계 정보 수집
|
RUNSTATS ON TABLE db2admin.department AND INDEXES ALL SET PROFILE
|
- 통계 정보 수집하지 않고 statistics profile 만 변경
|
RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND INDEXES ALL UPDATE PROFILE ONLY
|
- statistics profile 쿼리
- statistics profile 을 이용하여 통계 수집
|
RUNSTATS ON TABLE db2admin.department USE PROFILE
|
Sampling Runstats
계속적으로 증가하는 데이터베이스에 대해 모든 테이블에 대한 runstats를 수행하는
것은 CPU 나 메모리 등의 자원 사용이 증가하는 원인이 됩니다.
또한 테이블에 RUNSTATS가 실행될 때마다 FULL 테이블 스캔이 수행됩니다.
그러나, 데이터 sampling으로는 데이터의 일부만 스캔하게 됩니다.
만약 쿼리의 내용이 전체 트랜드와 패턴을 조사하는 내용이며, 일정부분의 오차를 가져도
패턴과 트랜드를 알아내는데 충분하다면, data sampling 이 FULL 테이블 스캔보다는 효율적입니다.
SAMPLED DETAILED 옵션을 주면 인덱스 통계를 계산할 때 sampling이 사용되며,
detailed 정보를 수집하는데 필요한 시간과 자원소모가 줄어들게 됩니다.
- sampling을 이용한 index detailed 통계 정보 수집
|
RUNSTATS ON TABLE db2admin.department AND SAMPLED DETAILED INDEXES ALL
|
V8.2 새로운 기능 ? Row-level Bernoulli sampling
Row-level Bernoulli sampling 은 sargable predicate 에 맞는 테이블 데이터중 P percent의
sample을 얻어오는 것입니다. 매 페이지마다 I/O 가 일어나고, 무작위로 row가 선택됩니다.
비록 매 페이지마다 I/O 가 일어난다고는 하지만, 여전히 데이터를 처리할 때 필요한
CPU 시간을 절약할 수 있습니다.
특히 Runstats는 CPU 집중적인 작업이기 때문에 특히 더 효과를 볼 수 있습니다.
- Row의 10% 로 distribution 통계 수집
|
RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION TABLESAMPLE BERNOULLI (10)
|
V8.2 새로운 기능 ? system page-level sampling
system page level sampling 은 ROW 대신 PAGE가 샘플링 되고 각 페이지는 P% 확률로 선택되고,
100-P% 확률로 버려지게 됩니다.
이것역시 매 페이지마다 I/O 가 일어나지만, Bernoulli 나 FULL 테이블 스캔보다 I/O 를
절약할 수 있습니다. 왜냐하면 이 경우 prefetch를 수행하기 때문입니다.
Runstats 시 REPEATABLE 옵션을 주게 되면 테이블 데이터가 바뀌지 않는다는 가정하에
몇번을 수행해도 동일한 Sampling 결과를 출력합니다.
옵션을 적어줄 때 숫자값을 함께 주게 되는데, 이 값만큼 반복될때는 sampling 이
동일하다는 것을 나타냅니다.
- Row의 10% 로 distribution 통계 수집. 같은 sampleset 을 반복적으로 사용가능
|
RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION TABLESAMPLE BERNOULLI (10) REPEATABLE (1024)
|
- 데이터 페이지를 10%로 system page level sampling을 통해 통계 수집.
|
RUNSTATS ON TABLE db2admin.department AND INDEXES ALL TABLESAMPLE SYSTEM (10)
|
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
