Мониторинг использования индексов

Создавая индекс на поле(я) таблицы, мы не можем с уверенностью утверждать, что они будут использованы оптимизатором (Cost-Based Optimizer), для построения наиболее оптимального плана выполнения запроса.
Для уверенности, что индексы реально используются приложениями, а не занимают попусту дисковое пространство, удобно использовать - Monitoring Index Usage.

Включаем мониторинг использования индекса (на примере индекса EMP_EMP_ID_PK таблицы hr.employees):

1. ALTER INDEX EMP_EMP_ID_PK monitoring usage;

Делаем выборку из таблицы, на которой построен индекс:

1. SELECT * FROM hr.employees e WHERE e.employee_id > 100;

Делаем выборку, чтоб удостовериться что индекс используется:

1. INDEX_NAME                 TABLE_NAME                     MONITORING USED
2. -------------------------- ------------------------------ ---------- ----
3. EMP_EMP_ID_PK              EMPLOYEES                      YES        NO

Значение "NO" в поле USED, говорит о том, что индекс не был использован, как при выполнении последнего запроса так и в целом, при выполнении любых запросов в системе с участием этой таблицы.

Попробуем немного видоизменить запрос, и посмотреть что получится:

1. SELECT * FROM hr.employees e WHERE e.employee_id = 100;

Делаем выборку, чтоб удостовериться что индекс используется:

1. INDEX_NAME                 TABLE_NAME                     MONITORING USED
2. -------------------------- ------------------------------ ---------- ----
3. EMP_EMP_ID_PK              EMPLOYEES                      YES        YES

Значение "YES" в поле USED, говорит о том, что индекс был использован.

Теперь можно отключить мониторинг использования индекса:

1. ALTER INDEX index_name nomonitoring usage;

Когда нужно(удобно) использовать Monitoring Index Usage:
Для обычных запросов, которые вы выполняете в ручном режиме в SQL+ либо какой-то другой среде, нет смысла использовать подобный мониторинг. Проще построить план выполнения (Explain Plan), который однозначно скажет, участвует ваш индекс в выборке или нет. Мониторинг использования индексов, очень удобен, когда нужно удостовериться в использовании индексов приложениями, которые взаимодействуют с базой посредством stored procedures, functions либо через технологии ORM (Object-relational mapping). В этом случае,
получить доступ к планам выполнения (Explain Plan) не всегда удобно и очевидно.

P.S. Использовать Monitoring Index Usage нужно осторожно, потому как он не дает понимания, какой именно запрос/процедура/функция использовали индекс в своей работе. Мониторинг удобно включать на индексы, чтобы выявить ненужные/лишние индексы, созданные без понимания того, как в дальнейшем они будут использоваться.