索引监控(实际应用)

 

索引监控 目的

应用程序在开发时，可能会建立众多索引，但是这些索引的使用情况，是否有些索引一直都没有用到过，这需要我们对这些索引进行监控，以便确定他们的使用情况，并为是否可以清除它们给出依据。

认为oracle数据库使用的索引不会超过设计时创建索引总数的25%,或者不以它们被期望的使用方式使用.在实际应用中,调优速度较慢的查询时,经常发现执行的sql调用了垃圾索引,而不是我们设计时建立的索引.所以我们有必要通过监控数据库索引的使用,释放那些未被使用的索引,从而节省维护索引的开销,优化性能.

http://blog.csdn.net/47522341/archive/2008/09/22/2962144.aspx

具体步骤

一、      开启索引监控

SELECT 'alter index ' || OWNER || '.' || INDEX_NAME || ' monitoring usage;'

  FROM DBA_INDEXES

 WHERE INDEX_TYPE = 'NORMAL'

   AND owner IN ('USER1','USER2');

 

开启数据库索引监控

 

查找用户

select * from dba_users where username not in ('SYS','SYSTEM','OUTLN','AURORA\$JIS\$UTILITY\$');

 

l         数据库1涉及下列用户，共1354个索引

'BD','CECFJS','CECFMEMO','CRM','DRAGON','FAIRPORT','DBSNMP','CUSTOMDATA',

'GJHPHONE','IMPMEMO','INVOICEPUBLIC','MEMOTOSQL','MGMT_VIEW',

'NEWZJ','PAY_MANAGE','SUMCONTRACT','TEST'

l         数据库2涉及下列用户，共196个索引

'BD','CHECKTICKT','DBSNMP','MGMT_VIEW','TEST' ,'WL'

 

查询出用户表，并编成开启数据库索引监控脚本

alter index DBSNMP.MGMT_DB_FILE_GTT_PK monitoring usage;

....

....
alter index DBSNMP.MGMT_DB_SIZE_GTT_PK monitoring usage;

 

说明：按照服务器的性能，监控不会影响应用，不过如果相关应用缓慢，将立即关闭监控。

监控将一直持续到高峰结束。

 

二、      生成监控视图

监视到索引的使用信息被存储在 V$OBJECT_USAGE 视图中。

视图中的一些列的具体含义：
INDEX_NAME: sys.obj$.name中索引的名字。
TABLE_NAME: sys.obj$obj$name中表的名字。
MONITORING: YES (索引正在被监视), NO (索引没有被监视)
USED: YES（索引被使用了） NO (索引没有被使用)
START_MONITORING: 监视开始的时间。
END_MONITORING: 监视结束的时间。
所有被使用过的索引，哪怕被用过一次也会显示在这个视图中。但一个用户只能监视他自己模式中索引的使用情况。ORACLE没有提供监视所有模式中的索引的功能。要想监视所有模式中的索引的使用情况，请用SYS登录。执行下列脚本。

CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE

(

OWNER,

INDEX_NAME,

TABLE_NAME,

MONITORING,

USED,

START_MONITORING,

END_MONITORING

)

AS

select u.name, io.name, t.name,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),

ou.start_monitoring,

ou.end_monitoring

from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u

where i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#

and io.owner# = u.user#

/

COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS

'Record of all index usage - developed by liu'

/

GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"
/
CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE
FOR SYS.V$ALL_OBJECT_USAGE
/

 

三、停止监控

执行以下查询,确认监控结束,一定切记终止索引监控,因为监控也会使用一定的资源.返回记录条数为0表示监控已终止.

 

SELECT INDEX_NAME, MONITORING, USED, START_MONITORING, END_MONITORING

  FROM V$OBJECT_USAGE

  WHERE end_monitoring IS NULL;

 

 

已生成停止监控的脚本。

关闭索引监控(从开启监控中得出停止监控的脚本)

 注：使用alter index nomonitoring usage取消监控。

 

四、生成监控报表

找出那些没有使用过的索引.

SELECT INDEX_NAME,table_name, MONITORING, USED, START_MONITORING, END_MONITORING

  FROM V$OBJECT_USAGE

 WHERE USED = 'NO';

 

找出那些使用过的索引.

   select * from V$ALL_OBJECT_USAGE where used='YES'

 

找出之后要判断哪些是我们建立时希望他使用的,根据找出的表名称table_name我们可以到dba_source中找出那些使用到这个表的查询语句.针对这些语句执行优化操作.

 

SELECT ds.owner||'.'||ds.NAME OName,ds.type,ds.line,ds.text

  FROM DBA_SOURCE DS

 WHERE INSTR(UPPER(DS.TEXT), UPPER('&请输入要检索的对象名称')) > 0  ;

 

这个应该是index monitor后的结果,还需要查v$sql_plan中是否有人使用过该索引,如果要删除,也要在晚上,否则一旦出现问题,就会引起宕机...

五、改进措施

1, 对一些检索比较频繁的表,找出系统中引用该表的查询语句,查看其执行计划,检索是否使用正确索引;

2,如果已经使用正确索引,则考虑通过重建索引等手段查看是否能提高查询速度;

3,如果索引确实无法增加数据检索的速度,则清除之

3, 经过合适的时间(比如一个星期)之后我们可以继续下面的步骤,首先我们可以查看一下索引的使用比率.

SELECT ROUND(100 * SUM(DECODE(USED, 'YES', 1, 0)) / COUNT(INDEX_NAME), 2)

  FROM V$OBJECT_USAGE;

 

select * from v$object_usage;
只是当前用户下有效，只能看到当前用户下的监视索引。

http://www.itpub.net/viewthread.php?tid=597992&highlight=%BC%E0%CA%D3%2B%CB%F7%D2%FD