Oracle索引监控(monitor index)

ㄣ负二代

Oracle索引监控(monitor index)

  合理的为数据库表上创建战略性索引，可以极大程度的提高了查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间，导致DML性能低下。Oracle/" target=_blank>Oracle 提供了索引监控特性来初略判断未使用到的索引。本文描述如何使用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用户)

[sql]

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;  

--> Author : Robinson  

--> Blog   : http://blog.csdn.net/robinson_0612  

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、演示索引监控

[sql]

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点提到的方法也只是初略值

kanke1257

谢谢分享！！

宇智波·佐助

沙发好了