[ORACLE] 통계정보

통계정보란?

  - 데이터베이스의 SQL 성능을 좌우하는 요소 중 하나

  - oracle의 옵티마이저가 실행계획 수립 시 참조하는 정보

  - 실행계획을 최적화하기 위해 수집된 통계정보를 참고하여 최적의 실행계획을 세움

  - analyze란 명령어를 사용하여 table 및 index, cluster의 통계정보를 생성

  - analyze를 하게되면 통계정보를 생성 후 data dictionary에 저장

 

 

 

※ 통계정보 수집방법

1. ANALYZE

  - AVG_SPACE, CHAIN_CNT 등 컬럼들의 통계를 수집

  - 인덱스, 테이블, 클러스터의 통계정보를 생성

  - ANALYZE가 생성한 통계정보들은 비용기준(Cost-based)의 옵티마이저가 가장 효율적인 실행계획을 수립하기 위해

    최소비용을 계산할 때 사용 된다.

  - 각 오브젝트의 구조를 확인하는 것과 체인(Chain) 생성 여부를 확인할 수 있어 시스템의 저장공간 관리를 도와준다.

 

  - ANALYZE는 다음과 같은 통계정보를 생성하여 데이터 사전에 저장 한다.

    1) 테이블 : 총 로우의수, 총 블럭의 수, 비어있는 블럭에 쓰여질 수 있는 빈 공간의 평군, 체인이 발생된 로우의 수,

                  로우의 평균 길이

    2) 인덱스 : 인덱스의 깊이(Depth), Leaf block의 개수, Distinct Key의 수, Leaf Blocks/Key의 평균, Data blocks/key의

                  평균, Clustering Factor, 가장 큰 key 값, 가장 작은 key 값

    3) 컬럼 : Distinct한 값의 수, 히스토그램 정보

    4) 클러스터 : Cluster Key당 길이의 평균

 

  - 문법

    : ANALYZE object-clause operation STATISTICS

                   [VALIDATE STRUCTURE[CASCADE]]

                   [LIST CHAINED ROWS[INTO tables]]

       - object-clause : TABLE, INDEX, CLUSTER 중에서 해당하는 오브젝트를 기술하고 처리할 오브젝트 명을 기술

       - operation  : COMPUTE, ESTIMATE, DELETE 중 한가지 기능 선택 가능

                        : COMPUTE - 각각의 값들을 정확하게 계산. 가장 정확하지만 속도가 가장 느림

                        : ESTIMATE - 자료사전의 값과 데이터 견본을 가지고 검사해 통계를 예상 (덜정확, 빠름)

                        : DELETE - 테이블의 모든 통계정보 삭제

 

 

  - 테이블 통계

    : ANALYZE TABLE [소유자] COMPUTE STATISTICS FOR [테이블명];

    : ANALYZE TABLE [소유자] ESTIMATE STATISTICS SAMPLE 50 PERCENT FOR [테이블명];

    (COMPUT : 전수 검사, ESTIMATE : 표본 검사)

 

  - 인덱스 통계

    :  ANALYZE INDEX [소유자].[인덱스명] COMPUTE STATISTICS;

       -> 필요한 인덱스의 통계정보를 수집

    : ANALYZE TABLE [소유자].[테이블명] COMPUTE STATISTICS FOR ALL INDEXES;

       -> 테이블에 속해 있는 모든 인덱스의 통계정보 수집

 

 정보수집

  - 테이블을 재생성 하거나, 새로 클러스터링을 한 경우, 인덱스를 추가하거나 재생성한 경우, 다량의 데이터를 SQL이나

     배치 애플리케이션을 통해 작업한 경우 ANALYZE를 수행 시켜 주는 것이 좋다.

  - 사용자는 USER_TABLES, USER_COLUMNS, USER_INDEXS, USER_CLUSTER 등의 자료사전 뷰를 통해 정보를 확인가능

  -  테이블을 ANALYZE 시킨다면 거기에 따르는 인덱스들도 같이 실시하는 것이 좋다.

  EX) 

 

 

 

 

2. DBMS_STAT

  - CBO와 관련된 정보만 통계정보로 수집

 

  - DBMS_STAT의 패키지의 주요 프로시져

     GATHER_DATABASE_STATS : 데이터베이스의 모든 Object에 대해 통계정보 생성

     GATHER_SCHEMA_STATS : 해당 스키마의 Object에 대한 통계정보 생성

     GATHER_TABLE_STATS : 테이블과 연관된 인덱스에 대한 대해 통계정보 생성

     GATHER_INDEX_STATS : 인덱스에 대해 대해 통계정보 생성

 

 

테이블 통계정보 확인

 : SELECT OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED FROM DBA_TABLES;

 

인덱스 통계정보 확인

 : SELECT OWNER,INDEX_NAME,NUM_ROWS,LAST_ANALYZED FROM DBA_INDEXES;

 

 

 

출처  : www.gurubee.net/lecture/1740

        : www.gurubee.net/lecture/2918