聊聊那些年折腾Oracle性能优化时摸索出的实用经验和小技巧

聊起折腾Oracle性能优化的那些年，真是满满的血泪史和成就感，那时候没什么现成的“一键优化”工具，全凭经验、耐心和一点点运气，我摸索出的东西，可能上不了教科书,但在实际救火中特别管用。

第一招：先别急着动SQL，看看“地基”稳不稳。

新手最容易犯的错就是一慢就怪SQL，然后扎进代码里出不来，我有个习惯，接到性能问题，第一件事就像老中医“望闻问切”，先看整体，我会马上去查当时数据库的等待事件（来源：Oracle的AWR/ASH报告中的Top Wait Events），这个报告就像病人的体温和血压，能告诉你数据库现在“卡”在哪儿了。

最常见的是“db file sequential read”（顺序读）和“db file scattered read”（分散读）太高，这通常意味着SQL在疯狂地全表扫描或者索引范围扫描，物理I/O成了瓶颈，如果看到“latch: cache buffers chains”（缓存链锁）争用，那说明有很多会话在抢着读内存里同一块数据，可能是热点块问题，还有一次，一个系统慢得离谱，我一看等待事件，“log file sync”（日志文件同步）高居榜首，这说明应用提交太频繁，或者磁盘写日志太慢，跟SQL本身关系反而不大，不看等待事件就优化，好比蒙着眼睛修车,可能白忙活一场。

第二招：抓“坏”SQL，要像老鹰抓小鸡一样准。

系统里SQL千千万，怎么知道该对谁下手？不能凭感觉，我常用的方法是去AWR报告里找“SQL ordered by Elapsed Time”（按消耗时间排序的SQL）和“SQL ordered by Gets”（按逻辑读排序的SQL），这里有个小技巧：我更看重“Gets”（逻辑读），因为执行时间会受到网络、并发等因素影响，但逻辑读（从内存里读了多少个数据块）更能真实反映SQL本身的“辛苦程度”，一个SQL如果逻辑读巨大，哪怕这次执行快，也是潜在的定时炸弹，把它优化好了,才是治本。

第三招：看懂执行计划，别被“索引”二字骗了。

抓到可疑SQL，下一步就是看它的执行计划，这里坑很多，最经典的教训就是“索引不是万能的”，有一次，一个SQL查询某个状态 status='ACTIVE' 的记录，明明status字段上有索引，但执行计划偏偏走了全表扫描，新手可能会觉得Oracle优化器傻了，其实不然，这是因为表中99%的记录status都是'ACTIVE'，走索引反而需要回表99%的次数，成本比直接全表扫描高得多,优化器聪明着呢。

看执行计划不能只看用没用到索引，关键看执行计划里的“Rows”（估算行数）和实际返回的行数是不是差得太远，如果估算值是100，实际扫了一百万行，那说明表的统计信息过时了，或者直方图信息缺失，导致优化器做出了错误的判断，这时候，你的首要任务不是改SQL，而是去重新收集一下表的统计信息（dbms_stats.gather_table_stats）,很可能问题就解决了。

第四招：绑定变量，一把双刃剑。

为了避免硬解析，我们都会强调用绑定变量（:1），但绑定变量会带来一个“绑定变量窥探”的问题，一个SQL根据城市查询，北京返回100万行，拉萨返回10行，如果第一次执行时传入的是“北京”，优化器会生成一个适合大数据量的执行计划（比如全表扫描），这个计划会被后续所有执行复用，哪怕你下次查“拉萨”，它也会傻乎乎地全表扫描,这就悲剧了。

对付这种情况，除了及时更新统计信息，在一些极端情况下，我会尝试使用提示（Hint）来强制索引，或者干脆把SQL拆成两条，用不同的SQL文本来应对截然不同的数据分布，这是最后的手段,要非常谨慎。

第五招：最好的优化是“别做”。

这是我印象最深的一个经验，一个批处理作业半夜跑，总是超时，我们几个人折腾了好几晚，尝试了所有索引优化、SQL改写，效果都不明显，后来，我闲着没事把整个业务流程梳理了一遍，发现它在一个循环里，频繁地访问一个配置表上万次，每次只取一条记录，我试着把这个配置表一次性全部读到程序的内存里，用Map来匹配，就改了十几行代码，结果，这个作业的运行时间从一个多小时直接降到了十分钟以内。很多时候，性能瓶颈不在数据库内部，而在应用程序的设计逻辑上，减少对数据库的访问次数，是最高效的优化。

还有一些零碎的小技巧：

• 分区表是大杀器： 对于亿级以上的大表，按时间分区是必须的，查询时带上分区键，能瞬间把扫描范围从整个表缩小到一个小分区,效果立竿见影。
• 注意隐式转换： 如果字段是VARCHAR2类型，你用 where id = 123（123是数字），Oracle会隐式地把字段转换成数字，导致索引失效，一定要写成 where id = '123',让类型匹配。
• UNION ALL 代替 UNION：如果确定两个结果集没有重复数据，一定用 UNION ALL，因为 UNION 会额外做去重排序,开销大得多。

说到底，Oracle性能优化没有银弹，它是一个综合性的判断过程，最重要的经验就是：相信数据，别信感觉，从整体到局部，先诊断，再开药。 这些土办法虽然朴实，但在那些年,真的帮我解决了一个又一个棘手的问题。