오라클 통계쌓기

오라클 통계정보란?

오라클 통계정보란 데이터베이스의 테이블, 인덱스, 파티션 등과 관련된 객체의 특성을 분석하여 수집한 정보를 의미하고 이 정보를 이용하여 오라클 옵티마이저가 적절한 실행 계획을 수립할 수 있습니다. 주요한 통계정보로는 테이블의 레코드 수, 인덱스의 리프 블록 수, 테이블의 컬럼값 중 가장 작은 값, 테이블의 칼럼중 가장 큰 값, 테이블의 칼럼 분보도, 인덱스의 유니크한 값의 수등이 있습니다. 이러한 통계 정보는 오라클 옵티마이저가 쿼리를 처리할 때, 실행 계획을 수립하는 데 사용됩니다.

오라클 통계정보를 생성하는 이유

오라클 통계정보를 생성해야 하는 이유는 쿼리 실행 계획을 최적화하여 데이터베이스 성능을 향상시키기 위해서 입니다. 쿼리 실행 계획은 오라클 옵티마이저가 쿼리를 처리하는 방법을 결정하는데, 이때 오라클은 테이블과 인덱스의 통계 정보를 참고하여 최적의 실행 계획을 선택합니다. 따라서 통계 정보가 부족하거나 정확하지 않으면 오라클 옵티마이저가 최적의 실행 계획을 선택하지 못하게 되어 쿼리 성능이 저하될 수 있습니다. 테이블의 통계 정보가 부족하면 오라클은 해당 테이블의 크기나 행 수를 모른 상태에서 실행 계획을 작성하게 됩니다. 이 경우 실행 계획이 부적절하게 작성될 가능성이 높아져서 쿼리 성능이 저하될 수 있습니다. 따라서 데이터베이스 성능을 향상시키기 위해서는 테이블과 인덱스의 통계 정보를 주기적으로 업데이트해주는 것이 중요합니다.

오라클 통계정보 생성하는 방법

오라클은 통계정보를 생성하기 위해 GATHER_TABLE_STATS 기능을 제공합니다. 아래와 같이 스키마에 대한 모든 통계나 특정 테이블에 대한 통계정보를 생성할 수 있습니다.

-- 스키마안의 모든 세그먼트에 대한 통계정보 갱신
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('계정명');

-- 테이블의 통계정보 갱신
EXEC DBMS_STATS.GATHER_TABLE_STATS('계정명', '테이블명');

GATHER_TABLE_STATS 옵션을 조금 더 상세히 살펴 보겠습니다.

• OWNNAME : 계정명을 지정할 수 있습니다.
• TABNAME : 테이블명을 지정할 수 있습니다.
• CASCADE : 해당 테이블에 연결된 인덱스, 파티션, 서브파티션 등의 통계 정보도 함께 수집합니다.
• ESTIMATE_PERCENT : 데이터의 일부를 샘플링하여 통계 정보를 추정하며 사용되는 행의 비율을 설정합니다.
• DEGREE : 병렬 처리에 사용될 프로세스 수를 지정합니다. (통계정보 생성 속도가 빨라집니다.)
• METHOD_OPT : 통계 정보를 수집하는 방법을 지정하는 옵션을 지정할 수 있습니다.

GATHER_TABLE_STATS기능을 사용하지 않고 강제로 통계를 쌓는 방법도 있습니다.

-- 해당 스키마에 해당하는 테이블의 통계정보 조회
SELECT TABLE_NAME, NUM_ROWS, CHAIN_CNT, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN 
FROM DBA_TABLES 
WHERE OWNER = '계정명';

-- 해당 스키마에 해당하는 인덱스의 통계정보 조회
SELECT TABLE_NAME, INDEX_NAME, STATUS, NUM_ROWS, LEAF_BLOCKS, BLEVEL 
FROM DBA_INDEXES 
WHERE OWNER = '계정명';

-- DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신
ANALYZE TABLE 계정명.테이블명 COMPUTE STATISTICS;

-- DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)
SELECT 'ANALYZE TABLE 계정명.' || TABLE_NAME || ' COMPUTE STATISTICS;' 
FROM DBA_TABLES WHERE OWNER = '계정명';

-- DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신
ANALYZE INDEX 계정명.인덱스명 VALIDATE STRUCTURE;

-- DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신(쿼리 생성)
SELECT 'ANALYZE INDEX 계정명.' || INDEX_NAME || ' VALIDATE STRUCTURE;' 
FROM DBA_INDEXES WHERE OWNER = '계정명';