简介：面向数据库管理员与后端开发者的Oracle SQL优化教程，聚焦查询执行效率，针对大表全表扫描、重复解析相同语句等常见瓶颈给出系统化解决思路，适合需深入理解优化器机制、索引访问方式与语句共享原理的读者。文档围绕三大核心策略展开：一是区分基于规则、基于成本与选择性三种优化器，强调采用CBO需借助ANALYZE更新统计信息并通过init.ora设定模式；二是比较全表扫描与通过ROWID访问表两种方式，指出索引能快速定位记录但存在维护成本需权衡；三是阐述共享SQL语句的优势与严格条件，包括字符级完全一致、对象相同、绑定变量同名，以及共享池大小对命中率与内存占用的平衡。此外还补充多表连接时基础表选择顺序对执行效率的影响。资源为单份docx文档，文件总数1个，压缩包约26KB，内容精炼实用。该文档目前已有57人学习，能帮助读者构建清晰的SQL调优框架并快速定位慢查询原因。
1. 一份Oracle SQL优化笔记，值不值得一行行读完
一条统计SQL在测试环境跑得好好的，一上生产库就卡了十几秒，DBA排查一圈，最后发现执行计划走了全表扫描，一张千万级的大表从头扫到尾。这类场景做数据库的人多少都碰过。这份Oracle SQL优化笔记收录了二十多条可以直接对着改的经验，从优化器选型、表访问方式、共享SQL，到EXISTS改写、执行计划分析，基本把日常慢SQL治理会遇到的切入点覆盖了一遍。没有理论铺陈，没有模型推导，每一条都是结论加例子，新手可以照着改，熟手也能从里面挑出之前忽略的细节。它适合做Oracle开发的工程师、DBA，以及刚接手慢查询排查、想快速建立优化直觉的数据运维。如果你正被一条执行时间飘忽不定的SQL困扰，这份笔记值得逐条读。
2. 优化器不是黑匣子：让CBO统计信息先落地
不少人对Oracle优化器的理解还停留在“Oracle自己会选执行计划，不用管”。实际上优化器的选择直接决定一条SQL是全表扫描还是索引扫描，而这份笔记的第一条就点破了关键：不跑统计信息，成本优化器根本没有判断依据。
2.1 三种优化器的选型：RULE、COST与CHOOSE
Oracle的优化器有三种模式，对应init.ora里的OPTIMIZER_MODE参数：RULE基于规则，不考虑数据分布，只看SQL写法；COST基于成本，根据表的统计信息估算各执行路径的代价；CHOOSE则是“选择性”，在二者之间自动切换。笔记里提醒了一个容易忽略的细节——CHOOSE的实际行为取决于表是否analyze过：如果表有统计信息，Oracle自动走CBO；如果表从没收集过统计信息，优化器会退回RULE。也就是说，你什么都不配的时候，系统对每张表的处理方式可能完全不同。
| 优化器模式 | 判断依据 | 适用场景 | 风险 |
|---|---|---|---|
| RULE | SQL写法、表顺序 | 老系统、无统计信息 | 不看数据量，容易选错路径 |
| COST | 表/索引统计信息 | 常规OLTP与报表 | 统计信息过期会误判 |
| CHOOSE | 有无统计信息自动切换 | 缺省配置 | 同一SQL在不同表上表现不一致 |
我在生产环境见过多次因为优化器模式是CHOOSE、某张表的统计信息过期，导致SQL一会儿走索引一会儿全表扫描的情况。笔记里给的方案很直接：尽量用ALL_ROWS或FIRST_ROWS这样的明确模式，别把选择权交给“有没有统计信息”这种随机因素。ALL_ROWS面向总吞吐量优化，适合报表；FIRST_ROWS面向响应时间优化，适合OLTP的单条查询。选定后，你就是把优化器的判断基准固定住了。
2.2 让CBO真正生效：ALTER SESSION与统计信息配套
改变优化器模式可以在实例级、会话级或SQL级完成。实例级在init.ora里改OPTIMIZER_MODE，要重启；会话级一条ALTER SESSION就能生效，适合临时验证。我在排查线上问题的时候通常先会话级切，确认效果再决定要不要改实例参数。
-- 会话级切换到基于成本的优化器 ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS; -- 切换后立即收集目标表的统计信息（现在推荐用DBMS_STATS） EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE);第一句把优化器固定为ALL_ROWS，第二句收集EMP表的统计信息。CASCADE=>TRUE表示连同表上的索引一起收集，避免索引统计信息缺失。老笔记里写的是ANALYZE命令，那个也能用，但ANALYZE收集的是基础统计，DBMS_STATS还能记录直方图信息，对数据倾斜严重的列更友好。统计信息是这个环节的地基，地基不牢，后面所有基于成本的判断都是空中楼阁。我一般会在批量导入数据后立刻跑一次统计信息收集，而不是等到SQL变慢才想起这个步骤。
3. 共享SQL与绑定变量：省掉一半无用解析的入口
优化器选对了，只是第一步。真正让系统扛住高并发的是SQL的共享机制。Oracle把解析过的SQL和执行路径放进SGA的共享池里，下次遇到一模一样的语句直接复用，省掉整个解析过程。笔记里给了共享的三个条件，拆开看全是细节。
3.1 共享池的机制：严格匹配比你想象的更苛刻
共享SQL的匹配是字符级的，空格、大小写、换行都会影响结果。笔记里举的例子很直白：SELECT * FROM EMP;和SELECT * from EMP;不共享，和Select * From Emp;也不共享。很多人以为大小写无所谓，实际上Oracle做的是逐字符比较，差一个空格都是新的硬解析。第二个条件是语句引用的对象必须完全相同——两个用户各自有私有同义词sal_limit，即使SQL文本一样，指向的是不同对象，不共享；而大家都访问public synonym时才能共享。第三个条件是绑定变量名必须一致，:blk1.pin和:blk1.ot_ind虽然类型相同，名字不同就不共享。
基于这套机制，开发规范里应该明确几条：SQL统一大小写风格、统一缩进格式、禁止在代码里拼字面值。否则同样的业务查询，每条SQL都是一次硬解析，共享池再大也扛不住。我见过一个系统峰值时共享池命中率掉到70%以下，检查发现是ORM框架生成的SQL每隔几条就有细微的空格差异，改掉格式化逻辑后命中率立刻回到95%以上。
3.2 绑定变量：让同类查询共用同一份解析结果
绑定变量是OLTP系统里最值得养成的手艺。比如按员工号查询：
-- 第1条：绑定变量写法，可被复用 SELECT emp_name, salary FROM emp WHERE emp_no = :emp_no; -- 第2条：字面值写法，每次都要硬解析 SELECT emp_name, salary FROM emp WHERE emp_no = 342; SELECT emp_name, salary FROM emp WHERE emp_no = 291;第2条的写法看起来很直观，但每换一个员工号，Oracle就当成一条新SQL解析一遍。第1条的绑定变量写法让所有员工号查询共用一份解析结果。在Java里对应PreparedStatement，在Python里对应参数化查询。笔记里说的严格匹配还有个隐藏要求：同名绑定变量在不同SQL里也不能乱用，:emp_no和:dept_no各归各。绑定变量看似只是换个写法，实际上是把“每条SQL都要解析”变成“同类SQL只解析一次”，高并发OLTP下收益极大，这也是慢SQL优化里最先检查的项目。
需要说明的是，绑定变量在OLTP场景下几乎稳赚不赔，但在数据仓库里要谨慎。CBO可以根据绑定变量窥视（bind peeking）拿到变量值判断执行计划，但有些场景下绑定变量会掩盖数据分布的极端情况，走错执行计划。老笔记没讲这层，因为当时版本还没有这个特性。现在做优化，遇到偏斜数据时我会把这类SQL单独摘出来，用字面值或加hint处理。
4. 改写SQL的收益边界：从表顺序到EXISTS的选择
SQL改写是这份笔记篇幅最大的部分，也是争议最多的地方。笔记里的绝大多数结论基于RBO时代，放到今天的CBO环境里需要重新验证。但改写的思路——减少扫描量、减少访问次数、提前过滤——依然有效。这一章把它拆成四个可操作的层面。
4.1 基础表怎么选：FROM子句从右往左的秘密
笔记明确说，Oracle解析FROM子句从右到左，写在最后的表是基础表（driving table），最先被处理。在多表连接时，选择记录条数最少的表作为基础表。它给的数字很有说明力：TAB1有16384条记录，TAB2只有1条，select count(*) from tab1, tab2执行时间是0.96秒，而select count(*) from tab2, tab1要26.09秒。同样的数据，只是表顺序换了下，差了27倍。
-- 高效：FROM子句最后写小表，小表作为基础表先处理 SELECT COUNT(*) FROM tab1, tab2; -- 低效：FROM子句最后写大表，先扫描并排序16384条记录 SELECT COUNT(*) FROM tab2, tab1;颜逻辑在于，RBO下连接常用排序合并方式实现：先扫描基础表并排序，再去扫描下一张表并合并。基础表越小，排序开销越小。三个以上表连接时，笔记建议选交叉表——被其他表引用的表——作为基础表。它给的例子里，EMP表同时被LOCATION和CATEGORY引用，EMP就承担了连接的枢纽角色。不过这条规则只在RBO下严格成立，CBO会自动调整连接顺序。我现在的做法是：把这条当成理解连接开销的思维模型，真正上线前用EXPLAIN PLAN确认实际执行顺序，而不是想当然地以为“我写了小表在最后，它就会先扫描它”。
4.2 WHERE子句的顺序：连接条件在前，过滤量大的条件在后
WHERE子句的解析是自下而上的，这给了SQL书写一个明确的指导：表之间的连接条件写在前面，能过滤掉最多记录的条件写在最后面。笔记里的低效写法是SAL > 50000 AND JOB = 'MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP WHERE DEPT_NO = 10)，高效写法把条件倒过来，先做子查询过滤，再用SAL和JOB过滤。原因是解析器先处理写在后面的条件，把过滤能力最强的条件放在最后，可以让前面步骤处理的数据量尽量小。
这在RBO下是硬规则，在CBO下Oracle会自己调整条件评估顺序，但“过滤量大的条件要尽早发挥作用”这个思想不变。遇到真实优化时，我仍然会把大过滤条件显式往前放，并配合执行计划确认。另一个相关点是HAVING的滥用：HAVING是在全部记录检索完之后才对结果集过滤，还要做排序、聚合等操作，能用WHERE提前过滤的绝不放HAVING。这条在今天的版本依然成立。
4.3 EXISTS与IN的选择：遇到命中就返回的效率差异
关于IN和EXISTS的争论是老生常谈。笔记里的结论是：EXISTS通常比IN高效，NOT EXISTS比NOT IN高效得多。原因是IN子句需要对子查询结果做排序合并，NOT IN还会对子查询表做全表遍历；而EXISTS一旦找到匹配记录就立即返回，不用等子查询算完。
-- 写法1：IN，一般会先处理子查询再匹配外表 SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'MELB'); -- 写法2：EXISTS，每匹配到一条就返回，不做完整子查询 SELECT * FROM emp e WHERE EXISTS (SELECT 1 FROM dept d WHERE d.deptno = e.deptno AND d.loc = 'MELB');NOT IN的替代有两招，笔记推荐最高效的一招是NOT EXISTS，其次是把NOT IN改写成外连接加IS NULL判断：
-- 用NOT EXISTS替代NOT IN SELECT * FROM emp e WHERE NOT EXISTS (SELECT 1 FROM dept d WHERE d.deptno = e.deptno AND d.dept_cat = 'A'); -- 用外连接替代NOT IN SELECT e.* FROM emp e, dept d WHERE e.dept_no = d.dept_no(+) AND d.dept_no IS NULL AND d.dept_cat(+) = 'A';外连接的写法核心是：右表找不到匹配的记录时，左表的记录保留下来，dept_no字段变成NULL，外层判断IS NULL就拿到所有不匹配的记录。这种写法在数据量大的场景下往往比NOT IN快，因为它避免了对子查询表的全量遍历。现代Oracle版本里，优化器有时会自动做等价改写，也许你写IN它内部也转成类似EXISTS的做法。所以这条要落地成习惯：改写后必须看执行计划确认，而不是迷信“EXISTS一定快”。如果改写前后执行计划一模一样，那就说明优化器替你做了工作。
4.4 减少访问次数：一次请求多干活，别让数据库空转
笔记把“减少访问数据库的次数”单独列了一条：执行每条SQL时，Oracle内部要做解析、估算索引利用率、绑定变量、读数据块等一系列工作，访问次数降下来，工作量自然降下来。它举的例子是从EMP表分别查员工号342和291的工资，分开查两次是最低效的，用一个游标循环查两次是次低效的，最高效的是一次自连接同时取出两条记录。
-- 高效：一次SQL取两条记录 SELECT a.emp_name, a.salary, b.emp_name, b.salary FROM emp a, emp b WHERE a.emp_no = 342 AND b.emp_no = 291;无关联的几个简单查询也可以合并成一条。笔记里用了DUAL笛卡尔积的写法合并三个查询，那确实是老版本的做法，可读性差，但“减少往返”的思路到今天仍是性能优化的主旋律。在应用层接口里，这对应批量查询接口、批量更新接口，而不是一条条循环调用。DECODE函数的用法也归在这一类：原来要分别按部门0020和0030扫描两次表，用DECODE可以一次扫描统计出两个部门的COUNT和SUM，减少对相同记录的重复扫描。另外两条降低解析成本的写法也值得一提：SELECT子句避免用*，因为Oracle会通过数据字典把所有列名转换一遍，白白多花时间；多表连接时给表起别名，并让别名出现在每个列名之前，减少解析出的歧义和额外处理。
5. 避坑清单：老优化笔记里最容易被误解的五处
这份笔记写于RBO年代，里面有几条结论在版本更迭后容易被误读。我在生产环境按笔记踩过不少坑，挑五条最典型的说一说，每条按“现象、原因、解决”来展开。
5.1 现象：本想避免全表扫描，加索引后反而更慢
原因：笔记里反复强调避免全表扫描，新手容易形成“全表扫描=慢”的错误认知。对只有几千行的小表，全表扫描可能比走索引更快——索引扫描要先读索引、再按ROWID回表，随机I/O多，全表扫描的顺序读在数据量小时反而高效。数据倾斜严重时同理，比如一个状态列99%都是正常值，走索引要回表海量行，比直接全表扫描更吃亏。
解决：不是“避免全表扫描”，而是“避免大表无过滤条件的全表扫描”。在EXPLAIN PLAN里先看表的数据量和过滤条件，小表、低选择性列，老老实实接受全表扫描。
5.2 现象：TRUNCATE清表后数据找不回来，只能从备份恢复
原因：笔记说TRUNCATE比DELETE高效，因为回滚段里不存放可恢复信息，资源占用少。这个结论是对的，但它把前提说清楚了：TRUNCATE是DDL，执行后数据不可恢复。很多人只记住“TRUNCATE比DELETE快”，忽略了后面那句“数据不能被恢复”。
解决：可以TRUNCATE的表，先确认业务上允许丢数据；对生产环境核心表，我宁可先备份再操作，或多留一步预处理位。DELETE慢归慢，至少还有COMMIT回滚的余地。清表这种高危操作，永远先想后悔药的事。
5.3 现象：count(*)和count(1)谁更快，查了两篇文章两个说法
原因：笔记正文说count()比count(1)稍快，但译者按又说实测没有显著差别。这类经验贴经常出现正文和译者意见相反的情况，如果照抄结论，容易被带到沟里。实际上Oracle优化器很聪明，count()不会真的去逐列取数，而是选取一条最小的索引扫描，两者在绝大多数场景下的差异可以忽略。
解决：不纠结count(*)和count(1)，真正要关注的是能不能走索引。统计记录数时命中一个窄索引，比在任何表上硬扫都强。
5.4 现象：改了SQL大小写风格，共享池命中率骤降
原因：共享SQL的匹配是字符级的，笔记里明确说了包括空格和换行。开发规范不统一时，同一个查询在不同模块里写成不同大小写、不同缩进，Oracle全部当作不同SQL去硬解析。
解决：制定统一SQL书写规范，ORM框架层做统一的格式化。平时用V$SQLAREA观察硬解析比例，如果版本允许，必要时用cursor_sharing参数兜底，但这是缓解手段，治本还是靠统一写法加绑定变量。
5.5 现象：FROM表顺序按老笔记调整后，执行计划没任何变化
原因：笔记整理自基于规则优化器的时代，表顺序在CBO下不再是决定性因素。处理多表连接时，CBO会基于统计信息自行决定驱动表，你手动调整FROM顺序，Oracle可能毫不在意。
解决：活用原笔记的思维模型，小表做驱动表的思想很有价值，但具体执行顺序以EXPLAIN PLAN为准。在CBO时代，把精力花在统计信息是否新鲜、连接列是否有索引、过滤条件是否有效上，好过手工调表顺序。
6. 不靠感觉上线：用EXPLAIN PLAN和TKPROF验证每次改写
优化做完必须验证，否则所谓“优化”全凭感觉。笔记里提供了三条验证手段，我日常组合着用。第一步先捞出最可疑的SQL：查V$SQLAREA这个动态视图，关注EXECUTIONS、DISK_READS、BUFFER_GETS几个字段，命中率低于90%的候选SQL都要逐一排查。这道工序能把“感觉慢”变成“数据上确实慢”。
第二步是EXPLAIN PLAN。在SQL*Plus里执行SET AUTOTRACE TRACEONLY，然后正常写SELECT语句，Oracle会返回执行计划而不打印结果数据。解读执行计划有个关键顺序：从里到外、从上到下，最内部的操作最先执行；同一层里操作号最小的先执行。NESTED LOOP是个例外，要先看它两侧提供数据的操作，其中操作号小的先执行。例如一个典型执行计划会显示先对EMP做全表扫描，再对DEPT做索引唯一扫描，最后嵌套循环连接两表。看到这个顺序，你就能验证自己改写的SQL是不是真的让优化器按预期执行。
第三步用TKPROF看时间分布。设置SQL_TRACE前，先确认TIMED_STATISTICS参数已开启，否则跟踪文件里没有时间信息，白跑一趟。跟踪文件生成在USER_DUMP_DEST指定的目录，直接用TKPROF转换成可读文本，里面会列出每条SQL的解析次数、执行次数、CPU时间和物理读，一眼看出时间花在解析还是执行上。注意，开启TRACE会带来额外开销，生产库上谨慎操作，最好在测试环境或低峰期进行。
我早期的习惯是改完SQL看到执行时间变短就认为大功告成，直到有一次把一条嵌套循环改成了哈希连接，开发环境快了，上生产后因为内存参数不同反而变慢，第二天早上就收到告警邮件。从那以后，无论多小的改写，我都会强制自己走一遍EXPLAIN PLAN或TKPROF再上线，确认执行计划的关键路径符合预期，而不是只盯着那句“执行时间从多少秒降到了多少毫秒”。希望帮到你——下次遇到慢SQL，先让数据说话。
本文还有配套的精品资源，点击获取
