Oracle-数据库中不可用的索引性能问题指南-解决 (oraclejob定时任务)
简介
不可用索引是指由于索引本身问题而无法被所有 SQL 使用的索引。与因 SQL 写法不当而无法使用索引的索引失效情况不同,索引变为不可用时,原本可以使用该索引的 SQL 都将无法使用该索引,只能选择全表扫描或全分区扫描,这将导致 SQL 执行效率大幅下降。如果并发高一些,将会耗尽数据库主机硬件资源,导致所有请求响应超时。导致索引不可用的常见情况
- 当用 truncate/drop/exchange 操作分区时,全局索引会失效。增加 updateglobalindexes 参数,全局索引就不会失效了。
- exchange 的临时表没有索引,或者有索引,没有用 includingindexes 的关键字,会导致局部的索引失效,就是某个分区失效。重建局部索引只能用 alterindexlocal_idxrebuildpartition p1 这样的方式。
- 分区表 SPLIT 的时候,如果 MAX 区中已经有记录了,这个时候 SPLIT 就会导致有记录的新增分区的局部索引失效!
- 对表执行 move 操作,将导致表上所有索引变为不可用。
- sqlldr 如带有 skip_index_mntenance=true 参数,数据导入时将不维护索引,会导致表上所有索引不可用。所以,在完成数据导入后,需重建表上所有索引。
不可用索引的严重性
一旦出现不可用索引,就特别容易引发生产事故。所以,请特别重视,排查原因,消除隐患。修复建议
1. 找出导致索引不可用的原因
可从 Oracle alert 日志中查找索引不可用的日志,根据时间点排查相关表的维护类操作,进而确定引发的原因。ssh 登陆 Oracle 服务器上,查看日志命令: ```bash cd /home/db/oracle/diag/rdbms/<库名>/<实例名>/trace grep -i -w unusable -B2 -A2 alert_<实例名>.log ```2. 修复不可用索引
对于全局分区索引,建议将索引删掉后重建。对于本地分区索引,重建不可用索引分区或索引子分区。 参考 SQL 语句: ```sql select from user_indexes where status=UNUSABLE; alter indexOracle数据库索引优化技术关联查询性能调优
数据库性能优化是无止境的 无论哪种优化技术只是一种手段 但最重要的不是技术 而是思想 掌握了索引优化技术仅仅刚入门 只有融会贯通 举一反三才能成为高手
◆m_patient 患者信息表 我们要用到的字段包括i_patientid(患者ID) s_name(患者姓名) s_code(患者住院号) i_age(患者年龄) i_dept(患者所在病区)
◆lis_code_dept 病区信息表 我们要用到的字段包括i_id(病区ID 主键 与m_patient中的i_dept关联) s_name(病区名)
最终我们构造的SQL如下
select a i_checkno a d_checkdate b s_name b s_code b i_age c s_name from lis_report a inner join m_patient b on a i_patientid = b i_patientid inner join lis_code_dept c on b i_dept = c i_id where a d_checkdate > and a d_checkdate < and b i_age>= and b s_name like 周% order by a d_checkdate desc
我们的SQL使用的这三张表除了创建主键时自动创建的索引外 均未创建其它索引 下图是无索引时的执行计划
表m_patient和lis_report都使用了全表扫描 m_patient全表扫描的成本是 lis_report全表扫描的成本是 只有表lis_code_dept因关联时使用的是其主键 因此这里使用了主键索引 从而避免了全表扫描 它的成本是 我们知道提高查询性能的目标之一就是消灭掉全表扫描 因此我们应该给表m_patient和lis_report加上适当的索引 在SQL代码的where子句中 对m_patient表 我们引用了i_age和s_name字段 对lis_report表 我们引用了d_checkdate字段 通常给这些条件中引用的字段加上索引会提高查询速度 我们先给m_patient的i_gae字段加上索引 下面是对应的执行计划
![解决](https://tyye.cn/zb_users/theme/tpure/style/images/lazyload.png)
表m_patient的全表扫描消失了 取而代之的是索引唯一性扫描 成本从 一下子降低到 了 注意这里并未使用我们给i_age增加的索引 但却靠它触发了使用表主键对应的索引 但表lis_report仍然是全表扫描 由于where子句中引用了该表的d_checkdate字段 因此我们给该字段加上索引看看效果
表lis_report的全表扫描消失了 取而代之的是索引范围降序扫描(INDEX RANGE SCAN DESCENDING) 成本也从 下降到 注意这里的索引范围降序扫描的来历 因为我的where子句中引用d_checkdate是介于 至 的一个范围 这时引用的这种字段上建立的索引通常都是执行范围扫描 因为这种条件返回的值往往不止一行 使用降序扫描的原因是order by子句使用了降序排序 如果我们将SQL代码中的 order by a d_checkdate desc 改为 order by a d_checkdate 则变为索引范围扫描(INDEX RANGE SCAN)
至此我们全部消除了全表扫描 我们看到加上索引后 查询执行的成本开销也有所降低 因为数据库表中的记录数不大 因此效果不太明显 如果有上百万条记录则会更直观
lishixinzhi/Article/program/Oracle//如何彻底解决oracle 索引失效问题
你的脚本是怎么写的?insert插入还会使索引失效?第一次听说。 。 。 。 应该是你脚本写的有问题吧比如删除分区的时候没有update global indexes先把脚本让我看看~~另外,你的分区表上的索引是分区索引local index还是全局索引global index?
免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。