[Oracle] 테이블(Table) 통계정보 수집 (DBMS_STATS.GATHER_TABLE_STATS)

"DBMS_STATS.GATHER_TABLE_STATS"은 Oracle Database에서 사용되는 프로시저로, 테이블의 통계를 수집하는 데에 쓰입니다.

이것은 쿼리 옵티마이저가 효율적인 실행 계획을 생성하는 데 도움이 되며, 테이블 내 데이터의 분포, 어떤 열이 인덱싱되어 있는지 등에 대한 정보를 포함합니다. 이 프로시저는 수집된 통계로 데이터 사전을 업데이트하고, 쿼리 옵티마이저가 쿼리 최적화 중에 이 통계를 사용합니다.

반응형

 

1. 구문 및 파라미터 정보

구문:

DBMS_STATS.GATHER_TABLE_STATS(
  ownname           IN VARCHAR2,         -- 테이블이 속한 스키마의 이름
  tabname           IN VARCHAR2,         -- 통계를 수집할 테이블의 이름
  partname          IN VARCHAR2 DEFAULT, -- 파티션 이름 (기본값: NULL)
  subpartname       IN VARCHAR2 DEFAULT, -- 파티션 서브티이브 이름 (기본값: NULL)
  estimate_percent  IN NUMBER DEFAULT,   -- 테이블의 샘플링 비율 (기본값: DBMS_STATS.AUTO_SAMPLE_SIZE)
  method_opt        IN VARCHAR2 DEFAULT, -- 통계 수집 방법에 대한 옵션 (기본값: 'FOR ALL COLUMNS SIZE AUTO')
  cascade           IN BOOLEAN DEFAULT   -- 종속 객체에 대한 통계도 수집할지 여부 (기본값: FALSE)
);

 

파라미터:

• ownname: 테이블이 속한 스키마의 이름을 나타내는 VARCHAR2 타입의 매개변수입니다.
• tabname: 통계를 수집할 테이블의 이름을 나타내는 VARCHAR2 타입의 매개변수입니다.
• partname: 통계를 수집할 파티션의 이름을 나타내는 VARCHAR2 타입의 매개변수입니다. (기본값: NULL)
• subpartname: 통계를 수집할 파티션 서브티이브의 이름을 나타내는 VARCHAR2 타입의 매개변수입니다. (기본값: NULL)
• estimate_percent: 테이블의 샘플링 비율을 나타내는 NUMBER 타입의 매개변수입니다. 
  (기본값: DBMS_STATS.AUTO_SAMPLE_SIZE)
• method_opt: 통계 수집 방법에 대한 옵션을 나타내는 VARCHAR2 타입의 매개변수입니다. 
  (기본값: 'FOR ALL COLUMNS SIZE AUTO')
• cascade: 종속 객체에 대한 통계도 수집할지 여부를 나타내는 BOOLEAN 타입의 매개변수입니다. (기본값: FALSE)

** (파티션테이블이 아닌)일반테이블의 경우 partname과 subpartname은 생략하면 됩니다.

2. 샘플

/*1.테이블 생성*/
CREATE TABLE employees (
  employee_id   NUMBER,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  department_id NUMBER
);

/*2.샘플 테이터 입력*/
INSERT INTO employees VALUES (1, 'John', 'Doe', 101);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 102);
INSERT INTO employees VALUES (3, 'Mike', 'Johnson', 101);
INSERT INTO employees VALUES (4, 'Emily', 'Davis', 103);

/*3.DBMS_STATS.GATHER_TABLE_STATS 사용*/
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'employees',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE
  );
END;

이 샘플에서는 employees라는 테이블을 생성하고 몇 가지 데이터를 입력한 다음, DBMS_STATS.GATHER_TABLE_STATS 프로시저를 사용하여 이 테이블의 통계를 수집합니다. 이 코드는 현재 사용자의 스키마에 속한 employees 테이블에 대한 통계를 수집하며, 모든 열에 대한 통계를 자동으로 수집하고 종속 객체에 대한 통계도 함께 수집합니다.

 

3. 통계정보 조회

SELECT table_name,
       num_rows,
       sample_size,
       last_analyzed
  FROM dba_tab_statistics
 WHERE table_name = 'employees';

위 SQL을 이용하여 employees 테이블의 통계정보를 조회 할 수 있습니다.

 

반응형

 

4. 마무리...

가끔 간단한 select 쿼리인데도 속도가 안나고 오래 걸리는 경우가 있는데,

통계정보를 수집하지 않아서 그런 경우가 많았습니다.

주기적으로 통계정보를 수집하거나,

배치프로그램의 경우에는 마지막에 통계정보를 수집하여 성능을 향상시킬 수 있습니다~