企业官网建设流程全解析

简介：这份Oracle SQL优化教程面向数据库管理员、后端开发及运维人员，聚焦查询执行效率低、资源消耗高等常见问题，帮助读者建立从优化器选择到内存复用的系统优化思路。资源为单个docx文档，压缩包约26KB，内容围绕三大核心策略展开：一是对比RULE、COST、CHOOSE三类优化器的适用场景，说明通过init.ora中OPTIMIZER_MODE参数配置及定期运行ANALYZE更新统计信息的方法；二是剖析全表扫描与ROWID访问的差异，讲解索引加速与维护开销之间的权衡；三是阐述共享SQL语句的匹配条件，包括字符级一致、对象相同及绑定变量命名一致等细节，并提示共享池大小的调优平衡。文档还涉及基于规则优化器下的表名顺序选择、基础表与交叉表判断等实战要点，适合希望深入理解Oracle执行机制、提升SQL调优能力的读者参考。目前已有57人学习。

1. 从一次全表扫描说起：这份 Oracle SQL 优化笔记到底能解决什么

上周帮朋友看一个 Oracle 报表库，一条统计查询跑了 47 秒，执行计划里赫然一个TABLE ACCESS FULL，表不大，八十多万行，但没走索引。翻执行计划时发现优化器模式是CHOOSE，统计信息停留在半年前。改完优化器模式、补了统计信息，同一条 SQL 降到 1.2 秒。这类问题在 Oracle 运维里太常见了，而手头这份《oracle sql优化教程.docx》正好把这类高频场景的解法按条目整理了出来。

它不是一本讲原理的教科书，而是一份偏实操的条目式笔记，覆盖优化器选型、表访问方式、共享 SQL、WHERE 子句顺序、EXISTS 与 IN 的替换、TRUNCATE 与 DELETE 的取舍、EXPLAIN PLAN 解读等二十多个具体优化点。适合两类人：一是刚接手 Oracle 库、面对慢 SQL 不知道从哪下手的开发或运维；二是写过几年 SQL、但优化手段还停留在“加索引”这一层的从业者。下面我按“先立住原理、再动手复现、最后说坑”的顺序，把这份笔记拆开讲透。

2. 优化器选型与统计信息：CBO、RBO、CHOOSE 到底怎么选

2.1 三种优化器的本质区别

Oracle 的优化器决定了 SQL 走哪条执行路径，选错了后面所有优化都是白费。这份笔记里提到的三种模式，本质区别在于“凭什么做决策”：

• RULE（基于规则）：不看数据量、不看统计信息，纯按一套写死的优先级规则选路径。比如“有索引就走索引”“驱动表放右边”。它的优点是稳定可预测，缺点是它根本不知道你的表里到底有多少行——一个只有 10 行的表走索引，可能比全表扫描还慢。
• COST（基于成本）：靠统计信息估算每种执行路径的代价（I/O、CPU），挑代价最小的。这是现代 Oracle 的默认方向，也是绝大多数场景该用的模式。
• CHOOSE（选择性）：一个“看情况”的模式——如果表做过ANALYZE，就自动转 CBO；没做过，就退回 RBO。问题就出在这个“看情况”上：统计信息一旦过期或缺失，优化器悄悄退回规则模式，执行计划可能突然劣化，而你完全不知道发生了什么。

笔记里那句“为了避免不必要的全表扫描，必须尽量避免使用 CHOOSE 优化器”是对的，但更准确的说法是：CHOOSE 的问题不是它一定差，而是它的行为不可预测，排查问题时多了一层黑匣子。

2.2 设置优化器模式的三种粒度

优化器模式可以在三个层级设置，优先级从低到高：

实例级（改init.ora或spfile）：

-- 查看当前优化器模式 SHOW PARAMETER optimizer_mode; -- 实例级修改（需重启或动态生效，视版本而定） ALTER SYSTEM SET optimizer_mode = ALL_ROWS SCOPE = BOTH;

会话级（当前连接有效，不影响别人）：

ALTER SESSION SET optimizer_mode = FIRST_ROWS;

语句级（用 hint 覆盖，只影响这一条 SQL）：

SELECT /*+ ALL_ROWS */ emp_name, salary FROM emp WHERE dept_no = 20;

参数说明：ALL_ROWS面向吞吐量，适合批处理、报表；FIRST_ROWS面向响应速度，适合交互式查询前几行；FIRST_ROWS_n可以指定返回前 n 行的最优。我一般会建议：OLTP 系统用ALL_ROWS做默认，个别需要快速返回的查询在语句级用 hint 覆盖，不要全局设FIRST_ROWS，否则大批量查询会吃亏。

2.3 统计信息：CBO 的命根子

