개요

방법

1. 샘플링

Tibero는 통계 수집을 위해 샘플링을 할 때 Table에 대해서 Row sampling이라는 방식을 사용합니다. Row sampling의 경우 샘플링 %가 낮아도 특성상 대부분의 Block을 스캔 하기 때문에 대용량일 경우 오랜 시간이 소요됩니다. 

통계를 산정하기 위해 샘플 크기를 설정하며, ESTIMATE_PERCENT의 비율에 따라 샘플링할 Row의 비율을 결정합니다. Table의 모든 Row를 대상으로 하면 정확한 통계 정보를 수집할 수 있지만 샘플 크기가 크면 통계 정보를 수집하는 시간과 리소스는 그만큼 상승됩니다. 즉, 통계 정보 수집이 일부 데이터를 샘플링하는 것이라 하더라도 대용량이면 샘플링 데이터 자체도 무시할 수 없는 양이 됩니다. 

Tibero는 각 Table에 적절한 샘플 크기를 결정하지만 자동으로 결정되는 샘플 크기는 작기 때문에 데이터의 분포도가 고르지 못한 상황에서는 적절한 샘플 크기를 설정하여 사용하는 것이 효율적입니다.

1.1 ESTIMATE_PERCENT

운영시스템 중에서 DBA가 자체적으로 Row 개수별 ESTIMATE_PERCENT값의 기준을 정하여 통계 정보를 수집하 는 경우가 많은데 샘플링 %값을 높이면 그만큼 더 정밀한 통계 정보가 나오는 것은 맞지만 통계 수집할 때 샘플링 비율만큼 또는 그 이상의 성능 저하는 피할 수 없습니다. 따라서 수집 시간과 정확성 사이에서 적절한 샘플링 %를 찾는 것이 필요합니다.

만약 0으로 설정할 경우 Tibero에서 자체적으로 결정하게 된다. 결정 기준에 대한 자세한 내용은 “ 1.3.2. 설정 항 목”을 참고합니다. 

ESTIMATE_PERCENT의 샘플링 비율을 결정하는 기준 항목을 적용하여 각각 5억 건과 20만 건의 Row개수가 있을 때의 샘플링 비율은 다음과 같습니다.

-- 5억 건의 대용량 Table일 경우 100,000 / row 개수 x 100 (%) 산정 방식을 적용한다. 이 경우 
estimate_percent는 100,000 / 500,000,000 * 100 = 0.02%가 된다.

-- 20만 건의 Table일 경우(1,000,000개 미만 10%)
이전 예제인 DAILY_LOG Table은 파티션 당 10,000개의 row에 20개의 파티션으로 구성되어 있으므로 총 
Row 개수는 200,000이다.

따라서 estimate_percent는 10%이다.

참고

ESTIMATE_PERCENT 기준은 “ 설정 항목”의 ESTIMATE_PERCENT를 참고

2. 동적 샘플링

동적 샘플링은 통계 정보가 없는 Table에 대해서 정확한 Selectivity와 Cardinality의 추정치를 결정하여 성능을 향상시키는데 목적이 있습니다. Tibero는 Parse되는 시점에 동적 샘플링을 수행할 것인지를 결정합니다. 

동적 샘플링 수행이 결정되면 해당 쿼리의 Table block에 대한 Random 샘플링 스캔을 하여 주요한 Single table에 대한 Selectivity를 결정합니다. 통계 정보가 있더라도 동적 샘플링이 필요한 상황이 있을 수 있으나 Pasing할 때 부하가 발생되고 통계 정보를 이용하는 것보다 부정확할 수 있기 때문에 특수한 상황이 아니라면 통계 정보를 생성하는 것이 좋습니다. 

샘플링으로 인한 오버헤드를 줄이고 바인드 변수를 사용해야 하는 OLTP 환경에서는 동적 샘플링을 피해야 하지만 Parse시간이 Execute 시간에 비해 저렴한 DW 환경에서는 동적 샘플링을 고려해 볼 수 있습니다.

정상적인 쿼리에는 동적 샘플링이 적용되지 않으며 아래와 같은 상황에는 Optimizer가 샘플링을 선택합니다.

• 통계 정보의 한계로 CBO가 정확한 예측을 할 수 없을 때 동적 샘플링을 하는 것이 좀 더 좋은 실행 계획을 만 들 수 있다고 판단될 때 
• Sampling time이 쿼리의 전체 실행 시간의 일부분에 지나지 않는다고 판단될 때 
• 쿼리 실행 시간이 오래 걸린다고 판단될 때

Block 단위로 샘플링을 수행합니다. 이때 16block으로 시작해서 Table block 총 개수 규모에 따라 최대 300block까 지 수행됩니다.

3. Granularity

대용량 파티션 Table의 경우 쿼리 조건문에 파티션 키 Column이 사용되는 경우가 많고, 그 외 Column들은 파티션 별로 분포도가 크게 다르지 않은 경우가 많습니다. 

