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;

--> 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'));

1 2 下一頁