꿈꾸는 개발자, DBA 커뮤니티 구루비

04 통계정보I

실행계획 수립시 CBO는 SQL 문장에서 액세스할 데이터 특성을 고려하기 위해 통계 정보를 이용한다.

옵티마이저가 참조하는 통계 정보(4가지)

테이블 통계
인덱스 통계
컬럼 통계(히스토그램 포함)
시스템 통계

(1) 테이블 통계


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 ;


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                  : 14
BLOCKS                    : 5
EMPTY_BLOCKS              : 0
AVG_SPACE                 : 0
AVG_ROW_LEN               : 37
AVG_SPACE_FREELIST_BLOCKS : 0
NUM_FREELIST_BLOCKS       : 0
SAMPLE_SIZE               : 14
LAST_ANALYZED             : 2010-04-14 오후 10:00:17
---------------------------------------------------------------

(2) 인덱스 통계


analyze INDEX emp_pk compute statistics ; -- 인덱스 통계 수집

analyze table emp compute statistics for ALL INDEXS ; -- 테이블에 속한 모든 인덱스의 통계 수집

analyze table emp compute statistics for TABLE for ALL INDEXS ; -- 테이블과 인덱스의 통계를 함께 수집


-- dbms_stats 패키지 이용
-- 특정 인덱스 통계만 수집
begin
    dbms_stats.gather_index_stats (ownname => 'scott', indname => 'emp_pk');
end;
/

-- 테이블에 속한 모든 인덱스 통계도 같이 수집
begin
    dbms_stats.gather_table_stats ('scoot', '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 = 'PK_EMP' ;

---------------------------------------------------------------
BLEVEL                  : 0
LEAF_BLOCKS             : 1
CLUSTERING_FACTOR       : 1
NUM_ROWS                : 14
DISTINCT_KEYS           : 14
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
SAMPLE_SIZE             : 14
LAST_ANALYZED           : 2010-04-14 오후 10:00:17
---------------------------------------------------------------

인덱스를 최초 생성하거나 재성성시 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) 컬럼 통계

analyze table emp compute statistics for ALL COLUMNS SIZE 254 ; -- 테이블, 인덱스를 제외한 컬럼의 통계정보만 수집

size 옵션은 히스토그램의 최대 버킷 개수를 지정하는 옵션
기본값은 75이고 1에서부터 254까지 허용된다.
히스토그램이 생성되지 않도록 하기 위해서는 1로 지정한다.


analyze table emp compute statistics for COLUMNS ENAME SIZE 10, SAL SIZE 20 ; -- ename과 sal만 통계 수집

analyze table emp compute statistics for COLUMNS SIZE 20 ENAME, SAL, HIREDATE ; -- ename, sal, hiredate 모두 20으로 지정

테이블, 인덱스, 컬럼에 대해서 동시에 통계 수집할 경우 아래와같이 한다.


analyze table emp compute statistics 
for table
for all indexes
for all indexed columns size 254 ; --

dbms_stats 패키지는 컬럼만 따로 통계를 수집하는 방법은 없다.

수집된 컬럼 통게 조회


-- 컬럼 통계 조회
SELECT num_distinct ,
       low_value ,
       high_value ,
       density ,
       num_nulls ,
       num_buckets ,
       last_analyzed ,
       sample_size ,
       avg_col_len ,
       histogram
FROM   dba_tab_columns
WHERE  owner = 'SCOTT'
AND    table_name = 'EMP'
AND    column_name = 'DEPTNO' ;

---------------------------------------------------------------
num_distinct   : 3
low_value      : C10B
HIGH_VALUE     : C11F
DENSITY        :0.0357142857142857
NUM_NULLS      : 0
NUM_BUCKETS    : 3
LAST_ANALYZED  : 2010-04-14 오후 10:00:17
SAMPLE_SIZE    : 14
AVG_COL_LEN    : 2
HISTOGRAM      : FREQUENCY
---------------------------------------------------------------

-- 컬럼 히스토그램 조회
SELECT endpoint_value ,
       endpoint_number
