简介这份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 覆盖只影响这一条 SQLSELECT /* 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 SCANTABLE 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 只在删除全表适用”这个提醒是对的但实际工作中还是有人踩。解决删部分数据用DELETEWHERE删全表且确认不需要回滚才用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 \ sysno \ sortexeela \ explainscott/tiger参数说明sysno过滤掉递归 SQLOracle 内部执行的只看用户 SQLsortexeela按“执行时间”排序也可以换成prsela解析时间或fchlafetch 时间explain让 TKPROF 顺便跑一次EXPLAIN PLAN输出里会带执行计划。输出文件里重点看三行Parse、Execute、Fetch各自的cpu、elapsed、disk、query、current。如果Parse的elapsed很高说明硬解析多要查共享池如果Fetch的disk高说明 I/O 是瓶颈。5.3 一个完整的排查闭环把上面的步骤串起来我处理慢 SQL 的固定流程是从V$SQLAREA按disk_reads排序捞出 Top 20。对嫌疑 SQL 开sql_trace跑一次拿 TKPROF 报告。看 TKPROF 里的执行计划确认是全表扫描、索引选择错误、还是连接顺序不对。针对性处理补统计信息、加 hint、改写 SQLEXISTS 替换 IN 等。改完再跑一次 TKPROF对比disk和elapsed的变化。确认改善后把改动记录到变更日志里标注 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和统计信息的最后收集时间这两个信息基本能解释一半以上的“莫名其妙变慢”。希望这份拆解能帮到你少走几个我踩过的弯路。本文还有配套的精品资源点击获取