만약 파티션별 물리적 정보(Row 개수, Block 개수, B-level, Leaf block 개수 등)가 큰 차이가 없을 것으로 예상되면, GLOABAL Granularity 옵션을 통한 통계 수집도 좋은 방법이 될 수 있습니다.

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

GLOBAL 옵션은 파티션 레벨이 아닌 Table, Index 전체 단위의 통계 정보가 수집됩니다.

파티션 레벨 통계 정보가 수집되지 않아도 Optimizer에서 실행 중에 Dynamic 샘플링을 해서 일부 물리적인 정보를 가져오므로 크게 오차가 생기지는 않습니다. GLOBAL로 수집하더라도 Column 통계 정보. 즉, 히스토그램이 수집되므로 Cardinality 예측값에 따른 Index 스캔과 Table 스캔 사이의 선택 문제도 어느 정도 해결됩니다. 

하지만 아무리 Table, Index 레벨만 수집한다고 해도 Table 자체가 대용량이기 때문에 샘플링을 하는 시간이 오래 걸릴 수 있습니다. 

파티션 옵션은 partition key column에 대한 min, max, distinct 개수, 히스토그램이 갱신되어 수집된 통계의 정확성을 높힙니다. 

Tibero는 통계 수집을 위해 샘플링을 할 때 Table에 대해서 Row sampling이라는 방식을 사용합니다. Row sampling의 경우 샘플링 %가 낮아도 특성상 대부분의 Block을 스캔하기 때문에 대용량일 경우 오랜 시간이 소요됩니다. 따라서 ALL granularity에 비해서는 빠르지만 자주 수집하는 데에는 역시 부담이 따릅니다. 

만약 파티션 키 Column 통계 정보가 제일 중요하게 작용하고 파티션 키 Column에 대한 equal 조건문이 대부분이라면 _OPT_BOUND_SELEC_ADJUST_DEGREE라는 tip 파라미터값이 도움이 됩니다. 

다음은 _OPT_BOUND_SELEC_ADJUST_DEGREE(Default : 100) 값을 설정하는 예입니다.

SQL> ALTER SYSTEM SET _OPT_BOUND_SELEC_ADJUST_DEGREE=100; 

_OPT_BOUND_SELEC_ADJUST_DEGREE 파라미터는 equal 조건문 대상값이 히스토그램 상의 범위에서 일정 부분 벗어나도 어느 정도의 선택도는 보정이 됩니다.

예를 들어 히스토그램 상의 Column값 범위가 0~100일 경우 파라미터값을 100으로 주면 100 ~ 200, -100 ~ 0 사 이의 값들도 일정 부분 히스토그램 범위 내의 값들처럼 선택도를 인정해줍니다. (범위에서 멀어질수록 선택도 감소)

• 설정값이 50일 경우 : 100 ~ 150, -50 ~ 0 까지 인정
• 설정값이 200일 경우 : 100 ~ 300, -200 ~ 0 까지 인정

_OPT_BOUND_SELEC_ADJUST_DEGREE 값 조정을 통해 통계 정보 수집 주기를 줄일 수 있지만 일별, 시간별로 증가하는 파티션 Table 같은 경우 기가바이트 또는 테라바이트 단위가 될 수 있으므로 수집 자체가 상당한 부담이 될 수 있습니다. 따라서 이 방법은 파티션 개수가 많고 데이터 규모는 크지 않은 환경에서만 사용하는 것을 권장합니다. 

참고

Granularity 설정 방법 및 예제에 대한 자세한 설명은 “파티션 Table 통계”를 참고

4. 통계 복제

파티션간에 통계 정보를 복사하는 기능으로 파티션이 추가되더라도 별도 통계 수집이 필요없다는 장점이 있습니다.

운영시스템에서 주기적으로 파티션을 추가하고 데이터를 로드할 때 데이터양이 매우 많기 때문에 통계 정보를 직접 수집하지 않고 새로운 파티션에만 통계 정보를 수집할 수 있습니다. 

파티션 키 Column(composite key일 경우 첫 번째 Column)에 대한 히스토그램도 보정해주기 때문에 새로 추가된 Column값에 대한 쿼리 조건문의 Cardinality가 크게 어긋나지 않습니다. 

“일정 주기로 파티션이 추가되는 Table”에서 설명한 예제의 DAILY_LOG Table 파티션이 P20250120까지 생성된 채로 통계 수집을 하고 P20250121 파티션이 추가되었을 경우 아래와 같이 통계 정보를 복사할 수 있습니다. (stats_copy.sql)

SQL> CALL DBMS_STATS.COPY_TABLE_STATS (USER, 'DAILY_LOG', 'P20230120', 'P20230121');
SQL> ALTER SYSTEM FLUSH PPC;

통계를 복제한 후 다음과 같이 확인합니다.

SQL> set autotrace traceonly exp planstat
SQL> SELECT * FROM DAILY_LOG WHERE LOG_DAY = '20250121';

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" = '20250121') (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