CBO 再聪明，喂给它的统计信息是错的，它就只能做出错误的估算。笔记里强调“必须经常运行 analyze 命令”，这个方向对，但现在的做法更推荐用DBMS_STATS包而不是老式的ANALYZE命令：

-- 收集单表统计信息 BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'SCOTT', -- 用户名 tabname => 'EMP', -- 表名 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 采样比例，自动即可 method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- 列统计与直方图 cascade => TRUE -- 同时收集索引统计 ); END; /

逻辑说明：estimate_percent用AUTO_SAMPLE_SIZE让 Oracle 自己决定采样量，比手工写 10% 更稳；method_opt的SIZE AUTO让 Oracle 判断哪些列需要直方图（数据分布倾斜的列才需要）；cascade => TRUE保证索引统计一起更新，否则索引的选择性估算会失真。

提示：ANALYZE命令在老版本里还能用，但 Oracle 官方从 10g 起就建议改用DBMS_STATS，因为ANALYZE会锁表且不支持并行，大表上跑一次可能影响业务。

2.4 验证优化器是否按预期工作

改完模式、收完统计信息，别急着说“优化完了”，用EXPLAIN PLAN或AUTOTRACE看一眼实际执行计划：

EXPLAIN PLAN FOR SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno AND e.sal > 5000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

看输出里的Optimizer=那一行，确认是ALL_ROWS还是CHOOSE；再看有没有TABLE ACCESS FULL出现在你预期该走索引的表上。这一步是后面所有优化的验证基础，跳过它等于闭着眼睛调参。

3. 表访问方式与 SQL 写法：从全表扫描到 ROWID 的实操路径

3.1 全表扫描和 ROWID 访问的取舍

Oracle 访问表数据就两条路：全表扫描（Full Table Scan）和通过 ROWID 访问。笔记里说“全表扫描是顺序访问每条记录”，这个描述略简化了——实际上 Oracle 会一次读多个数据块（多块读），所以全表扫描不一定慢，关键看数据量和选择性。

判断标准很简单：如果查询要返回表中大部分行（比如超过 20%~30%），全表扫描反而比走索引快，因为索引扫描是单块读，还要回表。反过来，如果只取少量行，走索引 + ROWID 回表效率高得多。

-- 强制走索引，观察执行计划变化 SELECT /*+ INDEX(emp idx_emp_deptno) */ emp_name, salary FROM emp WHERE dept_no = 20;

参数说明：INDEXhint 里第一个参数是表别名，第二个是索引名。加了这个 hint 后如果执行计划从TABLE ACCESS FULL变成INDEX RANGE SCAN+TABLE ACCESS BY INDEX ROWID，说明索引确实被用上了。但别滥用 hint——如果统计信息准确，CBO 自己会选对；hint 是排查问题时用的，不是日常写法。

3.2 共享 SQL 语句的三个硬条件

共享池（Shared Pool）里的 SQL 缓存能省掉重复解析的开销，但 Oracle 的匹配是“字符级严格匹配”，差一个空格都不行。笔记里列了三个条件，我把它翻译成可操作的检查项：

条件一：字符级完全一致。SELECT * FROM EMP;和select * from emp;在共享池里是两条不同的语句。常见翻车场景是代码里拼接 SQL 时大小写不统一、换行位置不同。

条件二：访问的对象完全相同。两个用户通过各自的 private synonym 访问同名对象，Oracle 认为它们是不同对象，无法共享。这个在多用户环境下特别容易踩。

条件三：绑定变量名必须一致。:blk1.pin和:blk1.ot_ind是不同的绑定变量名，即使运行时赋相同的值也不能共享。

-- 查看共享池中 SQL 的复用情况 SELECT sql_text, executions, parse_calls, ROUND(parse_calls / executions, 2) AS parse_ratio FROM v$sqlarea WHERE executions > 100 AND parse_calls / executions > 0.5 ORDER BY parse_ratio DESC;

逻辑说明：parse_calls / executions比值接近 1 说明每次执行都在重新解析，共享没生效。正常情况这个比值应该远小于 1。如果发现大量 SQL 的比值偏高，优先检查是不是没用绑定变量、或者 SQL 文本拼接方式不统一。

3.3 WHERE 子句顺序与表名顺序：RBO 时代的规则在 CBO 下还成立吗

笔记里花了很大篇幅讲“FROM 子句里最后一个表是驱动表”“WHERE 条件从下往上解析”“过滤最多的条件放最后”。这些规则在 RBO 下是铁律，但在 CBO 下，优化器会根据统计信息自己决定连接顺序和驱动表，你写在前还是在后，CBO 不一定照做。

那这些规则还有没有用？有，但场景变了：

