随着Oracle对CBO的进一步增强和改进, 对表进行分析已经成为一种常用的调优的手段,
当发现某个表的相关SQL语句的执行计划有问题时, 首先会想是不是统计信息过旧的问题, 如果的确是过旧, 则对这个表进行分析,
以让Oracle重新选择准确的最优执行计划, 以达到调优的目标. 用不合适的方式对表进分析, 则会造成十分严重的后果,
如对一个用户下的所有表进行分析, 或一下子分析多个表. 看到网友的
一篇贴子, 让我想起二年多前帮别人处理过的一个案例, 有位DBA对某用户下的所有表进行了分析.
基本情况是, IBM P570, 16CPU, 32GB内存, 24GB的SGA,
支持不了一个50G的9i数据库(OLTP类型), 二百个以内的会话. 在分析之前CPU利用率是50-70%左右, 在分析后则一直是100%.
面临这样的情况后, 由于没有做统计信息(Statistics)的备份, 因此无法恢复以前的情况, 首先做的是删除所有非分区表的统计信息,
对分区表做更精确的统计信息分析, 使之运行于RULE方式, 情况稍有好转, 但用户还是不能接受, 相信Oracle的CBO没有那么差,
因此性能问题的关键并不在于统计信息了. 在处理了以下几个问题之后, 成功地降底了CPU的利用率.
1, 发现繁烦的并行进程, 发现一个几有几十MB大小的表, 它的并行度不为1, 因此导致了这个表上的一个执行比较繁烦SQL采用了并行处理, 将并行度改成1后, CPU利用率降低了20%, 基本上恢复到了分析前的水准.
2, 发现一个后台发票打印程序的SQL有太多的逻辑读, 对发票打印模式进行分析后,
建议将执行繁率从每10秒执行一次降底到1分钟执行一次, 因为从售货台到打印发票的地方取发票需要走1分钟左右. 再检查其SQL,
发现其中的日期条件默认是两年, 也就是要查最近两年的销售记录, 再找出没有打印过的记录进行处理,
通过询问销售人员有没有人会来要求打印两周以前的发票, 当然是很少的了, 后来将这个默认的日期条件改成了最近一个月. 做了这个改动后,
系统的CPU利用率下降到了50%.
3, 接下来处理了一个库存查询有关的SQL, 对这个SQL本身没有作任何修改,
因为用户可以自定义条件(动态构造WHERE条件)进行查询, 发现很多用户只选了最少的条件进行过滤, 导致了这个SQL运行效率极低,
解决办法就是培训前台用户, 让他们使用这个功能时, 尽可能地提供比较准确的查询条件. 这样一来, CPU利用率下降到了35%左右.
4, 调整了一些SQL后, 新的SQL又出现了, 这一次的问题是, 看这个SQL的执行计划,
居然用了INDEX合并(Combine), 在where条件中用到了两个列, 开发人员在这两个列上分别建了索引, 但从单个列的角度来看,
效率不高, 但一组合效率则很高, 因此用复合索引解决此事. CPU利用率再次小降5%.
5, 后面又调了几个SQL, 这次是创建了几个新的索引, CPU的利用率已经下降到了20-25%, 目标完成.
总地来说, 分析表从来都不是优先考虑的调优手段, 从个人角度来看, 只有发现Oracle在某个表上选择了错误的执行计划后, 才会对单个表进行分析(分析之前先做备份, 除非很确定), 然后观察, 再分析下一个表.
原文地址：
http://www.anysql.net/dba/after_analyze_whole_schema.html