Table fragmentation ?

Table fragmentation ?

 

oracle database 를 운영하면서, 주간/월간 점검 또는 성능 이슈로 인하여 테이블 fragmentation 이슈에 대해서
직면하게 됩니다.
그래서, 간략하게 테이블 fragmentation 이슈에 대해서 정리해 보았습니다.

 

테이블 fragmentation 이슈로 발생할 수 있는 문제점은 크게 두 가지 정도 구분할 수 있습니다.
 - 첫 번째, Full table scan 으로 인한 성능 저하 문제
 - 두 번째, Segment Size 증가로 인한 disk 비용 증가

두 가지 문제점을 사전에 예방하기 위한 활동으로 DB 월간/주간 점검 시, 테이블 fragmention 대상 식별하거나 주요 테이블 또는
DML 이 빈번한 테이블에 대해서 정기적으로 reorg 작업 합니다.

 

대상 식별 및 확인 방법은 아래와 같습니다.
 - 해당 방법은 정기적으로 테이블에 대해서 analyze 을 수행할 경우 보다 정확한 데이터를 얻을 수 있습니다.
 - analyze를 하여도 운영중인 SQL 영향도가 크지 않다면 식별전 analyze 를 하는 것을 권장 드리며, 영향도가 있다면 테이블 row 전체 count 를하여
   진행하여도 문제가 되지 않습니다.

 

1) Fragmentation 테이블 식별
 - num_rows("전체 row 수") 는 analyze 를 통해서 근사값 또는 전체 count 하여 정확한 값으로 사용하여도 됩니다.
 - avg_row_len("평균 row 길이") 값은 analyze 를 사용하여 가장 근사값으로 사용 권장합니다.
 - blocks_needed 컬럼은 "전체 row 수"/"평균 row 길이"/"Block 당 사용 공간" 기준으로 산술적으로 필요한 block 수를 계산한 값
   : 8000 값은 한 block 의 크기 8192 bytes에서 일반적으로 한 block 가지고 있는 overhead 값인 약 200 bytes 를 제외한 근사 값
 
SQL>select table_name,
           blocks, num_rows, avg_row_len, pct_free,
           ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100))) blocks_needed
      from dba_tables
     where table_name = 'TB_LOGN_HIST';

TABLE_NAME                     BLOCKS      NUM_ROWS    AVG_ROW_LEN PCT_FREE    BLOCKS_NEED
------------------------------ ----------- ----------- ----------- ----------- -----------
TB_LOGN_HIST                    1792      101254          67          10         943

: 테이블의 현재 Block 개수는 1792 로 산술적으로 산정한 943 보다 849 만큼 비효율적으로 공간를 사용하고 있음을 확인 할 수 있다.
: 이 테이블의 업무적인 성격은 하루치 대량의 데이터를 삭제 및 적재하는 daily 배치이므로, 위와 같이 비효율적으로 block 을 사용할 여지가 있다.

 

2) Framentation 테이블 reorganization 작업
 - alter table move tablespace 방법
 - CTAS(CREATE TABLE ~ AS SELECT ~) 방법
 - trucate/exp(dp)&imp(dp) 방법 등등
 : 일반적으로 가장 선호 방법은 alter table move tablespace 또는 CTAS 이므로 이 방법으로 reorg 를 수행합니다.

SQL>alter table TB_LOGN_HIST move tablespace ts_hist_d;
SQL>analyze table TB_LOGN_HIST compute statistics;
SQL>select table_name,
           blocks, num_rows, avg_row_len, pct_free,
           ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100))) blocks_needed
     from dba_tables
    where table_name = 'TB_LOGN_HIST';

TABLE_NAME                     BLOCKS      NUM_ROWS    AVG_ROW_LEN PCT_FREE    BLOCKS_NEED
------------------------------ ----------- ----------- ----------- ----------- -----------
TB_LOGN_HIST                    1059      102302          71          10        1009

 

 : 테이블 reorg 작업 이후, 1792 에서 1059 으로 block 개수가 감소하여 segment size 및 full table scan 작업의 효율성을 증대가 되었습니다.
 : 그리고, 실제 block 개수와 산술적으로 산정한 block 개수간의 수치도 근소하게 차이나는 수준으로 되었음을 확인 할 수 있습니다.