[Oracle] index monitoring (usage check)

* Oracle 19c를 기준으로 테스트를 진행한 자료입니다.

 

- 생성된 인덱스의 수가 많은데, 어떤 인덱스가 사용되고 있는지 확인하고자 할 때 사용할 수 있는 방법.

  cf) 인덱스를 생성하는 이유는 sql query의 조회 성능을 높이기 위함이지만, 무분별한 인덱스의 생성은 지나친 인덱스 데이터의 증가를 불러올 수 있음. (실제 담당 고객사에 개발자의 무분별한 인덱스 사용으로 인하여 데이터가 달에 90GB 씩 (이 데이터의 대부분이 인덱스 데이터) 증가하는 것을 확인한 사례가 있음.)

 

- 11g 이하 버전에서는 v$object_usage dictionary view에서만 조회가 가능하며, 인덱스를 생성한 해당 유저에서 조회하여야 확인할 수 있음.

- 인덱스를 사용하는지 하지 않는지에 대한 여부를 정확하게 확인하기 위해서는 최소 한두 달 정도 해당 기능을 켜 둔 뒤 모니터링할 것을 권고함.

 

1) Monitioring 설정

-- 인덱스 모니터링 ON
SQL> alter index [index명] monitoring usage;

-- 인덱스 모니터링 OFF
SQL> alter index [index명] nomonitoring usage;

cf) Oracle 12.2부터 Index Monitoring을 설정하지 않아도 dba_index_usage_dictionary 에서 인덱스에 대한 사용 유/무를 확인할 수 있지만, 이는 sampling이기 때문에 누락된 정보가 있을 수 있음.

 

 

2) Monitoring 조회

SQL> select * from dba_object_usage;

-- 11g 이하 버전의 경우 해당 딕셔너리 뷰에서만 조회 가능
-- 아래의 예시의 경우 현재 사용하지 않았음을 확인 가능
SQL> select * from v$object_usage;
OWNER      INDEX_NAME       TABLE_NAME   MON USE START_MONITORING    END_MONITORING
---------- ---------------- ------------ --- --- ------------------- ---------------
HR         EMP_EMP_ID_PK    EMPLOYEES    YES NO  08/17/2023 10:20:01

-- 설정한 해당 인덱스를 통하여 데이터 조회 수행
SQL> select /*+ index(employees EMP_EMP_ID_PK) */ *
2  from employees
3  where employee_id=205;
	
EMPLOYEE_ID FIRST_NAME    LAST_NAME      EMAIL       PHONE_NUMBER   HIRE_DATE JOB_ID   SALARY     COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ------------- -------------- ----------- -------------- --------- -------- ---------- -------------- ---------- -------------
205         Shelley       Higgins        SHIGGINS    515.123.8080   07-JUN-02 AC_MGR   12008                     101            110

-- 인덱스 사용 여부를 다시 조회하면 USE 컬럼의 값이 YES로 변경된 것을 확인 가능함
SQL> select * from dba_object_usage;
	
OWNER      INDEX_NAME      TABLE_NAME   MON USE START_MONITORING    END_MONITORING
---------- --------------- ------------ --- --- ------------------- ---------------
HR         EMP_EMP_ID_PK   EMPLOYEES    YES YES 08/17/2023 10:20:01