index 분석을 통한 reblild 대상 알아보기

인덱스가 어느 정도의 영역을 사용하는지 알기 위해서는 index_stats 테이블을 만들고

analyze 를 수행하면 index 블럭의 사용 상황을 조회 하면 됩니다.

이러한 블럭의 사용 상황을 조회 하는 것뿐만 아니라 어떤 인덱스가 rebuild 대상인지, 

BITMAP INDEX 의 대상인지를 분석하는 방법을 설명 하고자 합니다.

DBA는 사용중인 인덱스들이 균형을 이루는지, 아니면 rebuild 를 해주어야 할만큼

균형이 깨졌는지 주기적으로 확인할 필요가 있습니다. 

균형이 깨져버린 인덱스는 인덱스의 특정 부분에만 I/O 를 집중시키게 함으로써 성능에 병목현상을 초래하게

됩니다.

아래의 예제는 균형이 깨진 인덱스 (unbalanced indexes) 인지 확인하는 예제 입니다.

1.분석하고자 하는 인덱스에 대한 통계치를 조사. 

  양이 많은 인덱스의 경우

  (수백만건 이상의 row를 지닌 테이블에 대한 인덱스의 경우) COMPUTE STATISTICS

  대신에  ESTIMATE 옵션을 사용합니다.

Example:

SQL>analyze index A1_PK compute statistics;

Index analyzed.

2. 인덱스가 얼마나 균형을 이루었는지 조사

아래의 스크립트를 수행합니다. (index_check.sql)

아래 예는 SCOTT 유저의 모든 인덱스에 대한 조사를 수행합니다.

SQL>select index_name, blevel,

decode(blevel, 0, 'OK BLEVEL',1,'OK BLEVEL',

2, 'OK BLEVEL', 3, 'OK BLEVEL', 4, 'OK BLEVEL','BLEVEL HIGH' ) 'OK?'

from dba_indexes

where owner = 'SCOTT';

INDEX_NAME                         BLEVEL OK?

------------------------------ ---------- -----------

S_CUSTOMER_ID_PK                        0 OK BLEVEL

S_DEPT_ID_PK                            0 OK BLEVEL

S_DEPT_NAME_REGION_ID_UK                0 OK BLEVEL

S_EMP_ID_PK                             0 OK BLEVEL

S_EMP_USERID_UK                         0 OK BLEVEL

S_IMAGE_ID_PK                           0 OK BLEVEL

S_INVENTORY_PRODID_WARID_PK               BLEVEL HIGH

S_ITEM_ORDID_ITEMID_PK                    BLEVEL HIGH

S_ITEM_ORDID_PRODID_UK                    BLEVEL HIGH

S_LONGTEXT_ID_PK                          BLEVEL HIGH

S_ORD_ID_PK                               BLEVEL HIGH

INDEX_NAME                         BLEVEL OK?

------------------------------ ---------- -----------

S_PRODUCT_ID_PK                           BLEVEL HIGH

S_PRODUCT_NAME_UK                         BLEVEL HIGH

S_REGION_ID_PK                            BLEVEL HIGH

S_REGION_NAME_UK                          BLEVEL HIGH

S_TITLE_TITLE_PK                          BLEVEL HIGH

S_WAREHOUSE_ID_PK                         BLEVEL HIGH

17 rows selected.

3. BLEVEL (Branch level)

 B-Tree 인덱스 형식의 일부이며 이는 오라클이 인덱스

서치를 할때 몇 단계를 거쳐서 블럭의 위치를 찾아내는가와 관계가 있습니다.

최악의 경우에는 각각의 BLEVEL 에 대해서 매번 디스크 읽기가 필요할 수 있습니다.

만일 BLEVEL 이 4 이상 나오게 되면 해당 인덱스를 rebuild 를 할 필요가 있습니다.

( select * from dba_indexes where owner = 'SCOTT' and blevel >= 4 ; )

주: 위의 index_check.sql 명령은 Analyze 되지 않은 인덱스에 대해서는

'BLEVEL HGH' 로 나타내게 됩니다.

