오라클 통계정보

728x90

통계 정보의 중요성

Oracle DBMS에서 통계정보는 상당히 중요하다.

통계정보란 무엇일까?

데이터베이스의 테이블, 인덱스 등과 관련된 객체의 특성을 분석하고 수집한 정보이다.

주요한 통계 정보로는

테이블의 레코드 수
테이블의 컬럼 값 중 가장 작은 값, 가장 큰 값
테이블의 컬럼 분포도
인덱스의 유니크한 값의 수 등
인덱스의 리프 블록 수 등

이 있다.

옵티마이저는 이러한 정보를 이용하여 최적의 실행 계획을 세우게 된다.

DBMS에서 실행되는 모든 SQL은 옵티마이저가 세운 실행 계획에 맞춰서 실행이 되는데,

이 실행 계획이 데이터베이스의 성능에 직결된다.

같은 SQL이라도 실행 계획에 따라 성능이 천차만별일 수가 있다.

테이블의 통계 정보가 부족하거나 부정확하다면 옵티마이저는 정확하지 않은 테이블의 정보로 실행 계획을 세우게 된다. 이는 쿼리 성능의 저하로 이어지는 것이다.

즉, 최적의 실행 계획으로 최고의 성능을 내기 위해서 통계 정보는 상당히 중요하다.

사실 예전 Oracle 7, 8 버전에서는 통계 정보가 그리 중요하지 않았다.

그 때는 Rule Based 방식의 옵티마이저가 실행계획을 세웠는데,

테이블의 정보와 상관 없이 이미 정해진 Rule에 의해 세웠기 때문이다.

그 Rule은 보통 where 조건 절에서 나오는 = 연산자, 범위 연산자(between 등) 등 어떤 연산자가 우선순위가 높은가에 대한 것들이다.

하지만 현재는 /*+ rule */ 힌트를 주지 않는 이상 Cost based 방식의 옵티마이저가 디폴트이다.

이는 옵티마이저가 실행계획을 세울 때 테이블, 인덱스의 통계정보를 사용한다는 것이다.

따라서 통계정보가 정확하지 않으면 옵티마이저도 이상한 실행계획을 세우게 되고 성능이 저하된다.

통계정보 조회

USER_TABLES 딕셔너리를 조회하여 통계정보가 만들어졌는지 조회해보자.

(DBA_TABLES에서 OWNER 컬럼에 현재 접속한 사용자를 조건으로 검색해도 된다.)

자 이제 JOBS 테이블을 이용하여 TEST_JOBS 테이블을 만들고 다시 조회해보자

TEST_JOBS 테이블을 만들었다.

다시 통계정보가 만들어졌나 조회 결과를 보면

보이는 바와 같이 통계정보가 없다.

통게정보는 바로 갱신되지 않는다는 것을 알 수 있다.

이는 테이블의 생성처럼 데이터의 INSERT나 삭제의 경우에도 마찬가지이다.

갱신되지 않은 채로 옵티마이저가 실행 계획을 세우면? 개판이난다.

따라서 테이블이나 인덱스가 변경될 때마다 매번 통계정보를 갱신해주어야 하는데

Oracle은 DBA가 특별히 건들지 않았다면 자동으로 매일 22시에 Autotask 작업이 돌아가 갱신이 된다.

통계정보 생성

물론 직접 통계 정보를 생성할 수도 있다.

위에서 생성한 TEST_JOBS 테이블에 대해서 통계 정보를 생성해보자.

두가지 방법이 있는데

첫 번쨰는 ANALYZE문으로 오래된 방법이다. 현재 Oracle에서는 권장되지 않는다.

두 번째는 DBMS_STATS.GATHER_TABLE_STATS 프로시저인데 Oracle 9i 이후부터 도입된 통계 수집 방법으로, 더 정교하고 포괄적인 통계 수집을 지원한다.

그렇다면 먼저 DBMS_STATS.GATHER_TABLE_STATS에 관해서 먼저 알아보자.

