Unusable index monitoring

Типичная задача об удалении неиспользуемых индексов решается индивидуально для каждой системы.
Можно выбрать такой алгоритм:
выбрать индексы, которые реально необходимо оптимизировать, например
* большие по размеру
* на поддержку которых уходит много времени: много операций записи

индексы не используются
* проверяем по dba_hist_sql_plan без учета сбора статистики
* проверяем по dba_hist_seg_stat

Главная идея такая: собираем все подознительное и из этого откидываем, что есть в планах или читается.

Мой запрос такой, но его можно подкорректировать в зависимости от нужд (в части bad_idx + поиграться с количеством чтений и записей + еще что-нибудь дописать)

WITH large_idx AS (
    SELECT owner, segment_name, segment_type, ROUND(sum(bytes)/1024/1024/1024, 2) size_gb
    FROM   dba_segments t
    WHERE  segment_type LIKE 'INDEX%'
    GROUP BY owner, segment_name, segment_type
    HAVING ROUND(sum(bytes)/1024/1024/1024, 2) > 5
),
seg_stat AS (
  SELECT o.object_name, o.owner, SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) READS,
    SUM(s.physical_write_requests_delta) + SUM(s.physical_writes_direct_delta) + sum(s.physical_writes_delta) writes
  FROM dba_hist_seg_stat s, dba_objects o
  WHERE s.obj# = o.object_id
    AND o.object_type LIKE 'INDEX%'
    AND o.owner LIKE 'OWNER%'
  GROUP BY o.object_name, o.owner
  HAVING SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) = 0
),
sql_plan AS (
SELECT /*+ MATERIALIZE*/
 p.*
FROM   (SELECT DISTINCT o.owner,
                        o.object_name,
                        sql_id
        FROM   dba_objects       o,
               dba_hist_sql_plan p
        WHERE  o.object_type LIKE 'INDEX%'
        AND    owner LIKE 'OWNER%'
        AND    p.object_owner = o.owner
        AND    p.object_name = o.object_name) p
WHERE  EXISTS (SELECT NULL
        FROM   dba_hist_sqltext t
        WHERE  p.sql_id = t.sql_id
        AND    t.sql_text NOT LIKE '%\*%dbms_stats%*\%')
),
bad_idx AS (
SELECT owner, segment_name object_name, 'LARGE' reason FROM large_idx
UNION
SELECT owner, object_name, 'NOT USED' reason FROM seg_stat WHERE READS = 0 AND writes > 10000
)
SELECT /*+ PARALLEL(8)*/i.owner, i.object_name, round((SELECT sum(bytes) FROM dba_segments s WHERE s.owner = i.owner 
  AND s.segment_name = i.object_name)/1024/1024, 2) mb, LISTAGG(reason, '; ') WITHIN GROUP (ORDER BY 1),
  'ALTER INDEX ' || i.owner || '.' || i.object_name || ' MONITORING USAGE;' monitiring_on,
    'ALTER INDEX ' || i.owner || '.' || i.object_name || ' NOMONITORING USAGE;' monitiring_off
FROM bad_idx i
WHERE (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM sql_plan)
  AND (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM seg_stat WHERE READS > 0)
GROUP BY i.owner, i.object_name
;

Пока сильные подозрения вызываем правильность заполнения dba_hist_seg_stat