오라클 11gR2 Blevel 확인 및 REBUILD 할 INDEX 대상 조회

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

방법 : REBUILD 할 INDEX 대상 조회

과거(dbms_stats 명령이 나오기 이전)에는 analyze index 인덱스명 compute statistics 명령으로 인덱스 통계를 수집하고

아래 적혀있는 blevel.sql 쿼리를 실행하여 blevel 이 4이상(HIGH LEVEL) 인 경우 rebuild 대상으로 판단했음
* blevel (Branch level)은 B-Tree 인덱스 형식의 일부이며 이는 오라클이 인덱스 탐색를 할때 몇 단계를 거쳐서 블럭의 위치를 찾아내는가와 관계가 있음

하지만 인덱스 용량에 따라 크기가 크면 클수록 통계를 수집할때 시간도 오래걸리고 인덱스 rebuild 시간도 오래 걸리게됨
물론 인덱스 rebuild는 online으로 가능하지만 시간이 많이 소요되고 작업간 성능 문제 발생 영향도도 무시할수 없음

 

그렇기 때문에 해당 인덱스가 정말 sql 수행시 문제가 되는지, 성능이 안나오는지 확인을 하고 rebuild 수행 여부를 판단해야함
rebuild 수행 전 먼저 인덱스 자체가 문제인지, 해당 쿼리에서 플랜이 잘못 풀려 잘못된 인덱스를 타고 있는건 아닌지에 대한 확인이 필요함

그리고 통계가 기존에 없던 경우라면 통계를 수집해서 플랜이 변경되거나 rebuild 이후 통계가 변경되 문제가 발생할 가능성도 존재함

다만 dba_indexes 에서 blevel이 4 이상인 경우 depth가 깊어 불필요한 블록 읽는 문제가 생기거나, 공간을 낭비할 수는 있음

 

아래는 dbms_stats 명령을 이용해 인덱스 통계를 수집하고 blevel 확인 및 rebuild 하는 방법을 설명함

 

 

확인이 필요한 인덱스 통계수집

SQL> exec dbms_stats.gather_index_stats('SCOTT','PK_DEPT');

 

 

dba_indexes 뷰를 이용해 blevel 확인(blevel.sql)

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?"
, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') last_analyzed
from dba_indexes
where owner = 'SCOTT'
order by blevel desc;
 
INDEX_NAME                         BLEVEL OK?       LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
TEST_IDX                                  BLEVEL HIGH    
TEST2_IDX                               2 OK BLEVEL 2017/08/27 06:00:21
TEST3_IDX                               2 OK BLEVEL 2017/08/27 06:00:18
PK_DEPT                                 2 OK BLEVEL 2017/08/27 06:00:18

* 위의 sql 수행시 통계가 수집되지 않은 인덱스에 대해서는 "BLEVEL HGH" 로 나타남

 

 

대상 인덱스 rebuild

SQL> alter index SCOTT.PK_DEPT rebuild online;
 
Index altered.

 

 

인덱스 blevel 재확인

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?"
, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') last_analyzed
from dba_indexes
where owner = 'SCOTT'
order by blevel desc;
 
INDEX_NAME                         BLEVEL OK?       LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
TEST_IDX                                  BLEVEL HIGH    
TEST2_IDX                               2 OK BLEVEL 2017/08/27 06:00:21
TEST3_IDX                               2 OK BLEVEL 2017/08/27 06:00:18
PK_DEPT                                 0 OK BLEVEL 2018/05/03 07:19:42

blevel이 0으로 변함

 

 

참조 : 989093.1, 989186.1

http://mclee.tistory.com/115

http://gilab.kr/dbtech/bbs/?bo_c=1040&bo_v=130

https://cafe.naver.com/dbian/4601

https://community.oracle.com/mosc/discussion/4023158/when-should-we-consider-an-index-as-a-candidate-for-rebuilding

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-CE899A61-1922-48E6-88DE-A7278C247E8C

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/indexes-and-index-organized-tables.html#GUID-89A9F85F-BE0E-4596-AEC3-CAF0D821B1CA

https://www.inflearn.com/questions/377672/index-rebuild%EA%B4%80%EB%A0%A8

https://cafe.naver.com/prodba/16759