FROM   dba_histograms
WHERE  owner = 'SCOTT'
AND    table_name = 'EMP'
AND    column_name = 'DEPTNO'
ORDER  BY endpoint_value ;

ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
            10               3
            20               8
            30              14

(4) 시스템 통계

시스템 통계는 I/O, CPU 성능과 같은 하드웨어 특성을 측정한 것으로 아래와 같은 정보들을 포함한다.

CPU 속도
평균적인 Single Block I/O 속도
평균적인 Multiblock I/O 속도
평균적인 Multiblock I/O 개수
I/O 서브시스템의 최대 처리량(Throughput)
병렬 Slave의 평균적인 처리량(Throughput)

과거에 이 항목들은 고정된 상수였다.
설치된 운영 시스템의 사양과 운영 환경(OLTP, DW)이이 반영되지 않아 시스템이 다를 경우 잘못된 선택을 하기 쉬웠다.
옵티마이저가 하드웨어 및 애플리케이션의 특성을 시스템 통계에 반영합으로써 옵티마이저가 더욱 합리적인 선택을 할 수 있게 되었다(9i이상)


SQL> SET LINE 100
SQL> SET PAGESIZE 100
SQL> COLUMN sname FORMAT A20
SQL> COLUMN pname FORMAT A20
SQL> COLUMN pval2 FORMAT A40
SQL>
SQL> SELECT sname ,
  2         pname ,
  3         pval1 ,
  4         pval2
  5  FROM   sys.aux_stats$ ;

SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- ----------------------------------------
SYSSTATS_INFO        STATUS                          COMPLETED
SYSSTATS_INFO        DSTART                          04-17-2010 09:28
SYSSTATS_INFO        DSTOP                           04-17-2010 09:28
SYSSTATS_INFO        FLAGS                         1
SYSSTATS_MAIN        CPUSPEEDNW           470.445344     
SYSSTATS_MAIN        IOSEEKTIM                    10       NoWorkload(10g 추가)
SYSSTATS_MAIN        IOTFRSPEED                 4096    
SYSSTATS_MAIN        SREADTIM                      5    
SYSSTATS_MAIN        MREADTIM                     30      
SYSSTATS_MAIN        CPUSPEED                    500       Workload(9i 이후)
SYSSTATS_MAIN        MBRC                         12      
SYSSTATS_MAIN        MAXTHR                524288000      
SYSSTATS_MAIN        SLAVETHR                5242880    

13 개의 행이 선택되었습니다.

Workload 시스템 통계
9i에서 도입된 Workload 시스템 통계는, 애플리케이션으로부터 일정 시간동안 발생한 시스템 부하를 측정,보관함으로써 그 특성을 최적화 과정에 반영할 수 있게 한 기능이다.
Workload 시스템 통계 항목에는 아래와 같이 6가지가 있다.

통계항목	설명
spuspeed	현재 시스템에서 단일 CPU가 초당 수행할 수 있는 표준 오퍼레이션 개수(단위 : 백만/초)
sreadtim	평균적인 Single Block I/O 속도(단위 : ms = 1/1000초)
mreadtim	평균적인 Multi Block I/O 속도(단위 : ms = 1/1000초)
mbrc	Multi Block I/O 방식을 사용할 때 평균적으로 읽은 블록 수
maxthr	I/O 서버시스템의 최대 처리량(단위 : 바이트/초)
slavethr	병렬 Slave의 평균적인 처리량(eksdnl : 바이트/초)

앞에서 보았다 sys.aux_stats$ 조회결과를 해석하면 다음과같다.

단일 CPU로 초당 500백만 개의 표준 오퍼레이션을 수행할 수 있다.
Single Block I/O 속도는 평균적으로 5 ms 이다.
Multi Block I/O 속도는 평균적으로 30 ms 이다.
Multi Block I/O 방식을 사용할 때 평균적으로 12개 블록 씩 읽는다.
I/O 서브시스템으로부터 초당 최대 500MB를 읽을 수 있다.
평균적으로 병렬 Slave가 초당 5MB를 읽는다.

