개요

1. Partition Table 통계

운영중인 System에서 일별, 월별로 파티션이 추가 또는 분리되는 로그성 및 통계성 Table이 다수 존재하는 경우 통계 정보를 수집하는 시점을 정하기가 쉽지 않고, 대용량 System인 경우 통계 수집 시간에 대한 부담이 큽니다.

 본 절에서는 대용량 System의 통계 수집 부하를 줄이기 위한 방법으로 통계 수집 레벨에 대해 설명합니다. 또한 일별, 월별과 같이 일정 주기에 따라 파티션이 추가 또는 분리되는 System에서의 통계 정보 전략 및 활용에 대해 설명합니다.

1.1 쿼리 조건별 Statistics level

PARTITION에서는 SQL 조건에 따라 Global 통계나 파티션 통계 중 하나가 사용되는데 파티션 키를 상수로 조회하는 것 외에는 Global 통계가 사용되므로 Global 통계를 수집하는 것이 정확한 실행 계획을 위해서 좋습니다.

• Table 스키마 생성

SQL> CREATE TABLE TEST_TABLE (A NUMBER, B NUMBER, C CHAR(2000))
 PARTITION BY RANGE (A)
 (PARTITION P1 VALUES LESS THAN (10000),
 PARTITION P2 VALUES LESS THAN (40000),
 PARTITION P3 VALUES LESS THAN (MAXVALUE));
 
SQL> INSERT INTO TEST_TABLE SELECT LEVEL,MOD(LEVEL,10),LPAD(LEVEL,10,'0')
 FROM DUAL CONNECT BY LEVEL<=100000;

SQL> commit;

SQL>  CREATE INDEX TEST_INDEX ON TEST_TABLE(A,B) LOCAL;

SQL>  BEGIN
 DBMS_STATS.GATHER_TABLE_STATS ('TEST_USER','TEST_TABLE',GRANULARITY='ALL');
 END;
 /

• 쿼리 조건절에 따라 Global 또는 파티션 통계를 다르게 선택합니다.

Statistics level - GLOBAL 통계

• 조건절 사용이 없을 때

SQL> SELECT * FROM TEST_TABLE;

Execution Plan
--------------------------------------------------------------------------------
1 PARTITION RANGE (ALL PART) (Cost:14037, %CPU:0, Rows:100090) (PS:1, PE:3)
2    TABLE ACCESS (FULL): TEST_TABLE (Cost:14037, %CPU:0, Rows:100090)

• 파티션 키 조건 사용되지 않을 때

SQL> SELECT * FROM TEST_TABLE WHERE B=3;

Execution Plan
--------------------------------------------------------------------------------
1 PARTITION RANGE (ALL PART) (Cost:14037, %CPU:0, Rows:100090) (PS:1, PE:3)
2   
 TABLE ACCESS (FULL): TEST_TABLE (Cost:14037, %CPU:0, Rows:100090

• 2개 이상의 파티션 키 조건 사용

SQL> SELECT * FROM TEST_TABLE WHERE A<10 OR A>50000;

Execution Plan
--------------------------------------------------------------------------------
1 PARTITION RANGE (ALL PART) (Cost:14037, %CPU:0, Rows:50049) (PS:1, PE:3)
2   
 TABLE ACCESS (FULL): TEST_TABLE (Cost:14037, %CPU:0, Rows:50049)

• 단일 파티션 조건이지만 Bind 변수 사용일 때

SQL> SELECT * FROM TEST_TABLE WHERE A=:x;
   
Execution Plan
--------------------------------------------------------------------------------
1 PARTITION RANGE (SINGLE PART) (Cost:3, %CPU:0, Rows:1) (PS:KEY, PE:KEY)
2    TABLE ACCESS (ROWID) LOCAL: TEST_TABLE (Cost:3, %CPU:0, Rows:1)
3         INDEX (RANGE SCAN): TEST_INDEX (Cost:2, %CPU:0, Rows:1)

• Global 통계 수집 후 통계 정보 확인

SQL> BEGIN
 DBMS_STATS.DELETE_TABLE_STATS ('TEST_USER','TEST_TABLE');
 DBMS_STATS.GATHER_TABLE_STATS ('TEST_USER','TEST_TABLE',GRANULARITY='GLOBAL');
 END;
 /
 
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED FROM USER_TABLES WHERE 
TABLE_NAME='TEST_TABLE';

 NUM_ROWS  BLOCKS    AVG_ROW_LEN  LAST_ANALYZED
 --------- --------- ------------ --------------------------------
 990030    33920     2005.787=279 2025/12/05
 
 SQL> SELECT NUM_ROWS,BLOCKS,LAST_ANALYZED FROM USER_TAB_PARTITIONS WHERE 
TABLE_NAME='TEST_TABLE';

 NUM_ROWS   BLOCKS   LAST_ANALYZED
 --------- --------- --------------
 0          0
 0          0 
 0          0

Statistics level - PARTITION 통계

• 단일 파티션 키 조건

SQL> SELECT * FROM TEST_TABLE WHERE A=100;
 
Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): TEST_TABLE (Cost:3, %CPU:0, Rows:1)
2   PARTITION RANGE (SINGLE PART) (Cost:2, %CPU:0, Rows:1) (PS:1, PE:1)
3     INDEX (RANGE SCAN): TEST_INDEX (Cost:2, %CPU:0, Rows:1)

