[ORACLE] 통계정보 수집과 백업 쿼리

통계정보의 의미와 필요성

통계정보는 데이터베이스의 성능 최적화를 위해 사용되는 핵심 요소 중 하나입니다. 통계정보는 데이터베이스 엔진이 쿼리를 최적화하고 실행 계획을 수립하는 데 도움을 주는 데이터입니다. 여기에는 테이블의 크기, 데이터 분포, 인덱스 효과 등이 포함됩니다.

 

통계정보의 중요성:

1. 쿼리 최적화: 데이터베이스 엔진은 쿼리를 실행할 때 최적의 경로를 선택하기 위해 통계정보를 활용합니다. 정확한 통계를 갖고 있으면 데이터 액세스 경로를 최적화하여 쿼리 성능을 향상시킬 수 있습니다.
2. 인덱스 성능 향상: 인덱스의 선택과 사용은 데이터의 분포와 관련이 있습니다. 통계 정보를 통해 엔진은 어떤 인덱스를 사용할지 결정하고 효과적으로 데이터에 액세스할 수 있습니다.

 

통계정보에 포함되는 내용

1. 테이블 통계:
   • 테이블의 전체 레코드 수
   • 테이블의 블록 수
   • 테이블의 평균 행 길이
   • 테이블에 대한 클러스터링 팩터(Clustering Factor)
2. 컬럼 통계:
   • 각 컬럼의 카디널리티(Cardinality): 컬럼의 고유한 값 수
   • 각 컬럼의 널(null) 값 비율
   • 각 컬럼의 최소, 최대값
3. 인덱스 통계:
   • 인덱스의 높이
   • 인덱스의 클러스터링 팩터
   • 인덱스의 블록 수
4. 파티션 통계:
   • 파티션된 테이블의 파티션 수
   • 각 파티션의 레코드 수 및 블록 수
5. 도메인 인덱스(Domain Index) 통계:
   • 도메인 인덱스의 높이 및 블록 수
   • 각 도메인 인덱스의 클러스터링 팩터
6. 각종 비율 및 통계:
   • 테이블 또는 인덱스의 사용률
   • 각 인덱스의 선택도
   • 테이블의 업데이트, 삭제, 삽입 비율

 

 

통계정보 수집

1. 스키마 단위 통계 수집

 

BEGIN
  DBMS_STATS.gather_schema_stats(
    ownname => '스키마_이름',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade => TRUE
  );
END;
/

 

설명:

• ownname: 통계를 수집할 스키마의 이름입니다.
• estimate_percent: 샘플링 비율을 자동으로 설정합니다. AUTO_SAMPLE_SIZE로 설정하면 Oracle 옵티마이저가 최적의 샘플링 비율을 자동으로 선택합니다.
• cascade: 종속된 객체의 통계 정보도 함께 수집합니다. 이는 인덱스, 파티션, 서브파티션 등을 포함합니다.

2. 특정 테이블 통계 수집

BEGIN
  DBMS_STATS.gather_table_stats(
    ownname => '스키마_이름',
    tabname => '테이블_이름',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
  );
END;
/

 

설명:

• ownname: 통계를 수집할 스키마의 이름입니다.
• tabname: 통계를 수집할 테이블의 이름입니다.
• estimate_percent: 샘플링 비율을 자동으로 설정합니다. AUTO_SAMPLE_SIZE로 설정하면 Oracle 옵티마이저가 최적의 샘플링 비율을 자동으로 선택합니다.

 

통계정보 백업 및 복원

통계정보 수집은 때때로 의도치 않은 성능 저하를 야기합니다. 따라서 통계정보 수집 전 반드시 기존 통계정보를 백업하여 성능 저하 시 복원할 수 있도록 해야합니다.

1. 통계정보 백업

 

BEGIN
  DBMS_STATS.EXPORT_TABLE_STATS(
    ownname   => '스키마_이름',
    tabname   => '테이블_이름',
    stattname => NULL,
    statid    => NULL,
    statown   => NULL,
    degree    => NULL
  );
END;
/

 

설명:

• EXPORT_TABLE_STATS: 테이블 통계를 백업하는 프로시저입니다.
• ownname: 통계를 백업할 스키마의 이름입니다.
• tabname: 통계를 백업할 테이블의 이름입니다.

2. 통계정보 복원

 

BEGIN
  DBMS_STATS.IMPORT_TABLE_STATS(
    ownname   => '스키마_이름',
    tabname   => '테이블_이름',
    stattname => NULL,
    statid    => NULL,
    statown   => NULL,
    degree    => NULL
  );
END;
/

 

설명:

• IMPORT_TABLE_STATS: 백업한 통계 정보를 복원하는 프로시저입니다.
• ownname: 통계를 복원할 스키마의 이름입니다.
• tabname: 통계를 복원할 테이블의 이름입니다.