CBO(Cost-Based Optimizer)

CBO(Cost-Based Optimizer)는 Oracle 데이터베이스의 쿼리 최적화 방법 중 하나입니다. CBO는 쿼리를 실행하는데 필요한 예상 리소스 사용량(비용)을 계산하고, 이를 기반으로 가장 효율적인 쿼리 실행 계획을 선택합니다.

 

CBO의 작동 방식을 이해하려면 Oracle 데이터베이스가 수집하는 통계 정보에 대해 알아야 합니다. 이러한 통계에는 테이블 크기, 테이블의 행 수, 테이블의 열 분포, 인덱스의 선택도, 클러스터링 정도 등이 포함됩니다. 이러한 통계는 Oracle의 DBMS_STATS 패키지를 사용하여 수집하고 유지할 수 있습니다.

 

CBO는 이러한 통계를 사용하여 쿼리를 수행하는 데 필요한 비용을 계산합니다. 이 때, 비용은 디스크 I/O, CPU 사용량, 네트워크 트래픽 등 여러 요소를 고려한 것입니다. CBO는 가능한 모든 실행 계획을 고려하고 각각의 비용을 계산한 후, 비용이 가장 적은 실행 계획을 선택합니다.

 

CBO는 또한 힌트를 사용하여 실행 계획을 조절할 수 있습니다. 힌트는 SQL 쿼리에 추가하여 CBO에게 특정 실행 계획을 고려하도록 지시하는 것입니다. 그러나 힌트는 항상 CBO가 따르는 것은 아닙니다. 힌트가 유효하지 않거나, 힌트가 지정한 실행 계획이 실제로 비용이 더 높을 경우, CBO는 힌트를 무시할 수 있습니다.

 

CBO의 정확성과 성능은 통계 정보의 정확성에 크게 의존합니다. 따라서 통계가 최신이고 정확하다면 CBO는 더 효과적인 실행 계획을 선택할 수 있습니다. 이를 위해 Oracle 데이터베이스 관리자는 주기적으로 통계 정보를 업데이트해야 합니다.

통계정보 업데이트 방법

Oracle 데이터베이스에서는 DBMS_STATS 패키지를 사용하여 통계 정보를 수집하고 관리합니다. 이 패키지는 여러 프로시저와 함수를 제공하여 테이블, 인덱스, 컬럼 등의 통계를 생성, 수정, 삭제, 보기 등을 수행할 수 있습니다.

테이블의 통계를 업데이트하는 가장 일반적인 방법은 DBMS_STATS.GATHER_TABLE_STATS 프로시저를 사용하는 것입니다. 아래는 이 프로시저를 사용하는 간단한 예입니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

위의 명령은 'SCHEMA_NAME' 스키마의 'TABLE_NAME' 테이블에 대한 통계를 수집합니다.

더욱 복잡한 설정이 필요한 경우에는, 추가 파라미터를 사용할 수 있습니다. 예를 들어, 테이블의 모든 인덱스에 대한 통계를 수집하려면 다음과 같이 할 수 있습니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', cascade=>true);

cascade=>true 옵션은 모든 관련 인덱스에 대한 통계도 같이 수집하라는 의미입니다.

통계를 정기적으로 업데이트하는 것이 중요합니다. 데이터의 분포가 변경되면(즉, 데이터가 추가, 수정, 삭제되면), 통계 정보도 이에 맞게 업데이트되어야 합니다. 이렇게 함으로써 CBO(Cost-Based Optimizer)는 최적의 실행 계획을 만들 수 있습니다.

 

Oracle의 DBMS_STATS 패키지는 테이블, 스키마, 데이터베이스, 인덱스 등에 대한 통계를 생성, 수정, 삭제하고 보는 등의 다양한 작업을 수행하는데 사용됩니다. 이 중 GATHER_TABLE_STATS 프로시저는 특정 테이블에 대한 통계를 수집하는데 사용됩니다.

 

GATHER_TABLE_STATS 프로시저의 기본 구문은 다음과 같습니다:

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          => 스키마 이름,
   tabname          => 테이블 이름,
   partname         => 파티션 이름, 
   estimate_percent => 통계 수집에 사용되는 행의 비율,
   block_sample     => 블록 샘플링 사용 여부,
   method_opt       => 컬럼 통계 수집 방법,
   degree           => 병렬처리 정도,
   granularity      => 통계 수집 정밀도,
   cascade          => 인덱스 통계 수집 여부,
   stattab          => 통계 테이블 이름,
   statid           => 통계 테이블 ID,
   statown          => 통계 테이블이 있는 스키마 이름,
   no_invalidate    => 통계 수집 후 실행 계획 무효화 방지 여부,
   force            => 기존 통계 무시 여부,
   statown          => 통계 테이블 소유자);

모든 매개변수는 선택적이며, 각 매개변수에 디폴트 값이 있습니다. 일반적으로 ownname과 tabname만 지정하면 테이블에 대한 통계를 수집할 수 있습니다.

 

다음은 특정 테이블의 통계를 수집하는 기본 예입니다:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

아래 예제는 테이블과 관련된 모든 인덱스에 대한 통계를 수집하고, 통계 수집에 30%의 행을 사용하도록 지정합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', estimate_percent=>30, cascade=>true);

이러한 통계 수집은 데이터베이스의 성능을 최적화하는 데 중요하며, 주기적으로 수행되어야 합니다. 데이터의 분포가 크게 변경될 경우, 통계도 이에 맞게 업데이트되어야 합니다.

통계수집 데이터 확인

Oracle에서 수집한 통계를 확인하는 방법은 다양하며, 사용자에게 가장 적합한 방법은 사용자의 특정 요구사항에 따라 달라집니다. 여기서는 가장 일반적으로 사용되는 방법 중 몇 가지를 소개하겠습니다.

1. ALL_TAB_STATISTICS 뷰: 테이블에 대한 통계를 확인하는 가장 일반적인 방법은 ALL_TAB_STATISTICS 뷰를 조회하는 것입니다. 이 뷰에는 각 테이블에 대한 통계, 테이블의 행 수, 블록 수 등의 정보가 포함되어 있습니다. 예를 들어, SCOTT 계정의 EMP 테이블에 대한 통계를 확인하려면 다음 쿼리를 실행합니다:이 쿼리는 EMP 테이블의 행 수(NUM_ROWS), 블록 수(BLOCKS), 평균 행 길이(AVG_ROW_LEN)를 반환합니다.
   
   
2. ALL_TAB_COL_STATISTICS 뷰: 열에 대한 통계를 확인하려면 ALL_TAB_COL_STATISTICS 뷰를 조회합니다. 이 뷰에는 각 열의 통계, 최소값, 최대값, 분포도 등의 정보가 포함되어 있습니다.이 쿼리는 EMP 테이블의 각 열의 고유한 값의 수(NUM_DISTINCT), 최소값(LOW_VALUE), 최대값(HIGH_VALUE)를 반환합니다.
   
   
3. ALL_INDEXES 및 ALL_IND_STATISTICS 뷰: 인덱스에 대한 통계를 확인하려면 ALL_INDEXES 및 ALL_IND_STATISTICS 뷰를 조회합니다.

이런 식으로 통계 정보를 조회하면 데이터베이스의 성능을 분석하고, 튜닝하는데 도움이 됩니다. 통계가 최신이고 정확하다면, Oracle의 코스트 기반 최적화기(Cost-Based Optimizer)는 더 효과적인 실행 계획을 생성할 수 있습니다.