自己在做SQL优化的时候最头疼的往往不是语法问题而是明明表结构没问题、索引也建了SQL跑起来就是慢。仔细一看执行计划问题多半出在表连接方式上。Oracle里的连接方法Join Methods一共就那么几种但选错一种性能可能是数量级的差距。这篇Part 4-2就是把官方SQL调优指南里关于连接方法的部分完整拆开揉碎讲清楚结合实测经验把嵌套循环、哈希连接、排序合并、笛卡尔连接这四种方式的原理、适用场景、控制手段一次说透。不管你是DBA、后端开发还是数据分析师只要写SQL、看执行计划这篇都值得完整读一遍。读完你至少能做三件事看懂执行计划里的连接方式选得对不对、知道为什么优化器选了这个连接方式、在必要时通过Hint和参数干预优化器的选择。1. 从执行计划看懂连接方法一张表看懂四种方式Oracle执行计划里表连接相关的操作符主要有四种NESTED LOOPS、HASH JOIN、SORT MERGE JOIN、MERGE JOIN CARTESIAN笛卡尔连接在执行计划里有时直接显示为CARTESIAN或MERGE JOIN CARTESIAN。很多初学者一看到执行计划里有多张表第一反应是看有没有走索引其实连接方法的优先级不亚于索引选择。选错了连接方法即使每张表都走了索引整体性能也可能非常糟糕。四种连接方式的核心差异用一个表格就能看明白连接方式实现原理内存/临时空间消耗适用场景常见执行计划关键字嵌套循环连接外层表逐行驱动内层表按连接条件查找匹配行内存消耗低小表驱动大表、内层表连接列有索引、返回少量行NESTED LOOPS哈希连接将小表构建哈希表大表逐行探测内存/PGA消耗较大可能落盘两张大表等值连接、无索引或索引选择性差、返回大量行HASH JOIN排序合并连接两边排序后做归并匹配排序区消耗大可能落盘非等值连接、、BETWEEN、数据已排序、旧版本优化器场景SORT MERGE JOIN笛卡尔连接无连接条件或连接条件失效两表全量交叉视数据量而定结果集巨大几乎都是性能事故极少数场景如小维表扩容才用MERGE JOIN CARTESIAN这四种方式不是Oracle随意选的优化器会基于成本Cost来决定。理解这个你就知道为什么有时候明明可以走嵌套循环优化器却选择了哈希连接——因为它算了账发现嵌套循环的成本更高。1.1 连接方法的本质数据是怎么“配对”的换个生活化的角度来理解连接方法的本质。嵌套循环是“一个萝卜一个坑地找”外层每取一行内层就拿着这个值去索引里查一次就像你手里有一串钥匙去一栋楼里挨个门试。哈希连接是先把一批钥匙按锁芯分类挂到墙上然后拿着另一批钥匙一一对应去碰适合大批量配对。排序合并则是先把两边都按同一个规则排好队再两队人从队头依次对比谁小谁往前走。理解了这个本质再看执行计划就通透了。比如执行计划里出现NESTED LOOPS你要关心的第一个问题是外层表是不是真的小驱动顺序到底对不对出现HASH JOIN你要关心的问题就变成了两个表的统计信息是否准确PGA是否够用有没有在临时表空间里落盘我在实际排查慢SQL时遇到过好几次这样的情况一个大SQL执行计划里显示HASH JOIN但等我检查完统计信息发现这张表的行数统计已经严重失真优化器根据错误统计信息选择了哈希连接实际数据量根本不需要那么重的连接方式。刷新统计信息后执行计划立刻变成了更合理的嵌套循环SQL从20秒跑到了0.8秒。这就引出了连接方法调优的核心逻辑不是你想用哪种就强制用哪种而是要让优化器掌握真实信息做出正确判断。2. 嵌套循环连接小表驱动大表的经典套路嵌套循环连接是最直观、最“朴素”的连接方式。它的执行逻辑是这样的优化器选择一张表作为驱动表outer table一张表作为被驱动表inner table从驱动表读取一行然后根据连接条件在被驱动表中查找匹配的行。查到了就返回一行结果再继续读驱动表的下一行如此往复直到驱动表所有行都被处理完。2.1 嵌套循环的内部机制与成本模型这个“一行一行查”的机制决定了它的一个核心特点访问被驱动表的次数等于驱动表返回的行数。如果驱动表有100行被驱动表就会被访问100次。所以嵌套循环的性能很大程度上取决于两个因素驱动表够不够小被驱动表连接列上的索引够不够高效。这个连接方式的成本可以用一个简单模型估算总成本约等于驱动表读取成本 驱动表行数 ×单次被驱动表查询成本。所以哪怕单次被驱动表的查询很快只要驱动表行数太大总成本照样爆炸。这也是为什么嵌套循环特别忌讳“大表驱动大表”——100万行的驱动表哪怕被驱动表走主键索引也要执行100万次索引查找再怎么快也快不到哪去。嵌套循环适合什么场景我用下来最典型的就两种两表连接其中一张表经过过滤后返回的行数很少比如几百行甚至几十行另一张表非常大但连接列有索引或主键。OLTP系统中需要快速返回少量数据的场景比如按订单号查订单明细、按用户ID查最近订单。2.2 嵌套循环的关键参数与HintOracle中控制嵌套循环的Hint是USE_NL。比如想让t1作为驱动表去驱动t2可以写成SELECT /* LEADING(t1) USE_NL(t2) */ * FROM t1, t2 WHERE t1.id t2.t1_id;这里LEADING(t1)指定驱动顺序USE_NL(t2)指定t2作为被驱动表时使用嵌套循环。需要注意的是USE_NL只指定了连接方式驱动顺序还需要配合LEADING或者依靠优化器自行判断。两个Hint配合使用才能既锁定连接顺序又锁定连接方式。还有一个相关参数值得留意OPTIMIZER_INDEX_COST_ADJ。这个参数默认100表示优化器认为索引扫描的成本和全表扫描相当。如果你希望优化器更倾向于使用索引、从而更倾向于选择嵌套循环可以把这个参数调低。比如OLTP系统中设置为10到25优化器就会更“偏爱”索引路径嵌套循环被选中的概率也会随之上升。这个参数我在实践中调过多次效果非常明显但要注意它影响的是整个数据库所有SQL最好在会话级别测试确认后再考虑系统级别修改。2.3 实战中判断嵌套循环是否健康的方法判断一个嵌套循环是否健康我一般看三个指标。第一是驱动表的实际返回行数这个可以通过执行计划的E-Rows和实际A-Rows对比来确认第二个是被驱动表的访问方式理想状态是走INDEX UNIQUE SCAN或INDEX RANGE SCAN如果看到被驱动表在走全表扫描那这个嵌套循环大概率有问题第三个是BUFFERS逻辑读的变化趋势嵌套循环的物理读可以不高但逻辑读如果跟着驱动表行数线性增长就得警惕了。我遇到过一个经典场景一个报表SQL嵌套循环被驱动表明明有索引但执行计划里显示全表扫描。排查后发现是因为SQL里连接列上做了函数处理导致索引失效。改成t1.create_time TRUNC(SYSDATE)而不是TRUNC(t1.create_time) TRUNC(SYSDATE)之后索引生效被驱动表的全表扫描变成了索引范围扫描SQL性能立刻恢复。这里给新手一个提醒索引列上做函数运算、隐式类型转换、或者使用LIKE %xxx都会导致索引失效嵌套循环直接退化。3. 哈希连接大表等值连接的主力军哈希连接是现代数据库处理大数据量连接的核心武器。它的核心思想是先选一张表作为构建表build table把这张表的连接列值经过哈希函数计算后放入内存中的哈希表然后扫描另一张探测表probe table对每一行的连接列值计算哈希值在哈希表中查找匹配项。哈希值相同、内容也相同的行就满足了连接条件。3.1 哈希连接为什么对大表更友好哈希连接最大的优势在于它只需要对构建表做一次全表扫描来建立哈希表然后探测表也只需要扫描一次。两张表的处理次数都是“1”而不是嵌套循环中驱动表有多少行就处理被驱动表多少次。所以数据量越大哈希连接相对于嵌套循环的优势就越明显。这里有一个细节值得深入理解哈希表是要占用内存的这部分内存来自PGAProgram Global Area中的WORKAREA_SIZE_POLICY所管理的排序区。如果构建表太大放不进内存Oracle会使用多趟multi-pass哈希连接把哈希表的一部分写到临时表空间后续再从临时段读回来。一旦落盘性能下降非常严重。如果你发现一个哈希连接SQL的direct tempfile write等待事件特别多基本可以断定构建表超出PGA了。哈希连接还有一个隐性优势它不像嵌套循环那样强烈依赖索引。两张没有任何索引的大表做等值连接哈希连接照样能高效处理。嵌套循环如果没有索引支撑就会退化成全表扫描而哈希连接即使全表扫描也只各扫一次。这就是为什么DW/BI系统里哈希连接是绝对主流——数据仓库的表普遍很大而且无法为所有查询组合都建索引。3.2 哈希连接的内存控制与Hint控制哈希连接的Hint是USE_HASH同样配合LEADING可以指定构建表和探测表SELECT /* LEADING(t1) USE_HASH(t2) */ * FROM t1, t2 WHERE t1.id t2.t1_id;默认情况下优化器会把LEADING指定的第一张表作为构建表也就是上面SQL里的t1。如果你明确知道哪张表更小把它放在构建表的位置更容易获得好性能。不过Oracle 11g之后的版本有HASH_JOIN_SWAP_JOIN_INPUTS之类的内部机制实际运行时可能会动态调整构建表所以我们没必要过于纠结谁做build表大致方向正确即可。哈希连接相关的关键参数主要有四个PGA_AGGREGATE_TARGETPGA总目标大小直接影响哈希表能够使用的内存上限。WORKAREA_SIZE_POLICY设置为AUTO时Oracle根据PGA目标自动管理排序区和哈希区。HASH_AREA_SIZE仅当WORKAREA_SIZE_POLICY为MANUAL时生效手动指定哈希区大小。_HASH_JOIN_ENABLED隐藏参数设置为FALSE可以全局禁用哈希连接只在极端情况下使用不建议生产环境轻易尝试。PGA内存我是这样把控的对于以OLTP为主的系统PGA_AGGREGATE_TARGET通常设置为物理内存的10%到20%就够用对于以报表、批量任务为主的数据仓库系统可以适当增大到30%以上但必须监控V$PGASTAT中PROCESS_MEMORY和ESTD_OVERFLOW_COUNT确保没有大量进程的内存使用超出PGA目标。我见过不少数据仓库系统因为PGA设置过小哈希连接频繁落盘I/O直接被打满整个数据库响应都跟着变慢。3.3 哈希连接的两大选择疑难点哈希连接有一个容易让人困惑的地方是并行度。并行执行时每个并行进程都会建立自己独立的哈希表。如果并行度设置过高每个进程可用的内存会被稀释反而容易导致哈希表落盘。我在并行度调优时的一般原则是从DOP4起步对比资源消耗和响应时间逐步摸索出最合适的值。并行度不是越大越好尤其是IO瓶颈的系统过高的并行度反而制造更多竞争。另一个疑难点是等值连接条件。哈希连接要求连接条件必须是等值关系比如t1.id t2.t1_id。对于范围条件如t1.value BETWEEN t2.low AND t2.high或者不等值条件如t1.value t2.value哈希连接无能为力Oracle会转而考虑排序合并连接。这一点在写SQL时就要有预判如果你要连接的范围条件就不要指望哈希连接能发挥作用应该重点检查排序合并连接的执行计划是否合理。4. 排序合并连接非等值连接场景下的可靠方案排序合并连接的执行过程分为两个阶段。第一阶段分别对两张表的连接列进行排序第二阶段从两边的排序结果中从头开始扫描像拉链一样逐个比较连接列的值匹配的返回。正因为两边都已经有序归并过程本身是线性的复杂度为O(NM)。4.1 为什么有了哈希连接还需要排序合并很多人会有疑问既然哈希连接那么快为什么还需要排序合并连接答案是哈希连接只能处理等值连接而排序合并连接没有这个限制。对于范围连接、不等值连接这类场景排序合并几乎是唯一高效的连接方式。举个例子假设你有两张表employee和salary_grade要查找每个员工对应的薪资等级条件是emp.salary BETWEEN grade.low AND grade.high。这是一个典型的范围连接哈希连接根本无法处理。此时排序合并连接的做法是把employee表按salary排序把salary_grade表按low排序然后两个有序列表从头开始推进匹配高效地找到所有落在薪资区间内的等级。另一个排序合并连接的优势是如果数据本身已经有序可以跳过排序阶段直接进行归并。比如连接列是主键、索引组织表IOT的连接列或者查询里已经带了ORDER BY且使用的是有序索引。这种情况下排序合并连接的成本会大幅下降可能比哈希连接更划算。不过在实际执行计划中SORT MERGE JOIN下面通常还是能看到两个SORT操作因为优化器不能保证基础数据一定满足排序要求除非有确切的信息表明排序可以省略。4.2 何时选择排序合并连接成本与场景平衡优化器选择排序合并连接通常基于以下判断两边表数据量都比较大、连接条件非等值、或者两个表的数据源经过预处理后已经有序。在等值连接场景下哈希连接通常比排序合并成本更低因为哈希连接只需要对一张表构建哈希表而排序合并需要同时排序两张表排序的额外开销可不小。排序合并连接的成本大头是排序。如果数据量很大排序无法在内存中完成就需要写临时表空间。这个情况和哈希连接落盘类似都会产生大量的direct path write temp和direct path read temp等待事件。所以排序合并连接的调优重点同样是确保PGA/排序区足够大尽可能让排序在内存中完成。控制排序合并连接的Hint是USE_MERGE用法SELECT /* USE_MERGE(t1 t2) */ * FROM t1, t2 WHERE t1.value BETWEEN t2.low AND t2.high;还有一个经常被忽略的参数是SORT_AREA_SIZE当WORKAREA_SIZE_POLICYMANUAL时它控制排序区大小。但现在的系统大多使用自动内存管理我更推荐关注PGA总体设置而不是手动调单个排序区。4.3 排序合并连接的典型问题排序过度我在实际优化中遇到过好几个排序合并“变慢”的案例根因不是归并本身慢而是排序阶段太慢。原因主要有三类第一排序列上缺少索引导致每次都要完整排序。这种情况如果SQL反复执行可以考虑在连接列上建索引让排序阶段变成索引扫描直接省掉SORT操作。第二数据分布严重倾斜。比如连接列里某个值占了80%以上的数据排序阶段虽然能完成但归并阶段会出现大量重复比较效率低下。这时候可以考虑用USE_HASH强制走哈希连接并配合LEADING来规避倾斜问题。第三查询返回了大量非必要的列。注意排序合并连接对连接列排序如果你SELECT *那排序区不仅要存连接列还要存所有列的副本内存压力陡增。一个很好的优化习惯是只查询需要的列不要无脑SELECT *。这不仅是网络传输上的节约对排序区也是实在的减压。5. 笛卡尔连接性能事故的高发地带笛卡尔连接是所有连接方式里最特殊、也最需要警惕的一种。它的特征是两表之间没有任何连接条件或者连接条件因为某种原因没有生效。结果就是左边表的每一行都和右边表的每一行组合一次。两张表各1000行笛卡尔连接的结果就是100万行如果两张表各100万行结果就是1万亿行——这种SQL基本能把数据库拖垮。5.1 笛卡尔连接在什么情况下出现虽然听起来很荒唐但笛卡尔连接在生产环境的出现频率并不低。我总结了几种常见成因开发人员写SQL时忘记了WHERE条件中的连接条件。多个表连接时某些表之间确实没有直接关联条件优化器只好先做笛卡尔积再后续过滤。使用CROSS JOIN显式指定的笛卡尔连接。视图展开view merge之后连接条件丢失。比如视图内部表和外层表之间没有连接条件关联上。使用WITH子句CTE时临时结果集在主查询中缺少连接条件。执行计划里出现MERGE JOIN CARTESIAN或CARTESIAN关键字时基本就可以认定是笛卡尔连接了。还有一种情况是高并发下执行计划里出现BUFFER SORT配合CARTESIAN多见于数据仓库里用维表做“放大”操作。这种属于有意使用但如果计算量大、又没控制好维表大小也会瞬间消耗大量临时空间。5.2 如何快速定位笛卡尔连接的元凶定位笛卡尔连接的方法不是去看执行计划就算了而是要回到SQL本身去分析表之间的关联关系。我有一次优化一个跑了40分钟的报表SQL执行计划里的笛卡尔连接一眼就能看到但问题是三张表都有连接条件为什么还是笛卡尔了逐层排查后发现问题出在其中一个UNION ALL分支里某张表在该分支中被引用但该分支只写了过滤条件、漏写了连接条件。Oracle并不会检查所有分支的关联是否完整你漏写了它就直接按笛卡尔执行。所以排查笛卡尔连接时不要只看主查询所有子查询、所有UNION ALL分支都要过一遍。另一个快速手段是用DBMS_XPLAN.DISPLAY_CURSOR抓取真实的执行计划配合GATHER_PLAN_STATISTICS提示查看每一行的A-Rows。如果某一步的A-Rows是百万级别甚至千万级别而它的E-Rows只有几百说明优化器的估算与实际情况严重不符这个步骤往往就是性能问题的核心。5.3 笛卡尔连接的唯一合法用途笛卡尔连接并不总是坏事。有一个合法的场景是我经常用到的用一个只有几行的小表去“复制”数据。比如有一个数字表nums包含1到10要和一张员工表做笛卡尔连接为每个员工生成10条记录这在生成测试数据或者维度展开时非常有效。SELECT e.employee_id, n.column_value AS seq_no FROM employees e CROSS JOIN (SELECT LEVEL AS column_value FROM dual CONNECT BY LEVEL 10) n;但即使是这种合法场景也要严格控制小表的规模。小表超过几千行时笛卡尔积的结果集就会变得可观。在正式环境里我会尽量避免显示使用CROSS JOIN而是用更清晰的方式描述“放大”逻辑减少后续维护者在阅读SQL时踩坑的概率。6. 连接顺序连接方法之外的另一半战场连接方法讲完了连接顺序同样重要。同一个SQL三张表连接顺序不同可能带来数量级的性能差异。用一个例子来说明表A有1000行表B有100万行表C有50行。如果先连接A和B可能产生50万行的中间结果再和C连接但如果先连接A和C中间结果可能只有几百行最终性能天差地别。优化器的职责就是评估所有可能的连接顺序选一个总成本最低的。6.1 优化器如何判断连接顺序优化器在决定连接顺序时依赖的是表、索引、列的统计信息。统计信息里最重要的是三块表的行数NUM_ROWS、列的基数NUM_DISTINCT、以及列的直方图HISTOGRAM。通过这些信息优化器可以估算每个过滤条件筛掉多少行进而估算每一步连接之后的结果集大小。但统计信息不是永远准确的。如果表数据量发生了大幅变化却没有重新收集统计信息优化器就会基于过时的数据做错误估算。典型的场景是一张表昨晚被批量插入了几百万行但统计信息还是三天前的行数统计只有几万行。优化器认为这是小表可能把它作为嵌套循环的驱动表实际一跑驱动表返回了几百万行SQL直接卡死。所以当SQL性能突降时我的习惯是先查一下统计信息的收集时间SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name IN (A, B, C);如果LAST_ANALYZED时间明显早于数据量的变化时间优先收集统计信息。收集的方式也不必一律用DBMS_STATS.GATHER_TABLE_STATS全量收集对于大表可以使用GRANULARITY AUTO配合采样比例。6.2 强制指定连接顺序与连接方法的Hint组合当统计信息没问题、但优化器依然选出不合理计划时通常是因为成本估算中的某些细节有偏差比如索引的选择性估算不准。这种时候就需要用Hint强制干预。最常用的组合就是LEADING加USE_NL、USE_HASH或USE_MERGE。SELECT /* LEADING(t1 t3 t2) USE_NL(t2) USE_HASH(t3) */ * FROM t1, t2, t3 WHERE t1.id t2.t1_id AND t3.code t1.code;上面这个Hint指定了连接顺序是t1 → t3 → t2并且t3使用哈希连接t2使用嵌套循环。要注意的是Hint不是写了就一定生效。如果Hint指定的顺序和方式产生了错误的连接条件或者受到了OUTLINE、SQL_PROFILE、SPMSQL Plan Management的干扰优化器可能忽略Hint。遇到Hint不生效时先检查该SQL是否被SPM固定了执行计划这是生产环境里最常见的Hint失效原因。6.3 通过交换连接顺序解决慢SQL的实操记录讲一个我实际处理过的案例。一个有6张表连接的报表SQL原先执行计划先连接两张最大的订单表中间结果集有800万行后面每一步都在处理这个庞大的中间结果总耗时23分钟。分析后发现SQL里有两个维表只有几千行其中一张维表可以先行过滤过滤后只剩几十行。如果先连接这张过滤后的维表和另一张维表中间结果最多几千行再和大表连接就轻松很多。我用LEADING强制调整了连接顺序SELECT /* LEADING(d1 d2 o1 o2 o3 o4) USE_NL(d2) USE_HASH(o1 o2 o3 o4) */ ...执行计划调整后SQL耗时从23分钟降到了40秒。整个过程没有改一行业务逻辑只是调整了连接顺序和连接方式。这再次验证了一个观点连接顺序和连接方法的选择往往比SQL写法本身对性能的影响更大。7. 常见问题与排查技巧实录连接方法的调优过程中我积累了不少问题和排查套路整理出来给各位参考。7.1 为什么执行计划里明明是哈希连接实际性能反而比嵌套循环还差这种情况通常出现在构建表非常大导致哈希表被写入临时表空间。虽然执行计划显示HASH JOIN但实际执行中因为频繁写临时段产生了大量I/O。排查方法是看执行计划的A-Rows和A-Time或者查询V$ACTIVE_SESSION_HISTORY里与direct tempfile write相关的等待。如果确认是哈希连接落盘优先考虑重新收集统计信息让优化器选择更合适的连接方式手动指定USE_NL让驱动表和被驱动表走索引或者增大PGA_AGGREGATE_TARGET。我一般会先尝试重新收集统计信息因为很多哈希连接落盘问题根源是统计信息错误导致优化器误判了构建表的大小。7.2 为什么嵌套循环没走索引全表扫描导致性能退化执行计划显示NESTED LOOPS但被驱动表是全表扫描时大概率是被驱动表的连接列索引失效。常见的索引失效原因包括在连接列上使用了函数、隐式类型转换、LIKE模糊匹配以通配符开头、以及连接列上存在空值。检查办法很简单看执行计划中被驱动表那一步的Access Predicates。如果显示的是FILTER而不是ACCESS说明条件没有直接用于索引定位。这种问题优先考虑修改SQL写法让连接列不参与任何运算。另外也可以用EXPLAIN PLAN FOR单独查看被驱动表的访问路径缩小排查范围。7.3 排序合并连接和哈希连接同一个SQL执行计划不稳定有些SQL今天执行计划是HASH JOIN明天变成SORT MERGE JOIN性能忽好忽坏。这通常有两个原因一是统计信息更新后优化器的成本估算发生了变化二是绑定变量值对选择性有较大影响而直方图无法覆盖所有情况。比如绑定变量传入的值范围很大时优化器只能按照平均选择性估算一旦实际传入的是一个少数值执行计划可能就不是最优的。对这种稳定性问题不走强制改SQL的路子。可用手段有三个SQL Plan ManagementSPM固定已知最优的计划SQL Profile稳定执行计划或者直接使用Hint改写并创建OUTLINE。我特别推荐SPM因为它能“好计划进坏计划拦”比简单地固定Outline更安全。7.4 笛卡尔连接导致临时表空间爆满的处理实录有次接到一个紧急工单系统告警临时表空间使用率接近100%。登录数据库查DBA_SEGMENTS发现一个临时段暴涨到200多GB。通过V$SESSION_LONGOPS找到了正在执行的SQL执行计划里果然出现了CARTESIAN连接。原来是一张表从几十万行涨到了上千万行日常维护脚本里漏了连接条件的小表被扩大后笛卡尔积失控。紧急处理办法是先杀掉这个会话释放临时段然后马上修改SQL。这个案例给我的教训是凡是涉及表连接的生产SQL尤其是批量任务上线前必须验证执行计划并且监控周期性的执行计划变化。大表数据量突变时优先检查相关SQL的执行计划是否产生笛卡尔连接。7.5 连接方法相关的常见问题速查表问题现象可能原因优先排查方向嵌套循环被驱动表全表扫描连接列索引失效、索引缺失检查Access Predicates、连接列数据格式哈希连接性能差、大量I/O哈希表落盘、PGA不足V$PGASTAT、direct tempfile write等待排序合并连接慢排序列无索引、数据倾斜、SELECT过多列连接列索引、检查排序区大小执行计划出现CARTESIAN漏写连接条件、视图展开丢失条件检查所有表关联条件、UNION ALL分支同一个SQL计划不稳定统计信息过期、绑定变量峰值选择性问题重新收集统计信息、SPM固定计划Hint不生效SPM固定计划、SQL Profile干扰检查DBA_SQL_PLAN_BASELINES、DBA_SQL_PROFILES7.6 判断连接方法是否需要干预的三个信号日常监控中我不建议看到执行计划不符合预期就立刻加Hint。除非出现以下三个信号否则优先相信优化器信号一SQL本身跑得慢且执行计划中存在明显的全表扫描或笛卡尔连接这个需要干预。信号二执行计划的E-Rows与实际A-Rows偏差超过一个数量级说明优化器估算失真此时先去校正统计信息或直方图。信号三同一SQL在不同数据量下性能波动剧烈影响到了业务。这种情况才考虑用Hint或SPM来固定执行计划。如果只是执行计划长得好实际响应时间也正常那么即使计划不是理论上最优的也没有必要强行改动。过度调优同样是调优大忌因为固定计划会降低系统对未来数据变化的适应能力。8. 经验笔记从连接方法看SQL调优的整体思路连接方法这个主题写到最后我想说点自己的整体感想。很多刚接触SQL调优的人一上来就研究各种Hint和参数这其实是本末倒置。连接方法的正确选择本质上依赖于优化器对数据分布和访问路径的准确判断。统计信息准确优化器自然大概率选出合理方案统计信息失真你再怎么调Hint也是修修补补。我在实际项目里SQL调优的顺序一般是这样先用真实数据跑一遍看执行计划和实际行数然后检查统计信息是否健康不健康就重新收集再检查执行计划里有没有全表扫描、笛卡尔连接这类明显的坑最后才考虑用Hint干预连接方法和顺序。这个顺序走下来80%的慢SQL都能在不改动业务逻辑的前提下解决。还有一个经常被忽视的点连接方法的调优要和索引设计、SQL写法协同考虑。嵌套循环依赖索引哈希连接对索引要求低但依赖PGA排序合并依赖排序区。你想让优化器选到合适的连接方法就得让这些基础资源各就各位。这就像一个团队协作连接方法只是最终的结果支撑它的是统计信息、内存、I/O、索引等一套体系。这套东西看着知识点多但只要你在真实环境里多处理几个慢SQL案例就会慢慢变成肌肉记忆。遇到慢SQL时先想着看懂执行计划里的连接方式再想为什么选这种方式最后决定要不要干预——这条路走熟了你也能成为别人眼中“看执行计划就知道问题在哪”的人。
Oracle SQL优化：四种表连接方式原理与调优实战
自己在做SQL优化的时候最头疼的往往不是语法问题而是明明表结构没问题、索引也建了SQL跑起来就是慢。仔细一看执行计划问题多半出在表连接方式上。Oracle里的连接方法Join Methods一共就那么几种但选错一种性能可能是数量级的差距。这篇Part 4-2就是把官方SQL调优指南里关于连接方法的部分完整拆开揉碎讲清楚结合实测经验把嵌套循环、哈希连接、排序合并、笛卡尔连接这四种方式的原理、适用场景、控制手段一次说透。不管你是DBA、后端开发还是数据分析师只要写SQL、看执行计划这篇都值得完整读一遍。读完你至少能做三件事看懂执行计划里的连接方式选得对不对、知道为什么优化器选了这个连接方式、在必要时通过Hint和参数干预优化器的选择。1. 从执行计划看懂连接方法一张表看懂四种方式Oracle执行计划里表连接相关的操作符主要有四种NESTED LOOPS、HASH JOIN、SORT MERGE JOIN、MERGE JOIN CARTESIAN笛卡尔连接在执行计划里有时直接显示为CARTESIAN或MERGE JOIN CARTESIAN。很多初学者一看到执行计划里有多张表第一反应是看有没有走索引其实连接方法的优先级不亚于索引选择。选错了连接方法即使每张表都走了索引整体性能也可能非常糟糕。四种连接方式的核心差异用一个表格就能看明白连接方式实现原理内存/临时空间消耗适用场景常见执行计划关键字嵌套循环连接外层表逐行驱动内层表按连接条件查找匹配行内存消耗低小表驱动大表、内层表连接列有索引、返回少量行NESTED LOOPS哈希连接将小表构建哈希表大表逐行探测内存/PGA消耗较大可能落盘两张大表等值连接、无索引或索引选择性差、返回大量行HASH JOIN排序合并连接两边排序后做归并匹配排序区消耗大可能落盘非等值连接、、BETWEEN、数据已排序、旧版本优化器场景SORT MERGE JOIN笛卡尔连接无连接条件或连接条件失效两表全量交叉视数据量而定结果集巨大几乎都是性能事故极少数场景如小维表扩容才用MERGE JOIN CARTESIAN这四种方式不是Oracle随意选的优化器会基于成本Cost来决定。理解这个你就知道为什么有时候明明可以走嵌套循环优化器却选择了哈希连接——因为它算了账发现嵌套循环的成本更高。1.1 连接方法的本质数据是怎么“配对”的换个生活化的角度来理解连接方法的本质。嵌套循环是“一个萝卜一个坑地找”外层每取一行内层就拿着这个值去索引里查一次就像你手里有一串钥匙去一栋楼里挨个门试。哈希连接是先把一批钥匙按锁芯分类挂到墙上然后拿着另一批钥匙一一对应去碰适合大批量配对。排序合并则是先把两边都按同一个规则排好队再两队人从队头依次对比谁小谁往前走。理解了这个本质再看执行计划就通透了。比如执行计划里出现NESTED LOOPS你要关心的第一个问题是外层表是不是真的小驱动顺序到底对不对出现HASH JOIN你要关心的问题就变成了两个表的统计信息是否准确PGA是否够用有没有在临时表空间里落盘我在实际排查慢SQL时遇到过好几次这样的情况一个大SQL执行计划里显示HASH JOIN但等我检查完统计信息发现这张表的行数统计已经严重失真优化器根据错误统计信息选择了哈希连接实际数据量根本不需要那么重的连接方式。刷新统计信息后执行计划立刻变成了更合理的嵌套循环SQL从20秒跑到了0.8秒。这就引出了连接方法调优的核心逻辑不是你想用哪种就强制用哪种而是要让优化器掌握真实信息做出正确判断。2. 嵌套循环连接小表驱动大表的经典套路嵌套循环连接是最直观、最“朴素”的连接方式。它的执行逻辑是这样的优化器选择一张表作为驱动表outer table一张表作为被驱动表inner table从驱动表读取一行然后根据连接条件在被驱动表中查找匹配的行。查到了就返回一行结果再继续读驱动表的下一行如此往复直到驱动表所有行都被处理完。2.1 嵌套循环的内部机制与成本模型这个“一行一行查”的机制决定了它的一个核心特点访问被驱动表的次数等于驱动表返回的行数。如果驱动表有100行被驱动表就会被访问100次。所以嵌套循环的性能很大程度上取决于两个因素驱动表够不够小被驱动表连接列上的索引够不够高效。这个连接方式的成本可以用一个简单模型估算总成本约等于驱动表读取成本 驱动表行数 ×单次被驱动表查询成本。所以哪怕单次被驱动表的查询很快只要驱动表行数太大总成本照样爆炸。这也是为什么嵌套循环特别忌讳“大表驱动大表”——100万行的驱动表哪怕被驱动表走主键索引也要执行100万次索引查找再怎么快也快不到哪去。嵌套循环适合什么场景我用下来最典型的就两种两表连接其中一张表经过过滤后返回的行数很少比如几百行甚至几十行另一张表非常大但连接列有索引或主键。OLTP系统中需要快速返回少量数据的场景比如按订单号查订单明细、按用户ID查最近订单。2.2 嵌套循环的关键参数与HintOracle中控制嵌套循环的Hint是USE_NL。比如想让t1作为驱动表去驱动t2可以写成SELECT /* LEADING(t1) USE_NL(t2) */ * FROM t1, t2 WHERE t1.id t2.t1_id;这里LEADING(t1)指定驱动顺序USE_NL(t2)指定t2作为被驱动表时使用嵌套循环。需要注意的是USE_NL只指定了连接方式驱动顺序还需要配合LEADING或者依靠优化器自行判断。两个Hint配合使用才能既锁定连接顺序又锁定连接方式。还有一个相关参数值得留意OPTIMIZER_INDEX_COST_ADJ。这个参数默认100表示优化器认为索引扫描的成本和全表扫描相当。如果你希望优化器更倾向于使用索引、从而更倾向于选择嵌套循环可以把这个参数调低。比如OLTP系统中设置为10到25优化器就会更“偏爱”索引路径嵌套循环被选中的概率也会随之上升。这个参数我在实践中调过多次效果非常明显但要注意它影响的是整个数据库所有SQL最好在会话级别测试确认后再考虑系统级别修改。2.3 实战中判断嵌套循环是否健康的方法判断一个嵌套循环是否健康我一般看三个指标。第一是驱动表的实际返回行数这个可以通过执行计划的E-Rows和实际A-Rows对比来确认第二个是被驱动表的访问方式理想状态是走INDEX UNIQUE SCAN或INDEX RANGE SCAN如果看到被驱动表在走全表扫描那这个嵌套循环大概率有问题第三个是BUFFERS逻辑读的变化趋势嵌套循环的物理读可以不高但逻辑读如果跟着驱动表行数线性增长就得警惕了。我遇到过一个经典场景一个报表SQL嵌套循环被驱动表明明有索引但执行计划里显示全表扫描。排查后发现是因为SQL里连接列上做了函数处理导致索引失效。改成t1.create_time TRUNC(SYSDATE)而不是TRUNC(t1.create_time) TRUNC(SYSDATE)之后索引生效被驱动表的全表扫描变成了索引范围扫描SQL性能立刻恢复。这里给新手一个提醒索引列上做函数运算、隐式类型转换、或者使用LIKE %xxx都会导致索引失效嵌套循环直接退化。3. 哈希连接大表等值连接的主力军哈希连接是现代数据库处理大数据量连接的核心武器。它的核心思想是先选一张表作为构建表build table把这张表的连接列值经过哈希函数计算后放入内存中的哈希表然后扫描另一张探测表probe table对每一行的连接列值计算哈希值在哈希表中查找匹配项。哈希值相同、内容也相同的行就满足了连接条件。3.1 哈希连接为什么对大表更友好哈希连接最大的优势在于它只需要对构建表做一次全表扫描来建立哈希表然后探测表也只需要扫描一次。两张表的处理次数都是“1”而不是嵌套循环中驱动表有多少行就处理被驱动表多少次。所以数据量越大哈希连接相对于嵌套循环的优势就越明显。这里有一个细节值得深入理解哈希表是要占用内存的这部分内存来自PGAProgram Global Area中的WORKAREA_SIZE_POLICY所管理的排序区。如果构建表太大放不进内存Oracle会使用多趟multi-pass哈希连接把哈希表的一部分写到临时表空间后续再从临时段读回来。一旦落盘性能下降非常严重。如果你发现一个哈希连接SQL的direct tempfile write等待事件特别多基本可以断定构建表超出PGA了。哈希连接还有一个隐性优势它不像嵌套循环那样强烈依赖索引。两张没有任何索引的大表做等值连接哈希连接照样能高效处理。嵌套循环如果没有索引支撑就会退化成全表扫描而哈希连接即使全表扫描也只各扫一次。这就是为什么DW/BI系统里哈希连接是绝对主流——数据仓库的表普遍很大而且无法为所有查询组合都建索引。3.2 哈希连接的内存控制与Hint控制哈希连接的Hint是USE_HASH同样配合LEADING可以指定构建表和探测表SELECT /* LEADING(t1) USE_HASH(t2) */ * FROM t1, t2 WHERE t1.id t2.t1_id;默认情况下优化器会把LEADING指定的第一张表作为构建表也就是上面SQL里的t1。如果你明确知道哪张表更小把它放在构建表的位置更容易获得好性能。不过Oracle 11g之后的版本有HASH_JOIN_SWAP_JOIN_INPUTS之类的内部机制实际运行时可能会动态调整构建表所以我们没必要过于纠结谁做build表大致方向正确即可。哈希连接相关的关键参数主要有四个PGA_AGGREGATE_TARGETPGA总目标大小直接影响哈希表能够使用的内存上限。WORKAREA_SIZE_POLICY设置为AUTO时Oracle根据PGA目标自动管理排序区和哈希区。HASH_AREA_SIZE仅当WORKAREA_SIZE_POLICY为MANUAL时生效手动指定哈希区大小。_HASH_JOIN_ENABLED隐藏参数设置为FALSE可以全局禁用哈希连接只在极端情况下使用不建议生产环境轻易尝试。PGA内存我是这样把控的对于以OLTP为主的系统PGA_AGGREGATE_TARGET通常设置为物理内存的10%到20%就够用对于以报表、批量任务为主的数据仓库系统可以适当增大到30%以上但必须监控V$PGASTAT中PROCESS_MEMORY和ESTD_OVERFLOW_COUNT确保没有大量进程的内存使用超出PGA目标。我见过不少数据仓库系统因为PGA设置过小哈希连接频繁落盘I/O直接被打满整个数据库响应都跟着变慢。3.3 哈希连接的两大选择疑难点哈希连接有一个容易让人困惑的地方是并行度。并行执行时每个并行进程都会建立自己独立的哈希表。如果并行度设置过高每个进程可用的内存会被稀释反而容易导致哈希表落盘。我在并行度调优时的一般原则是从DOP4起步对比资源消耗和响应时间逐步摸索出最合适的值。并行度不是越大越好尤其是IO瓶颈的系统过高的并行度反而制造更多竞争。另一个疑难点是等值连接条件。哈希连接要求连接条件必须是等值关系比如t1.id t2.t1_id。对于范围条件如t1.value BETWEEN t2.low AND t2.high或者不等值条件如t1.value t2.value哈希连接无能为力Oracle会转而考虑排序合并连接。这一点在写SQL时就要有预判如果你要连接的范围条件就不要指望哈希连接能发挥作用应该重点检查排序合并连接的执行计划是否合理。4. 排序合并连接非等值连接场景下的可靠方案排序合并连接的执行过程分为两个阶段。第一阶段分别对两张表的连接列进行排序第二阶段从两边的排序结果中从头开始扫描像拉链一样逐个比较连接列的值匹配的返回。正因为两边都已经有序归并过程本身是线性的复杂度为O(NM)。4.1 为什么有了哈希连接还需要排序合并很多人会有疑问既然哈希连接那么快为什么还需要排序合并连接答案是哈希连接只能处理等值连接而排序合并连接没有这个限制。对于范围连接、不等值连接这类场景排序合并几乎是唯一高效的连接方式。举个例子假设你有两张表employee和salary_grade要查找每个员工对应的薪资等级条件是emp.salary BETWEEN grade.low AND grade.high。这是一个典型的范围连接哈希连接根本无法处理。此时排序合并连接的做法是把employee表按salary排序把salary_grade表按low排序然后两个有序列表从头开始推进匹配高效地找到所有落在薪资区间内的等级。另一个排序合并连接的优势是如果数据本身已经有序可以跳过排序阶段直接进行归并。比如连接列是主键、索引组织表IOT的连接列或者查询里已经带了ORDER BY且使用的是有序索引。这种情况下排序合并连接的成本会大幅下降可能比哈希连接更划算。不过在实际执行计划中SORT MERGE JOIN下面通常还是能看到两个SORT操作因为优化器不能保证基础数据一定满足排序要求除非有确切的信息表明排序可以省略。4.2 何时选择排序合并连接成本与场景平衡优化器选择排序合并连接通常基于以下判断两边表数据量都比较大、连接条件非等值、或者两个表的数据源经过预处理后已经有序。在等值连接场景下哈希连接通常比排序合并成本更低因为哈希连接只需要对一张表构建哈希表而排序合并需要同时排序两张表排序的额外开销可不小。排序合并连接的成本大头是排序。如果数据量很大排序无法在内存中完成就需要写临时表空间。这个情况和哈希连接落盘类似都会产生大量的direct path write temp和direct path read temp等待事件。所以排序合并连接的调优重点同样是确保PGA/排序区足够大尽可能让排序在内存中完成。控制排序合并连接的Hint是USE_MERGE用法SELECT /* USE_MERGE(t1 t2) */ * FROM t1, t2 WHERE t1.value BETWEEN t2.low AND t2.high;还有一个经常被忽略的参数是SORT_AREA_SIZE当WORKAREA_SIZE_POLICYMANUAL时它控制排序区大小。但现在的系统大多使用自动内存管理我更推荐关注PGA总体设置而不是手动调单个排序区。4.3 排序合并连接的典型问题排序过度我在实际优化中遇到过好几个排序合并“变慢”的案例根因不是归并本身慢而是排序阶段太慢。原因主要有三类第一排序列上缺少索引导致每次都要完整排序。这种情况如果SQL反复执行可以考虑在连接列上建索引让排序阶段变成索引扫描直接省掉SORT操作。第二数据分布严重倾斜。比如连接列里某个值占了80%以上的数据排序阶段虽然能完成但归并阶段会出现大量重复比较效率低下。这时候可以考虑用USE_HASH强制走哈希连接并配合LEADING来规避倾斜问题。第三查询返回了大量非必要的列。注意排序合并连接对连接列排序如果你SELECT *那排序区不仅要存连接列还要存所有列的副本内存压力陡增。一个很好的优化习惯是只查询需要的列不要无脑SELECT *。这不仅是网络传输上的节约对排序区也是实在的减压。5. 笛卡尔连接性能事故的高发地带笛卡尔连接是所有连接方式里最特殊、也最需要警惕的一种。它的特征是两表之间没有任何连接条件或者连接条件因为某种原因没有生效。结果就是左边表的每一行都和右边表的每一行组合一次。两张表各1000行笛卡尔连接的结果就是100万行如果两张表各100万行结果就是1万亿行——这种SQL基本能把数据库拖垮。5.1 笛卡尔连接在什么情况下出现虽然听起来很荒唐但笛卡尔连接在生产环境的出现频率并不低。我总结了几种常见成因开发人员写SQL时忘记了WHERE条件中的连接条件。多个表连接时某些表之间确实没有直接关联条件优化器只好先做笛卡尔积再后续过滤。使用CROSS JOIN显式指定的笛卡尔连接。视图展开view merge之后连接条件丢失。比如视图内部表和外层表之间没有连接条件关联上。使用WITH子句CTE时临时结果集在主查询中缺少连接条件。执行计划里出现MERGE JOIN CARTESIAN或CARTESIAN关键字时基本就可以认定是笛卡尔连接了。还有一种情况是高并发下执行计划里出现BUFFER SORT配合CARTESIAN多见于数据仓库里用维表做“放大”操作。这种属于有意使用但如果计算量大、又没控制好维表大小也会瞬间消耗大量临时空间。5.2 如何快速定位笛卡尔连接的元凶定位笛卡尔连接的方法不是去看执行计划就算了而是要回到SQL本身去分析表之间的关联关系。我有一次优化一个跑了40分钟的报表SQL执行计划里的笛卡尔连接一眼就能看到但问题是三张表都有连接条件为什么还是笛卡尔了逐层排查后发现问题出在其中一个UNION ALL分支里某张表在该分支中被引用但该分支只写了过滤条件、漏写了连接条件。Oracle并不会检查所有分支的关联是否完整你漏写了它就直接按笛卡尔执行。所以排查笛卡尔连接时不要只看主查询所有子查询、所有UNION ALL分支都要过一遍。另一个快速手段是用DBMS_XPLAN.DISPLAY_CURSOR抓取真实的执行计划配合GATHER_PLAN_STATISTICS提示查看每一行的A-Rows。如果某一步的A-Rows是百万级别甚至千万级别而它的E-Rows只有几百说明优化器的估算与实际情况严重不符这个步骤往往就是性能问题的核心。5.3 笛卡尔连接的唯一合法用途笛卡尔连接并不总是坏事。有一个合法的场景是我经常用到的用一个只有几行的小表去“复制”数据。比如有一个数字表nums包含1到10要和一张员工表做笛卡尔连接为每个员工生成10条记录这在生成测试数据或者维度展开时非常有效。SELECT e.employee_id, n.column_value AS seq_no FROM employees e CROSS JOIN (SELECT LEVEL AS column_value FROM dual CONNECT BY LEVEL 10) n;但即使是这种合法场景也要严格控制小表的规模。小表超过几千行时笛卡尔积的结果集就会变得可观。在正式环境里我会尽量避免显示使用CROSS JOIN而是用更清晰的方式描述“放大”逻辑减少后续维护者在阅读SQL时踩坑的概率。6. 连接顺序连接方法之外的另一半战场连接方法讲完了连接顺序同样重要。同一个SQL三张表连接顺序不同可能带来数量级的性能差异。用一个例子来说明表A有1000行表B有100万行表C有50行。如果先连接A和B可能产生50万行的中间结果再和C连接但如果先连接A和C中间结果可能只有几百行最终性能天差地别。优化器的职责就是评估所有可能的连接顺序选一个总成本最低的。6.1 优化器如何判断连接顺序优化器在决定连接顺序时依赖的是表、索引、列的统计信息。统计信息里最重要的是三块表的行数NUM_ROWS、列的基数NUM_DISTINCT、以及列的直方图HISTOGRAM。通过这些信息优化器可以估算每个过滤条件筛掉多少行进而估算每一步连接之后的结果集大小。但统计信息不是永远准确的。如果表数据量发生了大幅变化却没有重新收集统计信息优化器就会基于过时的数据做错误估算。典型的场景是一张表昨晚被批量插入了几百万行但统计信息还是三天前的行数统计只有几万行。优化器认为这是小表可能把它作为嵌套循环的驱动表实际一跑驱动表返回了几百万行SQL直接卡死。所以当SQL性能突降时我的习惯是先查一下统计信息的收集时间SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name IN (A, B, C);如果LAST_ANALYZED时间明显早于数据量的变化时间优先收集统计信息。收集的方式也不必一律用DBMS_STATS.GATHER_TABLE_STATS全量收集对于大表可以使用GRANULARITY AUTO配合采样比例。6.2 强制指定连接顺序与连接方法的Hint组合当统计信息没问题、但优化器依然选出不合理计划时通常是因为成本估算中的某些细节有偏差比如索引的选择性估算不准。这种时候就需要用Hint强制干预。最常用的组合就是LEADING加USE_NL、USE_HASH或USE_MERGE。SELECT /* LEADING(t1 t3 t2) USE_NL(t2) USE_HASH(t3) */ * FROM t1, t2, t3 WHERE t1.id t2.t1_id AND t3.code t1.code;上面这个Hint指定了连接顺序是t1 → t3 → t2并且t3使用哈希连接t2使用嵌套循环。要注意的是Hint不是写了就一定生效。如果Hint指定的顺序和方式产生了错误的连接条件或者受到了OUTLINE、SQL_PROFILE、SPMSQL Plan Management的干扰优化器可能忽略Hint。遇到Hint不生效时先检查该SQL是否被SPM固定了执行计划这是生产环境里最常见的Hint失效原因。6.3 通过交换连接顺序解决慢SQL的实操记录讲一个我实际处理过的案例。一个有6张表连接的报表SQL原先执行计划先连接两张最大的订单表中间结果集有800万行后面每一步都在处理这个庞大的中间结果总耗时23分钟。分析后发现SQL里有两个维表只有几千行其中一张维表可以先行过滤过滤后只剩几十行。如果先连接这张过滤后的维表和另一张维表中间结果最多几千行再和大表连接就轻松很多。我用LEADING强制调整了连接顺序SELECT /* LEADING(d1 d2 o1 o2 o3 o4) USE_NL(d2) USE_HASH(o1 o2 o3 o4) */ ...执行计划调整后SQL耗时从23分钟降到了40秒。整个过程没有改一行业务逻辑只是调整了连接顺序和连接方式。这再次验证了一个观点连接顺序和连接方法的选择往往比SQL写法本身对性能的影响更大。7. 常见问题与排查技巧实录连接方法的调优过程中我积累了不少问题和排查套路整理出来给各位参考。7.1 为什么执行计划里明明是哈希连接实际性能反而比嵌套循环还差这种情况通常出现在构建表非常大导致哈希表被写入临时表空间。虽然执行计划显示HASH JOIN但实际执行中因为频繁写临时段产生了大量I/O。排查方法是看执行计划的A-Rows和A-Time或者查询V$ACTIVE_SESSION_HISTORY里与direct tempfile write相关的等待。如果确认是哈希连接落盘优先考虑重新收集统计信息让优化器选择更合适的连接方式手动指定USE_NL让驱动表和被驱动表走索引或者增大PGA_AGGREGATE_TARGET。我一般会先尝试重新收集统计信息因为很多哈希连接落盘问题根源是统计信息错误导致优化器误判了构建表的大小。7.2 为什么嵌套循环没走索引全表扫描导致性能退化执行计划显示NESTED LOOPS但被驱动表是全表扫描时大概率是被驱动表的连接列索引失效。常见的索引失效原因包括在连接列上使用了函数、隐式类型转换、LIKE模糊匹配以通配符开头、以及连接列上存在空值。检查办法很简单看执行计划中被驱动表那一步的Access Predicates。如果显示的是FILTER而不是ACCESS说明条件没有直接用于索引定位。这种问题优先考虑修改SQL写法让连接列不参与任何运算。另外也可以用EXPLAIN PLAN FOR单独查看被驱动表的访问路径缩小排查范围。7.3 排序合并连接和哈希连接同一个SQL执行计划不稳定有些SQL今天执行计划是HASH JOIN明天变成SORT MERGE JOIN性能忽好忽坏。这通常有两个原因一是统计信息更新后优化器的成本估算发生了变化二是绑定变量值对选择性有较大影响而直方图无法覆盖所有情况。比如绑定变量传入的值范围很大时优化器只能按照平均选择性估算一旦实际传入的是一个少数值执行计划可能就不是最优的。对这种稳定性问题不走强制改SQL的路子。可用手段有三个SQL Plan ManagementSPM固定已知最优的计划SQL Profile稳定执行计划或者直接使用Hint改写并创建OUTLINE。我特别推荐SPM因为它能“好计划进坏计划拦”比简单地固定Outline更安全。7.4 笛卡尔连接导致临时表空间爆满的处理实录有次接到一个紧急工单系统告警临时表空间使用率接近100%。登录数据库查DBA_SEGMENTS发现一个临时段暴涨到200多GB。通过V$SESSION_LONGOPS找到了正在执行的SQL执行计划里果然出现了CARTESIAN连接。原来是一张表从几十万行涨到了上千万行日常维护脚本里漏了连接条件的小表被扩大后笛卡尔积失控。紧急处理办法是先杀掉这个会话释放临时段然后马上修改SQL。这个案例给我的教训是凡是涉及表连接的生产SQL尤其是批量任务上线前必须验证执行计划并且监控周期性的执行计划变化。大表数据量突变时优先检查相关SQL的执行计划是否产生笛卡尔连接。7.5 连接方法相关的常见问题速查表问题现象可能原因优先排查方向嵌套循环被驱动表全表扫描连接列索引失效、索引缺失检查Access Predicates、连接列数据格式哈希连接性能差、大量I/O哈希表落盘、PGA不足V$PGASTAT、direct tempfile write等待排序合并连接慢排序列无索引、数据倾斜、SELECT过多列连接列索引、检查排序区大小执行计划出现CARTESIAN漏写连接条件、视图展开丢失条件检查所有表关联条件、UNION ALL分支同一个SQL计划不稳定统计信息过期、绑定变量峰值选择性问题重新收集统计信息、SPM固定计划Hint不生效SPM固定计划、SQL Profile干扰检查DBA_SQL_PLAN_BASELINES、DBA_SQL_PROFILES7.6 判断连接方法是否需要干预的三个信号日常监控中我不建议看到执行计划不符合预期就立刻加Hint。除非出现以下三个信号否则优先相信优化器信号一SQL本身跑得慢且执行计划中存在明显的全表扫描或笛卡尔连接这个需要干预。信号二执行计划的E-Rows与实际A-Rows偏差超过一个数量级说明优化器估算失真此时先去校正统计信息或直方图。信号三同一SQL在不同数据量下性能波动剧烈影响到了业务。这种情况才考虑用Hint或SPM来固定执行计划。如果只是执行计划长得好实际响应时间也正常那么即使计划不是理论上最优的也没有必要强行改动。过度调优同样是调优大忌因为固定计划会降低系统对未来数据变化的适应能力。8. 经验笔记从连接方法看SQL调优的整体思路连接方法这个主题写到最后我想说点自己的整体感想。很多刚接触SQL调优的人一上来就研究各种Hint和参数这其实是本末倒置。连接方法的正确选择本质上依赖于优化器对数据分布和访问路径的准确判断。统计信息准确优化器自然大概率选出合理方案统计信息失真你再怎么调Hint也是修修补补。我在实际项目里SQL调优的顺序一般是这样先用真实数据跑一遍看执行计划和实际行数然后检查统计信息是否健康不健康就重新收集再检查执行计划里有没有全表扫描、笛卡尔连接这类明显的坑最后才考虑用Hint干预连接方法和顺序。这个顺序走下来80%的慢SQL都能在不改动业务逻辑的前提下解决。还有一个经常被忽视的点连接方法的调优要和索引设计、SQL写法协同考虑。嵌套循环依赖索引哈希连接对索引要求低但依赖PGA排序合并依赖排序区。你想让优化器选到合适的连接方法就得让这些基础资源各就各位。这就像一个团队协作连接方法只是最终的结果支撑它的是统计信息、内存、I/O、索引等一套体系。这套东西看着知识点多但只要你在真实环境里多处理几个慢SQL案例就会慢慢变成肌肉记忆。遇到慢SQL时先想着看懂执行计划里的连接方式再想为什么选这种方式最后决定要不要干预——这条路走熟了你也能成为别人眼中“看执行计划就知道问题在哪”的人。
