简介这份Oracle SQL优化教程面向数据库管理员、后端开发与运维人员聚焦查询执行效率低、资源消耗高、系统响应慢等常见问题帮助读者建立从优化器选择到内存复用的系统优化思路。资源包共1个docx文档约26KB内容围绕三大核心策略展开一是合理选用RULE、COST、CHOOSE优化器并配合ANALYZE更新统计信息二是对比全表扫描与ROWID访问的适用场景权衡索引带来的查询加速与维护开销三是讲解共享SQL语句的字符级匹配、对象一致与绑定变量要求以及共享池参数的调整平衡。文档还涉及基于规则优化器下的表名顺序选择、基础表与交叉表判断等细节配有具体SQL示例与执行耗时对比便于读者理解执行计划差异。已有57人学习适合希望提升SQL调优实战能力、减少全表扫描与重复解析的数据库从业者参考。1. Oracle SQL 优化教程从执行计划到索引重建的完整落地路径一条 SQL 从 3 秒跑到 0.03 秒中间差的往往不是数据库版本而是你有没有看懂执行计划里那个TABLE ACCESS FULL。Oracle SQL 优化这件事说穿了就是三件事让优化器选对路径、让索引真正被用上、让统计信息别拖后腿。很多人拿到慢 SQL 第一反应是加索引结果索引加了执行计划还是全表扫描这就是典型的“索引建了但没生效”。这篇教程面向的是手里有 Oracle 数据库、正在被慢 SQL 折磨的一线开发和运维不管你是刚入门的 Oracle 新手还是已经写过几百条存储过程的老手下面这套从诊断到落地的流程都能直接抄作业。我会把执行计划怎么读、索引怎么建才不白建、统计信息怎么收集才不翻车一条条拆开讲清楚。2. 看懂执行计划慢 SQL 优化的第一刀2.1 执行计划到底在看什么执行计划是 Oracle 优化器给你的一份“行车路线图”它告诉你这条 SQL 打算怎么走先扫哪张表、用哪个索引、怎么连接、怎么排序。很多人用EXPLAIN PLAN FOR看完就完事了其实那只是预估计划真正跑起来可能完全不一样。我一般直接用DBMS_XPLAN.DISPLAY_CURSOR看实际执行计划因为只有实际跑过的计划才带A-Rows实际返回行数和Buffers逻辑读这两个指标才是判断慢不慢的核心。看执行计划重点盯四个地方第一有没有TABLE ACCESS FULL也就是全表扫描大表上出现这个基本就是问题第二A-Rows和E-Rows差多少差一个数量级以上说明统计信息不准第三Buffers逻辑读高不高逻辑读高说明扫描的数据块多第四连接方式对不对NESTED LOOPS适合小结果集驱动大表HASH JOIN适合大表对大表。-- 查看最近执行过的 SQL 的实际执行计划 SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( sql_id 你的sql_id, cursor_child_no 0, format ALLSTATS LAST ) );这段代码里sql_id可以从V$SQL视图查format ALLSTATS LAST是关键不加这个参数你看不到A-Rows和Buffers。cursor_child_no一般填 0但如果同一条 SQL 有多个子游标需要逐个看。执行计划是从下往上读的最下面那行是最先执行的步骤缩进越深越先跑。2.2 用 AWR 和 ASH 定位历史慢 SQL实际生产环境里慢 SQL 往往不是你现在跑的那条而是半夜跑批时拖垮数据库的那条。这时候V$SQL可能已经被刷出去了得靠 AWR 报告。AWR 里重点看SQL ordered by Elapsed Time和SQL ordered by CPU Time两个部分前者告诉你哪条 SQL 最耗时后者告诉你哪条 SQL 最吃 CPU。如果Elapsed Time高但CPU Time低说明大部分时间花在等待上可能是 I/O 或者锁如果两个都高那就是 SQL 本身写得有问题。ASH 报告更细它能告诉你某个时间段内数据库到底在等什么。Top Wait Events里如果db file sequential read排第一说明索引读得多但可能读得不对如果db file scattered read排第一说明全表扫描多。我一般会先用 AWR 找到 Top SQL再用 ASH 看这条 SQL 在等待什么最后用DISPLAY_CURSOR看实际执行计划三步下来基本能定位到根因。-- 从 V$SQL 中查找逻辑读最高的 SQL SELECT sql_id, sql_text, executions, buffer_gets, disk_reads, ROUND(buffer_gets / executions, 2) AS gets_per_exec, elapsed_time / 1000000 AS elapsed_sec FROM V$SQL WHERE executions 0 ORDER BY buffer_gets DESC FETCH FIRST 20 ROWS ONLY;这个查询按逻辑读排序gets_per_exec是每次执行的平均逻辑读如果这个值超过几万说明每次执行都在扫大量数据块。elapsed_sec是总耗时秒数注意elapsed_time单位是微秒所以要除以 1000000。FETCH FIRST 20 ROWS ONLY是 Oracle 12c 以上才支持的语法11g 得用ROWNUM 20包一层。2.3 执行计划里的三个关键陷阱第一个陷阱是E-Rows严重偏离A-Rows。比如预估返回 10 行实际返回 100 万行优化器就会选错连接方式可能该用HASH JOIN的地方用了NESTED LOOPS结果跑几个小时。这种情况八成是统计信息过期或者列上有函数导致优化器估不准。第二个陷阱是隐式类型转换。比如WHERE id 123id是NUMBER类型你传了个字符串Oracle 会把id隐式转成字符串再比较索引直接失效。这种问题在执行计划里表现为TABLE ACCESS FULL但你看 SQL 文本又觉得没问题血泪经验就是一定要检查绑定变量的类型。第三个陷阱是FILTER操作。执行计划里出现FILTER通常意味着有子查询没被展开或者有NOT IN、NOT EXISTS这类反连接。FILTER的特点是它会对每一行都执行一次子查询数据量大的时候性能极差。解决办法是把子查询改写成LEFT JOIN ... WHERE ... IS NULL或者用NOT EXISTS代替NOT IN。3. 索引优化建了不生效比不建更可怕3.1 B-Tree 索引的选择性与组合索引设计索引不是越多越好每个索引都会增加写入成本。建索引之前先算选择性SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name这个值越接近 1 越好。如果选择性低于 0.1比如性别字段只有男/女两个值建 B-Tree 索引基本没用优化器不会走。组合索引的顺序是门玄学但有个基本原则等值条件在前范围条件在后。比如WHERE status ACTIVE AND create_date SYSDATE - 7索引应该建(status, create_date)而不是反过来。因为status是等值匹配能快速缩小范围create_date是范围扫描放在后面才能用上索引的有序性。如果反过来create_date的范围扫描会导致status无法用索引过滤。-- 创建组合索引等值列在前范围列在后 CREATE INDEX idx_orders_status_date ON orders (status, create_date) TABLESPACE users COMPUTE STATISTICS; -- 查看索引使用情况 SELECT index_name, used, start_monitoring, end_monitoring FROM V$OBJECT_USAGE WHERE index_name IDX_ORDERS_STATUS_DATE;COMPUTE STATISTICS在创建索引时顺便收集统计信息省得后面再跑一次。V$OBJECT_USAGE需要先执行ALTER INDEX ... MONITORING USAGE才能看到数据USED列如果是YES说明索引被用过如果是NO说明建了白建。这个视图在排查“索引为什么不生效”时特别有用。3.2 索引失效的六种常见场景第一种列上用了函数。WHERE UPPER(name) ABC会导致name列上的索引失效解决办法是建函数索引CREATE INDEX idx_upper_name ON table_name(UPPER(name))。第二种隐式类型转换。前面说过了NUMBER列传字符串或者VARCHAR2列传数字都会导致索引失效。第三种LIKE %ABC前置通配符。这种写法没法用 B-Tree 索引因为索引是按前缀排序的。如果确实需要模糊搜索考虑用 Oracle Text 或者反向键索引。第四种IS NULL和IS NOT NULL。B-Tree 索引不存全为 NULL 的键值所以WHERE column IS NULL用不上索引。如果这类查询多可以建(column, 0)这样的组合索引让 NULL 值也有对应的索引条目。第五种NOT IN和!。这些不等条件通常会导致全表扫描因为优化器觉得走索引再过滤的成本比直接全扫还高。第六种统计信息过期。优化器是基于统计信息算成本的如果统计信息太旧它可能觉得全表扫描比索引扫描更便宜于是就不走索引了。3.3 用 SQL Tuning Advisor 自动诊断索引问题手动分析执行计划有时候会漏Oracle 自带的 SQL Tuning Advisor 能自动分析 SQL 并给出索引建议。用法很简单先用DBMS_SQLTUNE.CREATE_TUNING_TASK创建任务再执行最后看报告。-- 创建 SQL Tuning 任务 DECLARE task_name VARCHAR2(100); BEGIN task_name : DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id 你的sql_id, scope DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, time_limit 300, task_name tune_task_001, description 优化订单查询SQL ); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name tune_task_001); END; / -- 查看调优报告 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(tune_task_001) FROM DUAL;SCOPE_COMPREHENSIVE表示做全面分析包括索引建议、统计信息建议、SQL 重写建议。time_limit 300是分析时间上限单位秒生产环境别设太大免得影响正常业务。报告里会给出具体的建议比如“考虑创建索引idx_xxx”还会估算这条建议能带来多少性能提升。注意这个工具只是给建议不会自动改最终还得你自己判断。4. 统计信息与执行计划稳定性别让优化器猜错4.1 统计信息收集的正确姿势统计信息是优化器的“眼睛”眼睛不好使优化器就会瞎选。Oracle 10g 以后有自动统计信息收集任务默认在晚上 10 点到早上 6 点跑但很多生产库这个时间段还在跑批统计信息收集和业务抢资源结果两边都跑不好。我一般会关掉自动收集改成手动在业务低峰期跑。-- 手动收集表统计信息 BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname SCOTT, tabname ORDERS, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, degree 4, cascade TRUE, granularity AUTO ); END; /estimate_percent AUTO_SAMPLE_SIZE让 Oracle 自动决定采样比例一般 10% 左右就够。method_opt FOR ALL COLUMNS SIZE AUTO让 Oracle 自动决定哪些列需要直方图直方图对数据分布不均匀的列特别重要。degree 4是并行度大表可以设高一点小表设 1 就行。cascade TRUE表示顺便收集索引统计信息这个一定要开不然索引统计信息过期照样出问题。4.2 直方图什么时候该建什么时候不该建直方图是给优化器看的“数据分布图”它告诉优化器某个列的值是不是均匀分布。比如status列如果 90% 的行都是DONE只有 10% 是PENDING那查PENDING走索引很快查DONE走全表扫描更快。没有直方图优化器就不知道这个分布可能两种查询都走索引结果查DONE的时候慢得要死。但直方图不是越多越好。每个直方图都会增加统计信息收集的时间和存储空间而且如果列的数据分布经常变直方图反而会误导优化器。我一般只对这几类列建直方图状态列、类型列、有大量 NULL 值的列、经常出现在WHERE条件里的列。其他列用默认的SIZE AUTO让 Oracle 自己决定就行。-- 查看列是否有直方图 SELECT column_name, num_distinct, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name ORDERS; -- 手动删除某个列的直方图 BEGIN DBMS_STATS.DELETE_COLUMN_STATS( ownname SCOTT, tabname ORDERS, colname STATUS, col_stat_type HISTOGRAM ); END; /USER_TAB_COL_STATISTICS里histogram列如果是NONE说明没直方图FREQUENCY或HEIGHT BALANCED说明有。删除直方图用DELETE_COLUMN_STATScol_stat_type HISTOGRAM表示只删直方图保留其他统计信息。4.3 执行计划绑定与 SQL Plan Baseline有时候 SQL 本身没问题统计信息也没问题但执行计划突然变了从索引扫描变成全表扫描性能一落千丈。这种情况多半是优化器因为某些原因比如绑定变量窥探选了一个错误的计划。解决办法是用 SQL Plan Baseline 把好的执行计划固定下来。-- 从游标缓存中加载执行计划到 Baseline DECLARE plans PLS_INTEGER; BEGIN plans : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id 你的sql_id, plan_hash_value 1234567890 ); DBMS_OUTPUT.PUT_LINE(加载了 || plans || 个计划); END; /plan_hash_value可以从V$SQL里查找到那个跑得快的计划对应的哈希值。加载到 Baseline 之后优化器以后只会从这个 Baseline 里选计划不会再乱选。如果以后有更好的计划可以手动把新的加进去旧的标记为不可用。这个机制在 Oracle 11g 以上都有是保证执行计划稳定性的后悔药。5. 避坑与排查那些让我半夜爬起来改 SQL 的坑5.1 绑定变量窥探导致计划突变现象同一条 SQL有时候快有时候慢执行计划在两个之间来回跳。原因Oracle 的绑定变量窥探Bind Peeking在第一次执行时会偷看绑定变量的值然后根据这个值生成计划。如果第一次传的是PENDING优化器觉得返回行少选了索引扫描下次传DONE返回行多索引扫描就慢了但计划已经固定了。解决在 11g 以上可以设ALTER SYSTEM SET _optim_peek_user_binds FALSE但更推荐用 SQL Plan Baseline 固定计划或者用DBMS_STATS收集更准确的统计信息让优化器自己判断。5.2 索引重建后统计信息没更新现象重建了索引但执行计划还是走全表扫描。原因ALTER INDEX ... REBUILD不会自动更新统计信息优化器还以为索引是旧的。解决重建索引后手动跑一次DBMS_STATS.GATHER_INDEX_STATS或者重建的时候加COMPUTE STATISTICS。这个坑我踩过不止一次明明索引刚重建性能却没变化查了半天才发现是统计信息的问题。5.3 并行度设太高导致资源争抢现象一条 SQL 设了PARALLEL 16结果跑起来把整个库的 CPU 都吃满了其他业务全卡住。原因并行查询会占用大量 CPU 和 I/O 资源如果同时有多个并行 SQL 在跑资源根本不够分。解决生产环境并行度一般不超过 4而且只在跑批时段用。可以用ALTER SESSION SET PARALLEL_DEGREE_POLICY AUTO让 Oracle 自动决定并行度或者用资源管理器Resource Manager限制并行消费者的数量。5.4 隐式游标导致存储过程性能差现象存储过程里一个循环跑了几个小时里面就一条简单的SELECT。原因循环里用了隐式游标每次循环都重新解析 SQL解析开销比执行开销还大。解决把SELECT改成显式游标用BULK COLLECT一次性取一批数据或者用FORALL批量插入。这个在 Oracle 存储过程优化里特别常见尤其是 EBS 那种老系统很多代码都是逐行处理的改成批量处理后性能能提升几十倍。5.5 监听日志太大导致连接变慢现象数据库连接越来越慢有时候还连不上。原因listener.log文件太大Oracle 监听器写日志的时候 I/O 等待变长。解决定期清理监听日志或者关掉监听日志。11g 以上可以用lsnrctl set log_status off关掉10g 的话直接删listener.log文件但删之前要先停监听。这个坑比较隐蔽因为连接慢一般人不会想到是监听日志的问题。6. 用 10053 事件看优化器到底在想什么前面讲的都是“优化器选错了怎么办”但有时候你更想知道“优化器为什么选错”。Oracle 的 10053 事件能把优化器的决策过程完整打印出来包括它算了哪些执行路径、每个路径的成本是多少、最后为什么选了那个最差的。这个工具算是 SQL 优化的终极黑匣子用好了能省很多猜的时间。开启 10053 事件的方法很简单在当前会话执行ALTER SESSION SET EVENTS 10053 trace name context forever, level 1然后跑那条慢 SQL跑完后在USER_DUMP_DEST目录下会生成一个.trc文件。文件里重点看SINGLE TABLE ACCESS PATH和JOIN ORDER两部分前者告诉你单表访问路径的成本计算后者告诉你连接顺序是怎么定的。-- 开启 10053 事件 ALTER SESSION SET EVENTS 10053 trace name context forever, level 1; -- 执行你的慢 SQL SELECT /* 你的SQL */ * FROM orders WHERE status PENDING; -- 关闭 10053 事件 ALTER SESSION SET EVENTS 10053 trace name context off; -- 查看 trace 文件位置 SELECT value FROM V$DIAG_INFO WHERE name Default Trace File;level 1是最详细的级别会打印所有成本计算过程。V$DIAG_INFO里的Default Trace File就是当前会话的 trace 文件路径。打开文件后搜索Best::开头的行那是优化器最终选定的计划再往上翻能看到它比较过的其他计划。如果发现优化器算出来的成本和你实际跑出来的时间完全对不上那多半是统计信息的问题或者系统统计信息CPU 成本、I/O 成本不准。我一般会在两种情况下用 10053一种是执行计划怎么调都不对想看看优化器到底卡在哪另一种是升级数据库版本后 SQL 突然变慢想对比新旧版本的优化器行为差异。这个工具的输出很长新手容易看晕建议先只看Best::那几行找到最终计划后再往回查为什么其他计划被淘汰了。最后一个习惯每次优化完一条 SQL我都会把原始 SQL、执行计划、优化后的 SQL、新的执行计划、性能对比数据记到一个表格里。时间长了这就是你自己的 SQL 优化案例库下次遇到类似问题直接翻记录比重新分析快得多。Oracle SQL 优化这件事工具和方法就那些真正拉开差距的是你有没有把每次踩的坑变成自己的经验。希望帮到你。本文还有配套的精品资源点击获取
