4. 통계정보 Ⅰ

조회 수 13187 추천 수 0 2011.04.24 23:11:34
토시리 *.42.166.122

실행계획 수립시 옵티마이져는 데이터 특성을 고려하기 위해 다음과 같은 통계정보를 참조한다.

  • 테이블 통계
  • 인덱스 통계
  • 컬럼 통계
  • 시스템 통계
이번 절에서는 통계정보에 대해 알아보자.



(0) ANALYZE문
    통계정보를 수집하기위한 분석함수이다.
    테이블, 인덱스, 컬럼의 통계정보를 각각 혹은 조합을 이루어 한번에 수집하는것이 가능하다.


   ①COMPUTE (完全)

      전체의 통계정보를 취득함, 

      테이블스캔, 소트등이 실행되므로 많은 부하가 걸리지만 그만큼 정확한 정보가 취득가능하다.

구문
ANALYZE TABLE 「스키마. 」<테이블명> COMPUTE STATISTICS
              「FOR TABLE」                                                                               -- 테이블정보 수집
              「FOR ALL INDEXES」 「FOR ALL INDEXED COLUMNS」                          -- 인덱스정보 수집
              「FOR COLUMNS」 「FOR ALL COLUMNS」                                            -- 컬럼정보 수집

ANALYZE INDEX 「스키마. 」<인덱스명> COMPUTE STATISTICS                       -- 인덱스정보만 수집


   ESTIMATE (予測)

      특정 셈플정보를 지정하여, 셈플로부터 전체를 예측하여 계산.

      COMPUTE에 비해 부하는 덜 걸림,

      그러나, 지정된 데이타의 상황에 따라 같은 블록에서 취득될수도 있고, 전부 다른 블록에서 취득될수도 있는등

      데이터에 따라 다른 결과를 보여준다. (정확성이 떨어짐)

구문
ANALYZE TABLE 「스키마. 」<테이블명> ESTIMATE STATISTICS
              「SAMPLE n {ROWS | PERCENT}」                                                  -- 셈플지정
              「FOR TABLE」 「FOR ALL INDEXES」

ANALYZE INDEX 「스키마. 」<인덱스명> ESTIMATE STATISTICS                    -- 인덱스정보만 수집
              「SAMPLE n {ROWS | PERCENT}」                                                  -- 셈플지정




(1) 테이블 통계

1. 테이블 통계정보
  • 행수 (몇행의 레코드가 존재하는지)
  • 블록수 (실제사용하는데이터블록수)
  • 행의 평균길이 (전컬럼의 데이터)


2. 통계정보 수집

analyze table scott.emp compute statistics

analyze table scott.emp estimate statistics sample 5000 rows for TABLE

analyze table scott.emp estimate statistics sample 50 percent for TABLE

    ※오라클의 공식적인 입장은 analyze보다 dbms_stats를 권장한다. (dbms_stats에 대해서는 8절에서 다룹니다.)


3. 통계수집결과 조회

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'

 

result.JPG
















(2) 인덱스 통계

1. 인덱스 통계정보
  • leaf, 블록수
  • 레벨 (브란치, 블록의 깊이)
  • 클러스터화계수


2. 통계정보 수집

analyze index scott.pk_emp compute statistics

analyze table scott.emp compute statistics for ALL INDEXES                           -- 테이블에 속한 모든 인덱스

analyze table scott.emp compute statistics for TABLE for ALL INDEXES             -- 테이블과 테이블에 속한 모든 인덱스

    

3. 통계수집결과 조회

select blevel, leaf_blocks, clustering_factor

       , 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'

 

result2.JPG

4. 인덱스통계 자동수집에 관해

    인덱스 생성시에 아래와 같이 옵션을 주면 자동으로 인덱스 통계까지 수집된다. 

create index emp_ename_idx on emp(ename) COMPUTE STATISTICS

alter index emp_ename_idx rebuild COMPUTE STATISTICS

    ※10g 부터는 디폴트 설정임. (제외시키려면, _optimizer_compute_index_stats파라미터를 false로 설정)




(3) 컬럼 통계

1. 컬럼 통계정보
  • 컬럼내의 고정값수
  • 컬럼내의 NULL값수
  • 데이터의 배분 (히스토리의 통계정보)

  ※컬럼 히스토리에 대해서는 6절에서 살펴보겠습니다.


