1. 개요

    

   데이터베이스를 운영 중에 새로운 인덱스를 생성하거나, 기존의 인덱스 변경됨으로써 영향을 받아 사용되지 않는 인덱스가 생겨날 수 있다. 따라서 그러한 인덱스를 찾아 수정하거나 제거할 필요성이 제기된다. 실제로 튜닝작업 이후 기존에 있던 인덱스를 제거하려는 경우, 실제로 어떤 인덱스를 제거해야 할지 결정하기란 쉽지 않다. 특정 기간동안의 쿼리에 대한 엑세스 유형을 분석하고, 인덱스의 사용유무를 모니터링한 이후에 사용되지 않는 인덱스를 제거해야만 할 것이다.

    

   오라클 9i 에서 제공되는 다이나믹 뷰인 v$object_usage를 이용하여 위의 작업을 수행하고, 그 결과를 토대로 인덱스의 사용현황을 파악할 수 있다.

    

2. Index 사용현황 분석방법

    

3. V$OBJECT_USAGE

    

   Column Name	Null ?	Data Type	Description
   INDEX_NAME	NOT NULL	VARCHAR2(30)	인덱스명
   TABLE_NAME	NOT NULL	VARCHAR2(30)	인덱스 테이블명
   MONITORING		VARCHAR2(3)	모니터링 수행 여부
   USED		VARCHAR2(3)	인덱스 Access 여부
   START_MONITORING		VARCHAR2(19)	모니터링 수행 시작
   END_MONITORING		VARCHAR2(19)	모니터링 수행 종료

   표 1 – Desciption of 'v$object_usage

    

4. 사용방법

    

   -- # 인덱스 모니터링 사용 예제     -- 1. Sample Table 및 Index를 생성한다.   SQL> CREATE TABLE SAMPLE( 2 ID NUMBER(4), 3 DATA VARCHAR2(10) 4 ) 5 /   Table created.   SQL> INSERT INTO SAMPLE VALUES (1, 'aaaa');   1 row created.     SQL> INSERT INTO SAMPLE VALUES (2, 'bbbb');   1 row created.   SQL> INSERT INTO SAMPLE VALUES (3, 'cccc');   1 row created.   SQL> INSERT INTO SAMPLE VALUES (4, 'dddd');   1 row created.   SQL> COMMIT;   Commit complete.     -- 2. 테이블에 PK 인덱스를 추가한다.   SQL> select * from sample;   ID DATA ---------- -------------------- 1 aaaa 2 bbbb 3 cccc 4 dddd   SQL> ALTER TABLE SAMPLE ADD CONSTRAINT pk_sample_id PRIMARY KEY(id);   Table altered.   SQL> desc sample; Name Null? Type ------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(4) DATA VARCHAR2(10)   SQL> SELECT index_name, table_name, index_type 2 FROM user_indexes 3 WHERE table_name = 'SAMPLE';   INDEX_NAME TABLE_NAME INDEX_TYPE -------------------- -------------------- -------------------- PK_SAMPLE_ID SAMPLE NORMAL     -- 3. v$object_usage 뷰를 확인한다. – 아직까지는 아무 것도 조회되지 않는다.   SQL> set lines 110 SQL> set pages 999 SQL> column index_name format a20 SQL> column monitoring format a10 SQL> column used format a10 SQL> column start_monitoring format a20 SQL> column end_monitoring format a20 SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring 2 FROM v$object_usage;   no rows selected     -- 4. 인덱스 사용여부를 알기 위해 모니터링을 사용한다.   SQL> ALTER INDEX pk_sample_id MONITORING USAGE;   Index altered.         -- 5. v$object_usage 를 쿼리하여 모니터링 여부를 확인한다.   SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring 2 FROM v$object_usage;   INDEX_NAME MONITORING USED START_MONITORING END_MONITORING -------------------- ---------- ---------- -------------------- -------------------- PK_SAMPLE_ID YES NO 09/16/2007 15:40:41   -- 6. 해당 테이블에 대해 인덱스를 타도록 쿼리를 테스트한다. -- Execution Plan 을 보기 위해 plan table을 생성한다.   SQL> @$ORACLE_HOME/rdbms/admin/utlxplan   Table created.   SQL> set autotrace on explain SQL> SELECT * FROM sample WHERE id = 2;   ID DATA ---------- -------------------- 2 bbbb   Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SAMPLE' 2 1 INDEX (UNIQUE SCAN) OF 'PK_SAMPLE_ID' (UNIQUE)   -- 7. v$object_usage 를 쿼리하면 인덱스 사용여부를 알 수 있다.   SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring 2 FROM v$object_usage;   INDEX_NAME MONITORING USED START_MONITORING END_MONITORING -------------------- ---------- ---------- -------------------- -------------------- PK_SAMPLE_ID YES YES 09/16/2007 15:40:41   -- 7. 인덱스 모니터링을 중지한다.     SQL> ALTER INDEX pk_sample_id NOMONITORING USAGE;   Index altered.     SQL> SELECT index_name, monitoring, used, start_monitoring, end_monitoring 2 FROM v$object_usage;   INDEX_NAME MONITORING USED START_MONITORING END_MONITORING -------------------- ---------- ---------- -------------------- -------------------- PK_SAMPLE_ID NO YES 09/16/2007 15:40:41 09/16/2007 18:34:47

    

   <참고> Index Monitoring을 수행할 때 한 번이라도 사용되어 v$object_usage 뷰에 저장이 되었다면, 더이상 해당 인덱스에 대해 모니터링 되지 않는다. 그러므로 다량의 인덱스에 대한 모니터링으로 인해 시스템에 부하를 줄 염려는 없다.

    

    

    

    

