注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

金生丽水,雨润轩德

 
 
 

日志

 
 

oracle 数据库优化案例  

2012-10-24 22:48:04|  分类: oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

       某系统使用oracle 数据库 + IBM AIX,在运行了1年多时间后,业务系统经常抱怨系统响应缓慢,部分业务需要半个小时以上的时间才能获取结果。

      到现场后,看到大部分索引表都在默认表空间中,部分表可能会经常进行插入删除操作,可能会导致索引表的碎片较多,导致性能收到影响。同时,索引表均在一个表空间中,也可能会造成IO问题,从而影响系统性能。因此给出的第一个建议是根据分区和功能模块创建索引表空间,将默认表空间中的索引表在线重建(rebuild online)到规划的表空间中。

     在检测一个SQL语句时,发现一个奇怪现象,语句中将两个查询结果通过union连接起来,执行计划中看到使用了排序和全表扫描。而使用union all 保留重复行和不用排序时速度非常快。看起来,只要涉及到排序就会很慢。从这个角度出发,看了很多资料,但都没有一个感觉可信的解释。sort_area_size的大小也是系统默认值,其他参数也未见异常。

      解释不通就暂缓一下,继续分析其他的SQL,在跟踪执行计划时,偶然点了一下优化指导,结果优化知道提示表的统计信息失效,既然失效,就重新生成一次统计信息吧,结果比较令人吃惊,在更新统计信息后,查询速度明显提升,从执行计划中看到,速度提升的根源在于,更新统计信息后,执行计划使用了索引,而在此之前使用的全表扫描。(统计信息显示,表的行数为几百行,而更新后的统计数据为1000万多行,差别比较大)。基于这个SQL,查看了其他表的统计信息,结果显示都是至少半年前的数据,有些甚至是正式上线前的数据。

      从oracle 官方文档中,可以看到,oracle使用查询优化器对SQL进行分析,从而制定出执行计划。而查询优化器的一个基本消息来源就是统计数据。没有统计数据,或者统计数据失效,优化器可能会制定出非常低效的执行计划,从而导致查询缓慢。

     基于此,利用晚上的时间,将业务系统使用的schema的表全部更新了统计信息,第二天查看和了解,业务系统的运行速度有了较大提升。

     但,统计信息也不能解决所有问题,开始的例子中,更新统计信息后,无论是union还是union all,执行计划都是全表扫描,统计信息显示,两个表都是千万行以上,全表扫肯定是慢。从得到的结果看,百万行级别的全表扫描在5秒内可以完成,千万级别的表基本需要20秒以上的时间。进一步分析看到,SQL使用了like '%aaa%' 模糊匹配,虽然在这个字段上建有索引,但这种模糊匹配无法使用索引。想一想,如果让你从一本英语词典中找出带有a(无论是开头还是结尾还是中间)的单词,无论如何,你都要将每页都翻一遍才行。 这种情况下,只有通过业务层面来解决,是不是必须要使用这种模糊查询,可否是'aaa%' 或者 '%aaa'(可以使用reverse('%aaa')来利用索引)。

     其次,在SQL中也看到一些比如 substr(,name,2,1) in ('a','n')这样的函数使用,这种查询方式的结果也是无法使用索引,可以更改为name like 'a' and name like ’n',这样在name列上的索引就可以发挥作用。

  

   其实,从执行计划中可以对SQL进行分析,只要是涉及到大表,而又无法使用索引的情况,查询就会慢,如果表在百万行级别,速度还可以接受(5秒内),如果是千万级别的表,基本上就不可接受了,需要等待较长的时间才能跑完。

 

   从以上过程可以看出,首先,要保持较为准确的统计信息,让优化器可以更好的选择执行计划。

其次,在编写SQL时,要注意避免一些无法使用索引的地方,要保证SQL对大表的查询能够使用或具有相应的索引。

  评论这张
 
阅读(665)| 评论(0)
推荐

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018