Oracle索引的监控
2 BLOG文档结构图
3 相关知识点扫盲(摘自网络)
合理的为数据库表上创建战略性索引，可以极大程度的提高查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间，导致DML性能低下。 应用程序在开发时，可能会建立众多索引，但是这些索引的使用到底怎么样，是否有些索引一直都没有用到过，这需要我们对这些索引进行监控，以便确定他们的使用情况，并为是否可以清除它们给出依据。
冗余索引的弊端：
大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的CPU与I/O开销，具体表现如下:
a、浪费大量的存储空间，尤其是大表的索引，浪费的存储空间尤其可观(索引段的维护与管理)
b、增加了DML 操作（UPDATE、INSERT、DELETE）的开销
c、耗用大量统计信息(索引)收集的时间
d、结构性验证时间
f、增加了恢复所需的时间
本文介绍两种方式：
第一：开启监控功能；
第二：查看历史的执行计划，进行分析；
4 索引监控的方法
4.1 方法一：开启监控功能
1、单个索引监控
a、对于单个索引的监控，可以使用下面的命令来完成
alter index <INDEX_NAME> monitoring usage;
b、关闭索引监控
alter index <INDEX_NAME> nomonitoring usage;
c、观察监控结果(查询v$object_usage视图)
select * from v$object_usage;
2、schema级别索引监控
如果我们想在系统中监控所有的索引，那么我们可以通过下面脚本实现监控数据库所有的索引。注意我们要排除一些系统表的索引、以及LOB indexes。原因有下面两个：
1：LOB indexes不能修改，否则会报ORA-22864错误（ORA-22864: cannot ALTER or DROP LOB indexes）。
2：ORA-00701: object necessary for warmstarting database cannot be altered
ORA-00701: object necessary for warmstarting database cannot be altered
00701. 00000 - "object necessary for warmstarting database cannot be altered"
*Cause: Attempt to alter or drop a database object (table, cluster, or
index) which are needed for warmstarting the database.
*Action: None.
直接执行脚本来开启索引监控，当然监控索引时长非常重要，太短的话有可能导致查询出来的数据有问题，一般建议监控一周后即可，OLAP系统则需要适当延长监控的时间。
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' enable_monitor,
'ALTER INDEX ' || owner || '.' || index_name ||
' NOMONITORING USAGE;' disable_monitor
FROM dba_indexes
WHERE INDEX_TYPE != 'LOB'
and owner IN
(SELECT username FROM dba_users WHERE account_status = 'OPEN')
AND owner NOT IN ('SYS',
'SYSTEM',
'PERFSTAT',
'MGMT_VIEW',
'MONITOR',
'SYSMAN',
'DBSNMP')
AND owner not like '%SYS%';
监控一个月就大概可以知道那些是无用的索引了。
虽然v$object_usage表能记录索引监控和使用的状态，但它不能统计索引被使用的次数和频率，只记录了在开启索引监控的时间段索引是否被使用过，这一点要值的注意。
另外需要注意的2点：
① 10g在收集统计信息时会导致索引被监控、这并非SQL语句产生、而在11g则不会出现这种情况了
② 外键索引不会因为主表的DML操作而被监控到、不要因为该索引没用而将它给删了
4.1.1 个人实验
注意：实验部分内容太多，公众号有限制，小麦苗省略了这个部分，全过程可以参考http://blog.itpub.net/26736162/viewspace-2120752/ 或http://www.cnblogs.com/lhrbest/articles/5608772.html#3458413或者去云盘下载pdf版本阅读，谢谢大家的理解。
4.1.2 实验中用到的SQL
drop table TB_LHR_20160622 purge;
Create Table TB_LHR_20160622 nologging As select * from dba_objects;
create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);
create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);
select * from v$object_usage;
BEGIN
dbms_stats.gather_table_stats(USER,
'TB_LHR_20160622',
cascade => TRUE,
degree => 8);
END;
/
alter index ind_TB_LHR_20160622_id monitoring usage;
alter index ind_TB_LHR_20160622_name monitoring usage;
select count(1) from TB_LHR_20160622 t where t.object_id=88;
set line 9999 pagesize 9999
col owner format A10
COL INDEX_NAME FOR A25
COL TABLE_NAME FOR A20
COL MONITORING FOR A10
COL USED FOR A10
COL START_MONITORING FOR A20
COL END_MONITORING FOR A20
select * from v$object_usage;
注意：SELECT * FROM V$OBJECT_USAGE; 只能查看当前用户下被监控的索引信息。即使sys、system用户也不能查看其它用户的信息。
alter index ind_TB_LHR_20160622_id nomonitoring usage;
alter index ind_TB_LHR_20160622_name nomonitoring usage;
--- drop table t purge; 表删掉后 v$object_usage 中关于监控的信息也删除了
----切换用户后查询select * from v$object_usage;查询不到数据，下边这个SQL可以查询任何用户下的索引使用情况
create or replace view vw_INDEX_USAGE_lhr AS
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#;
create or replace public synonym syn_INDEX_USAGE_lhr for sys.vw_INDEX_USAGE_lhr;
set line 9999 pagesize 9999
col owner format A10
COL INDEX_NAME FOR A25
COL TABLE_NAME FOR A20
COL MONITORING FOR A10
COL USED FOR A10
COL START_MONITORING FOR A20
COL END_MONITORING FOR A20
SELECT * FROM syn_INDEX_USAGE_lhr;
批量监控系统的所有索引：
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' enable_monitor,
'ALTER INDEX ' || owner || '.' || index_name ||
' NOMONITORING USAGE;' disable_monitor
FROM dba_indexes
WHERE INDEX_TYPE != 'LOB'
and owner IN
(SELECT username FROM dba_users WHERE account_status = 'OPEN')
AND owner NOT IN ('SYS',
'SYSTEM',
'PERFSTAT',
'MGMT_VIEW',
'MONITOR',
'SYSMAN',
'DBSNMP')
AND owner not like '%SYS%';
4.2 方法二：查看历史的执行计划进行分析
虽然v$object_usage表能记录索引监控和使用的状态，但它不能统计索引被使用的次数和频率，只记录了在开启索引监控的时间段索引是否被使用过，因此想详细了解索引的使用情况我们可以利用AWR的一些视图dba_hist_sql_plan和dba_hist_sqlstat来弄清楚数据库访问某个索引的次数、索引访问的类型，如索引范围扫描或索引唯一扫描。
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.OBJECT_TYPE = 'INDEX'
AND NB.SUBOBJECT_NAME IS NULL) 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%'
/*and s.owner = 'FUNDZ'*/
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.object_owner = 'FUNDZ'
AND */
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);
如上图所示，有一个3.6G大的索引在13号到22号从没使用过，接下来，我们可以继续查询该索引是否联合索引，创建是否合理，分析为何不走该索引，从而判断是否可以删除索引。
另外下边的SQL可以查询出表上列的使用情况：
CREATE OR REPLACE VIEW VW_COLUMN_USAGE_LHR AS
SELECT oo.name owner,
o.name table_name,
c.name column_name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds,
u.timestamp
FROM sys.col_usage$ u, sys.obj$ o, sys.user$ oo, sys.col$ c
WHERE o.obj# = u.obj#
AND oo.user# = o.owner#
AND c.obj# = u.obj#
AND c.col# = u.intcol#
;
