Monitor Index in Oracle Databases

 

1. Basic index information:

sql
SELECT index_name, table_name, uniqueness, status, visibility
FROM all_indexes
WHERE index_name = '****'
AND owner = '********';

2. Index column details:

sql
SELECT index_name, column_name, column_position, descend
FROM all_ind_columns
WHERE index_name = '*******'
AND index_owner = '******'
ORDER BY column_position;

3. Index usage statistics:

sql
SELECT i.index_name, i.table_name, 
       s.leaf_blocks, s.distinct_keys, s.clustering_factor,
       s.last_analyzed, s.num_rows
FROM all_indexes i
JOIN all_ind_statistics s ON i.index_name = s.index_name AND i.owner = s.owner
WHERE i.index_name = '*********'
AND i.owner = '*******';

4. Check if the index is actually being used:

sql
SELECT o.owner, o.object_name, o.object_type, 
       o.last_ddl_time, s.executions, s.last_active_time
FROM all_objects o
LEFT JOIN v$segment_statistics s ON o.object_name = s.object_name AND o.owner = s.owner
WHERE o.object_name = '*********'
AND o.owner = '********'
AND s.statistic_name = 'logical reads';

5. To see index monitoring (if enabled):

sql
SELECT * FROM v$object_usage
WHERE index_name = '*********'
AND owner = '***********';


-- ============================================
-- All Indexes on a Specific Table
-- ============================================

SELECT i.INDEX_NAME,
       i.INDEX_TYPE,
       i.UNIQUENESS,
       i.STATUS,
       i.VISIBILITY,
       i.TABLESPACE_NAME,
       LISTAGG(c.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY c.COLUMN_POSITION) AS COLUMNS
FROM DBA_INDEXES i
JOIN DBA_IND_COLUMNS c
  ON i.OWNER = c.INDEX_OWNER
 AND i.INDEX_NAME = c.INDEX_NAME
WHERE i.TABLE_OWNER = '*******'
  AND i.TABLE_NAME  = '**********'
GROUP BY i.INDEX_NAME, i.INDEX_TYPE, i.UNIQUENESS, 
         i.STATUS, i.VISIBILITY, i.TABLESPACE_NAME
ORDER BY i.INDEX_NAME;


-- ============================================
-- With Size Information
-- ============================================

SELECT i.INDEX_NAME,
       i.INDEX_TYPE,
       i.UNIQUENESS,
       i.STATUS,
       i.VISIBILITY,
       LISTAGG(c.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY c.COLUMN_POSITION) AS COLUMNS,
       ROUND(s.BYTES/1024/1024, 2) AS SIZE_MB
FROM DBA_INDEXES i
JOIN DBA_IND_COLUMNS c
  ON i.OWNER = c.INDEX_OWNER
 AND i.INDEX_NAME = c.INDEX_NAME
LEFT JOIN DBA_SEGMENTS s
  ON i.OWNER = s.OWNER
 AND i.INDEX_NAME = s.SEGMENT_NAME
WHERE i.TABLE_OWNER = '******'
  AND i.TABLE_NAME  = '*******'
GROUP BY i.INDEX_NAME, i.INDEX_TYPE, i.UNIQUENESS, 
         i.STATUS, i.VISIBILITY, ROUND(s.BYTES/1024/1024, 2)
ORDER BY i.INDEX_NAME;


-- ============================================
-- Quick Check: Index on a Specific Column
-- ============================================

SELECT i.INDEX_NAME, c.COLUMN_NAME, c.COLUMN_POSITION
FROM DBA_IND_COLUMNS c
JOIN DBA_INDEXES i
  ON c.INDEX_OWNER = i.OWNER
 AND c.INDEX_NAME  = i.INDEX_NAME
WHERE c.TABLE_OWNER = '*******'
  AND c.TABLE_NAME  = '********'
  AND c.COLUMN_NAME = 'YOUR_COLUMN_NAME'  -- Replace with the WHERE clause column
ORDER BY c.INDEX_NAME, c.COLUMN_POSITION;