뒤에서 설명할 NoWorkload 시스템은 오라클이 무작위로 I/O를 발생시켜 측정한 값인 반면 Workload 시스템 통께는 실제 애플리케이션에서 발생하는 부하를 측정한 값이다.
때문에 Workload 시스템 통께를 제대로 활용하려면 통계 수집 전략을 잘 세워야 한다.

운영서버에서 수집한 정보를 테스트 서버로 복제할 때를 기준으로 시스템 통게 수집 방법과 절차를 설명하면 다음과 같다.

1. 시스템 통계를 담을 테이블(mystats)를 생성


begin
    dbms_stats.create_stat_table(
        ownname          => USER,
        stattab          => 'mystats',
        tblspace         => 'USERS',
        global_temporary => FALSE
    );
end;
/

2. 시스템 통계를 수집


<방법1>
begin
    dbms_stats.gather_system_stats(
        gathering_mode => 'interval',
        interval       => 720,
        stattab        => 'mystats',
        statid         => 'OLTP'
    ); -- 720분 동안 수집하고 자동으로 멈춤
end;
/

<방법2>
begin
dbms_stats.gather_system_stats(
gathering_mode => 'start',
stattab => 'mystats',
statid => 'OLTP'
);
end;
/

begin
dbms_stats.gather_system_stats(
gathering_mode => 'stop',
stattab => 'mystats',
statid => 'OLTP'
); -- 명시적으로 통계 수집을 중지시켜야 함
end;
/



참고로, 운영중인 시스템 통계를 mystats에 저장하려면 아래 명령어 사용

{code:sql}
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);
    dbms_stats.set_system_stats('MAXTHR', 524288000);
    dbms_stats.set_system_stats('SLAVETHR', 5242880);
end;
/

5. 시스템 통게를 적용하더라도 캐싱되어 있는 실행계획에는 영향을 미치지 않으로 Shared Pool Flush


alter system flush shared_pool ;

NoWorkload 시스템 통계
관리자가 명시적으로 선택하지 않더라도 CPU 비용 모델이 기본 비용 모델로 사용하기 위해서 오라클 10g에서 NoWorkload 시스템 통계를 도입하였다.
CPU 비용 모델은 시스템 통계가 있을때만 활욜되기때문이다.

No Workload 시스템 통계 항목과 (처음 데이터베이스를 기동할 때 설정되는) 기본 값은 다음표와 같다.

통계 항목	기본값		설명
cpuspeednw	데이터베이스 최초 기동 시 측정된 값	NoWorkload 상태에서 측정된 CPU 속도(eksdnl : Millions/sec)
ioseektim	10ms	I/O Seek Time을 뜻하며, 데이터를 읽으려고 디스크 헤드(head)를 옮기는 데 걸리는 시간을 나타낸다. 대개 5~15ms의 수치를 보이며, 디스크 회전 속도와 디스키 또는 RAID 스팩에 따라 달라진다.
		io seek time = seek time + latency time + operating system overhead time
iotfrspeed	4096 bytes/ms	I/O Transfer 속도를 뜻하며, 하나의 OS 프로세스가 I/O 서브 시스템으로부터 데이터를 읽는 속도를 나타낸다. 이 값은 초당 몇 MB에서 수백 MB까지 아주 다양하다.

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 통계와 주요한 차이점은 통계를 수집하는 방식에 있다. 즉, Workload 는 실제 애플리케이션에서 발생하는 부하를 기준으로 각 항목의 통계치를 측정하는 반면 NoWorkload는 모든 데이터파일 중에서 무작위로 I/O를 발생시켜 통계를 수집한다.
따라서, 시스템 부하 정도가 심할때 NoWorkload 시스템 통께를 수집하면 구해진 값들도 달라진다.

NoWorkload 시스템 통계 수집시는 아래와 같이 수행하면 된다.


begin
    dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD');
end;
/

문서에 대하여

최초작성자 : 황종필
최초작성일 : 2010년 04월 30일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*