DAILY_LOG Table을 샘플링하지 않고 DD Table에 저장된 통계 정보값들을 복사만 한다는 점에서 성능상으로 큰 이점이 있습니다. 

P20250121 파티션내 실제 물리적 통계값을 반영한 것이 아니므로 파티션별로 물리적 정보(Row 개수, Block 개수 등)가 큰 차이가 난다면 Optimizer에서 부정확한 Plan이 나올 수도 있습니다. 하지만 일별, 시간별 파티션 Table의 특징 상 쿼리들은 파티션 키 Column을 조건문으로 사용하고 키 외 Column들의 분포도도 파티션별로 차이가 크게 나지 않기 때문에 추가된 파티션의 물리적 통계 정보와 키 Column의 통계 정보만 채워넣어도 안정적인 Plan을 확보할 수 있습니다. 

통계 수집도 Table을 스캔하는 작업이 이루어지기 때문에 대용량일 경우 오랜 시간이 걸리므로 운영중인 서버에서의 주기적인 통계 수집은 신중할 필요가 있습니다. 

대용량 환경의 특성상 Cardinality 예측이 많이 어긋날 경우 잘못된 Plan으로 인해 과다한 Rowid scan, Index join으로 인한 과다한 Index scan이 발생할 수 있기 때문에 통계 정보의 적절한 갱신은 꼭 필요하지만 대용량이기 때문에 자주 수집을 할 수 없는 점도 기억해야 합니다. 

이러한 상황에서 통계 복제를 사용하면 운영 중인 서버에도 큰 부하 없이 꼭 필요한 통계 정보들을 갱신할 수 있으므로 안정적인 쿼리 Plan 추출 및 수행이 가능합니다.

5. 자동 통계 수집

자동 통계 수집 기능은 운영 시스템의 환경별로 사용 여부를 결정해야 한다. 일반적으로 중, 대형 이상의 업무 환경 시스템일 경우는 자동 통계 수집 기능은 사용하지 않도록 권장합니다. 이러한 시스템을 운영하는 Database 관리자는 Object별 전략을 세우고 정확하고 안정적인 통계 정보를 수동으로 수집할 수 있도록 준비하는 것이 좋습니다. 

다음은 JOB에 등록된 통계 수집을 확인하는 예입니다.

SQL> col next_date format a10;
SQL> col interval format a20;
SQL> col this_date format a10;
SQL> col last_date format a10;
SQL> col what format a50;
SQL> select next_date, interval,broken,failures, this_date,last_date,what 
  from dba_jobs 
 where what like '%dbms_stats%';

 NEXT_DATE INTERVAL         BROKEN FAILURES THIS_DATE  LAST_DATE WHAT
---------- ---------------- ------ -------- ---------- ---------- ----------------------
2023/06/20 TRUNC(SYSDATE)+1      N        1 2023/06/19 /*+EVERY_MIDNIGHT_STAT_GATHERING*/
                                                       begin 
dbms_stats.gather_database_stats
                                                       (gather_sys=>true, options
                                                       =>'GATHER AUTO',
                                                       method_opt=>'FOR ALL INDEXED COLU
                                                       MNS SIZE REPEAT'); end;

참고

상세 옵션에 대한 자세한 설명은 "tbPSM 참조 안내서"의 "DBMS_JOB"을 참고

Optimizer는 Object 통계를 기반으로 Cardinality를 추정하는 계산을 하는데 이것은 실행 계획에 많은 영향을 줍니다. Object 통계를 효율적으로 수집하기 위해 다양한 설정 옵션들을 제공하지만 기본적으로 제공하는 파라미터 기본값과 자동 통계 수집 기능을 사용하는 시스템이라면 최적의 성능을 보장할 수는 없습니다.

대용량 Database를 운영할 때 통계의 세부 설정을 통하여 수집 시간과 자원의 소모를 줄이는 방법을 사용하기 위해 DBMS_STATS 패키지의 프로시저별 파라미터 옵션 사용을 권장합니다.

수동 통계 수집은 대용량 시스템에서 사용하는 Partition Table의 통계를 수집할 때 다음과 같은 장단점이 있습니다.

• 장점

  새로 추가된 파티션에 대한 통계 수집 설정으로 수집 시간 및 자원 사용을 감소

  데이터 적재량이 증가하는 파티션 Table에 접근할 때 실행 계획의 잠재적인 안정성을 개선

• 단점

  직접적인 통계 옵션 설정으로 Global 통계를 정확하고 완벽하게 유지하는 것을 보장하지 않음

  DBA가 관리하는 시스템과 데이터 특성에 대한 상세한 지식이 필수로 요구됨 

  특히 복합 파티션에 대한 세부 설정들은 데이터 특성과 관련 지식이 많지 않으면 입력 및 관리 실수를 유발할 수 수 있음

  새로운 파티션에 대해 설정한 값이 일부 다른 시스템에서는 맞지 않을 수 있음