사용하지 않는 인덱스 찾기 - Index 모니터링 걸지 않고 확인하는 방법 (v$sql_plan, dba_hist_sql_plan)

앞 글에서 Index 에 Monitoring 을 걸어서 사용여부를 확인하는 방법에 대해 살펴봤습니다.

그런데, 모든 Index 에 monitoring 을 잔뜩 걸기가 쫌 거시기 한 경우~ 사용할 수 있는 방법이 있습니다.

물론 완전하지는 않지만....

 

v$sql_plan 뷰와 dba_hist_sql_plan 딕셔너리에서 조회하는 방법입니다.

 

Oracle DB 에서 실행되는 모든 SQL은 파싱되면서 정보를 라이브러리캐시(Library Cache)에 기록하게 되는데, 여기에서 Plan 정보를 조회할 수 있는 뷰가 v$sql_plan 입니다.

따라서, 여기에 Index 정보가 들어있다면, 적어도 언제가 한번 이상은 해당 인덱스를 사용한 적이 있다는 의미가 됩니다.

 

v$sql_plan 은 메모리에 있는 정보를 보여주는 다이나믹뷰 입니다. 메모리 정보가 없어지면 사라지게 됩니다.

그래서 dba_hist_sql_plan 정보도 함께 체크하는게 좋습니다.

 

얘는 디스크(AWR 리파지토리)에 기록되는 데이터이기 때문에 더 오래된 데이터도 확인할 수 있습니다.

AWR Retention 기간만큼(디폴트 8일)~

 

dba_hist_sql_plan 딕셔너리에 있는 정보는 100% 모두 들어있지는 않다는 단점이 있습니다.

 

그래서 Index Monitoring 걸기에 앞서, 사전에 v$sql_plan, dba_hist_sql_plan 에서 먼저 필터링 한 다음에,

사용내역이 확실히 보이지 않는 인덱스들에 대해서만 모니터링을 걸어주는 방식으로 해주면 so cool~ 되겠슴다~

 

 

아래는 위에서 사용한 스크립트 입니다.

-- 1) 메모리에서 조회
select object_owner, object_name, to_char(timestamp,'yyyy/mm/dd hh24:mi:ss')
from v$sql_plan
where object_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
  and object_type like 'INDEX%'
order by 3 desc;

--2) 디스크 AWR 에서 조회
select object_owner, object_name, to_char(timestamp,'yyyy/mm/dd hh24:mi:ss')
from dba_hist_sql_plan
where object_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
  and object_type like 'INDEX%'
order by 3 desc;