Looking for unused indexes in Oracle database

Convertir en PDF Version imprimable Suggérer par mail
Écrit par Sidibe   

 

This script can help to find unused index in a given database

 

--------------------------------
-- Index not recently used
--------------------------------

col TABLE_OWNER for a32
col TABLE_NAME for a32
col INDEX_NAMEfor a32
col INDEX_NAME for a32

SET LINESIZE 132 PAGESIZE 0


VARIABLE my_dbid NUMBER;

EXEC :my_dbid := UPPER('&1');

WITH
objects AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       object_id,
       owner,
       object_name
  FROM dba_objects
 WHERE object_type LIKE 'INDEX%'
   AND owner NOT IN ('ANONYMOUS','APEX_030200','APEX_040000','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS',
   'FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')
   AND owner NOT IN ('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR',
   'WMSYS','XDB','XS$NULL','PERFSTAT','STDBYPERF')
),
ash_mem AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       DISTINCT current_obj#
  FROM gv$active_session_history
 WHERE sql_plan_operation = 'INDEX'
   AND current_obj# > 0
),
ash_awr AS (
SELECT /*+ MATERIALIZE NO_MERGE DYNAMIC_SAMPLING(4) */
       DISTINCT current_obj#
  FROM dba_hist_active_sess_history
 WHERE sql_plan_operation = 'INDEX'
   AND snap_id BETWEEN 2984 AND 3191
   AND dbid = :my_dbid
   AND current_obj# > 0
),
sql_mem AS (
SELECT /*+ MATERIALIZE NO_MERGE DYNAMIC_SAMPLING(4) */
       DISTINCT object_owner, object_name
  FROM gv$sql_plan
WHERE operation = 'INDEX'
),
sql_awr AS (
SELECT /*+ MATERIALIZE NO_MERGE DYNAMIC_SAMPLING(4) */
       DISTINCT object_owner, object_name
  FROM dba_hist_sql_plan
 WHERE operation = 'INDEX' AND dbid = :my_dbid
)
SELECT /*+ NO_MERGE */
       i.table_owner,
       i.table_name,
       i.index_name
  FROM dba_indexes i
 WHERE (index_type LIKE 'NORMAL%' OR index_type = 'BITMAP'  OR index_type LIKE 'FUNCTION%')
   AND i.table_owner NOT IN ('ANONYMOUS','APEX_030200','APEX_040000','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS',
   'FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')
   AND i.table_owner NOT IN ('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB',
   'XS$NULL','PERFSTAT','STDBYPERF')
   AND (i.owner, i.index_name) NOT IN ( SELECT o.owner, o.object_name FROM ash_mem a, objects o WHERE o.object_id = a.current_obj# )
   AND (i.owner, i.index_name) NOT IN ( SELECT o.owner, o.object_name FROM ash_awr a, objects o WHERE o.object_id = a.current_obj# )
   AND (i.owner, i.index_name) NOT IN ( SELECT object_owner, object_name FROM sql_mem)
   AND (i.owner, i.index_name) NOT IN ( SELECT object_owner, object_name FROM sql_awr)
 ORDER BY
       i.table_owner,
       i.table_name,
       i.index_name;
       

 
< Précédent   Suivant >