오라클 데이터베이스를 관리하다보면 때론 데이터 추가 및 삭제로 인해 SQL이 느려지는 경우를 종종 겪게 된다. 테이블 구조나 인덱스 구조 변경이 없었음에도 SQL이 느려진 이유의 대부분은 실행계획의 재수립이 발생했기 때문이다. 실행계획의 재수립은 오브젝트 변경, 통계정보 변화 등으로 인해 일어난다.

통계정보란 무엇인가?

통계정보는 오라클의 옵티마이저가 실행계획을 작성할 때 참조하는 정보다. 실행계획을 최적화하기 위해서는 현재 데이터 상태를 참고할 무언가가 필요하다. 그래서 옵티마이저는 수집된 통계정보를 바탕으로 최적의 실행계획을 세운다.

통계정보는 테이블을 생성하고 데이터를 삽입할지라도 이러한 변경이 실시간으로 반영되지 않는다. DBA가 수동적으로 통계정보를 수집하는데, 오라클 10g 버전 이상부터는 자동스케줄링(JOB)를 통해 수집된다.

통계정보 수집에 자동스케줄링을 활용하면 SQL 실행계획의 재생성으로 인해 데이터베이스의 성능 저하가 야기될 수 있다. 그러므로 통계정보 정책을 기반으로 수동으로 통계정보를 수집하는 것이 바람직하다. 그렇다면 통계정보 정책 수립 시 고려해야 할 사항은 무엇일까? 지금부터 살펴보자.

통계정보 수집 시 고려사항

통계정보 수집은 고객에게 서비스하기 전 데이터베이스 준비과정의 하나다. 이는 통계정보 수집이 서비스에 영향을 주어서는 안 됨을 의미한다.

통계정보 수집은 CPU, 시스템 I/O 자원을 많이 사용하는 작업이다. 만약 서비스가 집중되는 시간에 통계정보를 수집한다면 업무 성능이 저하되고, 결국에는 시스템 장애가 발생할 수 있다. 그러므로 최소한의 고려사항을 준수해 통계정보를 수립해야 한다.

• - 시간 : 시스템 부하가 적은 날짜와 시간을 산정해 수집
• - 샘플 크기 : 데이터베이스와 세그먼트의 크기에 비례해 일정 부분만 추출
• - 정확성 : 오브젝트의 데이터와 통계정보의 데이터가 근접해야 함
• - 안정성 : 통계정보 수집으로 인한 데이터베이스 성능 저하를 최소화해야 함

통계정보 수집 방법

데이터베이스의 통계정보 수집 방법은 크게 두 가지다. 이 두 가지를 가르는 요인은 수집 범위다. ANALYZE는 AVG_SPACE, CHAIN_CNT 등 컬럼들의 통계를 수집하는 반면, DBMS_STAT는 CBO와 관련된 정보만 통계정보로 수집한다.

ANALYZE 명령어를 활용한 통계수집

-- 테이블 통계
① 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;
⇒ 테이블에 속해 있는 모든 인덱스의 통계정보 수집

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; 

위 [표]와 같이 통계정보를 수집한 후 정보를 확인하면, NUM_ROWS는 데이터가 추가되거나 삭제 됨에 따라 값이 변한다.

LAST_ANALYZED에서는 마지막 통계정보 수집 날짜와 시간 정보를 확인할 수 있다. 통계를 수집하지 않았다면 통계 관련 컬럼은 비어있을 것이다.

앞의 스크립트 예제는 NUM_ROWS만 표현했지만, 각각의 딕셔너리 리뷰(dictionary review)에 따라 BLOCKS, EMPTY_BLOCKS 등과 같은 통계 관련 컬럼이 추가로 있을 수 있다. 이러한 것들을 통해 더 많은 정보를 확인할 수 있다.

통계정보를 마치며

통계정보 수집은 오라클 9i 이전 버전에서는 꼭 필요한 작업은 아니었다. 하지만 옵티마이저가 RBO(Rule Base Optimizer)에서 CBO(Cost Base Optimizer)로 변경되면서 통계정보에 대한 중요도가 높아졌다.

정확한 통계정보 수집은 오라클의 옵티마이저 능력을 최대한 이끌어내는 가장 좋은 방법이다. 올바른 통계정보 수집 방법과 정책 수립을 통해 여러분의 데이터베이스를 안정적으로 운영하길 바란다.