索引监控(实际应用)

avinliu 发表于 2011-12-22 08:54

索引监控 目的
应用程序在开发时，可能会建立众多索引，但是这些索引的使用情况，是否有些索引一直都没有用到过，这需要我们对这些索引进行监控，以便确定他们的使用情况，并为是否可以清除它们给出依据。
认为oracle数据库使用的索引不会超过设计时创建索引总数的25%,或者不以它们被期望的使用方式使用.在实际应用中,调优速度较慢的查询时,经常发现执行的sql调用了垃圾索引,而不是我们设计时建立的索引.所以我们有必要通过监控数据库索引的使用,释放那些未被使用的索引,从而节省维护索引的开销,优化性能.
<A href="http://blog.csdn.net/47522341/archive/2008/09/22/2962144.aspx" target=_blank>http://blog.csdn.net/47522341/archive/2008/09/22/2962144.aspx</A>
具体步骤 
一、      开启索引监控 
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 <INDEX_NAME> 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; 只是当前用户下有效，只能看到当前用户下的监视索引。
<A href="http://www.itpub.net/viewthread.php?tid=597992&highlight=%BC%E0%CA%D3%2B%CB%F7%D2%FD" target=_blank>http://www.itpub.net/viewthread.php?tid=597992&highlight=%BC%E0%CA%D3%2B%CB%F7%D2%FD</A>

页: [1]

Chinaunix's Archiver

索引监控(实际应用)