3.4 통계정보 1

- 통계정보 : 옵티마이져가 실행계획을 수립 시 참조하는 정보
- CBO 관점 : 실행계획 수립시 SQL문장에서 액세스 할 테이터의 특성을 고려하기 위해 통계정보 이용
- COMPUTE  : 계산하다. 제품 하나 하나를 모두 검사하는 것.
- ESTIMATE : 추정,추산하다. 재료, 부품, 제품 등이 어느 조건에서 묶은 집단 검사


3.4.1 테이블 통계

- ANALYZE 와 DBMS_STATS 패키지

-- ANALYZE
ANALYZE TABLE EMP COMPUTE STATISTICS FOR TABLE;

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5000 ROWS FOR TABLE;

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 50 PERCENT FOR TABLE;

-- DBMS_STATS 패키지를 이용한 테이블 통계 수집
begin
    dbms_stats.gather_table_stats('scott', 'emp'  -- scott 계정의 emp 테이블을 대상으로
    , cascade=>false                              -- 인덱스 통계가 수집되지 않도록
    , method_opt=>'for columns')                  -- 컬럼 통계가 수집되지 않도록
end;
/

-- 수집된 테이블 통계 조회
SELECT  OWNER ||'.'||TABLE_NAME AS FULL_NAME,
        AVG_ROW_LEN, AVG_SPACE, AVG_SPACE_FREELIST_BLOCKS,
        BLOCKS, EMPTY_BLOCKS, NUM_FREELIST_BLOCKS,
        NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED
FROM    DBA_TABLES
WHERE   OWNER = 'scott'
AND     TABLE_NAME = 'emp';

3.4.2 인덱스 통계

-- 인덱스 통계수집
ANALYZE INDEX EMP_PK COMPUTE STATISTICS;

-- 테이블에 속한 모든 인덱스 통계수집
ANALYZE INDEX EMP_PK COMPUTE STATISTICS FOR ALL INDEXES;

-- 테이블 + 인덱스 함께 통계수집
ANALYZE INDEX EMP_PK COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES;

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

-- 수집된 인덱스 통계 조회
SELECT  OWNER ||'.'||TABLE_NAME ||'=>'|| INDEX_NAME AS FULL_NAME,
        AVG_DATA_BLOCKS_PER_KEY, AVG_LEAF_BLOCKS_PER_KEY, BLEVEL,
        CLUSTERING_FACTOR, DISTINCT_KEYS, LAST_ANALYZED,
        LEAF_BLOCKS, NUM_ROWS, SAMPLE_SIZE
FROM    DBA_INDEXES
WHERE   OWNER = 'scott'
AND     TABLE_NAME = 'emp'
AND     INDEX_NAME = 'emp_pk'

* COMPUTE STATISTICS 옵션 : 인덱스 최초생성 및 재생성시 자동으로 인덱스 통계까지 수집한다.
                            10G부터는 사용자가 명시하지 않아도 자동으로 인덱스 통계를 수집한다.
                            (기능방지하고자 했을시에는 _optimizer_compute_index_stats 파라메터를 false로 설정)

CREATE INDEX EMP_IDX01 ON EMP(ENAME) COMPUTE STATISTICS;

ALTER INDEX EMP_IDX01 REBUILD COMPUTE STATISTICS;

3.4.3 컬럼 통계

- 테이블 및 인덱스 통계를 제외하고 컬럼 통계만 수집

-- 모든 컬럼 통계수집
ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL COLUMNS SIZE 254;

-- 일부 컬럼 통계수집(각각의 히스토그램 버킷개수 설정)
ANALYZE TABLE EMP COMPUTE STATISTICS FOR COLUMNS EMANE SIZE 10, SAL SIZE 20;

-- 일부 컬럼 통계수집(일괄 히스토그램 버킷개수 설정)
ANALYZE TABLE EMP COMPUTE STATISTICS FOR COLUMNS SIZE 10 EMANE, SAL;

-- 테이블 + 인덱스 + 컬럼 모든 통계수집
ANALYZE TABLE EMP COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXES COLUMNS SIZE 254;


-- 수집된 컬럼 통계정보 확인
SELECT  OWNER ||'.'||TABLE_NAME AS FULL_NAME,
        AVG_COL_LEN, CHAR_USED, DENSITY,
        HIGH_VALUE, LAST_ANALYZED, LOW_VALUE,
        NUM_BUCKETS, NUM_DISTINCT, NUM_NULLS,
        SAMPLE_SIZE
FROM    DBA_TAB_COLUMNS
WHERE   OWNER = 'scott'
AND     TABLE_NAME = 'emp';

-- 컬럼 히스토그램 조회
SELECT  OWNER ||'.'||TABLE_NAME AS FULL_NAME,
        ENDPOINT_NUMBER,
        ENDPOINT_VALUE
