Oracle开索引监控及分析

mahan

一 如何使用索引监控

合理创建索引能极大提升查询效率，但实际会存在大量无用索引和冗余索引，不仅浪费存储空间，还降低sql执行效率。Oracle 提供了索引监控特性来初略判断未使用到的索引。以下描述如何使用Oracle 索引的监控。

1.1 单个索引监控
1）开启索引监控

alter index <INDEX_NAME> monitoring usage;

2）关闭索引监控

alter index <INDEX_NAME> nomonitoring usage;

3）观察监控结果（查询v$object_usage视图）

select * from v$object_usage

1.2 schema级别索引监控（不含SYS用户）
1）通过执行脚本来开启索引监控

View 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;

2）执行脚本打开索引监控

@/tmp/mnt_idx.sql;

3）查询索引监控情况

SET HEADING ON FEEDBACK ON TERMOUT ON;
SET PAGESIZE 80;
SELECT index_name，
monitoring，
used，
start_monitoring，
end_monitoring
FROM v$object_usage;

4）禁用索引监控

通过执行脚本来禁用索引监控：

View 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;

为了避免使用V$OBJECT_USAGE只能查询到当前用户下索引的监控情况，可以使用如下语句查询数据库中所有被监控索引的使用情况：

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

二 索引监控的建议与弊端

2.1 选择数据库高峰期实施索引监控，以及尽可能使用较长的监控周期来判断索引是否被使用，一般至少监控一周时间

2.2 可以对特定时间段实施多次监控以判断索引的使用频率（初略值）

2.3 索引监控在一定程度上耗用系统资源，一旦监控完毕后应即时关闭以避免其带来的额外开销

2.4 索引监控仅仅从索引的使用与否来描述索引使用，并未提供详细的索引使用频率，第2.2点提到的方法也只是初略值

三 分析索引使用率

以上方式仅提供的是索引是否被使用。索引被使用的频率未能得以体现。下面的脚本将得到索引的使用率，可以很好的度量索引的使用情况以及根据这个值来判断当前的这些索引是否可以被移除或改进。

WITH TMP1 AS
(SELECT I.OWNER INDEX_OWNER,
I.TABLE_OWNER,
TABLE_NAME,
INDEX_NAME,
INDEX_TYPE,
(SELECT NB.CREATED
FROM DBA_OBJECTS NB
WHERE NB.OWNER = I.OWNER
AND NB.OBJECT_NAME = I.INDEX_NAME
AND NB.SUBOBJECT_NAME IS NULL
AND NB.OBJECT_TYPE = 'INDEX') CREATED,
(SUM(S.BYTES) / 1024 / 1024) INDEX_MB,
(SELECT COUNT(1)
FROM DBA_IND_COLUMNS DIC
WHERE DIC.INDEX_NAME = I.INDEX_NAME
AND DIC.TABLE_NAME = I.TABLE_NAME
AND DIC.INDEX_OWNER = I.OWNER) COUNT_INDEX_COLS
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE I.INDEX_NAME = S.SEGMENT_NAME
AND I.OWNER = S.OWNER
AND S.OWNER NOT LIKE '%SYS%'
GROUP BY I.OWNER, I.TABLE_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
HAVING SUM(S.BYTES) > 1024 * 1024),
TMP2 AS
(SELECT INDEX_OWNER,
INDEX_NAME,
PLAN_OPERATION,
(SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
FROM DBA_HIST_SNAPSHOT NB
WHERE NB.SNAP_ID = V.MIN_SNAP_ID) MIN_DATE,
(SELECT MAX(TO_CHAR(NB.END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
FROM DBA_HIST_SNAPSHOT NB
WHERE NB.SNAP_ID = V.MAX_SNAP_ID) MAX_DATE,
COUNTS
FROM (SELECT D.OBJECT_OWNER INDEX_OWNER,
D.OBJECT_NAME INDEX_NAME,
D.OPERATION || ' ' || D.OPTIONS PLAN_OPERATION,
MIN(H.SNAP_ID) MIN_SNAP_ID,
MAX(H.SNAP_ID) MAX_SNAP_ID,
COUNT(1) COUNTS
FROM DBA_HIST_SQL_PLAN D, DBA_HIST_SQLSTAT H
WHERE D.OPERATION LIKE '%INDEX%'
AND D.SQL_ID = H.SQL_ID
GROUP BY D.OBJECT_OWNER, D.OBJECT_NAME, D.OPERATION, D.OPTIONS) V)
SELECT A.TABLE_OWNER,
A.TABLE_NAME,
A.INDEX_OWNER,
A.INDEX_NAME,
A.CREATED,
A.INDEX_TYPE,
A.INDEX_MB,
A.COUNT_INDEX_COLS,
B.PLAN_OPERATION,
CASE
WHEN MIN_DATE IS NULL THEN
(SELECT MIN(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
FROM DBA_HIST_SNAPSHOT NB)
ELSE
MIN_DATE
END AS MIN_DATE,
CASE
WHEN MAX_DATE IS NULL THEN
(SELECT MAX(TO_CHAR(NB.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'))
FROM DBA_HIST_SNAPSHOT NB)
ELSE
MAX_DATE
END AS MAX_DATE,
COUNTS
FROM TMP1 A
LEFT OUTER JOIN TMP2 B
ON (A.INDEX_OWNER = B.INDEX_OWNER AND A.INDEX_NAME = B.INDEX_NAME);