简介这份Oracle数据库性能优化PDF文档面向数据库管理员、后端开发及运维人员聚焦大数据量与高并发场景下系统响应变慢、资源争用等实际问题帮助读者建立从内存参数到SQL语句的系统化调优思路。资源包共1个PDF文件约128KB内容围绕数据库服务器内存分配与SQL优化两大主线展开涵盖系统全局区中共享池、数据缓冲区、日志缓冲区的参数设定建议以及基于规则优化器下驱动表选择、WHERE子句条件顺序、避免SELECT *、用WHERE替代HAVING等具体技巧并延伸至索引管理、分区策略、回滚段优化与执行计划控制等方向。目前已有1501人学习下载适合希望快速掌握Oracle调优要点、对照实际系统排查性能瓶颈的初中级技术人员参考。1. Oracle 数据库性能优化从一条慢 SQL 到整库吞吐翻倍的排查路径凌晨两点被叫起来处理生产库 CPU 打满登录一看 AWR 报告里一条 SQL 执行了 40 万次单次逻辑读 8000 多——这种场景做 Oracle 运维的基本都遇到过。Oracle 数据库性能优化不是调几个隐藏参数就能搞定的事它是一条从定位瓶颈、读懂执行计划、调整索引与 SQL 写法再到实例级参数和存储层配置的完整链路。这篇笔记面向已经能写 SQL、会基本的sqlplus操作但面对生产库性能问题时不知道从哪下手的 DBA 和后端开发。我会按自己实际排查的顺序把每一步的命令、参数含义和判断依据讲清楚包括 oracle sql 性能优化里最容易翻车的几个点以及 oracle 存储过程、分页查询这些高频场景的具体处理方式。看完你至少能独立完成一次从告警到定位到验证的完整优化闭环。2. 先定位再动手Oracle 性能问题的分层排查方法性能优化最忌讳上来就改参数。Oracle 的性能问题大致分四层SQL 层、会话/锁层、实例层、OS/存储层。排查顺序应该从最靠近业务的那层往上走因为越上层的问题越容易复现和验证改动风险也越小。2.1 用 AWR 和 ASH 锁定问题时段与 Top 等待事件AWRAutomatic Workload Repository是 Oracle 自带的性能快照仓库默认每小时采集一次保留 8 天。拿到性能告警后第一件事是确定问题时间段然后生成对应的 AWR 报告。-- 查看可用的 AWR 快照确定问题时段对应的 snap_id SELECT snap_id, TO_CHAR(begin_interval_time, YYYY-MM-DD HH24:MI) AS begin_time, TO_CHAR(end_interval_time, YYYY-MM-DD HH24:MI) AS end_time FROM dba_hist_snapshot WHERE begin_interval_time SYSDATE - 1 ORDER BY snap_id;找到问题时段的起止snap_id后用awrrpt.sql脚本生成报告# 在 sqlplus 中执行按提示输入 snap_id 范围和报告格式 sqlplus / as sysdba SQL ?/rdbms/admin/awrrpt.sql报告拿到手重点看三块Top 10 Foreground Events等待事件排名、SQL ordered by Elapsed Time耗时 SQL 排名、SQL ordered by Gets逻辑读排名。如果 DB CPU 排第一且占比超过 60%说明瓶颈在 CPU 计算上大概率是 SQL 本身写得有问题如果db file sequential read排前面说明大量单块读索引效率可能不够如果enq: TX - row lock contention出现那就是锁竞争得去看具体会话。ASHActive Session History比 AWR 更细采样粒度到秒级适合分析短时突发问题-- 查看过去 30 分钟内等待事件的时间分布 SELECT event, COUNT(*) AS samples, ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) AS pct FROM v$active_session_history WHERE sample_time SYSDATE - 30/1440 GROUP BY event ORDER BY samples DESC FETCH FIRST 15 ROWS ONLY;这里30/1440是 30 分钟Oracle 里日期减法单位是天。FETCH FIRST是 12c 及以上版本的写法11g 需要用ROWNUM嵌套子查询。2.2 从 v$sql 和执行计划里找到真正的元凶AWR 报告给出的是宏观排名要精确定位还得查v$sql和v$sqlarea。这两个视图的区别是v$sqlarea按 SQL 文本聚合v$sql按每个子游标一行。排查时先用v$sqlarea找到高消耗的 SQL 文本再用v$sql看它的多个执行计划。-- 按逻辑读排序找出消耗最高的 SQL SELECT sql_id, executions, ROUND(elapsed_time / 1e6, 2) AS elapsed_sec, ROUND(buffer_gets / executions, 0) AS gets_per_exec, ROUND(cpu_time / 1e6, 2) AS cpu_sec, SUBSTR(sql_text, 1, 80) AS sql_snippet FROM v$sqlarea WHERE executions 0 ORDER BY buffer_gets DESC FETCH FIRST 20 ROWS ONLY;拿到sql_id后看执行计划-- 查看指定 sql_id 的执行计划displays 的 allstats 会带上实际行数 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, NULL, ALLSTATS LAST));执行计划里重点看几个信号TABLE ACCESS FULL出现在大表上、Estimate行数和A-Rows实际行数差了几个数量级、Nested Loops驱动表返回行数过多导致被驱动表反复扫描。这些基本就是 SQL 性能问题的根因。提示DBMS_XPLAN.DISPLAY_CURSOR的第三个参数用ALLSTATS LAST需要statistics_level参数为TYPICAL或ALL默认TYPICAL就够用。如果显示不了实际行数检查一下v$sql_plan_statistics_all是否有数据。3. SQL 与索引优化执行计划里那些反复出现的坑定位到具体 SQL 之后优化手段无非几种改 SQL 写法、加/改索引、收集统计信息、用 SQL Plan Baseline 固定计划。这一章按我实际处理频率从高到低展开。3.1 索引失效的六种典型场景与验证方法索引失效是 SQL 性能问题里最常见的原因。以下六种情况在 oracle sql 性能优化中出现频率极高第一种在索引列上做函数运算。WHERE TO_CHAR(create_time,YYYYMMDD) 20240101这种写法会让 B-Tree 索引完全用不上。改成WHERE create_time DATE2024-01-01 AND create_time DATE2024-01-02就能走索引范围扫描。第二种隐式类型转换。WHERE order_no 12345而order_no是 VARCHAR2 类型时Oracle 会把列隐式转成数字索引失效。必须写成WHERE order_no 12345。第三种前导通配符。LIKE %keyword无法使用普通 B-Tree 索引。如果确实需要模糊搜索考虑 Oracle Text 索引或者把需求改成前缀匹配。第四种NOT IN、!、NOT EXISTS在大多数情况下不走索引。NOT EXISTS有时可以通过改写为LEFT JOIN ... WHERE ... IS NULL来改善。第五种联合索引的最左前缀原则。索引建在(a, b, c)上查询条件只有b和c时用不上这个索引。需要根据实际查询模式调整索引列顺序或补建索引。第六种统计信息过期。优化器基于统计信息选执行计划如果表数据变化很大但统计信息没更新可能选错计划。检查方法-- 查看表的统计信息最后收集时间 SELECT table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics WHERE table_name YOUR_TABLE;STALE_STATS为YES就说明统计信息过期了。收集命令-- 收集表及其索引的统计信息采样比例根据表大小调整 BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname YOUR_SCHEMA, tabname YOUR_TABLE, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, cascade TRUE, degree 4 ); END; /estimate_percent用AUTO_SAMPLE_SIZE让 Oracle 自己决定采样率cascade TRUE表示同时收集索引统计信息degree是并行度。大表收集统计信息建议放在业务低峰期避免消耗过多资源。3.2 分页查询与存储过程的性能写法Oracle 分页是热搜里反复出现的话题。传统写法用ROWNUM嵌套两层-- 传统 ROWNUM 分页取第 10001 到 10020 条 SELECT * FROM ( SELECT a.*, ROWNUM rn FROM ( SELECT id, name, create_time FROM orders WHERE status ACTIVE ORDER BY create_time DESC ) a WHERE ROWNUM 10020 ) WHERE rn 10000;12c 及以上版本可以用OFFSET ... FETCH-- 12c 分页写法语义更清晰 SELECT id, name, create_time FROM orders WHERE status ACTIVE ORDER BY create_time DESC OFFSET 10000 ROWS FETCH NEXT 20 ROWS ONLY;两种写法在深层分页时性能都不好因为要扫描并丢弃前 10000 行。真正的优化思路是记住上一页最后一条记录的排序键值用WHERE create_time :last_time来定位起点这就是所谓的键集分页keyset pagination。在数据量大、翻页深的场景下键集分页能把响应时间从秒级降到毫秒级。存储过程方面最常见的性能问题是循环里逐行 DML。比如在FOR ... LOOP里对同一张表反复INSERT每次都是一次上下文切换。改成BULK COLLECTFORALL批量处理-- 批量绑定写法减少 PL/SQL 引擎与 SQL 引擎的上下文切换 DECLARE TYPE t_id IS TABLE OF orders.id%TYPE; TYPE t_name IS TABLE OF orders.name%TYPE; l_ids t_id; l_names t_name; CURSOR c IS SELECT id, name FROM orders WHERE status PENDING; BEGIN OPEN c; LOOP FETCH c BULK COLLECT INTO l_ids, l_names LIMIT 500; EXIT WHEN l_ids.COUNT 0; FORALL i IN 1 .. l_ids.COUNT UPDATE orders SET status DONE WHERE id l_ids(i); COMMIT; END LOOP; CLOSE c; END; /LIMIT 500控制每批取多少行太小则批次多、开销大太大则 PGA 内存占用高。一般 200 到 1000 之间比较合适具体看行宽和 PGA 配置。FORALL一次性把整个集合提交给 SQL 引擎比逐行UPDATE快一个数量级。3.3 用 SQL Plan Baseline 锁住好计划有时候 SQL 文本没变、统计信息也正常但执行计划突然变差了。这种情况在 Oracle 11g 以后可以用 SQL Plan Baseline 把好的执行计划固定下来-- 从游标缓存中为指定 sql_id 创建 baseline DECLARE l_plans PLS_INTEGER; BEGIN l_plans : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id sql_id ); DBMS_OUTPUT.PUT_LINE(Loaded plans: || l_plans); END; /创建后优化器会优先选择 baseline 里的计划。如果后续有更好的计划可以手动演进 baseline-- 验证并接受新的执行计划 DECLARE l_report CLOB; BEGIN l_report : DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle sql_handle, plan_name plan_name, verify YES, commit YES ); DBMS_OUTPUT.PUT_LINE(l_report); END; /verify YES表示实际执行新计划并对比性能只有性能不差于原计划才会被接受。这个机制相当于给执行计划上了个后悔药避免升级或统计信息变化导致计划突变。4. 实例级参数与内存配置SGA、PGA 和那些不能乱动的隐藏参数SQL 层优化做完之后如果系统整体吞吐还是上不去就要看实例级配置了。这一块改动影响面大必须谨慎。4.1 SGA 与 PGA 的分配逻辑和调整方法Oracle 内存分两大块SGASystem Global Area和 PGAProgram Global Area。SGA 是所有会话共享的主要包括 Buffer Cache、Shared Pool、Large Pool、Java Pool 等。PGA 是每个服务进程私有的主要用于排序、哈希连接等操作。从 11g 开始Oracle 支持自动内存管理AMM和自动共享内存管理ASMM。检查当前配置-- 查看内存相关参数 SELECT name, value, isdefault FROM v$parameter WHERE name IN (sga_target,sga_max_size,pga_aggregate_target, memory_target,memory_max_target,db_cache_size, shared_pool_size) ORDER BY name;如果memory_target非零说明开了 AMMSGA 和 PGA 都由 Oracle 自动调配。如果memory_target为零而sga_target非零说明是 ASMMSGA 自动管理但 PGA 需要手动设pga_aggregate_target。判断 SGA 各组件是否合理看 Buffer Cache 的命中率-- Buffer Cache 命中率一般应高于 95% SELECT ROUND((1 - (physical_reads / (db_block_gets consistent_gets))) * 100, 2) AS hit_pct FROM v$buffer_pool_statistics WHERE name DEFAULT;Shared Pool 的命中率-- Shared Pool 命中率一般应高于 95% SELECT ROUND((1 - (SUM(reloads) / SUM(pins))) * 100, 2) AS hit_pct FROM v$librarycache;PGA 方面看v$pgastat里的over allocation count如果这个值持续增长说明pga_aggregate_target设小了-- PGA 过度分配统计 SELECT name, value FROM v$pgastat WHERE name IN (aggregate PGA target parameter, aggregate PGA auto target, over allocation count, cache hit percentage);调整 SGA 大小时注意sga_max_size是上限修改需要重启实例sga_target是目标值可以动态调整但不能超过sga_max_size。在 ASMM 模式下如果某个组件比如 Shared Pool频繁出现 ORA-04031 错误可以给它设一个最小值-- 为 Shared Pool 设置最小保留大小 ALTER SYSTEM SET shared_pool_size 2G SCOPE BOTH;SCOPE BOTH表示同时修改内存和 spfile重启后依然生效。SCOPE MEMORY只改当前实例重启失效。4.2 那些看起来能提速但实际会翻车的参数网上流传很多“Oracle 性能优化必调参数”但其中不少在生产环境是有风险的。optimizer_index_cost_adj默认值 100调低会让优化器更倾向走索引。有人把它设成 10 甚至 1 来“强制走索引”结果导致大量本该全表扫描的 SQL 走了索引反而更慢。这个参数应该保持默认除非你非常清楚自己在做什么。cursor_sharing设成FORCE可以让文本不同但结构相同的 SQL 共享游标减少硬解析。但它会导致绑定变量窥探问题某些 SQL 可能因为绑定了非代表性值而选错计划。默认EXACT在绝大多数场景下是安全的。_hidden_parametersOracle 有大量以下划线开头的隐藏参数比如_optimizer_peek_user_binds、_serial_direct_read等。这些参数没有官方文档支持不同版本行为可能不同生产环境不建议修改。如果确实需要必须在 Oracle Support 确认后再动。db_file_multiblock_read_count控制全表扫描时一次读多少个块。自动存储管理ASM下 Oracle 会自动调整手动设置反而可能干扰。如果非要设一般 16 到 128 之间超过 128 在大多数存储上不会带来额外收益。注意任何实例级参数修改前先在测试库验证并记录修改前的值。生产环境修改用SCOPE MEMORY先试观察至少一个业务周期再决定是否写入 spfile。5. 避坑与排查Oracle 性能优化中那些血泪教训这一章记录几个我在实际工作中踩过的坑每个都按现象、原因、解决三段来说。5.1 统计信息收集导致业务卡顿现象夜间自动统计信息收集任务运行时业务系统出现间歇性卡顿部分 SQL 响应时间从毫秒级涨到秒级。原因DBMS_STATS收集统计信息时会读取大量数据块消耗大量 I/O 和 CPU。如果表很大且没有设置并行度或采样率收集过程可能持续数小时期间与业务查询争抢资源。解决对大表设置合理的采样率和并行度并避开业务高峰。可以用DBMS_STATS.SET_TABLE_PREFS为特定表设置收集策略-- 为大表设置 10% 采样率和并行度 4 BEGIN DBMS_STATS.SET_TABLE_PREFS( ownname YOUR_SCHEMA, tabname BIG_TABLE, pname ESTIMATE_PERCENT, pvalue 10 ); DBMS_STATS.SET_TABLE_PREFS( ownname YOUR_SCHEMA, tabname BIG_TABLE, pname DEGREE, pvalue 4 ); END; /5.2 绑定变量窥探导致执行计划突变现象同一条 SQL 在v$sql里有多个子游标不同子游标执行计划不同有的快有的慢。业务侧表现为同一个功能时快时慢。原因Oracle 的绑定变量窥探bind peeking在第一次硬解析时会查看绑定变量的值据此选择执行计划。如果第一次传入的值恰好是数据分布中的极端值比如某个状态码只占 0.1% 的数据优化器可能选择索引扫描但后续传入的值占 90% 数据时索引扫描就非常慢。解决11g 及以上版本可以用自适应游标共享ACS让 Oracle 为不同绑定变量值维护多个子游标。检查 ACS 是否生效-- 查看 SQL 的子游标和是否启用了 ACS SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, plan_hash_value FROM v$sql WHERE sql_id sql_id;IS_BIND_AWARE为Y说明 ACS 已生效。如果还是有问题可以考虑用 SQL Plan Baseline 固定计划或者改写 SQL 让绑定变量不影响计划选择。5.3 索引太多导致 DML 性能下降现象为了优化查询加了很多索引查询确实快了但插入和更新操作越来越慢批量导入作业从 10 分钟涨到 40 分钟。原因每次 DML 操作都需要维护表上的所有索引。索引越多DML 开销越大。而且很多索引可能从未被使用过纯粹是负担。解决定期检查索引使用情况删除无用索引-- 查看索引使用统计需要先开启索引监控 ALTER INDEX your_schema.idx_name MONITORING USAGE; -- 一段时间后查看 SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring FROM v$object_usage WHERE used NO;USED NO且监控时间足够长的索引可以考虑删除。删除前确认没有 SQL 依赖它可以用DBMS_XPLAN或 SQL Tuning Advisor 验证。5.4 全表扫描不一定是坏事现象看到执行计划里有TABLE ACCESS FULL就紧张想方设法加索引消除它。原因全表扫描在多块读db_file_multiblock_read_count加持下读大量数据时效率可能高于索引扫描。索引扫描需要先读索引块再回表读数据块当需要返回的数据占表比例较高时一般超过 5% 到 10%全表扫描反而更快。解决不要盲目消除全表扫描。用DBMS_XPLAN看实际行数和逻辑读如果全表扫描的逻辑读在可接受范围内且响应时间满足要求就不需要改。优化目标是降低响应时间和资源消耗不是消灭某个特定的执行计划操作。5.5 RAC 环境下的序列争用现象RAC 环境下多节点同时插入数据时序列SEQUENCE成为瓶颈enq: SQ - contention等待事件频繁出现。原因默认情况下序列的CACHE值较小且 RAC 节点间需要协调序列值的分配。如果ORDER属性为YES还会强制全局有序进一步加剧争用。解决增大序列的CACHE值并考虑去掉ORDER属性如果业务不要求全局严格有序-- 修改序列缓存大小为 1000去掉 ORDER ALTER SEQUENCE your_seq CACHE 1000 NOORDER;CACHE值根据每秒插入量来定一般设为每秒插入量的 10 到 20 倍。NOORDER在 RAC 下允许各节点独立缓存序列值大幅减少争用。代价是序列值可能不连续如果业务对此有要求就不能用。6. 用 SQL Tuning Advisor 和实时监控把优化变成可重复的流程前面讲的都是手动排查的方法。实际工作中Oracle 自带的 SQL Tuning Advisor 可以自动化一部分分析工作配合实时 SQL 监控能把优化从“靠经验”变成“有流程”。6.1 用 SQL Tuning Advisor 自动分析问题 SQLSQL Tuning Advisor 会对指定的 SQL 做全面分析包括统计信息检查、索引建议、SQL 改写建议、执行计划分析等。调用方式-- 创建优化任务 DECLARE l_task_name VARCHAR2(100); BEGIN l_task_name : DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id sql_id, scope DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, time_limit 300, task_name tune_ || sql_id ); DBMS_OUTPUT.PUT_LINE(Task: || l_task_name); END; / -- 执行任务 BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name tune_sql_id); END; / -- 查看建议 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(tune_sql_id) FROM dual;SCOPE_COMPREHENSIVE表示做全面分析time_limit是分析时间上限秒。报告里会给出具体的建议和预期收益比如“建议创建索引 XXX预计性能提升 90%”。但要注意Advisor 的建议不能无脑执行索引建议要结合现有索引和 DML 频率综合判断。6.2 实时 SQL 监控抓取正在执行的慢 SQL对于执行时间超过 5 秒的 SQLOracle 会自动启用实时 SQL 监控。也可以手动开启-- 开启会话的实时 SQL 监控 ALTER SESSION SET statistics_level ALL; -- 或者用 MONITOR 提示 SELECT /* MONITOR */ COUNT(*) FROM big_table WHERE status ACTIVE;执行期间可以查看实时进度-- 查看正在监控的 SQL SELECT sql_id, status, elapsed_time/1e6 AS elapsed_sec, cpu_time/1e6 AS cpu_sec, buffer_gets, disk_reads, plan_hash_value, sql_text FROM v$sql_monitor WHERE status EXECUTING;执行完成后可以用DBMS_SQL_MONITOR.REPORT_SQL_MONITOR生成详细报告里面包含每个执行步骤的实际行数、时间消耗和等待事件比DBMS_XPLAN的ALLSTATS更详细。6.3 建立自己的性能基线表最后分享一个我自己的习惯建一张性能基线表定期记录关键 SQL 的响应时间和执行计划哈希值。这样当性能突然下降时可以快速对比是哪个环节变了。-- 创建性能基线记录表 CREATE TABLE perf_baseline ( snap_time DATE DEFAULT SYSDATE, sql_id VARCHAR2(13), plan_hash NUMBER, avg_elapsed NUMBER, -- 平均响应时间微秒 avg_gets NUMBER, -- 平均逻辑读 executions NUMBER, note VARCHAR2(200) ); -- 定期采集可以做成定时任务 INSERT INTO perf_baseline (sql_id, plan_hash, avg_elapsed, avg_gets, executions) SELECT sql_id, plan_hash_value, ROUND(elapsed_time / DECODE(executions, 0, 1, executions), 0), ROUND(buffer_gets / DECODE(executions, 0, 1, executions), 0), executions FROM v$sqlarea WHERE sql_id IN (sql_id_1, sql_id_2, sql_id_3) -- 替换为你的关键 SQL AND executions 0;当告警发生时先查这张表对比历史数据。如果plan_hash变了说明执行计划变了去查统计信息或绑定变量如果plan_hash没变但avg_gets涨了说明数据量或数据分布变了可能需要调整索引或收集统计信息。这个习惯帮我省了很多次盲猜的时间。提示v$sqlarea里的数据会随游标老化而消失采集频率建议至少每天一次关键系统可以每小时一次。采集脚本本身要轻量避免给系统增加额外负担。这套流程跑顺之后大部分 Oracle 性能问题都能在 30 分钟内定位到根因。真正花时间的是验证改动效果和评估影响面这部分没有捷径只能靠对业务的理解和谨慎的变更流程。希望帮到你。本文还有配套的精品资源点击获取