FROM    DBA_HISTOGRAMS
WHERE   OWNER = 'scott'
AND     TABLE_NAME = 'emp'
AND     COLUMN_NAME = 'ename'
ORDER   BY ENDPOINT_VALUE;

* dbms_stats 패키지를 이용한 컬럼만 통계수집하는 방법은 없다. 테이블 통계와 항상 같이 수집된다.


3.4.4 시스템 통계
- I/O, CPU 성능 같은 하드웨어적 특성을 측정.
- 오라클 9i 부터 제품이 설치된 하드웨어 및 애플리케이션 특성에 맞는 시스템 통계를 수집하고 이를 활용함으로써
  옵티마이져가 합리적으로 선택할 수 있다.

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

* 과거엔 위의 모든 항목이 고정된 상수였다. 즉, 옵티마이져 개발팀의 하드웨어적 사양에 맞춰져 있기에
  실제 운영시스템 사양 및 애플리케이션(OLTP, DW)에 따라 위 항목의 특성이 달라지므로 다른 환경이라면
  최적이 아닌 실행계획을 수립 가능성이 높아진다. 
 
- Workload 와 Noworkload 시스템 통계

-- 시스테 통계정보 조회 결과
SELECT SNAME, PNAME, PVAL1, PVAL2 FROM SYS.AUX_STATS$;

-------------------------------------------------------
SYSSTATS_INFO    STATUS        COMPLETED
SYSSTATS_INFO    DSTART        03-10-2004 01:02
SYSSTATS_INFO    DSTOP         03-10-2004 01:02
SYSSTATS_INFO    FLAGS         1   
-- Noworkload(10g부터)
SYSSTATS_MAIN    CPUSPEEDNW    904.866969500324   
SYSSTATS_MAIN    IOSEEKTIM     10   
SYSSTATS_MAIN    IOTFRSPEED    4096   
-- Workload(9i부터)
SYSSTATS_MAIN    SREADTIM       
SYSSTATS_MAIN    MREADTIM       
SYSSTATS_MAIN    CPUSPEED       
SYSSTATS_MAIN    MBRC       
SYSSTATS_MAIN    MAXTHR       
SYSSTATS_MAIN    SLAVETHR   

3.4.4.1. Workload
- 실제 애플리케이션으로부터 일정시간 동안 발생한 시스템 부하를 측정 및 보관 함으로써
  그 특성을 최적화 하는 과정에 반영할 수 있도록 활용한다.

CPUSPEED   : 현재 시스템에서 단일 cpu가 초당 수행할 수 있는 표준 오퍼레이션 개수(단위 : 백만/초)
SREADTIM   : 평균적인 Single Block I/O 속도(단위 : ms = 1/1000초)
MREADTIM   : 평균적인 Multiblock I/O 속도(단위 : ms = 1/1000초)
MBRC       : Multiblock I/O 방식을 사용할 때 평균적으로 읽은 블록 수
MAXTHR     : I/O 서브시스템의 최대 처리량(단위 : 바이트/초)
LAVETHR    : 병력 Slave의 평균적인 처리량(단위 : 바이트/초)

3.4.4.2. Noworkload
- 관리자가 명시하지 않아도 CPU 비용 모델이 기본 비용 모델로 사용되게 하려고 오라클 10g에서 도입
- 오라클이 무작위로 I/O를 발생시켜 측정
- Workload 시스템 통계를 수집하고 반영하는 순간 Noworkload 시스템 통계는 무시된다.

CPUSPEEDNW : Noworkload 상태에서 측정된 CPU 속도 (기본 테이터베이스최초 기동시 측정된 값)
IOSEEKTIM  : 테이타를 읽으려고 디스크 헤드를 옮기는데 걸리는 시간(기본 10ms)
IOTFRSPEED : 하나의 OS프로세스가 I/O서브시스템으로부터 데이터를 읽는 속도(기본 4096 bytes/ms)

* 참고 : Noworkload 시스템 통계수집 시 명령어

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

3.4.4.3. 시스템통계 활용 측면(Workload)

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

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

2. 시스템 통계 수집

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

-- 방법2.1(시작 : 정해진 시간이 지난 뒤 )
begin
  dbms_stats.gather_system_stats(
          gathering_mode => 'start'
        , stattab => 'mystats'
        , statid => 'OLTP');
end;
/

-- 방법2.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 명령어를 통한 테스트 서버로 복제

export ...
emport ...

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 와 Workload 통계 수집시 기본 설정 값을 그대로 사용하기보다 적당한 부하를 준 상태에서 수집한다.
- CBO의 관점에서 적절한 통계를 관리하려면 적절한 시점의 수집 시기와 주기를 가져가는냐에 대한 방법론이 필요하다.
- 또한, 애플리케이션에서 발생되는 동적SQL에 대한 부하를 어떻게 해결하느냐의 관철이 필요하다.

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