简介面向数据库管理员和初、中级开发人员的Oracle SQL优化教程以查询提速与资源节流为主线系统讲解优化器模式选择与CBO启用、全表扫描与ROWID索引访问的适用场景、共享SQL语句避免重复解析等核心策略并补充FROM子句表顺序对多表连接效率的影响。文档指出采用CHOOSE优化器可能导致大表全表扫描推荐定期运行ANALYZE更新统计信息、设置OPTIMIZER_MODE同时说明共享池大小调整与绑定变量一致性的细节。整个资源以1个docx文档封装压缩包大小约26KB内容紧凑、要点明确既适合日常调优查阅也可作为学习笔记。目前已有57人学习对想快速理解CBO、索引访问路径及共享SQL优化实践的读者颇有参考价值。1. 优化器模式与访问路径为什么 CHOOSE 会带来全表扫描接手过 Oracle 生产库的人大概率都遇到过这种场景一条 SQL 在测试环境跑得飞快上了生产就变成全表扫描日志里db file sequential read和db file scattered read交替刷屏。这时候第一个要查的往往不是 SQL 本身而是优化器模式和对象的统计信息。Oracle 的优化器分成 RULE基于规则、COST基于成本和 CHOOSE选择性三种缺省情况下用的是 CHOOSE而 CHOOSE 的走向完全取决于表有没有被analyze过analyze 过就走 CBO否则自动退回 RULE。很多慢 SQL 的根源就是统计信息缺失导致优化器误判选了一条全表扫的路径。本文把这套优化器选型、表访问方式、共享 SQL 机制和 20 多条经典改写规则拆开讲透适合 DBA、后端开发和所有需要跟慢 SQL 打交道的人。2. 共享 SQL 与游标缓存绑定变量和 SGA 共享池的匹配规则2.1 共享池不是缓存了就能命中Oracle 执行一条 SQL 时内部要做解析parse、估算索引利用率、绑定变量、读取数据块等一系列动作。其中解析是最消耗 CPU 的环节之一。为了避免重复解析Oracle 会把第一次解析后的语句连同执行计划一起放进 SGA 的共享池shared pool后续如果遇到完全相同的 SQL就直接复用解析结果和执行路径跳过硬解析。这个机制听着简单实际命中条件极其苛刻。Oracle 对语句做的是字符级严格匹配连空格、换行、大小写都必须完全一致。下面三条语句在共享池看来是三个完全不同的对象SELECT * FROM EMP; SELECT * from EMP; Select * From Emp;除此之外共享还要求语句引用的对象完全相同。用户 A 通过私有同义词访问sal_limit用户 B 也通过私有同义词访问sal_limit表面写的 SQL 一样实际上指向的是不同对象依然无法共享。第三个条件是绑定变量名必须一致WHERE pin :blk1.pin和WHERE pin :blk1.ot_ind即使运行时传的值相同也不算同一条语句。注意共享池只对简单表提供缓存加速多表连接查询并不适用。所以并不是把共享池调大了所有 SQL 都能获益。2.2 用绑定变量代替字面量理解了匹配规则就知道生产环境里写 SQL 的第一原则是用绑定变量。下面这个例子是典型的反模式-- 每次执行都是硬解析 SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO 342; SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO 291;改成绑定变量后语句结构固定共享池只需要解析一次-- PL/SQL 中使用绑定变量 DECLARE CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO E_NO; V_EMP_NAME EMP.EMP_NAME%TYPE; V_SALARY EMP.SALARY%TYPE; V_GRADE EMP.GRADE%TYPE; BEGIN OPEN C1(342); FETCH C1 INTO V_EMP_NAME, V_SALARY, V_GRADE; CLOSE C1; OPEN C1(291); FETCH C1 INTO V_EMP_NAME, V_SALARY, V_GRADE; CLOSE C1; END;Java 侧的PreparedStatement也是同样的道理?占位符能让不同参数值复用同一条解析结果。这里的关键在于绑定变量换来的是共享池命中率但也会让优化器失去对具体值的感知可能影响字段直方图的选择性判断。所以对于数据分布极不均匀的列是绑定变量还是字面量需要根据实际 selectivity 权衡不能一概而论。2.3 ARRAYSIZE 与会话级参数SQLPlus、SQLForms 和 Pro*C 里可以重新设置ARRAYSIZE参数它决定了每次数据库访问检索的数据行数。把这个值调大能显著减少客户端与服务器之间的往返次数建议设为 200 左右。共享池本身的大小通过init.ora中的参数控制池子越大能保留的语句越多但过大也会挤占内存通常需要结合shared_pool_size和library cache hit ratio一起观察而不是盲目调大。共享 SQL 这块的核心判断维度可以整理成下面这个表条件维度要求典型反例字符级比较包括空格、换行、大小写完全一致SELECT * FROM EMP与SELECT * from EMP对象级比较两个语句指向的物理对象相同私有同义词 vs 基表不同 schema 下的同名表绑定变量名变量名必须一致:blk1.pin与:blk1.ot_ind名字不同不共享3. FROM 与 WHERE 的解析顺序RBO 下的驱动表选择法则3.1 FROM 子句从右往左读Oracle 的解析器按从右到左的顺序处理 FROM 子句中的表名这个行为在基于规则的优化器RBO下表现得尤其明显。写在最后的表是驱动表driving table会被最先处理第一张表则最后被扫描。处理方式是对驱动表排序再与第二张表做排序合并连接。一个经典对照实验能直观展示顺序带来的量级差异表TAB1有 16,384 条记录表TAB2只有 1 条记录。-- 高效TAB2 写在 FROM 最后作为驱动表执行时间约 0.96 秒 SELECT COUNT(*) FROM TAB1, TAB2; -- 低效TAB1 变成驱动表执行时间约 26.09 秒 SELECT COUNT(*) FROM TAB2, TAB1;同样的表、同样的数据量只是换了下书写顺序执行时间差出 27 倍。原因是 1 条记录的 TAB2 作为驱动表时只需要扫描一次 TAB1 做匹配反过来则是先对 16,384 条记录排序再逐条探测I/O 和排序开销完全不在一个量级。提示CBO 模式下优化器会根据统计信息自己调整连接顺序FROM 顺序的影响较小。但前提是统计信息足够新、足够准否则 CBO 也会做出错误的顺序选择。3.2 三张以上表连接时的交叉表策略当 FROM 子里出现三张以上表时选择交叉表intersection table作为基础表是效率最高的做法。交叉表指被其他表引用的那张表。比如EMP描述了LOCATION和CATEGORY的交集EMP 就是交叉表-- 推荐写法EMP 作为驱动表EMP_NO 条件先过滤 SELECT * FROM LOCATION L, CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO C.CAT_NO AND E.LOCN L.LOCN; -- 不推荐EMP 换到 FROM 第一行过滤条件失去驱动作用 SELECT * FROM EMP E, LOCATION L, CATEGORY C WHERE E.CAT_NO C.CAT_NO AND E.LOCN L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000;第一段 SQL 里 EMP 被 LOCATION 和 CATEGORY 共同引用且 EMP_NO 的范围过滤能提前缩小区间连接基数最小。第二段把 EMP 放到 FROM 最前面驱动顺序变成先处理 LOCATION、CATEGORY再回连 EMP中间结果集膨胀得厉害。RBO 环境下这两段的执行计划有本质差别前者走嵌套循环后走合并后者经常变成大表的全表扫描再 sort merge join。WHERE 子句的解析顺序也要单独说一句Oracle 采用自下而上的顺序解析 WHERE 条件。所以表连接条件要写在其他过滤条件之前而能把记录数过滤掉最多的条件要放在 WHERE 子句的末尾因为它最先被求值。表顺序驱动表执行时间结论FROM TAB1, TAB2TAB21 行0.96 秒小表驱动推荐FROM TAB2, TAB1TAB116,384 行26.09 秒大表驱动避免这种顺序规则到了 CBO 时代变得不那么绝对但理解它的推导逻辑仍然有价值当你面对一个执行计划异常的 SQL 时能快速判断出优化器是不是选错了驱动表然后通过ORDEREDhint 或者调整统计信息来纠正而不是瞎猜。4. 谓词改写与关联重写EXISTS、NOT IN 与 DECODE 的取舍4.1 用 EXISTS 替换 IN用 NOT EXISTS 替换 NOT IN在基础表查询里为了满足一个条件去关联另一张表时EXISTS通常比IN效率更高。原因在于IN会先执行子查询并把结果集物化再与外层表做半连接EXISTS则是逐行探测只要子查询一命中就立刻返回不需要构造完整的结果集。-- 低效IN 子查询需要物化 DEPT 结果 SELECT * FROM EMP WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC MELB); -- 高效EXISTS 逐行探测命中即返回 SELECT * FROM EMP WHERE EMPNO 0 AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO EMP.DEPTNO AND LOC MELB);NOT IN的问题更严重。它在子查询里做内部排序和合并且对子查询表做全表遍历是这几类写法里最低效的。改法有两种外连接改写或者改成NOT EXISTS。-- 改写一外连接 IS NULL 过滤 SELECT * FROM EMP A, DEPT B WHERE A.DEPT_NO B.DEPT_NO() AND B.DEPT_NO IS NULL AND B.DEPT_CAT() A; -- 改写二NOT EXISTS语义最清晰 SELECT * FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO E.DEPT_NO AND D.DEPT_CAT A);注意外连接改写里B.DEPT_CAT() A的()不能漏漏了之后连接语义就变了可能把 DEPT 表里DEPT_CAT不为 A 的行也关联进来导致结果集错误。这类改写在 RBO 下执行路径从 FILTER 变成 NESTED LOOP性能提升非常明显。4.2 EXISTS 替换 DISTINCT避免排序去重一对多表关联时SELECT DISTINCT会让 Oracle 对结果集做排序去重。如果只是判断子表里是否存在对应记录用EXISTS更合适-- 低效DEPT 和 EMP 连接后排序去重 SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO E.DEPT_NO; -- 高效子查询一命中立刻返回 SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT X FROM EMP E WHERE E.DEPT_NO D.DEPT_NO);EXISTS更快的本质是提前终止RDBMS 核心模块在子查询条件满足后立即返回结果不需要等待整个子查询跑完也不需要做 DISTINCT 所需的排序和去重。4.3 DECODE 减少表扫描次数DECODE函数可以把多个条件统计合并成一次扫描适合同一张表上做多个维度的条件聚合。比如要分别统计 DEPT_NO 为 0020 和 0030 两个部门的 SMITH 相关记录数、工资合计SELECT COUNT(DECODE(DEPT_NO, 0020, X, NULL)) AS D0020_COUNT, COUNT(DECODE(DEPT_NO, 0030, X, NULL)) AS D0030_COUNT, SUM(DECODE(DEPT_NO, 0020, SAL, NULL)) AS D0020_SAL, SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) AS D0030_SAL FROM EMP WHERE ENAME LIKE SMITH%;这个写法的核心逻辑是COUNT只统计非 NULL 值DECODE在不匹配时返回 NULL于是匹配的行计 1、不匹配的行计 0SUM同理只累加匹配行的 SAL。原本要扫四遍表两次 COUNT、两次 SUM现在一遍扫描全部算完。DECODE同样可以放在GROUP BY和ORDER BY里做行转列之类的操作本质是用表达式计算代替多次表访问。写法内部行为适用场景IN物化子查询结果集做半连接子查询结果集小且分布均匀EXISTS逐行探测命中即返回外层表小、子查询有索引NOT IN内部排序合并 全表遍历基本不推荐NOT EXISTS / 外连接高效过滤不存在记录替代 NOT IN 首选写 SQL 时还需要注意减少对表的查询次数。比如一个 UPDATE 要更新多个列不要把每个列都写成独立子查询而要用元组比较或一次子查询赋值多列这样可以少访问几次表。5. DML 与统计类查询的执行开销TRUNCATE、COMMIT 和 count 的选型5.1 TRUNCATE 与 DELETE回滚与恢复的代价差异删除全表数据时DELETE 和 TRUNCATE 的开销差异常常被低估。DELETE 是 DML删除的每一条记录都会被写进回滚段以便在未 COMMIT 时恢复数据同时生成大量 redo 日志锁的持有时间也更长。TRUNCATE 是 DDL不写回滚段数据不可恢复但资源占用极小、执行时间极短。维度DELETETRUNCATE类型DMLDDL回滚信息写入回滚段可恢复不写回滚段不可恢复redo 生成大量极少执行速度慢快适用场景删除部分数据清空全表需要强调的是TRUNCATE 只能用于整表清空不能带 WHERE 条件一旦执行数据无法回滚。所以在生产环境里用它之前必须确认备份策略或者确认目标表的数据是可以重建的临时表、中间表。5.2 COMMIT 释放的资源比想象中多多使用 COMMIT 是提升程序性能成本最低的手段之一。COMMIT 释放的资源包括四块回滚段上用于恢复数据的信息、程序语句获得的锁、redo log buffer 中的空间以及 Oracle 管理上述三种资源时的内部开销。对长事务来说及时 COMMIT 还能避免锁等待链越拉越长减少其他会话的阻塞概率。但要注意两个边界。第一COMMIT 本身有开销循环里每条记录都 COMMIT 反而会拖慢批量处理通常按每 50100 行或按逻辑批次提交。第二对无法回滚的业务场景过频 COMMIT 也意味着失去了事务的原子性保障。所以「尽量多 COMMIT」的正确理解是在不破坏业务一致性的前提下尽早结束事务。5.3 count 的写法与 HAVING 的位置关于COUNT(*)、COUNT(1)、COUNT(EMPNO)谁更快网上争论多年。结论是:三者并没有显著性能差别COUNT(*)在 Oracle 的优化器里会被改写成对空常量的计数并不存在「先取所有列再数一遍」的开销。真正的影响因素是能否走索引如果 EMP 表上有 EMPNO 索引COUNT(EMPNO)可以直接扫索引而不是回表这个优势远大于*和1的写法差异。HAVING 子句的优化则更明确。HAVING 是在检索出所有记录后、对结果集做过滤发生在排序、聚合之后WHERE 是在聚合前过滤。所以能用 WHERE 完成的条件不要放到 HAVING 里-- 低效REGION ! SYDNEY 参与了聚合再被过滤 SELECT REGION, AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION ! SYDNEY AND REGION ! PERTH; -- 高效先过滤再聚合 SELECT REGION, AVG(LOG_SIZE) FROM LOCATION WHERE REGION ! SYDNEY AND REGION ! PERTH GROUP BY REGION;HAVING 只应保留针对聚合结果的比较比如HAVING COUNT(*) 100。把普通行级过滤条件写进 HAVING等于让数据库白白多算了一轮聚合。5.4 几个值得抄的 DML 改写删除重复记录的最高效写法利用的是 ROWID 的比较保留每组 EMP_NO 里最小的 ROWID删除其余DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO E.EMP_NO);更新多列时用一次元组赋值代替两个独立子查询-- 低效两个子查询分别访问 EMP_CATEGORIES UPDATE EMP SET EMP_CAT (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT 0020; -- 高效一次子查询赋值两列 UPDATE EMP SET (EMP_CAT, SAL_RANGE) (SELECT MAX(CATEGORY), MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT 0020;类似的多子查询的条件判断可以用元组比较合并SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME, DB_VER) (SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION 604);这类改写的本质都是减少对表的访问次数。每执行一条 SQLOracle 内部要经历解析、估算索引利用率、绑定变量、读数据块等步骤SQL 条数减半这些开销也近似减半。6. 用 V$SQLAREA、TKPROF 和 EXPLAIN PLAN 定位慢 SQL排查慢 SQL我一般按「先看全局 → 再抓现场 → 最后拆执行计划」的顺序走。第一步用 V$SQLAREA 找出共享池里最可疑的语句SELECT EXECUTIONS, DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) AS Hit_ratio, ROUND(DISK_READS / EXECUTIONS, 2) AS Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS 0 AND BUFFER_GETS 0 AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS 0.9 ORDER BY DISK_READS / EXECUTIONS DESC;这条语句从共享池中捞出了逻辑读高、但缓存命中率低于 90% 的 SQL按每次执行的平均物理读排序。EXECUTIONS是执行次数DISK_READS是物理读次数BUFFER_GETS是逻辑读次数Hit_ratio低于 0.9 说明大量逻辑读没有命中缓冲很可能存在全表扫描或索引选择错误。拿到 SQL_TEXT 之后再针对单条语句做会话级跟踪ALTER SESSION SET SQL_TRACE TRUE; ALTER SESSION SET TIMED_STATISTICS TRUE; -- 执行目标 SQL ALTER SESSION SET SQL_TRACE FALSE;跟踪文件生成在 USER_DUMP_DEST 指定的目录文件是原始格式需要用 TKPROF 转换后才可读tkprof orcl_ora_12345.trc output.txt explainscott/tiger sortprsela,exeela,fchela sysnoexplain参数指定账号用于生成执行计划sort按解析时间、执行时间、获取时间排序一眼就能看到卡在哪一步sysno用来过滤掉递归 SQL 的噪音。TKPROF 输出里的 parse count、execute count、CPU 时间等指标能精准区分一条 SQL 是慢在硬解析还是慢在物理 I/O。最后一步是拆执行计划。我习惯直接用 SQL*Plus 的 autotrace不用真正显示结果集SET AUTOTRACE TRACEONLY; SELECT * FROM dept, emp WHERE emp.deptno dept.deptno;得到的执行计划需要从里往外、从上往下读。以经典的两表连接为例0 SELECT STATEMENT OptimizerCHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF EMP 3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT 4 3 INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)实际的执行步骤是先对 EMP 做全表扫描再通过 PK_DEPT 唯一索引回表取 DEPT 行最后做嵌套循环连接。注意 NESTED LOOP 是少数不按操作号从小到大的执行的操作正确读法是检查为 NESTED LOOP 提供输入的两个子操作其中操作号最小的先执行。这个例子里 2 号EMP 全表扫先跑3 号DEPT 索引回表后跑两者结果喂给 1 号做循环。如果看到 EMP 上有 WHERE 条件却被全表扫了就该检查 EMP_NO 列的索引和统计信息多半是索引被隐式类型转换废掉或者统计信息过期让优化器低估了结果集大小。本文还有配套的精品资源点击获取