• 닫힌 범위를 표현하는 복수의 파티션 키 조건 사용

SQL> SELECT * FROM TEST_TABLE WHERE A>100 AND A<35000;

Execution Plan
--------------------------------------------------------------------------------
1 PARTITION RANGE (SUBSET PART) (Cost:5669, %CPU:0, Rows:34993) (PS:1, PE:2)
2   
TABLE ACCESS (FULL): TEST_TABLE (Cost:5669, %CPU:0, Rows:34993

• 파티션 키를 상수값으로 조회하는 것 외에는 Global 통계가 사용되므로 Global 통계를 수집하는 것이 정확한 실행 계획을 위해서 좋습니다.
• 파티션 통계를 수집한 후 통계 정보 확인 예제

SQL> BEGIN
 DBMS_STATS.DELETE_TABLE_STATS ('TEST_USER','TEST_TABLE');
 DBMS_STATS.GATHER_TABLE_STATS ('TEST_USER','TEST_TABLE',GRANULARITY='PARTITION');
 END;
 /
 
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED FROM USER_TABLES WHERE 
TABLE_NAME='TEST_TABLE';

NUM_ROWS   BLOCKS     AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------------------------
0          0           0

SQL> SELECT NUM_ROWS,BLOCKS,LAST_ANALYZED FROM USER_TAB_PARTITIONS WHERE 
TABLE_NAME='TEST_TABLE';

NUM_ROWS   BLOCKS   LAST_ANALYZED
--------- --------- ------------- 
10040      3456      2025/12/05
30320      10240     2025/12/05
59950      20224     2025/12/05

1.2 Global Statistics 수집 여부

대량의 데이터를 가지고 있는 Table인 경우 Global 통계를 수집할 때 시간과 부하가 많이 걸리므로 파티션 통계 정보만 수집하는 경우가 있습니다. 

Tibero는 Global 통계 수집의 부하를 줄이기 위해 Global 통계값을 Partition-level 통계로부터 가져오는 기능을 제공합니다. 즉, Global 통계를 위해 Table 스캔을 하지 않고 Partition-level에서 수집된 정보를 이용하여 Global 통계를 갱신함을 의미합니다. 

단, 추정치이므로 Global 통계를 수집한 날짜가 오래된 경우 Global 통계를 다시 수집해야 합니다.

1.3 Global 및 Partition level Statistics 수집

다음은 Global 통계와 Partition 통계의 수집 원리에 대한 설명입니다. 통계 수집할 때 적절한 방법을 선택합니다.

• Global 통계와 Partition-level의 모든 통계 수집

모든 레벨의 통계를 수집하는 granularity 옵션 중 'ALL'을 사용하여, Partition-level의 통계와 Global 통계를 동시 에 수집합니다.

전체 PARTITION을 2번 FULL SCAN하기 때문에 시간이 많이 걸리나, 처음에 통계 정보를 생성할 때는 이 방법을 선택합니다.

• Partition-level 통계만 수집하고 Global 통계는 Partition-level 통계 정보에 의해 갱신

통계를 수집할 대상 Table이 너무 커 Global 통계 수집에 오랜 시간이 소요될 때 Partition-level 통계를 수집합니다. 이때 partition key column에 대한 min, max, distinct 개수, 히스토그램 정보 등이 갱신되므로 수집된 통계의 정확성을 높일 수 있습니다. 

Global 통계 수집보다는 실행 계획이 다소 부정확해질 수 있는 위험이 있지만 통계 수집에 오랜 시간이 걸리지 않는 장점이 있습니다.

• Global 통계와 파티션 통계 수집의 수집 시간

다음은 Global 통계와 파티션 통계 수집의 수집 시간에 대한 예입니다.

-- Global 통계와 파티션 레벨의 모든 통계를 수집하는 경우

SQL> /* 파티션 레벨 통계 수집
(granularity='PARTITION', no_invalidate= FALSE) */
BEGIN
 DBMS_STATS.DELETE_TABLE_STATS (OWNNAME=USER,TABNAME='TEST_TABLE' );
 DBMS_STATS.GATHER_TABLE_STATS
 (OWNNAME='TEST_USER',TABNAME='TEST_TABLE',
 PARTNAME=NULL, NO_INVALIDATE=FALSE,
 GRANULARITY='PARTITION');
 END;
 /

-- Global 통계 및 파티션 통계 수집 확인
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED
  FROM USER_TABLES WHERE TABLE_NAME='TEST_TABLE';

SQL> SELECT NUM_ROWS,BLOCKS,LAST_ANALYZED
 FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TEST_TABLE';
 
SQL>  /* Global 파티션 레벨 통계 수집
(granularity='GLOBAL', estimate_percent = 10, degree = 4,no_invalidate= FALSE)
시간 단축을 위해 샘플링 10%, Parallel=4 옵션을 사용함 */
 BEGIN
 DBMS_STATS.DELETE_TABLE_STATS (OWNNAME=USER,TABNAME='TEST_TABLE' );
 DBMS_STATS.GATHER_TABLE_STATS
 (OWNNAME='TEST_USER',TABNAME='TEST_TABLE',
 PARTNAME=NULL,ESTIMATE_PERCENT=10,
 DEGREE=4,NO_INVALIDATE=FALSE,
 GRANULARITY='GLOBAL');
 END;
 /
 
 -- Global 통계 및 파티션 통계 수집 확인
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED
 FROM USER_TABLES WHERE TABLE_NAME='TEST_TABLE';

 SQL> SELECT NUM_ROWS,BLOCKS,LAST_ANALYZED
 FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TEST_TABLE';

1.4 일정 주기로 파티션이 추가되는 Table

일, 월이 Range Partition Key Column값으로 들어가며, 일정 주기에 따라 지속적으로 파티션이 증가하기 때문에 매일 키 Column값의 범위가 달라지면서 통계 정보가 실제 데이터 분포를 따라가지 못하는 상황이 발생합니다. 이는 쿼리에서의 조건문들의 Selectivity 및 Cardinality 예측값을 부정확하게 만드는 요인으로 작용합니다.

다음은 일별, 월별과 같이 일정 주기에 따라 파티션이 추가 또는 분리되는 System에서의 통계 정보 수집을 스키마 형식을 이용하여 설명한 예입니다.

• Partition table을 생성합니다.

SQL> @create_table_partition.sql

• 테스트 데이터를 입력합니다.

SQL> BEGIN
 FOR i IN 1..20 LOOP
   INSERT INTO DAILY_LOG
     SELECT '202501'||LPAD(i,2,'0'), LEVEL, LEVEL, LEVEL, LEVEL, LEVEL
     FROM DUAL CONNECT BY LEVEL <= 10000;
     COMMIT;
   END LOOP;
 END;

• 통계 정보를 수집합니다.

SQL> CALL DBMS_STATS.GATHER_TABLE_STATS (USER, 'DAILY_LOG', granularity=>'ALL');

• 새로운 파티션을 추가하고 데이터를 입력합니다. (add_partition.sql)

Table은 날짜값을 문자열로 표현한 Column을 파티션 키 값으로 하고 있고, 매일 새로운 파티션이 현재 날짜 값으로 추가되지 때문에 매일 키 column의 최댓값이 증가합니다.

SQL> ALTER TABLE DAILY_LOG ADD PARITTION P20250121 VALUES LESS TAHN ('20250122');
SQL> INSERT INTO DAILY_LOG SELECT '20250121',1,1,1,1, FROM DUAL CONNECT BY LEVEL <=10000;
SQL> COMMIT;

위 예에서는 각 파티션마다 10,000개의 Row가 있고, Table에 P20250101 ~ P20250120까지 추가된 상태에서 통 계 수집이 완료된 후 P20250121 파티션이 추가되었으며, 추가된 파티션에 데이터도 입력되었습니다. 따라서 P20250121은 통계 정보 수집이 되지 않았기 때문에 LOG_DAY Column 통계 정보도 최댓값이 '20250120'일 것입니다.

• Plan을 조회합니다.
• 통계 정보가 있는 20250120의 Plan을 조회한 결과 (view_partition_plan.sql)

SQL> @view_partition_plan.sql

SQL> set autotrace traceonly exp planstat
SQL> SELECT * FROM DAILY_LOG WHERE LOG_DAY='20250120';
 
Execution Plan
--------------------------------------------------------------------------------
1 PARTITION RANGE (SINGLE PART) (Cost:4240, %CPU:0, Rows:9944) (PS:20, PE:20)
 2   
TABLE ACCESS (FULL): DAILY_LOG (Cost:4240, %CPU:0, Rows:9944)

Predicate Information
---------------------------------------------------------------------------
2 - filter: ("DAILY_LOG"."LOG_DAY" = '20250120') (0.050)

Execution Stat
--------------------------------------------------------------------------------
1 PARTITION RANGE (SINGLE PART) (Time:.02 ms, Rows:10000, CR:0, CU:0, Starts:1)
 2   
TABLE ACCESS (FULL): DAILY_LOG (Time:108.34 ms, Rows:10000, CR:10145, CU:203, 
Starts:1

SQL> set autotrace off

현재 LOG_DAY Column의 히스토그램 상에서 최댓값은 '20230120'이므로 Cardinality가 유사하게 예측되어 올바 른 Plan(FULL) 이 생성됩니다.

• 통계 정보가 없는 20250121의 Plan을 조회한 결과 (view_partition_plan.sql)

SQL>  @view_partition_plan.sql
SQL> set autotrace traceonly exp planstat
SQL> SELECT * FROM DAILY_LOG WHERE LOG_DAY='20250121';
 
Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): DAILY_LOG (Cost:3, %CPU:0, Rows:1)
2   
PARTITION RANGE (SINGLE PART) (Cost:2, %CPU:0, Rows:1) (PS:21, PE:21)
3     
  
INDEX (RANGE SCAN): DAILY_LOG_IDX (Cost:2, %CPU:0, Rows:1)

Predicate Information
---------------------------------------------------------------------------
2 - filter: ("DAILY_LOG"."LOG_DAY" = '20250121') (0.000)

Execution Stat
--------------------------------------------------------------------------------
1 TABLE ACCESS (ROWID): DAILY_LOG (Time:2582.04 ms, Rows:10000, CR:10000, CU:0, 
Starts:1)
2   
PARTITION RANGE (SINGLE PART) (Time:0. ms, Rows:10000, CR:0, CU:0, Starts:1)
3       INDEX (RANGE SCAN): DAILY_LOG_IDX (Time:12.49 ms, Rows:10000, CR:369, CU:0, 
Starts:1)

SQL> set autotrace off

히스토그램 상의 최댓값을 넘어선 '20230121' 날짜에 대해서는 잘못된 Plan(RANGE SCAN)을 생성하여 쿼리 속도가 느려지게 됩니다. 이 Table의 경우 일 별 파티션이므로 쿼리 속도가 체감할 만큼 크게 느껴지지 않지만 시간 별 파티션일 경우에는 통계 정보가 더 빠르게 어긋납니다.

1.5 대용량 파티션 통계 수집

대용량 파티션 환경에서는 Tibero에서 기본으로 제공하는 자동 통계 수집 기능을 권장하지 않으며, 운영하고 있는 System 업무 환경에 맞는 전략적인 통계 수집 및 활용이 요구됩니다. 

다음은 Granularity 값이 파티션 통계 수집 성능에 미치는 영향에 대한 설명입니다.

대용량 파티션과 Granularity

대용량 System의 경우 특정 Table 파티션 개수가 수천 개, 크기는 수 테라바이트인 Table이 있을 수 있습니다. 이때 1% 이하로 샘플링한다고 해도 기본 몇 기가바이트 이상의 데이터를 샘플링하게 되며, 파티션별로 통계 수집을 할 경우 수천 번의 샘플링이 이루어지기 때문에 이로 인해 소요되는 시간 또한 무시할 수 없습니다. 만약 로컬 Index까지 몇 개 있다고 하면 훨씬 더 많은 작업이 생겨나게 됩니다.

다음은 “2.2.4. 일정 주기로 파티션이 추가되는 Table”에서 설명한 예제의 LOG_DAY 파티션에 대해 로컬 Index를 생성하고 Granularity를 기본값인 ALL로 설정한 후 수집했을 때 몇 번의 샘플링 및 수집 과정이 이루어지는지에 대 설명입니다.

• Index 생성 (create_index.sql)

SQL> CREATE INDEX DAILY_LOG_IDX_01 ON DAILY_LOG (LOG_DAY) LOCAL;
SQL> CREATE INDEX DAILY_LOG_IDX_02 ON DAILY_LOG (LOG_DAY, C1) LOCAL;
SQL> CREATE INDEX DAILY_LOG_IDX_03 ON DAILY_LOG (LOG_DAY, C1, C2) LOCAL;
SQL> CREATE INDEX DAILY_LOG_IDX_04 ON DAILY_LOG (LOG_DAY, C3) LOCAL;

파티션의 개수는 윤년이 있는 2024년, 2028년을 감안하면 365 x 10 + 2 = 3562개입니다.

• 통계 수집

통계 수집의 순서는 다음과 같다. 만약 파티션의 개수가 위와 같이 3562개인 경우 몇 번의 샘플링 수집이 이루어지는지 알 수 있습니다.

a. Table에 대해서 수집: 1회 

b. Table 파티션에 대해서 수집: 3,562회 

c. Index별로 수집: 4회 

d. Index 파티션 각각에 대해서 수집: 3,562 * 4회

수집 결과 총 17,815회의 샘플링 및 수집이 이루어진다. 만약 히스토르갬을 정밀히 수집하기 위해 'FOR ALL COLUMNS SIZE 200' 등의 옵션을 주면 Column 각각에 대해서도 샘플링을 합니다.

이때 ESTIMATE_PERCENT가 낮다고 하더라도 17,815회 만큼의 횟수를 수행하면 수 시간 또는 수 일이 걸리게 된됩니다. 단위로 증가하는 파티션인데 Table 하나에 대해 이렇게 많은 시간이 소모된다면 매우 잘못된 수집 방식입니다.

참고

대용량 System 통계 수집할 때 업무 환경 특성을 고려한 전략이 필요하며, 이런 환경에서 활용할 수 있는 기능은 "통계의 고급 기능" 참고