Oracle索引监控(monitor index)

Oracle索引监控(monitor index)

合理的为数据库表上创建战略性索引，可以极大程度的提高查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引，这些索引耗用了大量的存储空间，导致DML性能低下。Oracle 提供了索引监控特性来粗略判断未使用到的索引。本文描述如何使用Oracle 索引的监控。
 
1、冗余索引的弊端
      大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的CPU与I/O开销，具体表现如下:
            a、耗用大量的存储空间(索引段的维护与管理)
            b、增加了DML完成的时间
            c、耗用大量统计信息(索引)收集的时间
            d、结构性验证时间
            f、增加了恢复所需的时间
 
2、单个索引监控  
            a、对于单个索引的监控，可以使用下面的命令来完成
                    alter index monitoring usage;
            b、关闭索引监控
                  alter index nomonitoring usage;
            c、观察监控结果(查询v$object_usage视图)
                  select * from v$object_usage
 
3、schema级别索引监控(不含SYS用户)

a、直接执行脚本来开启索引监控  
robin@SZDB:~/dba_scripts/custom/sql> more idx_monitor_on.sql   
SET HEADING OFF  FEEDBACK OFF  TERMOUT OFF  ECHO OFF;  
SET PAGESIZE 0;  
SPOOL /tmp/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;  
@/tmp/mnt_idx.sql;  
SET HEADING ON FEEDBACK ON TERMOUT ON;  
SET PAGESIZE 80;  
 
SELECT index_name,  
            monitoring,  
            used,  
            start_monitoring,  
            end_monitoring  
  FROM v$object_usage;  
 
ho rm -rf /tmp/mnt_idx.sql  
 
b、禁用索引监控  
robin@SZDB:~/dba_scripts/custom/sql> more 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;  
@/tmp/un_mnt_idx.sql;  
SET HEADING ON FEEDBACK ON  TERMOUT ON;  
SET PAGESIZE 80;  
   
SELECT index_name,  
            monitoring,  
            used,  
            start_monitoring,  
            end_monitoring  
  FROM v$object_usage;  
 
ho rm -rf /tmp/un_mnt_idx.sql  
 
c、查看索引监控结果  
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'));  

         
4、演示索引监控
 
a、单个索引监控  
-->演示环境  
scott@CNMMBO> select * from v$version where rownum<2;  
 
BANNER  
----------------------------------------------------------------  
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
 
-->创建测试表  
scott@CNMMBO> create table tb_emp as select * from emp;  
 
-->为测试表创建索引  
scott@CNMMBO> create index i_tb_emp_empno on tb_emp(empno);  
 
-->收集统计信息  
scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','TB_EMP',cascade=>true);  
 
-->查看索引信息  
scott@CNMMBO> @idx_info  
Enter value for owner: scott  
Enter value for table_name: tb_emp  
 
Table Name                              INDEX_NAME                                        CL_NAM                            CL_POS STATUS    IDX_TYP                DSCD  
------------------------- ------------------------------ -------------------- ------ -------- --------------- ----  
TB_EMP                                      I_TB_EMP_EMPNO                                EMPNO                                        1 VALID      NORMAL                  ASC  
 
-->查看索引使用情况  
-->此时use列为NO，表明索引未被使用到  
scott@CNMMBO> @idx_usage_tb                             
Enter value for 1: tb_emp  
Enter value for 2: all  
Enter value for 2: all  
 
Table Name                              INDEX_NAME                                        USE START_MONITORING      END_MONITORING  
------------------------- ------------------------------ --- ------------------- -------------------  
TB_EMP                                      I_TB_EMP_EMPNO                                NO  03/19/2013 17:43:49  
 
-->实施即席查询  
scott@CNMMBO> select empno,ename,job from tb_emp where empno=7788;  
 
        EMPNO ENAME          JOB  
---------- ---------- ---------  
          7788 SCOTT          ANALYST  
 
-->再次查看时USE列已经为YES  
scott@CNMMBO> @idx_usage_tb  
Enter value for 1: tb_emp  
Enter value for 2: all  
Enter value for 2: all  
 
