본 문서에서는 Object 통계 수집 방법에 대해 설명합니다.

Optimizer 통계는 Database와 Object에 대한 상세한 정보를 나타내고, 쿼리 Optimizer는 SQL문에 대해 가장 좋은 실행 계획을 선택하기 위해 통계를 이용합니다.

방법

1. Object 통계

Object 통계는 Table 통계, Index 통계, 히스토그램을 포함한 Column 통계를 포함합니다.

1.1 Table 통계

-- 통계 정보 존재 확인
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED
 FROM ALL_TABLES WHERE OWNER='TEST_USER' AND TABLE_NAME='TEST_TABLE';
 
 NUM_ROWS  BLOCKS     AVG_ROW_LEN LAST_ANALYZED
--------- ---------- ----------- --------------------------------
 0         0           0
 
 -- Table 통계 정보 생성
SQL> BEGIN
 DBMS_STATS.GATHER_TABLE_STATS 
(OWNNAME='TEST_USER',TABNAME='TEST_TABLE',
 PARTNAME=NULL,ESTIMATE_PERCENT=10,
 METHOD_OPT='FOR ALL COLUMNS SIZE 1',
 DEGREE=4,CASCADE_INDEXES=FALSE,
 NO_INVALIDATE=FALSE, GRANULARITY='ALL');
 END;
 /
 
-- 통계 정보 존재 확인
SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED
 FROM ALL_TABLES WHERE OWNER='TEST_USER' AND TABLE_NAME='TEST_TABLE';
 
 NUM_ROWS    BLOCKS    AVG_ROW_LEN  LAST_ANALYZED
 ---------- ---------- ----------- --------------------------------
 996480      2128      7.96044075  2025/12/05

1.2 Index 통계

• 통계 정보 존재 확인

SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,CLUSTERING_FACTOR,NUM_ROWS,LAST_ANALYZED
 FROM ALL_INDEXES WHERE OWNER='TEST_USER' AND INDEX_NAME='TEST_INDEX';
 
 BLEVEL    LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS   LAST_ANALYZED
---------- ----------- ------------- ----------------- ---------- ----------------------------
 0            0         0            0                  0          0
 
 -- Index만 수집
 SQL>  BEGIN
 DBMS_STATS.GATHER_INDEX_STATS
 (OWNNAME='TEST_USER',IDXNAME='TEST_INDEX',PARTNAME=NULL,
 ESTIMATE_PERCENT=1,DEGREE=4,NO_INVALIDATE=TRUE);
 END;
 /
 
 -- Table과 Index 동시 수집
 BEGIN
 DBMS_STATS.GATHER_TABLE_STATS 
(OWNNAME='TEST_USER',TABNAME='TEST_TABLE',PARTNAME=NULL,
 ESTIMATE_PERCENT=10, METHOD_OPT='FOR ALL COLUMNS SIZE 1',
 DEGREE=4,CASCADE_INDEXES=TRUE,
 NO_INVALIDATE=FALSE, GRANULARITY='ALL');
 END;
 /
 
 -- 통계 정보 존재 확인
 SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,CLUSTERING_FACTOR,NUM_ROWS,LAST_ANALYZED
 FROM ALL_INDEXES WHERE OWNER='TEST_USER' AND INDEX_NAME='TEST_INDEX';
 
 BLEVEL    LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS   LAST_ANALYZED
---------- ----------- ------------- ----------------- ---------- ----------------------------
 2          2830         944650      .116093795         1013600    2025/12/05
 

GATHER_INDEX_STATS는 해당 Index만 수집할 수 있으며, 컬럼 정보는 생성할 수 없습니다.

1.3 Column 통계

-- 모든 Column들을 단일 Bucket으로 수집 (히스토그램 미생성)
 BEGIN
 DBMS_STATS.GATHER_TABLE_STATS 
 (OWNNAME='TEST_USER',TABNAME='TEST_TABLE',
 PARTNAME=NULL,ESTIMATE_PERCENT=0,
 METHOD_OPT='FOR ALL COLUMNS SIZE 1');
 END;
 /
 
 -- Index가 걸린 Column들만 단일 Bucket으로 수집 (히스토그램 미생성)
 BEGIN
 DBMS_STATS.GATHER_TABLE_STATS 
(OWNNAME='TEST_USER',TABNAME='TEST_TABLE',
 PARTNAME=NULL,ESTIMATE_PERCENT=0,
 METHOD_OPT='FOR ALL INDEXED COLUMNS SIZE 1');
 END;
 /
 
 -- Column 수집(히스토그램 Bucket 개수 지정)
 BEGIN
 DBMS_STATS.GATHER_TABLE_STATS 
(OWNNAME='TEST_USER',TABNAME='TEST_TABLE',
 PARTNAME=NULL,ESTIMATE_PERCENT=0,
 METHOD_OPT='FOR ALL COLUMNS SIZE 100');
 END;
 /
 
 -- Column별로 다른 Bucket 개수 지정
