database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 9271 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 9271
REBUILD 할 INDEX 와 BITMAP INDEX 로 만들어야 하는 인덱스를 조사하기
작성자
정재익(advance)
작성일
2002-01-05 17:38
조회수
6,026

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

이 글에 대한 댓글이 총 1건 있습니다.

http://www.koug.net 으로 접속하시면 됨돠.

 

유동아이피여서 바뀌었거덩여....^^;;

 

아흑...고정아이피...ㅠ,.ㅠ

jinuxer님이 2002-06-03 17:42에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
9276몇가지 유용한 SQL scripts
정재익
2002-01-05
10821
9273MANIPULATING LARGE OBJECTS USING BMS_LOB PACKAGE
정재익
2002-01-05
6028
9272ORACLE DATABASE 재설치하기
정재익
2002-01-05
6507
9271REBUILD 할 INDEX 와 BITMAP INDEX 로 만들어야 하는 인덱스를 조사하기 [1]
정재익
2002-01-05
6026
9270PARALLEL QUERY OPTION 사용 및 확인
정재익
2002-01-05
6834
9269WEB상에서 PLSQL로 EXCEL FILE만드는 예제
정재익
2002-01-05
5045
9268Listener패스워드 설정 방법
정재익
2002-01-05
5410
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.050초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다