4. ANALYZE 명령의 VALIDATE STRUCTURE 옵션을 사용

INDEX_STATS 테이블에 추가적인 인덱스 정보를 생성합니다. 

이 테이블은 OWNER 에 대한 정보를 가지고 있지 않으므로 현재의 세션에서 수행된 anlayze 정보를 나타내고 있습니다.

SQL>analyze index SCOTT.S_EMP_ID_PK    validate structure;

Index analyzed.

5. 아래와 같이 인덱스에 대한 정보를 조회 합니다.

SQL>  select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,

     (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS

     from index_stats

     where NAME='&index_name';

Enter value for index_name: S_EMP_ID_PK

old   4:         where NAME='&index_name'

new   4:         where NAME='S_EMP_ID_PK'

PCT_DELETED DISTINCTIVENESS

----------- ---------------

          0               0

6. 조회된 결과에 대한 분석

PCT_DELETED 컬럼은 ?p % leaf 노드가 지워져서 사용되지 않은 상태로 있는지 나타내

줍니다. 지워져서 사용되지 않은 빈도가 높을수록 인덱스는 불 균형 (unbalanced)

상태가 심한 것입니다. 만일 PCT_DELETED 가  20% 이상으로 나타나면 인덱스는

REBUILD 의 대상이라고 보면 됩니다. 만일 좀 더 자주 INDEX REBUILD 를 할 수 있다면

REBUILD 할 PCT_DELETED 기준은 10% 까지로 낮출 수도 있습니다.

높은 PCT_DELETED  값을 그대로 방치 할 경우엔 과도한 redo 할당으로 성능 저하가

생길 수도 있습니다.  

DISTINCTIVENESS 컬럼은 인덱스가 만들어진 컬럼의 값이 얼마나 자주 반복되는 지를

보여줍니다.

예를 들면

만일 1만건의 row와 9000건의 서로 다른 값을 가진 테이블이 있을 때

DISTINCTIVENESS 값은 다음과 같이 계산됩니다.

(10000-9000)*100/10000=10

위와 같은 결과라면 컬럼의 값들이 잘 분산 되어 있다는 결론을 내릴 수 있습니다.  

그러나 만약 2가지 값으로 중복되어 있다면 

(10000-2)*100.10000 = 99.98 

이럴경우는 rebuild 대상이 아니고 BITMAP index 대상이다.

인덱스 분석 수집 정보 확인

ANALYZE INDEX 명령을 수행 한 후 INDEX_STATS를 조회 하면 됩니다.

 
  
SQL> SELECT blocks,  btree_space,  used_space,  pct_used "사용율(%)",
            lf_rows,  del_lf_rows "삭제행"
     FROM  INDEX_STATS;
  
BLOCKS  BTREE_SPACE  USED_SPACE  사용율(%)   LF_ROWS   삭제행
------  -----------  ----------  --------   --------  --------
     5        23984       12489        53        892        51 
 
 
-- 인덱스가 삭제된 행이 많으면 인덱스를 재구축 해야 합니다.
-- 예를 들어 LF_ROW에 대한 DEL_LF_ROWS의 비가 30%를 초과하면 
-- 인덱스를 재구축 해야 합니다.
  
 
-- 인덱스의 재구축..
SQL> ALTER INDEX board_pk  REBUILD;
 인덱스가 변경되었습니다.
 
 
-- 분석 자료의 수집
SQL> ANALYZE INDEX board_pk VALIDATE STRUCTURE;
인덱스가 분석되었습니다.
 
 
-- 다시 index_stats를 조회 하면 삭제행이 0으로 
-- 나오는것을 확인 할 수 있습니다.
SQL> SELECT blocks, btree_space, used_space, pct_used "사용율(%)",
            lf_rows, del_lf_rows "삭제행"
     FROM INDEX_STATS;
 
 BLOCKS  BTREE_SPACE  USED_SPACE  사용율(%)  LF_ROWS    삭제행
-------  -----------  ----------  ---------  -------   --------
      5        24032       11775        49       841        0