• 如果你还在用 RBO（老系统、或者优化器意外退回 RBO），这些规则必须遵守。
• 如果是 CBO，写 SQL 时按“小表驱动大表”“过滤条件尽量早生效”的思路组织，至少不会给优化器添乱，但不要指望靠调整书写顺序来强制改变执行计划——那是 hint 的活。

-- 低效写法：过滤条件写在前面，连接条件写在后面 SELECT * FROM emp e, dept d WHERE e.sal > 5000 AND e.deptno = d.deptno; -- 更清晰的写法：连接条件在前，过滤条件在后 SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.sal > 5000;

两种写法在 CBO 下执行计划可能完全一样，但第二种可读性更好，排查时一眼能看出连接关系。

3.4 EXISTS 与 IN 的替换：什么时候真的有效

笔记里说“用 EXISTS 替代 IN”“用 NOT EXISTS 替代 NOT IN”，这个建议在大多数情况下成立，但原因值得说清楚：

IN子句在执行时，Oracle 可能把子查询结果集全部物化出来再做匹配；EXISTS则是逐行判断，子查询条件一旦满足就返回，不用等全部结果。对于子查询结果集大的场景，EXISTS通常更快。

NOT IN的问题更严重：如果子查询结果里包含 NULL 值，整个NOT IN会返回空结果集——这是一个逻辑陷阱，不只是性能问题。NOT EXISTS没有这个坑。