5. DB 전체 Index 사용현황 분석

6. 개요

   현업에서 운영중인 DB내에서 데이터의 액세스를 빠르게 수행하기 위해 인덱스를 생성한다. 생성한 인덱스가 쿼리에서 더 이상 사용되지 않는다고 판단이 되면 제거를 하는 것이 테이블의 데이터 조작(DML)시 인덱스 갱신에 대한 부하를 최소화 할 수 있다. 특히 OLTP 업무가 많은 DB의 경우 테이블에 불필요한 인덱스가 많이 생성되어 있다면 DML 작업은 그 만큼 느려질 수 밖에 없다.

   한편, 통계정보의 갱신이나 튜닝등으로 수행되는 플랜이 변경된 경우에도 인덱스 사용여부를 민감하게 체크해 볼 필요가 있다. 그것이 DBA나 튜너가 의도한 대로 인덱스를 사용하거나 사용하지 않음을 판단한 경우라면, 인덱스의 종속여부를 결정할 수 있다. 하지만 그외 다른 인덱스들에 대해서는 운영중에 사용되고 있는지 곧바로 알기 어렵다.

   그래서 DB내에 유저가 생성한 모든 인덱스에 대하여 일정기간 동안 모니터링을 수행하여, 사용여부를 판가름해 볼 수 있다. 앞서 오라클에서 제공하는 인덱스 모니터링 기능을 사용하여 구현할 수 있다.

    

7. 인덱스 모니터링 방법

• 우선 전체 인덱스를 파악해서 유저가 생성한 인덱스 만을 찾아서 모니터링을 설정한다.
• 다음은 모니터링 대상을 추출하여, 모니터링을 수행하는 스크립트를 작성한다.
• 도메인 인덱스나 LOB 타입 인덱스는 모니터링을 할 수 없다.
• SYS 유저로 수행한다.

• 다음과 같이 v$object_usage 를 조회하면 아무것도 나오지 않는다.

• 위 쿼리에서 모니터링한 인덱스들이 조회가 되지 않는 이유는 V$OBJECT_USAGE 가 자기 자신의 보유한 오브젝트에 대해서만 보여주기 때문이다. 여기서는 SYS 유저로 쿼리를 실행하고, SYS에 관련된 인덱스에 대해서는 모니터링 하지 않았기 때문에 아무것도 나타나지 않는다.
• V$OBJECT_USAGE 딕셔너리 뷰에 해당하는 실제 데이터는 OBJECT_USAGE에 포함되어 있다.

*