BEGIN
 DBMS_STATS.GATHER_TABLE_STATS 
(OWNNAME='TEST_USER',TABNAME='TEST_TABLE',
 PARTNAME=NULL,ESTIMATE_PERCENT=0,
 METHOD_OPT='FOR COLUMNS SIZE 2 A SIZE 100 C SIZE 200');
 END;
 /
 
 -- Column 히스토그램 조회
SELECT COLUMN_NAME, NUM_DISTINCT, 
LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,AVG_COL_LEN
  FROM ALL_TAB_COL_STATISTICS 
 WHERE TABLE_OWNER='TEST_USER' AND TABLE_NAME='TEST_TABLE';
 
 COLUMN_NAME NUM_DISTINCT LOW_VALUE                                   HIGH_VALUE           
 DENSITY    NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE 
AVG_COL_LEN
----------- ------------ ------------------------------------------ ------------------
----------- ------------ --------- ----------- ------------- 
----------- 
A           1000501      1.5000000000000000000000000000000000000E+01 
9.9999900000000000000000000000000000000E+05 .000014521 0         100         2025/12/05    
68763       3.97988744
B           1001370      2.6000000000000000000000000000000000000E+01 
1.0000000000000000000000000000000000000E+06 .000000999 0         1           2025/12/05
100137      3.98002736
C           1000501      -9.999990000000000000000000000000000000E+05
-1.500000000000000000000000000000000000E+01 .000014566 0         200         2025/12/05
68763       4.97988744

2 Partition Table 통계

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

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

2.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

2.2 Global Statistics 수집 여부

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

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

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

2.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';

2.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의 경우 일 별 파티션이므로 쿼리 속도가 체감할 만큼 크게 느껴지지 않지만 시간 별 파티션일 경우에는 통계 정보가 더 빠르게 어긋납니다.

2.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 통계 수집할 때 업무 환경 특성을 고려한 전략이 필요하며, 이런 환경에서 활용할 수 있는 기능은 "통계의 고급 기능" 참고

3. Incremental Global 통계

새로운 파티션에 Data가 추가되거나 기존의 파티션 중 수정된 데이터가 있는 경우, Table level의 통계 수치를 다시 수집하기 위해 Table 전체를 모두 Scan해야 합니다. 다시 말해 파티션이 적용된 Table의 Global 통계 정보를 수집하기 위해서는 각 파티션을 Scan하는 Partition-level 통계 정보를 수집한 후, Global 통계 정보를 수집하기 위해 전체 파티션을 Scan하는 작업을 수행해야 합니다.

예를 들어 파티션 및 Table의 통계 정보를 수집하기 위해 Granularity를 ALL로 지정하면 전체 파티션이 2번 Scan 되므로 시간에 영향을 미치게 됩니다. 이를 피하기 위해 새로 추가된 파티션의 통계 정보만 생성하고 Global 통계 정보를 생성하는 방법도, Global 통계 정보 생성을 위해 전체 파티션을 Scan하게 되므로 비효율적입니다. (해당 파티션 1회 Scan + 전체 파티션 1회 Scan)

이때 Incremental Global('APPROX_GLOBAL AND PARTITION' granularity)를 사용하면 이러한 성능 문제를 개선할 수 있습니다. Incremental Global 통계는 전체 Table을 Scan하지 않고 변경된 파티션만 Scan하여 Global Table 통계를 갱신합니다. (해당 파티션 1회 Scan + 기존 파티션 정보를 이용하여 GLOBAL 통계 갱신)

해당 기능은 파티션 Table에 대한 Global 통계 수집을 향상시키는 것을 목적으로 합니다. 파티션 Table의 경우 시스템은 각 Partition 통계와 Table에 대한 전체 Global 통계를 유지하게 됩니다.

Incremental Global 통계의 적용 과정은 다음과 같습니다.

1. Table을 생성하고 데이터를 입력한 후 파티션을 생성합니다.

2. Granularity를 ALL로 지정하여 초기 GLOBAL, PARTITION 통계를 수집합니다.

3. 통계 수집된 상태를 확인합니다. (모든 GLOBAL, PARTITION, Table column에 통계가 수집됨)

4. 신규 파티션을 추가하고 해당 데이터를 입력합니다.

5. Granularity를 “APPROX_GLOBAL AND PARTITION”로 지정하여 gather_table_stats를 수행합니다.

6. 통계 수집된 상태를 확인합니다.

대용량 파티션 Table 중 Range 파티션의 경우 과거 파티션에 대한 데이터 변경이 거의 발생하지 않기 때문에 Global 통계를 매번 수집하는 것은 비효율적일 수 있습니다. 이를 개선하기 위해 과거 파티션 통계 수집에 필요한 정보를 저장해 두고, Global 통계 정보를 수집할 때 과거 파티션은 저장된 정보를 이용하여 통계 수집 시간을 단축할 수 있습니다.