摘要：从实例看的索引监控与无效索引维护一般观点认为数据库使用的索引不会超过设计时创建索引总数的或者不以它们被期望的使用方式使用在实际应用中调优速度较慢的查询时经常发现执行的调用了垃圾索引而不是我们设计时建立的索引所以我们有必要通过监控数据库索引的
从实例看oracle的索引监控与无效索引维护
一般观点认为oracle数据库使用的索引不会超过设计时创建索引总数的25%,或者不以它们被期望的使用方式使用.在实际应用中,调优速度较慢的查询时,经常发现执行的sql调用了垃圾索引,而不是我们设计时建立的索引.所以我们有必要通过监控数据库索引的使用,释放那些未被使用的索引,从而节省维护索引的开销,优化性能.
为了查看目前系统中索引是否有效,我从2008.09.19号开始设置了索引监控,到目前共跟踪了4天的运行数据.下面我根据得到的索引监控信息,分几个角度解析bi系统的后台数据库索引的有效性,及维护无效索引的内存,io和时间花销.
1,索引有效性统计
首先创建一个用来存储索引在监控时间段内是否被使用的临时表ods.jax_t2.,
CREATE TABLE ods.jax_t2(
owner VARCHAR2(100),
index_name VARCHAR2(100),
table_name VARCHAR2(100),
MONITORING VARCHAR2(10),
used VARCHAR2(10)
)TABLESPACE odsd;
然后分别使用各不同账户登陆,并执行下面语句,将用户的信息统一写入ods.jax_t2中.
INSERT INTO ods.jax_t2(owner,index_name,table_name,monitoring,used)
SELECT USER,index_name,table_name,MONITORING,used FROM V$OBJECT_USAGE;
COMMIT;
最后通过查询表ods.jax_t2可以得到索引有效使用率.
SELECT owner, COUNT(INDEX_NAME),
NVL(SUM(DECODE(USED, "YES", 1, 0)), 0) 有效索引数目,
ROUND(100 * NVL(SUM(DECODE(USED, "YES", 1, 0)), 0) /
COUNT(INDEX_NAME),
2) 索引有效率
FROM ods.jax_t2
GROUP BY owner
ORDER BY 索引有效率;
Owner 索引总数 有效索引数 有效索引率率(%)
DC 130 0 0
OD 31 0 0
PRICE 6 0 0
DP22 70 11 15.71
WAREHOUSE 91 19 20.88
TODS 224 48 21.43
FBI 89 26 29.21
ODS 355 105 29.58
DP23 70 28 40
DW 50 23 46
RPT 13 6 46.15
CTL 32 20 62.5
合计 1161 286 24.63
2,索引占用空间信息统计
数据字典dba_segments中存储有各数据库对象的空间分配情况.我们连立dba_segments和ods.jax_t2可以查询得到各用户总的空间分配和有效索引,无效索引所占用的空间大小.从统计信息中我们看到,在总共的61G索引中,只有11G左右的索引被有效利用.其他的索引空间在监控期间未被使用,这就是说,这50G的索引只有维护开销,而没能起到我们所设想的增加查询速度的功能.
SELECT DS.OWNER, SEGMENT_TYPE, ROUND(SUM(BYTES) / 1024 / 1024),
round(SUM(decode(jt.used,"YES",ds.bytes,0))/1024/1024) 有效索引,
round(SUM(decode(jt.used,"NO",ds.bytes,0))/1024/1024) 无效索引
FROM DBA_SEGMENTS DS,ods.jax_t2 jt
WHERE ds.owner = jt.owner AND ds.segment_name = jt.Index_Name
AND DS.OWNER NOT IN ("SYS", "SYSTEM", "OUTLN", "WMSYS")
AND DS.SEGMENT_TYPE = "INDEX"
GROUP BY DS.OWNER, DS.SEGMENT_TYPE
ORDER BY 无效索引
Owner 对象类型 索引总空间(M) 有效索引空间(M) 无效索引空间(M)
DP23 INDEX 5 2 3
DP22 INDEX 4 1 4
OD INDEX 7 0 7
RPT INDEX 10 1 9
CTL INDEX 34 22 13
FBI INDEX 199 2 197
PRICE INDEX 200 0 200
TODS INDEX 1504 270 1235
DC INDEX 2188 0 2188
DW INDEX 5212 2325 2887
ODS INDEX 22240 8703 13537
WAREHOUSE INDEX 29750 4 29745
总计 Index 61353 11330 50023
3,部分索引维护的空间和时间花销
在这里,我选择了数据抽取过程中两个相对执行时间教程的表CR_CUSTOMER_EXPIATION_A as CCEA和CR_ORDER_ROLE as COR表进行一下分析.
CCEA COR
记录占用空间 28 (M) 2112 (M)
索引占用空间 40 (M) 5072 (M)
日维护记录行数 删除/插入 550138/550952 258593/279324
无效索引数/索引总数 1/1 2/4
删除所需时间 50.20 (S) 172 (S)
插入所需时间 16.25 (S) 39.22 (S)
去掉无效索引后删除时间 19.88 (S) 23.77 (S)
去掉无效索引后插入所需时间 2.78 (S) 13.75 (S)
根据上面的比较结果我们看到,目前系统中索引占用的总数据大小高达60G以上,但实际有效的索引占用空间只有10G左右,绝大多数的索引只是增加了我们的维护时间和空间开销,而无法为系统的性能提供支持,测试数据显示,在删除无效索引之后,系统的维护速度得到大幅度提高.所以我建议:
1, 对一些检索比较频繁的表,找出系统中引用该表的查询语句,查看其执行计划,检索是否使用正确索引;
2,如果已经使用正确索引,则考虑通过重建索引等手段查看是否能提高查询速度;
3,如果索引确实无法增加数据检索的速度,则清除之.
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/34987.html
摘要：从实例看的索引监控与无效索引维护一般观点认为数据库使用的索引不会超过设计时创建索引总数的或者不以它们被期望的使用方式使用在实际应用中调优速度较慢的查询时经常发现执行的调用了垃圾索引而不是我们设计时建立的索引所以我们有必要通过监控数据库索引的 从实例看oracle的索引监控与无效索引维护 一般观点认为oracle数据库使用的索引不会超过设计时创建索引总数的25%,或者不以它们被期望的使用...
摘要：重点观察，数据库的承载力变化主要性能瓶颈等。如判断就是数据库的承载能力不够，可按照不同层次进行考虑。在整个评估数据库承载能力中，这一步骤是最复杂的也是最难的一部分。要区分清楚是否是数据库承载能力不足，还是其他组件的问题。 作为DBA，有时会被挑战类似这样的问题： 如果现有业务规模增加10倍、100倍，数据库是否能够支撑？ 下个月我们搞大促，数据库这边没问题吧？ 计划进行去O工作，代码...
摘要：问答集萃接下来，我们分享本期整理出的问题和诊断总结，供大家参考学习，详细的诊断分析过程可以通过标题链接跳转到小程序中查看。冗余损失三分之二。将来要怎么办诊断结论在第版中已弃用。 引言近期我们在DBASK小程序新关联了韩锋频道、互联网侦察、数据库SQL、SQL数据库开发、跨界架构师、石杉的架构笔记等数据领域的公众号，聚合更新展示，欢迎大家阅读分享。 问答集萃接下来，我们分享本期整理出的问...
摘要：新晋技术专家下面是墨天轮部分新晋的技术专家。大家可以点击往期阅读墨天轮技术专家邀请函了解详情，申请成为我们的技术专家，加入专家团队，与我们一起创建一个开放互助的数据库技术社区。新关联公众号墨天轮是一个开放互助的数据库技术社区。 引言 近期我们在DBASK小程序增加了数据库 MongoDB、Redis、 Elasticsearch、DB2、Weblogic 等新的的专题栏目和一些新的技术...
摘要：新晋技术专家下面是墨天轮部分新晋的技术专家。大家可以点击往期阅读墨天轮技术专家邀请函了解详情，申请成为我们的技术专家，加入专家团队，与我们一起创建一个开放互助的数据库技术社区。新关联公众号墨天轮是一个开放互助的数据库技术社区。 引言 近期我们在DBASK小程序增加了数据库 MongoDB、Redis、 Elasticsearch、DB2、Weblogic 等新的的专题栏目和一些新的技术...
阅读 1912·2023-04-25 21:26
阅读 1320·2021-11-25 09:43
阅读 1811·2019-08-30 15:52
阅读 821·2019-08-30 14:05
阅读 2518·2019-08-29 16:10
阅读 309·2019-08-29 13:48
阅读 1744·2019-08-29 12:47
阅读 1176·2019-08-23 18:04
