oracle数据库索引未被使用的问题及其解决2007

一次，在进行WEB页面上进行历史数据文件检查时，发现数据库访问及其慢，原先只需要几分钟的查询，现在几十分钟都不能搞定，

并且在查询阶段，数据库服务器的CPU负荷暴增

 

Oracle服务的CPU使用率接近50%，说明数据库可能问题！

 

首先，排除这个功能模块本身的问题，因为在完成这个功能模块时，经过了好几次测试，每次查询耗时都在6，7分钟左右，

东海健康系统中总共有接近4000个通道，算下来每个通道平均只耗时200ms上下，为了保险起见，我修改了JSP页面，对

单个通道的历史文件检查进行了耗时计算，结果显示：

 

一个简单的查询居然耗时6秒，绝对有问题！

 

确定了范围，找到该功能模块，把数据库查询SQL语句样例提取出来

select * from bhis_datafile where channelid = 585  and starttime >= to_date('20060901', 'yyyymmdd') and starttime < to_date('20061001', 'yyyymmdd') order by filename

这句SQL首先在本地运行，由于本地只有07年以后的数据，所以把时间范围调整了一下：

select * from bhis_datafile where channelid = 585  and starttime >= to_date('20070301', 'yyyymmdd') and starttime < to_date('20070401', 'yyyymmdd') order by filename

一开始感觉是order by的问题，把order by去掉后，

在PL/SQL Developer中运行该句，发觉用时才100ms左右

然后把这句话放到东海那边运行，发现也才100ms

这时候以为问题解决了，可我抱着试试看的想法，把时间调整回06年时，发觉还是很慢，差不多6，7秒的样子，没办法，按F5，一看发现有问题

 

Oracle数据库在查询该表时并没有使用到index索引，但是索引应该是有的，查看表结构

 

表索引是存在的，于是，汪洋调整了一下语句

select /*+ rule */* from bhis_datafile where channelid = 585  and starttime >= to_date('20060901', 'yyyymmdd') and starttime < to_date('20061001', 'yyyymmdd') order by filename

基于规则的查询优化。

select /*+ index(bhis_datafile, SYS_C005965) */* from bhis_datafile where channelid = 585  and starttime >= to_date('20060901', 'yyyymmdd') and starttime < to_date('20061001', 'yyyymmdd') order by filename

基于索引的查询优化

select /*+ first_rows*/* from bhis_datafile where channelid = 585  and starttime >= to_date('20060901', 'yyyymmdd') and starttime < to_date('20061001', 'yyyymmdd') order by filename

优先返回第一行查询结果

 

Oracle查询语句优化的文档：

http://192.168.1.86/oracle920doc/server.920/a96533/hintsref.htm#4781

 

都运行了一遍，发觉index索引存在，并且查询速度很快，100ms左右，和07年的是一样的，一旦把/* */中的设置去除，就变得很慢很慢

但问题是07年度的数据是没有这种问题的，只有06年有，奇怪！

问题是找到了，但为什么会发生这种情况呢，暂时无法得知，先不管，把问题解决再说。

这时候就只有把死马当活马医了：

首先：执行alter index SYS_C005965 rebuild，重建索引

但完成后发觉查询速度反倒比以前慢了。。。。

随后：执行alter index SYS_C005965 compute statistics，对该索引重新计算统计信息

完成后，比前面稍快些，但还是查询一次耗时将近4，5秒

最后：执行analyze table bhis_datafile ESTIMATE statistics，对该表进行统计信息评估

OK，这句执行完，数据库恢复正常，查询速度恢复到100ms一次，完全正常，按F5查看，发觉index索引被使用上了。

 

最后，问题总算是解决了，但为什么会发生这种奇怪的现象，还有待近一步研究。