[Oracle]테이블 및 파티션 통계 정보 관리

왜 통계 정보를 관리해야 하나요?

우리는 근본적으로 왜 이 통계 정보를 관리해야 하는지 알아야 할 필요가 있습니다. 오라클 데이터베이스에서 테이블과 파티션의 통계 정보를 관리하는 것은 데이터베이스의 성능을 결정짓는 가장 핵심적인 작업 중 하나입니다. 통계 정보가 부실하면 오라클도 비효율적인 실행 계획으로 쿼리를 실행 하게 되기 때문이죠.

 

통계 정보를 철저히 관리해야 하는 이유는 다음과 같습니다.

1. CBO(Cost-Based Optimizer)의 의사결정 근거

CBO는 sql을 실행할 때 "어떤 방법이 가장 빠르고 저렴할까?"를 계산합니다. 이 때 CBO가 참고하는 데이터가 바로 통계 정보입니다.

• 데이터 분포 확인: 테이블에 데이터가 10건 있는지 1000만건 있는지에 따라, Full Scan을 할지 Index Scan을 할지 결정한다.
• 조인 방식 결정: 어떤 테이블을 먼저 읽을지, 조인 알고리즘(Hash Join, Nested Loop, ...)은 무엇을 쓸지 통계 정보를 바탕으로 판단한다.

2. 실행 계획의 최적화

통계 정보가 최신 상태로 유지되어야 오라클이 최적의 실행 계획을 세울 수가 있습니다.

• 인덱스 활용도: 특정 칼럼의 데이터가 골고루 퍼져 있는지(Cardinality), 중복도가 높은지(Selectivity)를 알아야 인덱스를 사용할지 말지 정확히 결정한다.
• 잘못된 판단 방지: 데이터는 엄청나게 늘어났는데 통계 정보가 과거에 머물러 있다면, 옵티마이저는 데이터가 적은줄 알고 인덱스 대신 풀 스캔을 선택하는 등에 악성 쿼리를 만들어 낼 수 있다.

3. 파티션 테이블의 효율적인 활용 (Partition Pruning)

파티션 테이블의 경우, 파티션 단위의 통계 정보가 매우 중요합니다.

• 파티션 프루닝: 쿼리 조건에 맞는 파티션만 골라서 읽어야 하는데, 통계 정보가 없거나 부정확하면 불필요한 파티션까지 모두 뒤지는 낭비가 발생한다.
• 글로벌 vs 파티션 통계: 전체 테이블 레벨의 통계(Global Stats)와 개별 파티션 레벨의 통계가 조화를 이루어야 대용량 데이터 조회 시 성능을 보장받을 수 있다.

4. 데이터 변화에 따른 성능 유지

데이터베이스는 살아있는 생물처럼 계속 변합니다. (Insert, Update, Delete)

• Stale Statistics(오래된 통계): 데이터가 10% 이상 변하면 통계 정보는 'Stale' 상태가 됩니다. 이를 방치하면 갑자기 운영 중인 시스템의 특정 기능이 느려지는 현상이 발생할 수 있다.
• 예측 가능성: 주기적인 통계 정보 수집은 실행 계획이 갑자기 변하는 것을 방지하여 시스템의 안정성을 높여준다.

---

 

오라클에서 파티션에 대한 통계 정보를 수집하고 해당 테이블에 통계 정보를 넣는 방법은 DBMS_STATS 패키지를 활용하는 것입니다. 

 

파티션 테이블에 대한 통계 수집을 위해서는 DBMS_STATS.GATHER_TABLE_STATS 또는 DBMS_STATS.GATHER_PARTITION_STATS 를 사용하면 됩니다. 여기서는 파티션에 대한 통계를 수집하고 이를 해당 테이블에 반영하는 방법을 알아보겠습니다.

1. 테이블의 파티션 통계 수집 및 테이블에 반영하기

특정 테이블의 파티션 통계 수집

파티션 테이블에 대한 통계를 수집하는 기본적인 방법은 DBMS_STATS.GATHER_TABLE_STATS를 사용하는 것입니다. 이 방법은 테이블에 속한 모든 파티션에 대해 통계를 수집하여 테이블의 통계로 반영합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname   => 'SCHEMA_NAME',        -- 테이블이 속한 스키마 이름
    tabname   => 'TABLE_NAME',         -- 통계를 수집할 테이블 이름
    cascade   => TRUE                  -- 파티션까지 포함하여 수집 (TRUE)
);

해당 테이블의 모든 파티션에 대해 통계가 수집되고, 그 정보가 테이블에 반영됩니다.

 

특정 파티션에 대한 통계만 수집

만약 테이블의 특정 파티션에 대해서만 통계를 수집하고 싶다면 DBMS_STATS.GATHER_PARTITION_STATS를 사용합니다. 이 경우, 파티션 별로 통계를 수집할 수 있습니다.

EXEC DBMS_STATS.GATHER_PARTITION_STATS(
    ownname   => 'SCHEMA_NAME',        -- 스키마 이름
    tabname   => 'TABLE_NAME',         -- 테이블 이름
    partname  => 'PARTITION_NAME'      -- 통계를 수집할 파티션 이름
);

 

해당 쿼리는 특정 파티션에 대해서만 통계를 수집하여 그 파티션에 대한 통계를 업데이트합니다.

---

2. 통계 정보 확인

통계 정보가 제대로 수집되었는지 확인하려면 DBA_TAB_STATS 또는 USER_TAB_STATS 뷰를 통해 조회할 수 있습니다.

테이블의 통계 조회

SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed
FROM dba_tab_stats
WHERE table_name = 'TABLE_NAME' AND owner = 'SCHEMA_NAME';

 

파티션의 통계 조회

파티션별 통계를 확인하려면 DBA_TAB_PARTITIONS 뷰를 사용할 수 있습니다.

SELECT table_name, partition_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed
FROM dba_tab_partitions
WHERE table_name = 'TABLE_NAME' AND owner = 'SCHEMA_NAME';

---

3. 추가적인 통계 수집 옵션

estimate_percent 옵션을 사용하여 샘플링 비율을 지정하거나, degree를 지정하여 병령 처리할 수 있습니다.

샘플링 비율을 설정하여 통계 수집

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname       => 'SCHEMA_NAME',
    tabname       => 'TABLE_NAME',
    cascade       => TRUE,
    estimate_percent => 10               -- 10% 샘플링 (샘플링 비율: 0~100%)
);

 

병렬 처리로 통계 수집

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname       => 'SCHEMA_NAME',
    tabname       => 'TABLE_NAME',
    cascade       => TRUE,
    degree        => 4                    -- 병렬 처리의 degree (1~N)
);

---

결론

파티션 테이블에 대한 통계를 수집하여 해당 테이블에 반영하는 과정은 DBMS_STATS.GATHER_TABLE_STATS와 DBMS_STATS.GATHER_PARTITION_STATS를 통해 이루어집니다. cascade => TRUE 옵션을 사용하면 파티션까지 포함하여 테이블 전체의 통계를 수집할 수 있습니다. 또한, 수집한 통계 정보를 확인하려면 DBA_TAB_STATS 또는 DBA_TAB_PARTITIONS 뷰를 조회하여 실제 통계가 어떻게 반영되었는지 확인할 수 있습니다.