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 유저로 수행한다.

 

 

-- create_index_monitoring.sql------------------------------------------------------------

 

set feedback off

set heading off

set lines 120

set pages 9999

 

spool index_monitoring.sql

 

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;'

FROM DBA_INDEXES

WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'CTXSYS', 'OLAPSYS', 'DRSYS', 'MDSYS', 'XDB',

'WMSYS', 'ORDSYS', 'ODM', 'WKSYS', 'OUTLN', 'SYSMAN', 'EXFSYS', 'DMSYS', 'RMAN')

AND INDEX_TYPE NOT IN ('DOMAIN', 'LOB');

 

spool off

 

set heading on

set feedback on

 

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

 

SQL> desc v$object_usage

Name Null? Type

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

INDEX_NAME NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

MONITORING VARCHAR2(3)

USED VARCHAR2(3)

START_MONITORING VARCHAR2(19)

END_MONITORING VARCHAR2(19)

 

set lines 110

set pages 999

column index_name format a20

column monitoring format a10

column used format a10

column start_monitoring format a20

column end_monitoring format a20

SELECT index_name, monitoring, used, start_monitoring, end_monitoring

FROM v$object_usage;

 

no rows selected.

 

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

 

 

SQL> desc object_usage

 

Name Null? Type

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

OBJ# NOT NULL NUMBER

FLAGS NOT NULL NUMBER

START_MONITORING VARCHAR2(19)

END_MONITORING VARCHAR2(19)

 

set lines 140

set pages 9999

col owner format a10

col index_name format a35

col table_name format a30

col monitoring format a10

col start_monitoring format a20

col end_monitoring format a20

 

spool report_index_usage.txt

select username as owner,

io.name as index_name,

t.name as table_name,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as monitoring,

decode(bitand(ou.flags, 1), 0, 'NO', 'YES') as used,

ou.start_monitoring,

ou.end_monitoring

from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, dba_users u

where i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#

and user_id=t.owner#;

spool off;

*

사업자 정보 표시
(주)블루원 | 김홍태 | 서울특별시 용산구 원효로 4가 135 금홍 2빌딩 | 사업자 등록번호 : 106-86-76684 | TEL : 02-3272-7200 | Mail : support_ora@blueone.co.kr | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기

+ Recent posts