-- 低效：NOT IN，且子查询有 NULL 风险 SELECT * FROM emp WHERE dept_no NOT IN (SELECT dept_no FROM dept WHERE 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' );

参数说明：SELECT 'X'里的'X'是占位符，EXISTS 只关心子查询有没有返回行，不关心返回什么列，写'X'比写*更明确。外连接改写（(+)语法）也是一种方案，但可读性差，我一般优先用NOT EXISTS。

4. 避坑与排查：那些执行计划没告诉你的翻车现场

4.1 统计信息刚收集完，执行计划反而变差了

现象：手动跑了一次GATHER_TABLE_STATS，结果原本走索引的查询变成全表扫描，响应时间翻倍。

原因：统计信息更新后，CBO 对数据分布的估算变了。比如原来统计信息过期，CBO 以为表里只有几千行，走了索引；实际有几十万行，全表扫描才是对的。或者直方图收集后，CBO 发现某列数据倾斜严重，改变了访问路径。

解决：先别急着回滚统计信息。用DBMS_XPLAN对比新旧执行计划，看代价估算的变化。如果新计划确实更优（比如全表扫描在多块读下实际更快），接受它；如果确实劣化，可以用DBMS_STATS.LOCK_TABLE_STATS锁定统计信息，或者用OPTIMIZER_FEATURES_ENABLE参数回退优化器行为到旧版本。

4.2 绑定变量导致执行计划“忽好忽坏”

现象：同一条 SQL，有时快有时慢，执行计划在索引扫描和全表扫描之间跳。

原因：绑定变量窥探（Bind Peeking）。Oracle 在第一次执行时偷看绑定变量的值，据此生成执行计划，之后复用这个计划。如果第一次传入的值选择性很好（走索引），但后续传入的值选择性很差（该走全表扫描），就会用错计划。

解决：Oracle 11g 以后可以用自适应游标共享（Adaptive Cursor Sharing），让优化器为不同的绑定变量值生成不同计划。检查V$SQL里的IS_BIND_SENSITIVE和IS_BIND_AWARE列。如果还是不行，考虑对这条 SQL 禁用绑定变量窥探，或者用/*+ BIND_AWARE */hint。

4.3 TRUNCATE 用错了，数据没了后悔药也没有

现象：想清理表里部分数据，顺手写了TRUNCATE TABLE，结果全表清空，回滚段里也没有记录。

原因：TRUNCATE是 DDL 不是 DML，不写回滚段，不能回滚，也不触发触发器。笔记里说“TRUNCATE 只在删除全表适用”，这个提醒是对的，但实际工作中还是有人踩。

解决：删部分数据用DELETE+WHERE，删全表且确认不需要回滚才用TRUNCATE。如果表有外键引用，TRUNCATE还会报错，需要先禁用约束。执行前先SELECT COUNT(*)确认行数，别凭感觉。

4.4 EXPLAIN PLAN 的结果和实际执行不一样

现象：EXPLAIN PLAN显示走索引，实际执行却是全表扫描。

原因：EXPLAIN PLAN只是让优化器“模拟”一下，不真正执行 SQL，也不考虑运行时才确定的信息（比如绑定变量值、并行度）。实际执行时，优化器可能根据运行时条件选不同计划。

解决：用AUTOTRACE或DBMS_XPLAN.DISPLAY_CURSOR看实际执行计划，后者能拿到游标缓存里的真实计划：

-- 先执行 SQL SELECT /*+ GATHER_PLAN_STATISTICS */ emp_name FROM emp WHERE dept_no = 20; -- 再查实际执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

ALLSTATS LAST会显示每一步的实际行数、逻辑读、物理读，和估算值对比，一眼能看出估算偏差在哪。

5. 用 V$SQLAREA 和 TKPROF 定位低效 SQL：一套可复用的排查流程

5.1 从 V$SQLAREA 捞出“嫌疑 SQL”

笔记里给了一条查低效 SQL 的语句，思路是对的，但实际用的时候有几个细节要调。我一般会按“执行次数多 + 单次磁盘读高”来排序，而不是只看命中率：

SELECT sql_id, executions, disk_reads, buffer_gets, ROUND(disk_reads / executions, 2) AS reads_per_exec, ROUND(buffer_gets / executions, 2) AS gets_per_exec, sql_text FROM v$sqlarea WHERE executions > 0 AND buffer_gets > 0 AND (buffer_gets - disk_reads) / buffer_gets < 0.9 -- 命中率低于 90% ORDER BY disk_reads DESC FETCH FIRST 20 ROWS ONLY;

参数说明：reads_per_exec是单次执行的物理读，越高说明 I/O 越重；gets_per_exec是逻辑读，高说明内存里翻了很多块。两个都高，基本可以锁定是问题 SQL。FETCH FIRST 20 ROWS ONLY是 12c 以后的写法，老版本用ROWNUM <= 20。

5.2 用 SQL Trace + TKPROF 拿到执行级数据

V$SQLAREA给的是累计值，要拿到单次执行的详细分解（解析时间、执行时间、CPU 时间、等待事件），得用 SQL Trace：

-- 开启当前会话的 SQL Trace ALTER SESSION SET sql_trace = TRUE; ALTER SESSION SET timed_statistics = TRUE; -- 必须开，否则没有时间数据 -- 执行你要分析的 SQL SELECT ... FROM ... WHERE ...; -- 关闭 Trace ALTER SESSION SET sql_trace = FALSE;

Trace 文件生成在USER_DUMP_DEST目录下，是二进制格式，直接看不了，需要用 TKPROF 转换：

tkprof /path/to/trace/orcl_ora_12345.trc /tmp/output.txt \ sys=no \ sort=exeela \ explain=scott/tiger

参数说明：sys=no过滤掉递归 SQL（Oracle 内部执行的），只看用户 SQL；sort=exeela按“执行时间”排序，也可以换成prsela（解析时间）或fchla（fetch 时间）；explain让 TKPROF 顺便跑一次EXPLAIN PLAN，输出里会带执行计划。

输出文件里重点看三行：Parse、Execute、Fetch各自的cpu、elapsed、disk、query、current。如果Parse的elapsed很高，说明硬解析多，要查共享池；如果Fetch的disk高，说明 I/O 是瓶颈。

5.3 一个完整的排查闭环

把上面的步骤串起来，我处理慢 SQL 的固定流程是：

1. 从V$SQLAREA按disk_reads排序，捞出 Top 20。
2. 对嫌疑 SQL 开sql_trace，跑一次，拿 TKPROF 报告。
3. 看 TKPROF 里的执行计划，确认是全表扫描、索引选择错误、还是连接顺序不对。
4. 针对性处理：补统计信息、加 hint、改写 SQL（EXISTS 替换 IN 等）。
5. 改完再跑一次 TKPROF，对比disk和elapsed的变化。
6. 确认改善后，把改动记录到变更日志里，标注 SQL_ID 和修改原因。

这套流程不依赖第三方工具，SQL*Plus 就能跑完。TOAD 和 SQL Developer 的图形化执行计划当然更直观，但排查问题时，命令行拿到的数据更原始、更可信。

5.4 一个容易忽略的点：ARRAYSIZE 参数

笔记里提到“在 SQLPlus 中重新设置 ARRAYSIZE 参数，建议值为 200”。这个参数控制每次 fetch 从数据库取多少行到客户端，默认值通常偏小（比如 15），导致网络往返次数多。在 SQLPlus 里：

SET ARRAYSIZE 200

在 Pro*C 或 OCI 里也有对应的设置。对于返回大量行的查询，调大这个值能明显减少 fetch 次数。但别设太大，否则客户端内存占用会上去，200 到 500 之间是比较稳的范围。

从那以后我每次接手一个不熟悉的 Oracle 库，第一件事就是查optimizer_mode和统计信息的最后收集时间，这两个信息基本能解释一半以上的“莫名其妙变慢”。希望这份拆解能帮到你，少走几个我踩过的弯路。

本文还有配套的精品资源，点击获取