메뉴 건너뛰기

bysql.net

4. 통계정보 Ⅰ

2011.04.26 00:26

darkbeom 조회 수:17818

3.4 통계정보 1

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


3.4.1 테이블 통계


- ANALYZEDBMS_STATS 패키지


* 오라클 공식입장 : 'ANALYZE' 명령어는 사용하지 말라
* 교재의 공식입장 : 테이블 및 인덱스, 컬럼 통계를 따로따로 수집함으로써 통계수집개념 이해도를 높이기 위함.


-- 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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^




번호 제목 글쓴이 날짜 조회 수
60 Front Page file 운영자 2011.02.15 149196
59 3. 인덱스 파티셔닝 darkbeom 2011.06.19 53386
58 3. 다양한 인덱스 스캔 방식 file 멋진넘 2011.02.18 33506
57 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.29 30475
56 2. 파티션 Pruning 실천하자 2011.06.21 25672
55 3. 뷰 Merging 실천하자 2011.05.15 23037
54 3. 해시 조인 file darkbeom 2011.03.20 21206
53 2. 서브쿼리 Unnesting darkbeom 2011.05.15 19292
» 4. 통계정보 Ⅰ darkbeom 2011.04.26 17818
51 7. 인덱스 스캔 효율 [1] 휘휘 2011.03.08 16596
50 7. Sort Area 크기 조정 실천하자 2011.06.13 14702
49 4. 테이블 Random 액세스 부하 [1] file darkbeom 2011.02.23 14343
48 1. 인덱스 구조 [1] file 실천하자 2011.02.16 13932
47 4. 조인 순서의 중요성 운영자 2011.03.27 13918
46 1. 기본 개념 멋진넘 2011.06.28 13063
45 8. 고급 조인 테크닉-1 [1] file darkbeom 2011.04.03 12956
44 9. 비트맵 인덱스 file 실천하자 2011.03.05 12068
43 1. 옵티마이저 file 실천하자 2011.04.18 10930
42 6. 히스토그램 실천하자 2011.04.24 10669
41 6. Sort Area를 적게 사용하도록 SQL 작성 file 실천하자 2011.06.13 8589