合 Oracle索引的监控

前言部分

导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，\~O(∩_∩)O\~：

① 掌握oracle中索引的监控方法

② sys.col_usage\$的初步了解

相关知识点扫盲(摘自网络)

合理的为数据库表上创建战略性索引，可以极大程度的提高查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间，导致DML性能低下。 应用程序在开发时，可能会建立众多索引，但是这些索引的使用到底怎么样，是否有些索引一直都没有用到过，这需要我们对这些索引进行监控，以便确定他们的使用情况，并为是否可以清除它们给出依据。

冗余索引的弊端：

大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的CPU与I/O开销，具体表现如下:

a、浪费大量的存储空间，尤其是大表的索引，浪费的存储空间尤其可观(索引段的维护与管理)

b、增加了DML 操作（UPDATE、INSERT、DELETE）的开销

c、耗用大量统计信息(索引)收集的时间

d、结构性验证时间

f、增加了恢复所需的时间

本文介绍两种方式：

第一：开启监控功能；

第二：查看历史的执行计划，进行分析；

索引监控的方法

方法一：开启监控功能

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操作而被监控到、不要因为该索引没用而将它给删了

个人实验

新建1个表TB_LHR_20160622，并创建2个索引：

SYS@raclhr2> select * from v\$version;

BANNER

-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SYS@raclhr2> Create Table TB_LHR_20160622 nologging As select * from dba_objects;

Table created.

SYS@raclhr2> create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);

Index created.

SYS@raclhr2> create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);

Index created.

查询v\$object_usage视图，收集统计信息：

SYS@raclhr2> select * from v\$object_usage;

no rows selected

SYS@raclhr2> BEGIN

2 dbms_stats.gather_table_stats(USER,

3 'TB_LHR_20160622',

4 cascade => TRUE,

5 degree => 8);

6 END;

7 /

PL/SQL procedure successfully completed.

SYS@raclhr2> select * from v\$object_usage;

no rows selected

开启索引的监控：

SYS@raclhr2> alter index ind_TB_LHR_20160622_id monitoring usage;

Index altered.

SYS@raclhr2> COL INDEX_NAME FOR A25

SYS@raclhr2> COL TABLE_NAME FOR A20

SYS@raclhr2> COL MONITORING FOR A10

SYS@raclhr2> COL USED FOR A10

SYS@raclhr2> COL START_MONITORING FOR A20

SYS@raclhr2> COL END_MONITORING FOR A20

SYS@raclhr2> select * from v\$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

------------------------- -------------------- ---------- ---------- -------------------- --------------------

IND_TB_LHR_20160622_ID TB_LHR_20160622 YES NO 06/22/2016 15:15:54

SYS@raclhr2> alter index ind_TB_LHR_20160622_name monitoring usage;

Index altered.

SYS@raclhr2> select count(1) from TB_LHR_20160622 t where t.object_id=88;

COUNT(1)

----------

1

SYS@raclhr2> explain plan for select count(1) from TB_LHR_20160622 t where t.object_id=88;

Explained.

SYS@raclhr2> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------

Plan hash value: 2688591802

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

|* 2 | INDEX RANGE SCAN| IND_TB_LHR_20160622_ID | 1 | 5 | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("T"."OBJECT_ID"=88)

14 rows selected.

SYS@raclhr2> COL INDEX_NAME FOR A25

SYS@raclhr2> COL TABLE_NAME FOR A20

SYS@raclhr2> COL MONITORING FOR A10

SYS@raclhr2> COL USED FOR A10

SYS@raclhr2> COL START_MONITORING FOR A20

SYS@raclhr2> COL END_MONITORING FOR A20

SYS@raclhr2> select * from v\$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

------------------------- -------------------- ---------- ---------- -------------------- --------------------

IND_TB_LHR_20160622_ID TB_LHR_20160622 YES YES 06/22/2016 15:15:54

IND_TB_LHR_20160622_NAME TB_LHR_20160622 YES NO 06/22/2016 15:16:17

注意：SELECT * FROM V\$OBJECT_USAGE; 只能查看当前用户下被监控的索引信息。即使sys、system用户也不能查看其它用户的信息，如下，但我们可以创建一个视图来解决这个问题。

SYS@raclhr2> conn scott/tiger

Connected.

SCOTT@raclhr2> select * from v\$object_usage;

no rows selected

SCOTT@raclhr2> conn / as sysdba

Connected.

SYS@raclhr2> create or replace view vw_INDEX_USAGE_lhr AS

2 SELECT U.NAME OWNER,

3 IO.NAME INDEX_NAME,

4 T.NAME TABLE_NAME,

5 DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,

6 DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,

7 OU.START_MONITORING START_MONITORING,

8 OU.END_MONITORING END_MONITORING

9 FROM SYS.USER\$ U,

10 SYS.OBJ\$ IO,

11 SYS.OBJ\$ T,

12 SYS.IND\$ I,

13 SYS.OBJECT_USAGE OU

14 WHERE I.OBJ# = OU.OBJ#

15 AND IO.OBJ# = OU.OBJ#

16 AND T.OBJ# = I.BO#

17 AND U.USER# = IO.OWNER#;

View created.

SYS@raclhr2> create or replace public synonym syn_INDEX_USAGE_lhr for sys.vw_INDEX_USAGE_lhr;

Synonym created.

SYS@raclhr2> grant select on sys.vw_INDEX_USAGE_lhr to public;

Grant succeeded.

SYS@raclhr2> conn scott/tiger

Connected.

SCOTT@raclhr2> set line 9999 pagesize 9999

SCOTT@raclhr2> col owner format A10

SCOTT@raclhr2> COL INDEX_NAME FOR A25

SCOTT@raclhr2> COL TABLE_NAME FOR A20

SCOTT@raclhr2> COL MONITORING FOR A10

SCOTT@raclhr2> COL USED FOR A10

SCOTT@raclhr2> COL START_MONITORING FOR A20

SCOTT@raclhr2> COL END_MONITORING FOR A20

SCOTT@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING

---------- ------------------------- -------------------- ---------- ---------- -------------------- --------------------

SYS IND_TB_LHR_20160622_ID TB_LHR_20160622 YES YES 06/22/2016 15:15:54

SYS IND_TB_LHR_20160622_NAME TB_LHR_20160622 YES NO 06/22/2016 15:16:17

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。