Oracle 索引監控(monitor index)

      合理的為資料庫表上建立戰略性索引，可以極大程度的提高了查詢效能。但事實上日常中我們所建立的索引並非戰略性索引，恰恰是大量冗餘或是根本沒有用到的索引耗用了大量的儲存空間，導致DML效能低下。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使用者)

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.sqlSELECT '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.sqlb、禁用索引監控robin@SZDB:~/dba_scripts/custom/sql> more idx_monitor_off.sqlSET HEADING OFF  FEEDBACK OFF  TERMOUT OFF  ECHO OFF;SET PAGESIZE 0;SPOOL /tmp/un_mnt_idx.sqlSELECT '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_0612SELECT index_name,       monitoring,       used,       start_monitoring,       end_monitoring  FROM v$object_usage;ho rm -rf /tmp/un_mnt_idx.sqlc、查看索引監控結果set linesize 190SELECT 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_infoEnter value for owner: scottEnter value for table_name: tb_empTable 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_empEnter value for 2: allEnter value for 2: allTable 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列已經為YESscott@CNMMBO> @idx_usage_tbEnter value for 1: tb_empEnter value for 2: allEnter value for 2: allTable 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層級的索引監控-->切換到另外一個資料庫cnbo1scott@CNMMBO> conn goex_admin/xxxxx@cnbo1Connected.-->下面的查詢表明沒有表開啟索引監控goex_admin@CNBO1> @idx_usage;no rows selected-->開啟索引監控goex_admin@CNBO1> @idx_monitor_onINDEX_NAME                     MON USE START_MONITORING    END_MONITORING------------------------------ --- --- ------------------- -------------------PK_AAH                         YES NO  03/19/2013 17:48:32IDX_GOAAE1                     YES NO  03/19/2013 17:48:32PK_GOAAT                       YES NO  03/19/2013 17:48:32PK_GOAACTL                     YES NO  03/19/2013 17:48:32.......                            ................-->關閉索引監控goex_admin@CNBO1> @idx_monitor_offINDEX_NAME                     MON USE START_MONITORING    END_MONITORING------------------------------ --- --- ------------------- -------------------PK_GOARL                       NO  NO  03/19/2013 17:48:30 03/19/2013 17:50:02IDX_GOAQU1                     NO  NO  03/19/2013 17:48:30 03/19/2013 17:50:02IDX_GOAQU2                     NO  NO  03/19/2013 17:48:30 03/19/2013 17:50:02-->串連到原來的db，查看曾經開啟索引監控的使用方式goex_admin@CNBO1> conn scott/tiger@cnmmboConnected.goex_admin@CNMMBO> @idx_usageEnter value for input_owner: GOEX_ADMINEnter value for input_owner: GOEX_ADMINOWNER           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:04GOEX_ADMIN      ACC_GRP_EXT_INFO_TBL_LOG_PK    ACC_GRP_EXT_INFO_TBL_LOG  YES YES 02/22/2013 15:58:42GOEX_ADMIN      IDX_TDCL_CONTRACT_NUM          TRADE_CLIENT_TBL          YES YES 02/22/2013 15:58:42GOEX_ADMIN      IDX_TDCL_SETTLED_DATE          TRADE_CLIENT_TBL          YES YES 02/22/2013 15:58:42GOEX_ADMIN      IDX_TDCL_ACC_NUM               TRADE_CLIENT_TBL          YES YES 02/22/2013 15:58:41GOEX_ADMIN      IDX_TDCL_INSTRU_ID             TRADE_CLIENT_TBL          YES YES 02/22/2013 15:58:42

5、索引監控的建議與弊端
    a、選擇資料庫高峰期實施索引監控，以及儘可能使用較長的監控周期來判斷索引是否被使用
    b、可以對特定時間段實施多次監控以判斷索引的使用頻率(初略值)
    c、索引監控在一定程度上耗用系統資源，一旦監控完畢後應即時關閉以避免其帶來的額外開銷
    d、索引監控僅僅從索引的使用與否來描述索引使用，並未提供詳細的索引使用頻率，b點提到的方法也只是初略值

 

更多參考:

有關Oracle RAC請參考
     使用crs_setperm修改RAC資源的所有者及許可權
     使用crs_profile管理RAC資源設定檔
     RAC 資料庫的啟動與關閉
     再說 Oracle RAC services
     Services in Oracle Database 10g
     Migrate datbase from single instance to Oracle RAC
     Oracle RAC 串連到指定執行個體
     Oracle RAC 負載平衡測試(結合伺服器端與用戶端)
     Oracle RAC 伺服器端串連負載平衡(Load Balance)
     Oracle RAC 用戶端串連負載平衡(Load Balance)
     ORACLE RAC 下非預設連接埠監聽配置(listener.ora tnsnames.ora)
     ORACLE RAC 監聽配置 (listener.ora tnsnames.ora)
     配置 RAC 負載平衡與容錯移轉
     CRS-1006 , CRS-0215 故障一例 
     基於Linux (RHEL 5.5) 安裝Oracle 10g RAC
     使用 runcluvfy 校正Oracle RAC安裝環境

有關Oracle 網路設定相關基礎以及概念性的問題請參考：
     配置非預設連接埠的動態服務註冊
     配置sqlnet.ora限制IP訪問Oracle
     Oracle 監聽器日誌配置與管理
     設定 Oracle 監聽器密碼(LISTENER)
     配置ORACLE 用戶端串連到資料庫

有關基於使用者管理的備份和備份恢複的概念請參考
     Oracle 冷備份
     Oracle 熱備份
     Oracle 備份恢複概念
     Oracle 執行個體恢複
     Oracle 基於使用者管理恢複的處理
     SYSTEM 資料表空間管理及備份恢複
     SYSAUX資料表空間管理及恢複
     Oracle 基於備份控制檔案的恢複(unsing backup controlfile)

有關RMAN的備份恢複與管理請參考
     RMAN 概述及其體繫結構
     RMAN 配置、監控與管理
     RMAN 備份詳解
     RMAN 還原與恢複
     RMAN catalog 的建立和使用
     基於catalog 建立RMAN儲存指令碼
     基於catalog 的RMAN 備份與恢複
     RMAN 備份路徑困惑
     使用RMAN實現異機備份恢複(WIN平台)
     使用RMAN遷移檔案系統資料庫到ASM
     linux 下RMAN備份shell指令碼
     使用RMAN遷移資料庫到異機

有關ORACLE體繫結構請參考
     Oracle 資料表空間與資料檔案
     Oracle 密碼檔案
     Oracle 參數檔案
     Oracle 聯機重做記錄檔(ONLINE LOG FILE)
     Oracle 控制檔案(CONTROLFILE)
     Oracle 歸檔日誌
     Oracle 復原(ROLLBACK)和撤銷(UNDO)
     Oracle 資料庫執行個體啟動關閉過程
     Oracle 10g SGA 的自動化管理
     Oracle 執行個體和Oracle資料庫(Oracle體繫結構)