4. 통계정보 I (by sunshiny) [2010.09.25]
04 통계정보 I
실행계획 수립시 CBO는 SQL 문장에서 액세스할 데이터 특성을 고려하기 위해 통계 정보를 이용한다.
옵티마이저가 참조하는 통계 정보(4가지)
- 테이블 통계
- 인덱스 통계
- 컬럼 통계(히스토그램 포함)
- 시스템 통계
(1) 테이블 통계
테이블 통계만 수집할 때는 아래 명령어를 사용하며, compute는 전수 검사, extimate는 표본 조사를 뜻한다.
analyze table emp compute statistics for TABELE ;
analyze table emp estimate statistics sample 5000 rows for TABLE ;
anlayze table emp estimate statistics sample 50 percent for TABLE ;
통계정보를 수집할 때 이제는 analyze 명령어를 사용하지 말라는 것이 오라클의 공식적인 입장이다.
아래는 dbms_stats 패키지를 이용하는 예시다.
인덱스 통계가 수집되지 않도록 cascade를 false로 지정했고, 컬럼 통계가 수집되지 않도록 하려고 `for columns` 옵션에 컬럼명을 지정하지 않았다.
begin
dbms_stats.gather_table_stats('scott', 'emp', cascade=>false, methode_opt=>'for columns') ;
end ;
/
-- 아래는 수집된 테이블 통계를 조회한 결과다.
SELECT num_rows ,
blocks ,
empty_blocks ,
avg_space ,
avg_row_len ,
avg_space_freelist_blocks ,
num_freelist_blocks ,
sample_size ,
last_analyzed
FROM dba_tables
WHERE owner = 'SCOTT'
AND table_name = 'EMP'
---------------------------------------------------------------
NUM_ROWS : 15
BLOCKS : 5
EMPTY_BLOCKS : 0
AVG_SPACE : 0
AVG_ROW_LEN : 37
AVG_SPACE_FREELIST_BLOCKS : 0
NUM_FREELIST_BLOCKS : 0
SAMPLE_SIZE : 15
LAST_ANALYZED : 25-SEP-2010
---------------------------------------------------------------
(2) 인덱스 통계
-- 인덱스 통계를 수집할 때는 아래 명령어를 사용한다.
analyze INDEX emp_pk compute statistics ;
-- 테이블에 속한 모든 인덱스 통계를 수집할 때는 아래 명령어를 사용한다.
analyze table emp compute statistics for ALL INDEXES ;
-- 테이블과 인덱스 통계를 함께 수집하려면 아래와 같이 한다.
analyze table emp compute statistics for TABLE for ALL INDEXES ;
-- 아래는 dbms_stats 패키지를 이용하는 방법이다.
-- 특정 인덱스 통계만 수집
begin
dbms_stats.gather_index_stats
( ownname => 'scott'
, indname => 'emp_pk') ;
end;
/
-- 테이블에 속한 모든 인덱스 통계도 같이 수집
begin
dbms_stats.gather_table_stats ('scott', 'emp', cascade=>true) ;
end;
/
-- 아래는 수집된 인덱스 통계를 조회한 결과다.
select blevel, leaf_blocks, clustering_factor, num_rows, distinct_keys
, avg_leaf_blocks_per_key, avg_data_blocks_per_key
, sample_size, last_analyzed
from dba_indexes
where owner = 'SCOTT'
and table_name = 'EMP'
and index_name = 'EMP_PK' ;
---------------------------------------------------------------
BLEVEL : 0
LEAF_BLOCKS : 1
CLUSTERING_FACTOR : 1
NUM_ROWS : 15
DISTINCT_KEYS : 15
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
SAMPLE_SIZE : 15
LAST_ANALYZED : 25-SEP-2010
---------------------------------------------------------------
참고로, 인덱스를 최초 생성하거나 재생성할 때 아래와 같이 compute statistics 옵션을 주면 자동으로 인덱스 통계까지 수집된다.
인덱스는 이미 정렬되 있으므로 통계정보 수집에 오랜 시간이 소요되지 않는다.
10g부터는 사용자가 이 옵션을 명시하지 않아도 오라클이 알아서 인덱스 통계까지 수집해 준다.(이 기능을 방지하려면 __optimizer_compute_index_stats_ 파라미터를 false로 설정하면 된다.)
create index emp_ename_idx on emp(ename) COMPUTE STATISTICS ;
alter index emp_ename_idx rebuild COMPUTE STATISTICS ;
(3) 컬럼 통계
size 옵션은 히스토그램의 최대 버킷 개수를 지정하는 옵션으로서, 1부터 254까지 허용된다.
size를 명시하지 않으면 오라클이 75를 기본 값으로 사용하므로 히스토그램이 생성되지 않도록 하고 싶을 때는 size 옵션을 1로 명시해야 한다.
-- 아래는 테이블, 인덱스 통계는 제외하고 컬럼 통계만 수집하는 방법이다.
analyze table emp compute statistics for ALL COLUMNS SIZE 254 ;
-- 일부 컬럼에 대한 통계만 수집할 때는 아래와 같이 한다.
analyze table emp compute statistics for COLUMNS ENAME SIZE 10, SAL SIZE 2O ;
-- 히스토그램 버킷 개수를 컬럼별로 지정하지 않고 똑같이 20으로 지정할 때는 아래와 같이 하면 된다.
analyze table emp compute statistics for COLUMNS SIZE 20 ENAME, SAL, HIREDATE ;
-- 물론 테이블, 인덱스, 컬럼 통계를 아래와 같이 동시에 수집할 수도 있다.(그만큼 통계정보를 수집하는데 많은 비용을 소요)
analyze table scott.emp compute statistics
for table
for all indexes
for all indexed columns size 254 ;
dbms_stats 패키지로 컬럼 통계만 따로 수집하는 방법은 없다.
앞에서 `(1) 테이블 통계`에서 보았듯이 테이블 통계와 항상 같이 수집된다.
-- 컬럼 통계 조회
SYS@ora10g>SELECT num_distinct ,
2 low_value ,
3 high_value ,
4 density ,
5 num_nulls ,
6 num_buckets ,
7 last_analyzed ,
8 sample_size ,
9 avg_col_len ,
10 histogram
11 FROM dba_tab_columns
12 WHERE owner = 'SCOTT'
13 AND table_name = 'EMP'
14 AND column_name = 'DEPTNO' ;
---------------------------------------------------------------
num_distinct : 4
low_value : C10B
HIGH_VALUE : C129
DENSITY : 0.033333333
NUM_NULLS : 0
NUM_BUCKETS : 4
LAST_ANALYZED : 26-SEP-2010
SAMPLE_SIZE : 15
AVG_COL_LEN : 2
HISTOGRAM : FREQUENCY
---------------------------------------------------------------
-- 컬럼 히스토그램 조회
SYS@ora10g>SELECT endpoint_value ,
2 endpoint_number
3 FROM dba_histograms
4 WHERE owner = 'SCOTT'
5 AND table_name = 'EMP'
6 AND column_name = 'DEPTNO'
7 ORDER BY endpoint_value ;
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
10 2
20 8
30 14
40 15
(4) 시스템 통계
시스템 통계는 I/O, CPU 성능과 같은 하드웨어 특성을 측정한 것으로 아래와 같은 정보들을 포함한다.
- CPU 속도
- 평균적인 Single Block I/O 속도
- 평균적인 Multiblock I/O 속도
- 평균적인 Multiblock I/O 개수
- I/O 서브시스템의 최대 처리량(Throughput)
- 병렬 Slave의 평균적인 처리량(Throughput)
과거에 이 항목들은 고정된 상수였다.
설치된 운영 시스템의 사양과 운영 환경(OLTP, DW)이 반영되지 않아 시스템이 다를 경우 잘못된 선택을 하기 쉬웠다.
옵티마이저가 하드웨어 및 애플리케이션의 특성을 시스템 통계에 반영함으로써 옵티마이저가 더욱 합리적인 선택을 할 수 있게 되었다(9i이상)
아래는 수집된 시스템 통계정보를 조회한 결과다.
이중 cpuspeednw, ioseektim, iotfrspeed는 10g에 추가된 NoWorkload 시스템 통계이고,
아래쪽 여섯 개 항목은 9i부터 있던 Workload 시스템 통계이다.
Workload 시스템 통계
9i에서 처음 도입도니 Workload 시스템 통계는, 애플리케이션으로부터 일정 시간 동안 발생한 시스템 부하를 측정, 보관함으로써 그 특성을 최적화 과정에 반영할 수 있게 한 기능이다.
통계를 수집하는 동안 애플리케이션이 I/O 집약적인 쿼리를 주로 수행했다면 통계정보에 그것이 반영될 것이므로 이를 적용한 이후 옵티마이저는 덜 I/O 집약적인 실행계획을 선택할 것이다.
Workload 시스템 통계 항목에는 아래 6가지가 있다.
위 설명에 따라 앞서 보았던 sys.aux_stats$ 조회결과를 해석하면 다음과 같다.
- 단일 CPU로 초당 1,149백만 개의 표준 오퍼레이션을 수행할 수 있다.
- Single Block I/O 속도는 평균적으로 5.253 ms이다.
- Multiblock I/O 속도는 평균적으로 3.122 ms이다.
- Multiblock I/O 방식을 사용할 때 평균적으로 15개 블록씩 읽는다.
- I/O 서브시스템으로부터 초당 최대 1.73GB를 읽을 수 있다.
- 평균적으로 병렬 Slave가 초당 20MB를 읽는다.
뒤에서 설명할 NoWorkload 시스템 통계는 오라클이 무작위로 I/O를 발생시켜 측정한 값인 반면
Workload 시스템 통계는 실제 애플리케이션에서 발생하는 부하를 측정한 값이다.
만약 수집기간 동안 애플리케이션에서 Full Table Scan이 발생하지 않는다면 mreadtim와 mbrc 항목이 측정되지 않을 것이며, 병렬 쿼리가 수행되지 않는다면 slavethr 항목이 측정되지 않는다.
운영 서버에서 수집한 정보를 테스트 서버로 복제할 때를 기준으로 시스템 통계 수집 방법과 절차를 설명하면 다음과 같다.
1. 시스템 통계를 담을 테이블(여기서는 mystats)를 생성한다.
begin
dbms_stats.create_stat_table(
ownname => USER
, stattab => 'mystats'
, tblspace => 'USER'
, global_temporary => FALSE
);
end;
/
2. 시스템 통계를 수집한다.
<방법 1>
begin
dbms_stats.gather_system_stats(
gathering_mode => 'interval'
, interval => 720
, stattab => 'mystats'
, statid => 'OLTP'
);
end;
/
<방법 2>
begin
dbms_stats.gather_system_stats(
gathering_mode => 'start'
, stattab => 'mystats'
, statid => 'OLTP'
);
end;
/
방법 1은 720분 동안 수집하고 자동으로 멈추지만 방법 2를 선택했다면 정해진 시간이 지난 뒤 아래와 같이 통계 수집을 중지시켜야 한다.
begin
dbms_stats.gather_system_stats(
gathering_mode => 'stop'
, stattab => 'mystats'
, statid => 'OLTP'
);
end;
/
참고로, 새로 수집하지 않고 이미 운영 서버에 적용 중이 시스템 통계를 mystats 테이블에 담으려면 아래 명령어를 이용한다.
begin
dbms_stats.export_system_stats('mystats', 'OLTP', USER);
end;
/
3. OS 상에서 Exp/Imp 명령어를 이용해 통계 테이블(여기서는 mystats)을 테스트 서버로 복제한다.
4. 복제한 통계 데이터를 테스트 서버에 적용한다.
begin
dbms_stats.import_system_stats('mystats', 'OLTP', USER);
end;
/
참고로, 운영 서버에 수집된 값을 메모해 아래와 같이 수동으로 설정할 수도 있다.
begin
dbms_stats.set_system_stats('CPUSPEED', 500);
dbms_stats.set_system_stats('SREADTIM', 5.0);
dbms_stats.set_system_stats('MREADTIM', 30.0);
dbms_stats.set_system_stats('MBRC', 12);
end;
/
5. 시스템 통계를 적용하더라도 기존에 캐싱돼 있던 실행계획에는 영향을 미치지 않으므로 Shared Pool을 비운다.
alter system flush shared_pool ;
Noworkload 시스템 통계
관리자가 명시적으로 선택하지 않더라도 CPU 비용 모델이 기본 비용 모델로 사용되게 하려고 오라클 10g에서 NoWorkload 시스템 통계를 도입하였다. CPU 비용 모델은 시스템 통계가 있을 때만 활성화되기 때문이다.
NoWorkload 시스템 통계 항목과(처음 데이터베이스를 기동할 때 설정되는) 기본 값은 다음 표와 같다.
Workload 시스템 통계를 수집하고 반영하는 순간 NoWorkload 시스템 통계는 무시된다.
Workload 시스템 통계가 수집되기 전까지는 아래 공식을 이용해 추정된 값을 사용한다.
- cpuspeed = cpuspeednw
- mbrc = db_file_multiblock_read_count
- sreadtim = ioseektim + db_block_size / iotfrspeed
- mreadtim = ioseektim + mbrc * _db_block_size / iotfrspeed
시스템 통게를 제대로 활용하려면 Workload 시스템 통계가 바람직하지만 이를 수집하기 어려운 환경이 존재하며, 그럴 때 NoWorkload 시스템 통계를 사용한다.
어떤 이유에서건 NoWorkload 시스템 통계를 이용하기로 했다면 기본 설정 값을 그대로 사용하지 말고, 적당한 부하를 준 상태에서 NoWorkload 시스템 통계를 수집해 주어야 한다.
Workload와 마찬가지로 부하를 준 상태에서 측정된 값을 사용해야 시스템 통계로서 의미가 있다.
Workload 통계와 주요 차이점은 통계를 수집하는 방식에 있다.
즉, Workload는 실제 애플리케이션에서 발생하는 부하를 기준으로 각 항목의 통계치를 측정하는 반면 NoWorkload는 모든 데이터 파일 중에서 오라클이 무작위로 I/O를 발생시켜 통계를 수집한다.
따라서 시스템 부하 정도가 심할 때 NoWorkload 시스템 통계를 수집하면 구해지니 값들도 달라진다.
이런 차이점을 이해한다면 Workload와 마찬가지로 NoWorkload도 시스템 부하가 어느 정도 있는 상태에서 수집되는 것이 바람직 하다는 것을 알 수 있다.
시스템 통계를 도입한 취지가 최적화 과정에 시스템 특성을 반영하자는 것임을 상기하자.
NoWorkload 시스템 통계를 수집할 때는 dbms_stats.gather_system_stats 프로시저를 아무런 인자도 주지 않고 호출하거나 gathering_mode 인자를 아래와 같이 지정하면 된다.
begin
dbms_stats.gather_system_stats( gathering_mode => 'NOWORKLOAD' );
end;
이 프로시저를 실행하는 동안 I/O 서브시스템에 약간의 부하가 발생하며, I/O 성능과 데이터베이스 크기에 따라 적게는 수 초, 길게는 수 분이 소요될 수 있다.
문서에 대하여
- 최초작성자 : ~sunshiny
- 최초작성일 : 2010년 09월 25일
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*