Table Name                              INDEX_NAME                                        USE START_MONITORING      END_MONITORING  
------------------------- ------------------------------ --- ------------------- -------------------  
TB_EMP                                      I_TB_EMP_EMPNO                                YES 03/19/2013 17:43:49  
 
-->禁用索引监控  
scott@CNMMBO> alter index I_TB_EMP_EMPNO nomonitoring usage;  
 
Index altered.  
 
b、schema级别的索引监控  
-->切换到另外一个数据库cnbo1  
scott@CNMMBO> conn goex_admin/xxxxx@cnbo1  
Connected.  
 
-->下面的查询表明没有表开启索引监控  
goex_admin@CNBO1> @idx_usage;  
 
no rows selected  
 
-->开启索引监控  
goex_admin@CNBO1> @idx_monitor_on  
 
INDEX_NAME                                        MON USE START_MONITORING      END_MONITORING  
------------------------------ --- --- ------------------- -------------------  
PK_AAH                                                YES NO  03/19/2013 17:48:32  
IDX_GOAAE1                                        YES NO  03/19/2013 17:48:32  
PK_GOAAT                                            YES NO  03/19/2013 17:48:32  
PK_GOAACTL                                        YES NO  03/19/2013 17:48:32  
.......                                                      ................  
 
-->关闭索引监控  
goex_admin@CNBO1> @idx_monitor_off  
INDEX_NAME                                        MON USE START_MONITORING      END_MONITORING  
------------------------------ --- --- ------------------- -------------------  
PK_GOARL                                            NO  NO  03/19/2013 17:48:30 03/19/2013 17:50:02  
IDX_GOAQU1                                        NO  NO  03/19/2013 17:48:30 03/19/2013 17:50:02  
IDX_GOAQU2                                        NO  NO  03/19/2013 17:48:30 03/19/2013 17:50:02  
 
-->连接到原来的db，查看曾经开启索引监控的使用情况  
goex_admin@CNBO1> conn scott/tiger@cnmmbo  
 
Connected.  
 
goex_admin@CNMMBO> @idx_usage  
Enter value for input_owner: GOEX_ADMIN  
Enter value for input_owner: GOEX_ADMIN  
 
OWNER                    INDEX_NAME                                        Table Name                              MON USE START_MONITORING      END_MONITORING  
--------------- ------------------------------ ------------------------- --- --- ------------------- ----------------  
SCOTT                    I_TB_EMP_EMPNO                                TB_EMP                                      NO  YES 03/19/2013 17:43:49 03/19/2013 17:46:04  
GOEX_ADMIN          ACC_GRP_EXT_INFO_TBL_LOG_PK      ACC_GRP_EXT_INFO_TBL_LOG  YES YES 02/22/2013 15:58:42  
GOEX_ADMIN          IDX_TDCL_CONTRACT_NUM                  TRADE_CLIENT_TBL                  YES YES 02/22/2013 15:58:42  
GOEX_ADMIN          IDX_TDCL_SETTLED_DATE                  TRADE_CLIENT_TBL                  YES YES 02/22/2013 15:58:42  
GOEX_ADMIN          IDX_TDCL_ACC_NUM                            TRADE_CLIENT_TBL                  YES YES 02/22/2013 15:58:41  
GOEX_ADMIN          IDX_TDCL_INSTRU_ID                        TRADE_CLIENT_TBL                  YES YES 02/22/2013 15:58:42  


5、索引监控的建议与弊端
      a、选择数据库高峰期实施索引监控，以及尽可能使用较长的监控周期来判断索引是否被使用
      b、可以对特定时间段实施多次监控以判断索引的使用频率(粗略值)
      c、索引监控在一定程度上耗用系统资源，一旦监控完毕后应即时关闭以避免其带来的额外开销
      d、索引监控仅仅从索引的使用与否来描述索引使用，并未提供详细的索引使用频率，b点提到的方法也只是粗略值
          （Oracle 监控索引的使用率 参见：http://blog.csdn.net/robinson_0612/article/details/8823133）
     
转自：http://www.2cto.com/database/201303/196854.html