오라클 통계정보 생성 방법 (analyze, dbms_utility, dbms_stats)

Oracle DBMS 에서 통계정보(Stats Info)가 얼마나 중요한지에 대해서는 앞 페이지에서 설명했습니다.

 

이 페이지에서는 통계정보를 생성하는 방법에 대해서 설명드리겠습니다.

통계정보를 생성하는 방법은 3가지가 있습니다.

 

        1) Analyze 커맨드

        2) DBMS_UTILITY 패키지

        3) DBMS_STATS 패키지

 

어떤 것을 쓰든 통계정보가 다 만들어지긴 합니다만, 위 1, 2 번의 경우는 옛날 Oracle 7 버전에서 만들어진 겁니다.

그냥 과거 호환성 차원에서 남아있는 것이죠~

그래서 Oracle사에서는 DBMS_STATS 패키지를 사용할 것을 권장하고 있습니다.

따라서 우리는 머리 아프게 여러가지 방법을 알고 있을 필요없고, 통계정보 뜰 때는 DBMS_STATS 패키지만 사용하면 된다고 생각하면 되겠습니다.

DBMS_STATS 패키지는 Oracle 8i 버전부터 사용 가능합니다.

 

 

DBMS_STATS 패키지로 통계정보를 생성하는 커맨드는 아래와 같습니다.

 

-- Database
SQL> exec dbms_stats.gather_database_stats;
SQL> exec dbms_stats.gather_database_stats(estimate_percent=>15);

-- Schema
SQL> exec dbms_stats.gather_schema_stats(ownname=>‘XXX', cascade=>TRUE);
SQL> exec dbms_stats.gather_schema_stats('SCOTT', estimate_percent=>15);

-- Table
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'EMP', partname=>NULL);
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'EMP_1', 'EMP_1_P1_S01');
            -- 파티션에 대해서 통계정보를 수집 (EMP_1 테이블의 EMP_1_P1_S01 파티션)
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'EMP', cascade=>TRUE);
            -- cascade: 테이블의 인덱스에 대해서도 통계 정보를 수집

-- Index
SQL> exec dbms_stats.gather_index_stats('SCOTT', 'PK_EMP');
SQL> exec dbms_stats.gather_index_stats('SCOTT', 'PK_EMP', estimate_percent=>15);

-- Delete Statistics :
SQL> exec dbms_stats.delete_database_stats(no_invalidate=>false);
            -- no_invalidate : 이 파라메터를 true로 설정하면, 관련 Plan을 삭제하지 않음
SQL> exec dbms_stats.delete_schema_stats(ownname=>'SCOTT', no_invalidate=>false);
SQL> exec dbms_stats.delete_table_stats(ownname=>'SCOTT', tabname=>'EMP');
SQL> exec dbms_stats.delete_index_stats('SCOTT', 'PK_EMP');

 

통계정보는 특정 테이블, 인덱스 단위로 생성할 수도 있고, DB유저 전체 또는 DB 전체에 대해서 생성할 수도 있습니다.

DB 전체의 테이블, 인덱스 들에 대해 통계정보 생성을 실행하면 꽤 오래 걸리겠죠?

모든 테이블들을 다 읽어야 하니 오래 걸릴 뿐만아니라 시스템 부하도 좀 있습니다.

따라서 DB 전체 또는 스키마(DB 유저) 단위로 통계정보를 생성하는 경우는 주로 야간이나 주말을 이용해야 합니다.

 

통계정보 작업을 빨리 끝내기 위해서 전체 테이블을 다 읽지 않고, 일부만 읽어서 통계정보를 생성할 수도 있습니다.

설문조사시 표본집단 선정해서 하듯이 하는 겁니다.

위 커맨드 예에서 estimate_percent=>15 와 같이 설정한것이 15% 만 샘플링해서 통계정보를 생성하겠다는 옵션입니다.

 

 

Oracle 10g 부터는 이렇게 수동으로 통계정보 생성을 돌리지 않아도 저녁 10시에 자동으로 통계정보 생성 작업이 돌아갑니다. 이를 Job Schedule 또는 Auto Task 라고 합니다.

 

* Oracle DB Autotask 는 여길 참고 ==> 오라클 DB Autotask 와 Maintenance Window