DBMS_STATS.GATHER_TABLE_STATS

DBMS_STATS.GATHER_TABLE_STATS 프로시저의 파라미터는 다음과 같다.

DMBS_STATS.GATHER_TABLE_STATS(
    OWNNAME => '스키마 이름'        -- 스키마 이름
  , TABNAME => '테이블 이름'        -- 테이블 이름
  , ESTIMATE_PERCENT => 100                 -- 통계 추정 비율(기본 값은 100)
  , CASCADE => TRUE -- 관련 인덱스 및 파티션 통계도 수집 여부
  , METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO' -- 통계 수집 방법 및 세부 옵션
  , DEGREE => 4 --병렬 수집 차수(기본 값은 1)
);

이 중에서 OWNNAME과 TABNAME은 필수적인 파라미터이고 나머지는 선택적이다.여기서는 CASCADE 정도만 추가해서 실행해보자.

통계정보를 생성해주고 다시 조회를 해보면

정상적으로 통계정보가 생성되었다.

이제 옵티마이저는 정확한 통계 정보를 가지고 실행 계획을 세울 수 있다.

다음으로 권장하지 않는 방법이지만 ANALYZE 문에 대해서도 간단하게 알아보자.

ANALYZE TABLE [테이블명] COMPUTE STATISTICS; 쿼리를 사용하면 된다.

문법 설명은 밑에서!!

통계정보를 생성해주고 다시 조회를 해보면

정상적으로 통계정보가 생성되었다.

ANALYZE TABLE [테이블명] COMPUTE STATISTICS; 쿼리 문법에 대해서 정리를 하자면

형식은 아래와 같다.

ANALYZE object [object_name] operation STATISTICS;

먼저, ANALYZE STATISTICS 말 그대로 통계를 분석해라는 명령이다.

통계 정보를 계산하라는 구문이고 중간에 들어가는 값을 알아보자.

object : TABLE, INDEX, CLUSTER 중에 해당하는 오브젝트
object_name : 오브젝트 이름
operation(주요 옵션)
- COMPUTE : 오브젝트의 통계 정보를 실제로 정확하게 계산한다. 가장 정확한 통계 정보를 얻을 수 있지만 처리 속도가 가장 느리다.
- ESTIMATE : 오브젝트의 통계 정보를 추정해서 계산한다.(자료사전의 값과 데이터 견본 등을 가지고 통계를 예상) 실제 데이터를 완전히 스캔하지 않고 통계 정보를 구하기 때문에 덜 정확하지만 처리 속도가 빠르다.
- DELETE : 오브젝트의 모든 통계 정보를 삭제한다.
- 이 외에도 다양한 옵션이 있으며, 데이터베이스 버전과 설정에 따라 사용 가능한 옵션이 달라질 수 있다.

이상으로 오라클의 통계정보에 대해 정리해보았다.

옵티마이저의 실행계획에 필요한 자료인 만큼 데이터베이스 성능에 큰 영향을 미치는 중요한 정보이다.

따라서 테이블의 변경 등 이후에 평소와 다르게 성능에 문제가 생길 시 의심해볼만한 정보이므로

아는만큼 보이니 간단하게나마 알고 있다면 빠른 해결에 도움이 될 수도 있다.

통계정보 수집할 때는 DBMS_STATS.GATHER_TABLE_STATS 프로시저를 쓰자!

http://www.gurubee.net/lecture/2918

옵티마이저의 눈, 통계정보

데이터베이스의 SQL 성능을 좌우하는 요소 중 하나가 통계정보다. 통계정보에 대한 이해가 부족하면 데이터베이스 성능 저하의 원인을 찾고 해결..

www.gurubee.net

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=seong_han&logNo=110044000844

Cost-Based Optimization란

대규모 데이타베이스(VLDB)를, 그것도 24시간 가용상태로 유지해야 하는 환경이라면, 아마도 성...