|
REBUILD 할 INDEX 와 BITMAP INDEX 로 만들어야 하는 인덱스를 조사하기
인덱스가 어느 정도의 영역을 사용하는지 알기 위해서는 index_stats 테이블을 만들고 analyze 를 수행하면 index 블럭의 사용 상황을 조회 하면 됩니다.
-- Bulletin # 11328 참고
이러한 블럭의 사용 상황을 조회 하는 것뿐만 아니라 어떤 인덱스가 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 를 할 필요가 있습니다.
주: 위의 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 컬럼은 ？ % 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
위와 같은 결과라면 컬럼의 값들이 잘 분산 되어 있다는 결론을 내릴 수 있습니다.
그러나 1만건의 row 가 있지만 2가지 값으로만 중복되어 있다면 -- 다시 말해서 select count(*) from (select distinct column from TAB) 한 결과가 2라면 -- DISTINCTIVENESS 값은 다음과 같이 계산됩니다.
(10000-2)*100/10000 = 99.98
이러한 결과는 이 인덱스에는 매우 적은 distinctive 값들로 이루어져 있기 때문에 rebuild 할 대상이 아니라 BITMAP index 로 만들 대상이라는 것을 알려줍니다.
원본출처 : http://211.209.69.159:8000/Orastudy/board.jsp?bbs=tiptech&pg=1&seq=335&act=view
|