2. 통계정보 수집

analyze table scott.emp compute statistics for ALL COLUMNS SIZE 254                            -- 모든 컬럼 (히스토리버킷수:254) 

analyze table scott.emp compute statistics for COLUMNS ENAME SIZE 10, SAL SIZE 20      -- 일부컬럼 (히스토리버킷수 10, 20)

analyze table scott.emp compute statistics for COLUMNS SIZE 20 ENAME, SAL, HIREDATE  -- 일부컬럼 (히스토리버킷수 全10)
analyze table emp compute statistics
          for TABLE for ALL INDEXES for ALL INDEXED COLUMNS SIZE 254                          -- 테이블, 인덱스, 컬럼 동시 

    

3. 통계수집결과 조회

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'


result3.JPG














(4) 시스템 통계

1. 시스템 통계정보
  • CPU속도
  • 평균적인 싱글블록 I/O속도
  • 평균적인 멀티블록 I/O속도
  • 평균적인 멀티블록 I/O개수
  • I/O 서브시스템의 최대 처리량
  • 병렬 슬레이브의 평균적인 처리량

  

과거에는 옵티마이져 개발팀 하드웨어에 맞춰진 고정값이였지만, 환경에 적합한 최적화된 선택을 옵티마이져가 하지 못하는 경우가 발생되어 오라클 9i부터는 하드웨어 및 어플리케이션에 맞는 시스템 통계를 수집하여 참고 하는 방식으로 진화되었다.


2. 시스템 통계정보 수집결과 조회

select sname, pname, pval1, pval2 from sys.aux_stats$


    result4.JPG

5행~7행 : NoWorkLoad로써, 오라클이 무작위로 I/O를 발생시켜 측정한값
8행~13행 : WorkLoad로써, 실제 어플리케이션에서 발생한 부하를 측정한값 (위는 실제 어플이 없었으므로 통계정보가 Null) 


3. WorkLoad 시스템 통계
   ①통계항목
  • SREADTIM : 평균적인 싱글블록 I/O속도 (ms)
  • MREADTIM : 평균적인 멀티블록 I/O속도 (ms)
  • CPUSPEED : 현재 시스템에서 단일 CPU가 초당 수행할 수 있는 표준 오퍼레이션 개수 (백만/초)
  • MBRC : 멀티블록 I/O방식을 사용할 때 평균적으로 읽은 블록수
  • MAXTHR : I/O 서브시스템의 최대 처리량 (바이트/초)
  • SLAVETHR : 병렬 슬레이브의 평균적인 처리량 (바이트/초) 


   ②통계수집정보를 테스트서버로 복제할 때의 수집방법과 절차
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');

end;

    ※스크립트 개시후 720분간 통계정보를 수집, 종료한다.


방법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;

    ※<수집개시>스크립트를 실행, 원하는 시점에 <수집종료>스크립트를 실행하여 종료시킨다. 


3. OS상에서 Exp/Imp 명령어를 이용해 통계 테이블을 테스트 서버로 복제한다.


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;



4. NoWorkLoad 시스템 통계

   통계항목
  • CPUSPEEDNW : NoWorkLoad 상태에서 측정된 CPU속도 (millions/sec)
  • IOSEEKTIM : I/O Seek Time 을 뜻하며, 데이터를 읽으려고 디스크헤드를 옮기는 데 걸린 시간 (ms)
  • IOTFRSPEED : I/O Transfer 속도를 뜻하며, OS프로세스가 I/O서브시스템으로부터 데이터를 읽는 속도 (bytes/ms) 

 

    WorkLoad 시스템 통계를 수집하고 반영하는 순간 NoWorkLoad 시스템 통계는 무시된다.

    기본적으로 WorkLoad 시스템 통계 정보를 이용하고, 이를 수집하기 힘든 환경일 경우 NoWorkLoad 시스템 통계정보를 참고한다.

    이때는, 시스템에 어느정도의 부하를 준상태에서 NoWorkLoad 시스템 통계정보를 수집하는것이 바람직하다.


    ②통계정보 수집

begin

  dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD');

end;





  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 토시리 (cotosiry)
  • 최초작성일: 2011년 4월 23일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ~^o^/