做 Oracle 优化这几年我最大的体会是执行计划分析才是真正拉开SQL性能差距的分水岭。索引加得再多、参数调得再勤如果你看不懂优化器到底怎么跑这条SQL所有优化都像闭着眼睛开车。执行计划就是Oracle给SQL画的“行车路线图”它精确告诉你先访问哪张表、用没用索引、表之间怎么连接、每步处理了多少行。这篇文章我会把自己在重庆思庄技术项目里常用的执行计划分析方法、采集脚本和几十个实战场景踩过的坑一起整理出来适合刚入门想系统学Oracle优化的朋友也适合已经在做一线运维、想补强SQL调优方法论的工程师。读完你至少能独立完成一次执行计划的解读和常规问题定位。1. 执行计划分析的前提先搞懂这是什么东西、什么时候需要看1.1 执行计划到底是什么执行计划是Oracle优化器CBOCost-Based Optimizer根据SQL文本、表统计信息、系统参数、约束条件等信息计算出的一个“物理执行方案”。它不关心你SQL怎么写只关心最终怎么去取数先扫描A表还是先扫描B表、通过索引回表还是直接全表扫描、两表是用嵌套循环还是哈希连接、排序在哪个步骤发生。所有这些都能从执行计划里读出来。我经常拿快递分拣来打比方。你下了一个订单SQL仓库Oracle看订单后决定从哪个货架取货、用什么车配送、按什么路线走。执行计划就是这个“取货方案和配送路线”它不是SQL本身而是Oracle在拿到SQL后“想”出来的物理动作。不同时间点同一句SQL执行计划都可能因为统计信息变化、数据量增长而完全不同。在Oracle 11g及以后版本里默认优化器是全自动的。CBO会为每条SQL生成多个可能的执行计划然后根据代价Cost选一个最小的。这里要提醒一个新手的认知误区Cost只是一个估算值不代表真实执行时间。Cost低并不代表一定快这就像导航估算的“预计到达时间”不等于你实际到达时间堵车、红绿灯都可能让估算失准。正因为如此执行计划分析不能只看Cost数字要看清楚每一步操作和实际行数。1.2 什么样的SQL才值得花时间做执行计划分析不是所有慢SQL都要一上来就分析执行计划。如果一个SQL只是偶尔慢一次可能是因为锁、资源竞争或者会话阻塞去看执行计划反而抓不到重点。我建议按下面这个顺序先做一个初步分类。第一类频繁执行但单次很快的SQL。比如某个ERP系统里每秒执行几十次的小查询单次只要几毫秒但累计起来占用了大量数据库时间。这类SQL主要问题往往是执行次数太多、逻辑读偏高分析执行计划时要重点看它有没有做无谓的全表扫描或者低效的索引访问。第二类执行时间很长、偶发或者持续变慢的SQL。比如报表查询跑了几分钟甚至几十分钟这种SQL优先分析执行计划确认是否存在嵌套循环放大、排序下盘、笛卡尔积等典型问题。第三类同一个功能在不同环境表现差异巨大的SQL。比如开发环境1秒返回生产环境要10秒这种情况不要急着改代码先对比两边的执行计划差异通常一目了然。这里有一个我反复强调的“黄金法则”先看执行计划再看索引最后才考虑改写SQL。很多工程师一遇到慢查询就疯狂加索引结果索引建了一堆执行计划还是不走问题根本不在索引缺失而在优化器压根没考虑用索引。所以理性做法是先把SQL的执行计划抓出来看清优化器在干什么再决定下一步动作。如果你的问题SQL走的是存储过程也用同样的方法把存储过程里单独拎出那一条SQL执行计划单独分析而不是整个存储过程一把抓经验告诉我90%的存储过程性能问题最后都定位在某一条SQL上。2. 拿到执行计划后先看这五个关键点2.1 从ID和Operation看执行顺序读执行计划的第一步是看执行顺序。Oracle执行计划的显示方式看起来像一棵树ID是步骤号Operation是操作名称。很多人以为执行顺序就是从上往下或者从ID从小到大这是错的。真正的执行顺序是先执行缩进最深的节点同缩进情况下按ID靠后的优先执行。换句话说要从最右边的、缩进最深的操作开始读然后向上回溯。我给你一个常见的例子------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 2900 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 100 | 2900 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 10 | 140 | 1 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX| DEPT | 10 | 150 | 1 (0)| 00:00:01 | | 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------这个计划里先执行的是ID4的INDEX UNIQUE SCAN然后ID3用索引结果回表DEPT再和ID2的全表扫描EMP做NESTED LOOPS连接最后ID0输出结果。所以阅读顺序是4→3→2→1→0而不是0到4。如果缩进层级比较复杂可以把执行计划理解成一个函数的调用栈内层调用先执行外层等内层结果出来后再继续。操作名里还藏着大量信息。TABLE ACCESS FULL是全表扫描INDEX RANGE SCAN是索引范围扫描INDEX UNIQUE SCAN是索引唯一扫描SORT ORDER BY是排序操作HASH JOIN是哈希连接。每个操作名都代表了优化器选择的一种物理路径。2.2 识别全表扫描以外的“坏计划”信号说到TABLE ACCESS FULL全表扫描我必须要纠正一个流传很广的错误观念“看到全表扫描就认为SQL有问题”。全表扫描本身不是问题问题在于在不对的场景下做了全表扫描。比如一张1000万行的表查询条件能过滤掉99%的数据但优化器还是走了全表扫描这就是典型需要关注的场景。反过来如果表本身只有几百行优化器选全表扫描反而是最合理的这时候你强行走索引就是画蛇添足因为索引扫描要经历索引块读取和回表两次I/O开销比直接读几个数据块大得多。真正需要警惕的执行计划信号有哪些我在团队里给新人列过一个“坏味道清单”大表全表扫描排序。如果一个大表被全表扫描后还要做ORDER BY或GROUP BY意味着大量数据要在内存或临时表空间里排序SQL慢通常是这一步搞出来的。嵌套循环里出现大表驱动。嵌套循环适合驱动表小、被驱动表走索引的场景。如果驱动表本身几十万行被驱动表又没走索引整个操作行数会被放大成几十万乘以全表扫描响应时间直接爆炸。意外的笛卡尔乘积。执行计划里出现“MERGE JOIN CARTESIAN”或“NESTED LOOPS”且连接条件完全没生效这种基本都是SQL少写了连接条件结果集被盲目放大。INDEX FULL SCAN。注意这个操作它虽然用了索引但本质是把整个索引从头到尾扫一遍如果索引列很多、索引很大这种扫描成本可能比全表扫描更高。看到这些信号后不要急着下结论还要结合真实执行行数和谓词信息来判断严重程度。2.3 JOIN方式三选一关键看数据量Oracle常用的连接方式有三种嵌套循环NESTED LOOPS、哈希连接HASH JOIN、排序合并连接SORT MERGE JOIN。很多初学者靠背定义区分它们但实际做优化时要学会“根据数据量预判”。嵌套循环适合“一个表很小、另一个表在连接列上能高效走索引”的场景。它像拿着小名单挨个去查另一个表每查一个都很精准。如果小表只有100行大表连接列有索引那总共也就100次索引访问性能非常好。但如果大表连接列没有索引每次都要全表扫100次全表扫描就是灾难。哈希连接适合“两个表数据量都比较大且连接列没有合适的索引或选择性不高”的场景。它先把小表build table读进内存建哈希表再扫描大表probe table逐行去哈希表里探测。两张大表关联哈希连接通常是首选因为它只需要扫描两次内存里完成大部分匹配。排序合并连接适合的场景相对窄它要求两个表都能按连接列排序后再做合并通常用在“连接条件是不等值比较、、”或者“哈希连接因为排序需求无法发挥”时。三者没有绝对优劣关键是看基数估计。我在下面整理了一张对比表方便快速决策。连接方式适用场景短板优化重点NESTED LOOPS驱动表小被驱动表连接列有索引驱动表大时放大行数保证驱动表过滤后行数小HASH JOIN两表都大、无有效索引、等值连接内存不足会下盘控制PGA、收集准确统计信息SORT MERGE JOIN不等值连接、已有排序条件排序开销大避免参与表过大2.4 对比Rows、Bytes与A-Rows判断实际代价执行计划里有一组非常重要的数字Rows估算行数、Bytes估算字节数、Cost估算代价以及在带A-Rows实际行数的真实执行计划里显示的Actual Rows。我要求项目组成员每次解读执行计划时做同一个动作对比Rows和A-Rows的差距。为什么这个对比这么关键因为Oracle优化器是根据统计信息估算行数的。如果统计信息过期或者采样率不足Rows会和实际情况差出几个数量级。比如执行计划上写着Rows1实际跑出来A-Rows100000说明优化器严重低估了数据量它据此选择了嵌套循环或索引扫描结果必然慢。反过来Rows100000而A-Rows1说明优化器高估了数据量可能选了全表扫描或哈希连接造成不必要的开销。我举一个真实例子。之前有个客户反馈某订单明细查询凌晨跑得好好的早上上班时间就变慢。抓出真实执行计划后发现同样的SQLRows估算从一开始的几百变成了一万A-Rows却只有几十。原因是这张表的统计信息在夜里被另一个定时任务更新了新的直方图数据让优化器改变了连接策略。我们最后通过锁定该表的统计信息并手动设置合理的收集策略解决。所以记住这个判断方法估算行数和实际行数偏差超过10倍时第一优先怀疑统计信息问题而不是SQL写法问题。这也是为什么我建议在分析时尽量抓真实执行的计划比如DBMS_XPLAN.DISPLAY_CURSOR配合ALLSTATS LAST而不是只依赖EXPLAIN PLAN的估算值。2.5 注意谓词信息中的Access与Filter差异执行计划下面通常还有一块谓词信息Predicate Information里面写着每个步骤实际施加的条件条件分为Access和Filter两类。Access表示这个条件参与了索引定位比如“INDEX RANGE SCAN”条件下写着“DEPT_NO:1”说明Oracle通过这个值直接缩小了索引搜索范围。Filter表示这个条件是在行被取出来之后再做过滤比如“TABLE ACCESS FULL”后写着“FILTER(STATUSN)”说明所有行都要经过这个条件筛选一遍。两者性能差距非常大。Access是先缩小范围再取值Filter是先拿全部数据再逐行过滤。如果本该作为Access的等值条件出现在Filter里通常意味着索引没建对、或条件里的列发生了隐式类型转换、或被函数包裹导致索引失效。我经常见到类似“WHERE TO_CHAR(CREATE_DATE,YYYY-MM-DD)2025-01-01”的写法优化器没法直接用CREATE_DATE索引因为条件被函数包裹索引列失去了原始值。这时谓词信息里就会显示Filter而不是Access。解决办法是把条件改成“CREATE_DATE TO_DATE(2025-01-01,YYYY-MM-DD) AND CREATE_DATE TO_DATE(2025-01-02,YYYY-MM-DD)”。另外要留意谓词信息里的“TRUNC(SYSDATE)”这类写法。很多人习惯写“WHERE CREATE_DATE TRUNC(SYSDATE)”这对CREATE_DATE本身没有套函数是可以走索引的。但如果你写“WHERE TRUNC(CREATE_DATE) TRUNC(SYSDATE)”那问题就来了CREATE_DATE被TRUNC包裹后索引基本失效。日常Excel习惯搬到SQL里要格外小心SQL不是这么理解日期的。3. 执行计划的四种采集方式和常用脚本3.1 EXPLAIN PLAN FOR小问题快速验证EXPLAIN PLAN FOR是最基础的执行计划采集方式语法就是EXPLAIN PLAN FOR SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE DEPTNO 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);它的原理是让优化器生成执行计划并写入PLAN_TABLE$但不真正执行SQL。优点是快、无副作用适合在开发环境验证SQL写法调整后的计划变化。缺点也非常明显它看不到真实的A-Rows、执行时间和等待事件而且因为不执行某些动态采样和绑定变量行为也无法完全模拟。我一般只在两个场景用EXPLAIN PLAN一是新写SQL快速确认是否走了目标索引二是测试不同的hint写法能不能改变计划。真正判断线上性能问题时我很少用它因为计划是“算出来”的不是“跑出来”的和生产环境的真实状态可能有偏差。有一个使用细节要提醒EXPLAIN PLAN FOR之后查询的是当前会话的PLAN_TABLE如果多个会话同时执行注意不要串了。建议用EXPLAIN PLAN SET STATEMENT_ID加上唯一标识来区分查询时也带上WHERE STATEMENT_IDxxx。3.2 DBMS_XPLAN.DISPLAY_CURSOR抓线上真计划线上定位问题我最常用的是DBMS_XPLAN.DISPLAY_CURSOR。它能从库缓存Library Cache里直接取出某条SQL最近一次真实执行的执行计划配合格式化选项还能看到A-Rows、A-Time、Physical Reads等信息。基础用法是这样SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR( sql_id xxxxxxxxxxxx, format ALLSTATS LAST ));format参数里ALLSTATS LAST表示显示最后一个执行游标的实际统计信息包括实际行数A-Rows、实际时间A-Time、逻辑读Buffers等。如果你只想看计划本身用TYPICAL就行想看到更全的谓词和列信息用ADVANCED或ALL。使用前提是先拿到SQL_ID。可以通过V$SQL或V$SQLAREA按SQL文本模糊匹配SELECT SQL_ID, SQL_TEXT, ELAPSED_TIME, EXECUTIONS FROM V$SQL WHERE SQL_TEXT LIKE %你要找的关键词% AND SQL_TEXT NOT LIKE %V$SQL%;这里有个很重要的细节DISPLAY_CURSOR取出的是“当前还在库缓存里的SQL”的执行计划。如果SQL已经老化、被挤出库缓存或者实例重启过这个函数就取不到计划。遇到这种情况可以从AWR里挖历史SQL或者用下面要说的10046事件重新跑一次。3.3 10046事件和TKPROF追根溯源当一个问题SQL徘徊了几轮前面两种方法都解释不了我就会上SQL Trace。SQL Trace是一个诊断工具开启后Oracle会把SQL的每个执行步骤、等待事件、绑定变量值、物理读、逻辑读都记录到trace文件里。开启方式很多最直接的是在会话里执行ALTER SESSION SET EVENTS 10046 trace name context forever, level 12; -- 执行你要分析的SQL ALTER SESSION SET EVENTS 10046 trace name context off;level 12代表同时记录绑定变量和等待事件是最常用的调试级别。Trace文件生成在USER_DUMP_DEST目录文件名一般包含实例名和进程号。然后用TKPROF工具格式化tkprof orcl_ora_12345.trc output.txt sysno sortprsela,exeela,fchelaTKPROF输出里我最关注的几个指标是COUNT执行次数、ELAPSED耗时、DISK物理读、QUERY一致性读、ROWS处理行数。如果某个步骤QUERY非常高但ROWS非常低说明索引使用或过滤条件出了问题如果DISK很高说明排序下盘或物理读太频繁。SQL Trace的另一个价值是能看到等待事件。比如一个执行计划看起来没问题但等待事件里大量出现“db file sequential read”说明索引读确实很多如果大量出现“PX Deq: Execution Msg”说明并行度策略不合理。执行计划分析不应该只看“形态”还要结合“等待”来综合判断这是很多新手会忽略的维度。3.4 AWR和SQL Monitor Report场景化的计划视图处理生产问题时如果SQL已经跑完但你没来得及抓现场可以借助AWRAutomatic Workload Repository的SQL顺序。AWR里保存了快照之间的TOP SQL信息包含执行计划、平均耗时、执行次数、物理读等。查询方式SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id));DISPLAY_AWR的好处是可以看历史计划并且能看到计划随时间的演变。比如同一个SQL在过去7天里出现了计划变更我就能定位出“从哪天开始变慢那天到底发生了什么”。对于周期性问题比如每周一早上变慢AWR的快照对比几乎是唯一靠谱的手段。如果SQL执行时间超过6秒默认阈值且使用了并行或部分场景还可以用DBMS_SQLTUNE.REPORT_SQL_MONITOR生成SQL Monitor Report。这个报告是Oracle 11g以后推出的实时监控功能显示SQL在执行过程中的真实行数、耗时分布、并行度使用情况比传统的执行计划更直观。它能展示每个操作符的实际执行时间相当于给执行计划加了“计时器”对复杂SQL分析非常有帮助。4. 压箱底的执行计划实战案例4.1 案例一一张大表的全表扫描如何变成索引扫描之前有个生产系统运维同事反馈一张交易明细表的按客户查询变得很慢。SQL简化一下是这样SELECT * FROM TXN_DETAIL WHERE CUST_ID C10089 AND CREATE_DATE SYSDATE - 30;我抓出执行计划后看到的第一个操作就是TXNDETAIL上的TABLE ACCESS FULL扫了几百万行Rows估算却只有几十行。表上有CUST_ID的索引为什么不用仔细看谓词信息才发现SQL实际传入的CUST_ID被一个存储过程的变量定义为CHAR(20)而表里CUST_ID是VARCHAR2(20)。Oracle在比较时发生了隐式转换相当于把CUST_ID套了一层TO_CHAR或TO_NUMBER逻辑索引列被函数包裹索引自然失效。这类问题非常典型。解决方法是把变量类型和列类型对齐比如把存储过程里变量改成VARCHAR2(20)或者改写条件“WHERE CUST_ID TRIM(:CUST_ID)”。这里要注意如果条件改写成“TRIM(CUST_ID)TRIM(:CUST_ID)”索引也无法使用正确的做法是只处理变量一侧保持列不被函数包裹。改完后重新抓执行计划索引扫描生效SQL从十几秒降到几十毫秒。这个案例给我们的教训是解读执行计划时看到“有索引不用”不要第一反应去删索引或加hint先看谓词信息里是不是发生了类型转换或函数包裹。4.2 案例二两张大表关联为什么HASH JOIN突然失效另一个客户遇到的是日报统计SQL两个千万级大表关联之前一直走HASH JOIN跑二三十秒某天突然变成几十分钟。我抓到的执行计划里两表关联方式变成了NESTED LOOPS驱动表数据量又特别大导致被驱动表被反复扫描。为什么优化器放弃了HASH JOIN排查后发现两个大表的关联列上有一方统计信息严重过期优化器以为驱动表过滤后只剩几百行于是选了嵌套循环。真实过滤行数是几十万嵌套循环直接放大到几十万次索引探测。后来通过重新收集两张表和关联列的统计信息执行计划恢复为HASH JOIN性能回归。这个案例值得记住的是执行计划突然变化第一怀疑对象永远是统计信息变化或绑定变量窥视。索引还在、SQL没改动计划却变了说明优化器的“计算输入”变了统计信息、系统参数、数据分布都可能影响。我刚入行时遇到类似问题第一反应是加hint强制走HASH JOIN虽然临时解决了但掩盖了统计信息管理的根因后来统计信息再次变化问题又会冒出来。正确的做法是治好统计信息的“根”再用hint救“急”。4.3 案例三分页查询与COUNT(*)的执行计划陷阱Oracle分页是老话题了网上搜“Oracle分页”能看到一堆示例但很多人不知道分页SQL的执行计划也有讲究。典型的Oracle分页SQL是SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY CREATE_DATE DESC) RN, T.* FROM BIG_TABLE T WHERE STATUS A ) WHERE RN BETWEEN 1 AND 20;这个写法本身没问题但执行计划分析时要关注两点。第一内层是否先做了全表排序再取前20条。如果CREATE_DATE上有索引优化器可能用索引避免排序但如果过滤条件STATUS和排序条件CREATE_DATE分别在不同的索引上优化器就得先过滤再排序排序的代价取决于中间结果集大小。第二如果分页查询还要统计数据总量比如返回“总记录数”很多人会再跑一条SELECT COUNT(*)这时COUNT本身也有执行计划问题。COUNT(*)最常见的坑是全表扫描。如果表很大且过滤条件选择性好可以考虑用索引快速扫描来实现COUNT或者把总量查询改成基于统计信息近似估算业务上是否接受误差要和产品商量。我个人在项目里通常建议如果分页结果集很大、翻页很深用ROW_NUMBER分页会越翻越慢因为每次都要重新计算整个结果集的排序。这时候可以考虑“FETCH FIRST 20 ROWS ONLY”配合游标分页或者用“上一页最后一条记录的值”作为下一页的边界即键集分页。这些方案的执行计划更可控性能也更稳定。4.4 案例四EBS WIP非标工单查询的执行计划陷阱Oracle EBS是制造业用得很多的ERP套件WIPWork in Process模块的非标工单查询经常是优化重灾区。非标工单和标准工单的区别在于工艺路线和装配件关系更灵活数据模型里涉及WIP_DISCRETE_JOBS、WIP_OPERATIONS、WIP_MOVE_TRANSACTIONS等核心表查询逻辑往往要关联一大堆外层表。这类查询的执行计划通常存在两个陷阱。第一个陷阱是外连接条件放错位置。EBS的开放接口表和事务历史表量大如果查询把本该作为外连接表的过滤条件写在WHERE子句里等于把外连接变成了内连接优化器可能认为能通过过滤缩小驱动行数但实际由于NULL值被过滤结果集错误的同时执行计划也可能选错连接方式。第二个陷阱是在WIP_MOVE_TRANSACTIONS这类高频事务表上做全表扫描。事务表数据量增长快统计信息很容易过期如果查询没有正确使用COMPLETION_DATE、JOB_ID等索引列上的谓词计划很容易从索引扫描翻车成全表扫描。处理EBS非标工单慢查询时我的建议是不要直接调复杂的表单SQL而是把最核心的取数逻辑抽成一条独立SQL先调优。先抓出FORM对应的SQL文本定位到哪里在扫描WIP核心表再用DBMS_XPLAN.DISPLAY_CURSOR看A-Rows重点看WIP_MOVE_TRANSACTIONS这一步的实际行数。如果发现它被扫描了大几百万行优先看有没有JOB_ID或TRANSACTION_DATE的索引可以命中。另外EBS环境的并发管理器请求经常杀不完SQL Monitor Report在这种场景特别有用能直接看到整个报表请求里哪个SQL、哪个步骤耗时最久。5. 执行计划优化中碰到的典型问题和排查经验5.1 统计信息不准、过期、没收集怎么定位执行计划不准十有八九是统计信息问题。定位方法很简单用DBMS_STATS.GET_TABLE_STATS看一下表级统计信息SELECT NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED FROM USER_TAB_STATISTICS WHERE TABLE_NAME BIG_TABLE;如果LAST_ANALYZED离现在很久或者NUM_ROWS和实际行数差异巨大基本可以断定统计信息过期。对于分区表还要看分区级别的统计信息因为即使表级统计信息新鲜某个分区可能已经很久没分析了。比如一张按天分区的流水表昨天的分区今天被大量插入但统计信息快照还是昨天之前的优化器就可能低估该分区行数。重新收集统计信息要注意方式。全量收集大表时不要用100%采样太慢也容易给生产带来额外压力。推荐用DBMS_STATS的AUTO_SAMPLE_SIZEOracle会自动决定合适的采样比例EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, BIG_TABLE, METHOD_OPT FOR ALL COLUMNS SIZE AUTO, DEGREE 8);对于数据分布严重不均的列建议手动设置直方图桶数。比如订单状态列绝大多数是“已完成”只有少量“待处理”这种场景没有直方图优化器会严重低估少量状态值的基数。我见过一个案例SQL查询STATUSNEW实际只有几百行但没有直方图时优化器以为有50万行选了大表全表扫描加哈希连接响应时间从毫秒级变成秒级。重新收集列统计信息时不光要收集还要等STATUS列创建了直方图才有效。5.2 绑定变量窥视Bind Peeking的坑Oracle 11g之前有一个经典问题叫绑定变量窥视Bind Peeking。意思是第一次执行SQL时优化器会“偷看”绑定变量的实际值并基于这个值生成执行计划后续执行都复用同一个计划。问题在于如果第一次传入的是一个分布极端的值生成的最优计划可能对后续其他值完全不适合。举个例子一张订单表ORDER_STATUS有99%是“已完成”1%是“待处理”。SQL是“SELECT * FROM ORDERS WHERE STATUS:S”。第一次执行传入‘已完成’优化器看到大量行选择了全表扫描后续所有执行包括传入‘待处理’的都沿用全表扫描而‘待处理’明明该走索引。这是典型的绑定变量窥视带来的计划不稳定。Oracle 11g之后引入了自适应游标共享Adaptive Cursor SharingACSOracle会根据实际执行情况动态扩展游标共享但也不是银弹。我自己在处理这类问题时除升级版本外还可以通过几个手段缓解一是为不同分布的值写不同SQL或拆分业务逻辑二是用SQL Plan ManagementSPM锁定稳定计划三是在11g以后用DBMS_STATS的FREQUENCY直方图让优化器更准确判断。如果生产环境已经出现计划不稳定我建议先抓两条不同绑定值下的真实执行计划确认是不是窥视问题再决定用哪一种方案不要一上来就禁用绑定变量那会把共享池搞乱。5.3 怎样用提示Hints让优化器听话当分析确认优化器选择错误而统计信息已经正确时可以考虑用Hints施加干预。Hints就是SQL注释里的指令告诉优化器“你必须这样走”。常用语法SELECT /* INDEX(EMP IDX_EMP_DEPT) */ EMPNO, ENAME FROM EMP WHERE DEPTNO 10;常用的Hints包括INDEX指定索引扫描、FULL强制全表扫描、LEADING指定驱动表、USE_HASH强制哈希连接、USE_NL强制嵌套循环、NO_INDEX禁用索引。这里我特别强调一点Hints是“手术刀”而不是“止痛药”。它能快速解决当前问题但数据库环境一变比如索引改名、数据量暴增、统计信息变化写死的Hints就可能是新的性能陷阱。所以用Hints一定要注释清楚原因并且在代码评审时留痕后续维护人员才知道这个Hints为什么存在。我最常用的Hints场景是两表连接顺序问题。优化器选了不合适的驱动表但我又不想大改SQL结构就用“LEADING(小表) USE_HASH(大表)”强制走预期路线。要注意Hints里的表名不能带SCHEMA前缀除非用了别名那就写别名。还有一个容易犯的错写“/* INDEX(TABLE_NAME) */”但实际SQL里表用了别名导致Hints失效优化器完全无视。Oracle里的Hints匹配的是表别名或表名这里要非常小心。5.4 从计划优化到一线脚本的自查清单做执行计划分析久了我自己沉淀了一套自查清单每次遇到慢SQL就按这个顺序过一遍步骤检查内容常用手段1确认SQL是否频繁执行、耗时分布V$SQL / V$ACTIVE_SESSION_HISTORY2抓真实执行计划和A-RowsDBMS_XPLAN.DISPLAY_CURSOR3对比估算行数和实际行数观察Rows与A-Rows差距4检查谓词信息里Access/Filter分布查看Predicate Information5核对统计信息新鲜度和直方图USER_TAB_STATISTICS / DBMS_STATS6确认连接方式和驱动表是否合理检视JOIN操作符7检查是否存在类型转换或函数包裹对照表列定义和SQL条件8必要时启用10046或SQL MonitorTKPROF / REPORT_SQL_MONITOR这套清单看起来简单但真正按顺序执行下来大多数执行计划问题都能定位到具体环节。我强烈建议刚入门的朋友把这张表贴在手边遇到问题不要凭感觉直接加索引按清单走一遍你会发现自己对SQL执行机制的理解会快很多。平时我在一线还会用两个小脚本。一个查V$SQL里的TOP SQLSELECT SQL_ID, SQL_TEXT, ELAPSED_TIME/EXECUTIONS AVG_ELAPSED, BUFFER_GETS/EXECUTIONS AVG_BUFFER_GETS, EXECUTIONS FROM V$SQL WHERE EXECUTIONS 100 ORDER BY AVG_ELAPSED DESC FETCH FIRST 20 ROWS ONLY;另一个快速看某个SQL的历史执行计划变化SELECT PLAN_HASH_VALUE, COUNT(*), MIN(SNAP_ID), MAX(SNAP_ID) FROM DBA_HIST_SQLSTAT WHERE SQL_ID xxxxxxxxxxxx GROUP BY PLAN_HASH_VALUE;如果PLAN_HASH_VALUE种类很多说明SQL计划不稳定要优先处理。6. 最后再分享一个我坚持了很多年的小习惯每次做完一次执行计划分析不管问题大小我都会在工单记录里留下一段话原始执行计划的关键截图、估算和实际行数、改动后的计划、改动的根因。这个习惯最初只是为了让同事能接替我的工作后来越来越发现它值钱。因为Oracle环境和业务数据是动态变化的两个月后同一个SQL再次变慢你翻开旧记录对比一下计划马上就能判断是统计信息漂移了还是数据分布变了根本不需要从头排查。另外一个小技巧要是你对某个执行计划是否合理拿不准先别急着调优去SQL Monitor里看一次真实运行时的等待分布。如果时间几乎都花在一个你预期不该有大量等待的操作上那说明这个操作本身才是矛盾的焦点这时候再回头看计划就不会被Cost数字带偏。执行计划分析不是一个高深莫测的本领它就是一条需要不断重复的实践路径抓计划、读操作、比行数、查统计、验证改动。多接几个真实工单多保存几份前后对比的计划你很快就能形成自己的判断直觉。