索引监控实施过程

 合理的为数据库表上创建战略性索引，可以极大的提高dml性能，减少资源的争用情况。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到索引耗用了大量性能。oracle 提供了索引监控特性来初略判断未使用的索引。

1、冗余索引的弊端
    大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的CPU与I/O开销，具体表现如下:
       a、耗用大量的存储空间(索引段的维护与管理)
       b、增加了DML完成的时间
       c、耗用大量统计信息(索引)收集的时间
       d、结构性验证时间
       f、增加了恢复所需的时间

2、单个索引监控  
       a、对于单个索引的监控，可以使用下面的命令来完成
           alter index <INDEX_NAME> monitoring usage;

       b、关闭索引监控

          alter index <INDEX_NAME> nomonitoring usage;

       c、观察监控结果(查询v$object_usage视图)
          select * from v$object_usage

3、schema级别索引监控(不含SYS用户)

a:对非sys用户下所有索引启用监控：

[oracle@gstsmsgateonedb sql]$ cat idx_monitor_on.sql 
set heading off feedback off termout off echo off;
set pagesize 0
spool /home/oracle/sql/mnt_idx.sql
select 'alter index '|| owner ||'.'|| index_name || ' MONITORING USAGE;'
FROM dba_indexes
WHERE owner IN (SELECT username
FROM dba_users
 WHERE account_status = 'OPEN')
AND owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP');
SPOOL OFF;

b:对启用监控的索引停止监控

[oracle@gstsmsgateonedb sql]$ cat idx_monitor_off.sql 
SET HEADING OFF  FEEDBACK OFF  TERMOUT OFF  ECHO OFF;  
SET PAGESIZE 0;  
SPOOL /tmp/un_mnt_idx.sql  
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' NOMONITORING USAGE;'  
  FROM dba_indexes  
  WHERE owner IN (SELECT username  
                   FROM dba_users  
                  WHERE account_status = 'OPEN')  
       AND owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP');  
  
SPOOL OFF;

c:查看所有启用监控的索引：

[oracle@gstsmsgateonedb sql]$ cat idx_monitor_view.sql 
SET HEADING ON FEEDBACK ON  TERMOUT ON;  
SET PAGESIZE 80;  
  
SELECT index_name,  
       monitoring,  
       used,  
       start_monitoring,  
       end_monitoring  
  FROM v$object_usage; 

d:具体的用户，表索引监控情况查看：

[oracle@gstsmsgateonedb sql]$ cat idx_usage_tb.sql 
set linesize 190  
SELECT u.name owner,  
       io.name index_name,  
       t.name table_name,  
       DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') monitoring,  
       DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') used,  
       ou.start_monitoring start_monitoring,  
       ou.end_monitoring end_monitoring  
  FROM sys.user$ u,  
       sys.obj$ io,  
       sys.obj$ t,  
       sys.ind$ i,  
       sys.object_usage ou  
 WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# AND u.user# = io.owner#  
       AND u.name=decode(upper('&input_owner'),'ALL',u.name,upper('&input_owner'));