qileilove

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

posted on 2013-06-08 12:34 順其自然EVO 閱讀(311) 評論(0)  編輯  收藏 所屬分類: DB2