DBMS_STATS 패키지

오라클에서 테이블이나 인덱스의 현재 테이블 레코드의 개수, 행의 평균 길이, 데이터가 저장된 블록 수, 컬럼값의 최대값등을 통계정보하며 DBA_TABLES, DBA_TAB_COLUMNS, DBA_INDEXES, INDEX_STATS 등 같은 데이터 딕셔너리 뷰를 통해 확인할 수 있다.

 

오라클은 테이블이나 인덱스와 같은 데이터베이스 오브젝트에 대한 통계 정보 생성을 용이하게 하기 위해 DBMS_STATS 패키지를 제공하는데 이 패키지를 이용하면 테이블이나 인덱스의 모든 데이터를 근간으로 통계 정보를 생성 할 수 있으며 생성된 Sample 데이터를 기반에서 하여 통계 정보를 평가 하는 것이 가능하다.

 

대용량의 테이블이라면 모든 데이터를 가지고 액세스 경로를 추측 하는 것 보다 이러한 샘플링 데이터를 가지고 추측하는 것이 훨씬 용이하다. 대체로 샘플링 데이터는 5% 이하로 row나 block에 만들며 DBMS_STATS PACKAGE의 automatic sampling procedure를 이용하는 것이 좋다.

 

통계정보용 테이블 생성하기 DBMS_STATS 패키지의 create_stat_table 프러시저를 이용하여 만들면 된다.

 

SQL> conn / as sysdba

연결되었습니다.

 

SQL> execute dbms_stats.create_stat_table('scott','stat_tab','users');

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

여기에서 사용된 인자는 계정, 통계정보용 테이블 이름, 테이블스페이스 이름이다.

 

DBMS_STATS 패키지에는 몇 개의 유용한 프러시저가 있는데 다른 레벨로 통계 정보를 생성 할 수 있다.

 

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

gather_schema_stats: 해당 스키마의 Object에 대해 통계 정보를 생성.

gather_table_stats : 테이블과 그 테이블과 연관된 인덱스에 대해 대해 통계 정보를 생성.

gather_index_stats : 인덱스에 대해 대해 통계 정보를 생성.

 

아래의 예제를 참고 하자.

 

아래는 SCOTT이라는 스키마 계정과 EMP라는 테이블에 대해 통계 정보를 생성했다.

 

SQL> execute dbms_stats.gather_schema_stats(ownname => 'scott');

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

SQL>  execute dbms_stats.gather_table_stats('scott','emp');

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

SQL>select * from dba_tables where owner = ‘SCOTT’ 으로 확인 하자.(last_anaylizes)

 

테이블 내의 인덱스된 컬럼 들 및 대상 테이블과 연관된 모든 인덱스에 대한 통계 정보 생성.

 

BEGIN

             DBMS_STATS.GATHER_TABLE_STATS(

                           schema_name, ′table_name′,

                           method_opt => ′for all indexed columns′,

                           cascade => true);

END;

/

 

주)위의 예제인 경우 자동으로 테이블과 인덱스의 모든 데이터에 대해 통계 정보가 계산된다. 만약 만들 통계정보가 단순히 샘플이거나 큰 테이블의 모든 데이터가 아니라 일부의 레코드에 대해 제한하는 경우에는 dbms_stats.auto_sample_size를 이용하면 된다.

 

SQL> execute dbms_stats.gather_schema_stats(ownname => 'scott', estimate_percen => dbms_stats.auto_sample_size);

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

샘플링 Percent를 dbms_stats.auto_sample_size로 한 경우 오라클에서 알아서 결정을 하게된다. 테스트를 해보면 오라클10g, 오라클11g등에서 약간 다르게 나타나며 테이블의 데이터 값의 분포도가 많이 변화된 경우에 용이하다.

 

이번에 시스템과 관련된 통계 정보를 생성하여 보도록 하자.

 

dbms_stats를 이용하여 CBO(cost based optimizer)에게 system performance 통계정보를 제공 할 수 있는데 이 패키지는 CPU 사용과 I/O 퍼센트 등의 정보도 통계정보를 생성시 추가 한다. dbms_stats.gather_system_stats 프러시저를 이용하면 되고 파라미터는 다음과 같다.

 

gathering_mode : 통계 정보에 대한 수집을 특정한 시기 또는 기간에 하는 경우에는 interval 이나 start/stop 값을 주면 되고 noworkload라고 하면 시스템은 통계 정보를 general하게 수집한다.

 

interval : gathering_mode에서 interval이라고 한 경우에만 사용 합니다.

stattab : 시스템의 통계 정보가 모아질 테이블을 기술 합니다.

 

statown : 시스템의 통계 정보가 모아질 테이블의 Owner를 기술한다. (현재 패키지를 실행 할 Schema와 다를 경우에 기술)

 

SQL> begin

  2  dbms_stats.gather_system_stats (

  3     gathering_mode => 'interval',

4     interval => 60,   //분단위

5     stattab => 'stat_tab',

  6     statown => 'scott');

  7  end;

8  /

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

 

주)DBMS_STATS.gather_system_stats 를 사용전에 job_queue_processes 매개변수를 SET 시켜야 하는데 기본값은 0, 양수 값으로 세팅 해야한다. 그렇지 않으면 gather_system_stats 프러시져가 동작하지 않을 수 있다. 현재 세션에서 이 값을 다이나믹하게 설정하려면 alter system set job_queue_processes = 20 이라고 하면 된다.

출처 : http://ojc.asia/bbs/board.php?bo_table=LecOrccleTun&wr_id=79