简介Oracle数据库性能优化是DBA与后端开发人员必须掌握的核心技能这份PDF文档聚焦高并发、大数据量场景下的常见性能瓶颈系统梳理了从内存分配、SQL写法到索引与执行计划的调优思路。内容以SGA内存参数调整为重点详解共享池、数据缓冲区与日志缓冲区的设置要点并给出1G内存下共享池150M-200M、最大不超过500M的参考值同时结合基于规则的优化器讲解驱动表选择、WHERE条件顺序、避免SELECT *等实用技巧。资源为单个PDF文档压缩包仅128KB内容紧凑易读适合已掌握Oracle基础、希望系统提升调优能力的学习者。已有1500人学习下载简明实用可作为日常数据库排障与性能优化的参考资料。1. 为什么先别急着调参数oracle数据库性能优化的第一步是分清楚瓶颈在谁接到一个oracle数据库性能优化的需求时我最怕的结论是把SGA调大一点。业务侧反馈慢、CPU冲高、订单积压最后落到DBA手里的往往只有一句数据库不行。可真动手查十次里有七次是SQL问题两次是I/O和内存配置问题剩下一次才是真需要扩容。如果不先分清楚瓶颈在谁参数一动原本能快速定位的线索就全被搅浑了。所以这篇笔记的路线是先拿执行计划把慢SQL揪出来再用AWR报告给整库做一次体检然后才谈SGA/PGA怎么调最后把反复踩过的坑列出来。这套顺序适合两类人被拉去救火的DBA以及要跟DBA配合改SQL的应用开发。跟着走完你至少能从库好慢推进到这条SQL在等什么、哪个参数不对。2. 从执行计划看SQL问题连接方式、统计信息与真实行数2.1 用 gather_plan_statistics 让执行计划说真话两条SQL的最小复现常见做法是先用EXPLAIN PLAN拿优化器预估的执行计划。但预估计划有个致命弱点它依赖统计信息统计信息一旦过期计划里的Rows和Cost全是假的。所以我一般会让SQL真跑一次用gather_plan_statistics提示让Oracle在真实执行时收集每一步的行数与耗时执行完再取计划。下面是最小的可复现步骤-- 实际执行一次SQL并让Oracle在内存里保存每一步的真实行数与耗时 SELECT /* gather_plan_statistics */ o.customer_id, SUM(o.amount) AS total_amount FROM orders o JOIN customers c ON c.customer_id o.customer_id WHERE o.order_date DATE 2025-01-01 GROUP BY o.customer_id; -- 执行完立刻取这条SQL刚才的真实执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT ALLSTATS LAST));关键在第二个查询的参数ALLSTATS LAST 会输出 Starts、E-Rows、A-Rows、A-Time、Buffers 这几列。E-Rows是优化器预估的行数A-Rows是真实行数Buffers是这一步消耗的逻辑读。对比E-Rows和A-Rows如果差出几个数量级基本就能断定执行计划选错了方向问题多半出在统计信息或数据分布上。Buffers高而E-Rows小则说明优化器认为这里只返回几行于是选了Nested Loop实际上数据量远不止代价被严重低估。这个动作是后面一切分析的基础。先把计划长什么样和真实行数是多少摆在桌面上再去谈调索引还是改连接方式才不会凭感觉下手。2.2 三种连接方式的代价信号Nested Loop 并不是慢的代名词很多刚接触执行计划的开发会一看到 Nested Loop 就紧张觉得 Hash Join 才是高级货。这里有个常见误解。Nested Loop 的适用条件是驱动集合返回少量行内层表通过索引以单块读方式逐行匹配如果驱动行数是几百走索引的 Nested Loop 可能只要几毫秒。问题从来不是连接方式本身而是优化器基于错误统计信息选了一种代价被严重低估的连接方式。判断该用哪种常见规则是驱动集很小且内层有高效索引选 Nested Loop两个集合都大、等值连接优先 Hash Join非等值连接或排序本就可复用Sort Merge Join 也有胜出场景。如果确认执行计划选错但又不想改SQL可以用 hint 强行纠正来验证是否是连接方式导致的偏差-- 用USE_HASH强制走哈希连接观察耗时变化只作为验证手段 SELECT /* LEADING(o c) USE_HASH(c) */ o.customer_id, SUM(o.amount) AS total_amount FROM orders o JOIN customers c ON c.customer_id o.customer_id WHERE o.order_date DATE 2025-01-01 GROUP BY o.customer_id;LEADING 指定驱动顺序USE_HASH 指定被驱动表走哈希连接。先记下原计划耗时再对比加了 hint 之后的耗时如果差距明显说明问题出在连接方式选型。hint 只是验证工具确认后建议重写SQL或刷新统计信息而不是把 hint 留在生产代码里长期运行——绑定变量变化后 hint 很可能会把执行计划锁死在另一个不合理的方向。三种访问路径的典型适用场景可以压成一张对照表排查时扫一眼就心里有数访问路径核心前提典型的翻车场景Nested Loop驱动行数小、内层走索引驱动表统计信息过时实际返回百万行内层逐行回表Hash Join两表都大、等值连接一边被错误估计为小表导致驱动顺序颠倒Sort Merge Join非等值连接、排序可复用无索引且重复排序大量数据临时表空间被撑爆2.3 统计信息过期执行计划翻车的头号原因让优化器错误估算的根源九成是统计信息不新鲜。Oracle 默认会在夜间维护窗口自动收集统计信息但开发环境手动造的数据、每天新增几十万行的流水表统计信息很可能在下一次自动收集之前就已经失真。重新收集统计信息是成本最低的挽救手段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 8, cascade TRUE ); END; /estimate_percent 用 AUTO_SAMPLE_SIZE 让 Oracle 按需采样大型表比固定百分比快得多method_opt 的 SIZE AUTO 让直方图只在优化器认为需要时生成避免小表也背一堆直方图开销cascade 为 TRUE 表示同时收集索引统计信息。对几十亿行的分区表我一般会加上 granularity AUTO 并按分区收集而不是整表一把梭否则可能跑上数小时。收集完成后重新执行 2.1 里的命令重点看 E-Rows 是否贴近 A-Rows。很多场景到这里 SQL 就自己好了不需要改一行代码。也可以先确认哪些表已经被 Oracle 标记为统计信息过期-- 查看表的统计信息状态STALE_STATSYES 说明需要重新收集 SELECT table_name, stale_stats, last_analyzed FROM dba_tab_statistics WHERE owner SCOTT AND table_name IN (ORDERS, CUSTOMERS);2.4 别忘了执行计划缓存v$SQL 里可能存着旧计划统计信息刷新后某些情况下 SQL 仍会走旧执行计划。原因通常是游标缓存里的计划被复用Oracle 没有判断出统计信息已变化。这时候可以手动让该 SQL 的游标失效-- 先找到目标SQL的SQL_ID SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE %orders% AND parsing_schema_name SCOTT; -- 让该SQL在共享池中的游标失效下次执行会重新硬解析 EXEC DBMS_SHARED_POOL.PURGE(SQL, 0g8m3y2z, C);DBMS_SHARED_POOL.PURGE 是让指定游标从共享池移除的常用做法配合统计信息更新一起操作。注意在线系统频繁做 PURGE 会导致硬解析变多、CPU 短暂上升建议低峰期操作。这条命令在 Oracle 10.2.0.4 之后才提供旧版本需要先用 alter system flush shared_pool代价是清掉整个共享池影响面大得多。3. 用AWR报告完成一轮性能体检DB Time与Top Event的读法3.1 手工生成AWR报告awrrpt.sql与快照选择如果连慢SQL都还没定位或者慢是整库慢而非一条SQL慢AWR 报告是下一个动作。AWR 快照默认每小时采集一次保留 8 天Oracle 已经把数据库各维度的汇总数据算好。生成报告用自带的 awrrpt 脚本sqlplus / as sysdba SQL ?/rdbms/admin/awrrpt.sql Enter value for report_type: html Enter value for num_days: 1 Enter value for begin_snap: 1254 Enter value for end_snap: 1279? 会自动展开成 ORACLE_HOME 路径。选 html 格式方便用浏览器打开并检索关键词num_days 填 1 表示列出最近一天的快照编号然后手工指定 begin_snap 和 end_snap。两个快照之间跨度不要超过一两天否则报告的平均负载会被闲时稀释等事件的噪声看不出问题。生成后报告会写到当前目录文件名形如 awrrpt_1_1254_1279.html。报告非常长新手一打开容易懵。按顺序读三块最前面的负载概览确认 DB Time 和 CPU 的关系Top 10 Foreground Events 看等待事件SQL Statistics by Elapsed Time 看消耗大户。后面两块读明白了性能问题的大方向也就定了。3.2 DB Time与Top Events先判断瓶颈在数据库内部还是外部AWR 首页有一排红绿灯指标重点看 DB Time 和 Elapsed Time 的关系。假设一个小时内数据库平均有 12 个活跃会话DB Time 是 12 小时如果墙钟时间只有 1 小时说明数据库内部消耗巨大反之如果墙钟时间 2 小时而 DB Time 只有 20 分钟那大部分时间花在了数据库外部应用逻辑、网络、客户端缓冲这时候去调数据库参数基本没有意义。接下来看 Top 10 Foreground Events 表。这张表把前台会话的等待时间按事件汇总是最直接定位瓶颈的入口。三个高频事件和对应动作血泪经验告诉我可以先背下来等待事件通常指向第一步动作db file sequential read索引单块读常见为索引选择差或索引缺失查关联SQL的执行计划确认索引是否被正确使用db file scattered read大表全表扫描或全分区扫描确认扫描是否合理考虑分区裁剪或索引log file syncCommit提交等待常见为redo写慢或提交频繁检查redo log大小与磁盘I/O延迟事件名不吓人关键在量的量级。一次全表扫描有几万次 db file scattered read 很正常但如果这个事件在 Total Wait Time 里占掉 40% 以上就得顺着 Top SQL 进去看是哪些 SQL 在反复全扫。3.3 从ASH把等待事件落回到具体SQL一个可复用的诊断SQLAWR 看到的是聚合结果ASH活动会话历史把每秒的采样保存下来能让我们反查某段时间每个正在等待的会话在跑什么 SQL。常见做法是查 v$active_session_history-- 找出最近一小时内产生log file sync等待最多的SQL_ID SELECT sql_id, COUNT(*) AS waits, ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) AS pct FROM v$active_session_history WHERE sample_time SYSDATE - INTERVAL 1 HOUR AND event log file sync GROUP BY sql_id ORDER BY waits DESC FETCH FIRST 5 ROWS ONLY;把 event 换成 Top Event 里的具体名称就能找出谁在制造这个等待。拿到 SQL_ID 后再从 v$sql 或 dba_hist_sqltext 取出完整 SQL 文本结合第 2 章的取计划方法一次性能问题的定位链条就闭环了。这个查询在 Oracle 12c 及以上能用 FETCH FIRST老版本改成 ROWNUM 5 的嵌套写法。3.4 从AWR的Top SQL直接跳到执行计划AWR 报告里 SQL Statistics by Elapsed Time 部分会列出 SQL_ID 和耗时占比这是定位慢SQL的另一条入口。很多经验不足的DBA会在这个环节停下来去开发那边问这条SQL在干什么正确的做法是直接从 SQL_ID 抓执行计划-- 通过SQL_ID取历史SQL文本和执行计划 SELECT sql_text FROM dba_hist_sqltext WHERE sql_id 0g8m3y2z; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(0g8m3y2z));DISPLAY_AWR 读的是历史快照里保存的计划即使当前游标已经被挤出共享池也能还原当时执行用的计划。这个函数不要求 SQL 现在还在缓存里对排查昨天凌晨为什么慢这类问题特别有用。4. 内存参数调整SGA、PGA配比与redo log的连带效应4.1 先看现状再动手v$parameter与v$sgastatSQL 和等待事件都看过了剩下的工作才是参数调整。在调整之前先用两个查询确认现状当前内存架构的参数值、以及 SGA 各组件实际占用-- 查看内存相关参数当前值确认是否启用了自动内存管理 SELECT name, value, isdefault, ismodified FROM v$parameter WHERE name IN (memory_target, memory_max_target, sga_target, sga_max_size, pga_aggregate_target); -- 查看SGA内部各池的实际占用评估是否有人为设置的极值 SELECT pool, SUM(bytes) / 1024 / 1024 AS mb FROM v$sgastat GROUP BY pool;v$parameter 的 ismodified 列会告诉你这些参数是默认值还是被改过。凡是看到 memory_target 大于 0说明库走的是自动内存管理SGA 和 PGA 都由 Oracle 按负载浮动分配这种情况下手工随便设 sga_target反而会关闭自动分配的逻辑引起其他组件收缩。先搞清楚现状是避免把配置越改越乱的第一步。4.2 SGA_TARGET和PGA_AGGREGATE_TARGET怎么设自动管理与手工干预的边界如果 memory_target 没启用或者 DBA 明确要手工控制内存分布常见做法是设定 sga_target 和 pga_aggregate_target。经验配比OLTP 系统 SGA 里主要是 buffer cache 和 shared poolPGA 主要用于排序、哈希连接SGA:PGA 按 70:30 到 80:20 分配如果系统大量跑报表、排序和并行查询PGA 占比可以提高到 40%。一次性修改的命令如下ALTER SYSTEM SET sga_max_size 16G SCOPE SPFILE; ALTER SYSTEM SET sga_target 16G SCOPE BOTH; ALTER SYSTEM SET pga_aggregate_target 4G SCOPE BOTH;sga_max_size 是上限sga_target 是自动管理下的目标值Oracle 会在各组件之间动态调整。SCOPEBOTH 表示立即生效并写入 spfileSCOPESPFILE 表示只写入 spfile、下次重启生效。注意sga_max_size 修改后不能低于当前 sga_target且一般只在停机窗口改。注意改完 sga_max_size 重启前先确认 OS 层的共享内存内核参数够大否则实例可能起不来。老库经常碰到参数没问题、系统内核 shmmax 不够导致启动失败的情况。调整后观察方向不是内存占满了没而是 v$sysmetric 里的 Buffer Cache Hit Ratio、以及 Top Events 里 db file sequential read 的变化。如果调大 SGA 后全表扫描事件没减少说明 SQL 本身在扫全表内存调多大都没用。4.3 redo log与log file sync一个被忽略的I/O瓶颈如果 Top Event 里 log file sync 居高不下而 SQL 本身并不复杂问题很可能不在内存而在 redo log。log file sync 等待的是 commit 提交时 LGWR 把日志写入磁盘的耗时redo log 太小会导致日志切换过于频繁进而拖慢 checkpoint 和写入性能。判断方法很直接查日志切换频率-- 查看最近24小时每小时日志切换次数 SELECT TO_CHAR(first_time, YYYY-MM-DD HH24) AS hour, COUNT(*) AS switches FROM v$log_history WHERE first_time SYSDATE - 1 GROUP BY TO_CHAR(first_time, YYYY-MM-DD HH24) ORDER BY 1;我一般的经验线任何一小时切换超过 4 到 5 次就要检查 redo log 大小了。加大 redo log 是治本方案操作前要清理归档空间并做一次日志切换否则当场触发归档目录积压。调整日志组还要保证每一组文件和成员分布在独立的物理磁盘上否则 I/O 还是挤在一个盘上。4.4 调参数后的24小时靠v$sysmetric判断是否改善参数改完不意味着结束。我一般会隔一天再看 v$sysmetric 的历史值-- 对比调整前后同一时间段的关键指标 SELECT metric_name, ROUND(average, 2) AS avg_value, maxval FROM v$sysmetric_history WHERE metric_name IN (Response Time Per Txn, CPU Usage Per Sec, Logical Reads Per Sec) AND group_id 2 ORDER BY metric_name;group_id2 代表近一小时的按分钟聚合指标。比较调整前后同时间段的曲线如果 CPU 占用和响应时间有明显收敛才算参数调整真正生效。只看某一瞬间的 Top Event 容易误判跨 24 小时对比才能排除业务高峰噪声。5. Oracle性能优化避坑清单五个反复踩到的坑5.1 现象统计信息刚收集完SQL反而更慢了原因全库统一用 FOR ALL COLUMNS SIZE 254 生成了大量直方图优化器被小样本里的数据偏差带偏或者 estimate_percent 固定为 5%、10% 这类低比例抽样数据不足以代表真实分布。解决对频繁变更的大表单独收集method_opt 用 SIZE AUTO收集完用 2.1 的方法对比 E-Rows 和 A-Rows确认误差收敛后再放回生产计划。5.2 现象同一个SQL今天走索引明天全表扫描原因绑定变量窥探。Oracle 在第一次硬解析时探测绑定变量值并据此生成计划后续变量值分布变化时旧计划继续被复用。确认当前是否有绑定敏感标记可以查 v$sql 的 bind-aware 相关列-- 查看该SQL的子游标是否启用了自适应游标共享 SELECT child_number, executions, bind_aware, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_id 0g8m3y2z;解决升级到 11g 以上并启用自适应游标共享ACS对无法回避的SQL用 SQL Plan Management 锁定稳定计划。这里最容易犯的错是看到计划变了就加 hint结果数据分布再变时又被 hint 锁死等于把问题从优化器误判变成了人为强制误判。5.3 现象索引明明建了执行计划里却看不到原因SQL 对索引列做了函数包裹比如 WHERE to_char(order_date, YYYY-MM-DD) 2025-01-01函数运算让优化器无法直接使用普通 B 树索引。解决改写为范围条件 order_date TO_DATE(2025-01-01, YYYY-MM-DD)如果 SQL 归属的应用不好改再考虑建函数索引-- 函数索引写法对表达式建索引 CREATE INDEX idx_orders_dt_str ON orders(TO_CHAR(order_date, YYYY-MM-DD));两个方案里改写 SQL 优先函数索引会带来额外的写入和存储成本而且统计信息收集时要确保包含了函数表达式列否则照样估不准。5.4 现象夜间批处理一到某个点就变慢白天正常原因默认维护窗口在晚上 10 点触发自动收集统计信息大批量更新和统计作业挤在一起抢占了 I/O 和 CPU。解决把维护窗口挪到业务低谷或者直接对批处理表锁定统计信息等批处理结束再手动收集-- 锁定统计信息阻止自动收集任务在批处理期间动它 EXEC DBMS_STATS.LOCK_TABLE_STATS(SCOTT, ORDERS); -- 批处理结束后手动收集一次 EXEC DBMS_STATS.GATHER_TABLE_STATS(SCOTT, ORDERS);白天的慢 SQL 不一定白天才有原因夜里自动作业与业务的争抢经常被忽略。遇到一到固定时间就慢的规律性故障第一反应应该是查那个时间点在跑什么定时任务而不是先把 SQL 拿出来分析一遍。5.5 现象shared_pool 被压到最小换来一个 ORA-04031原因有人在省内存思路下把 shared_pool_size 压到几百 MB结果共享池频繁收缩、SQL 硬解析激增最终内存碎片化报 ORA-04031。解决共享池不是越低越好也不是越高越好。先确认共享池是否真的有压力-- 检查shared pool reserved区域是否存在持续的压力 SELECT free_space, free_count, request_failures FROM v$shared_pool_reserved;request_failures 如果持续增长说明共享池确实不够。按库大小放到 2GB 到 8GB 的正常区间再观察硬解析是否下降。这种参数越小越好的调优直觉是典型的玄学调参调之前先确认业务负载模型比动参数靠谱得多。6. 把AWR的Top SQL变成每周例行巡检一套可落地的脚本前面所有的排查都发生在故障出现后我更倾向的做法是把其中可复用的部分固化成巡检脚本每周定时执行让性能问题在用户投诉之前暴露。最常见的做法是把 v$sql 里的高消耗 SQL 和等待事件变化汇总成一张表-- 每周巡检按单次平均耗时排序找出最值得追的慢SQL SELECT sql_id, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec, ROUND(cpu_time / 1000000, 2) AS cpu_sec, disk_reads, executions, ROUND(elapsed_time / 1000000 / DECODE(executions, 0, 1, executions), 4) AS avg_sec FROM v$sql WHERE elapsed_time 100000000 -- 累计耗时超过100秒的SQL AND executions 0 ORDER BY avg_sec DESC FETCH FIRST 20 ROWS ONLY;这里关注的不是总耗时而是单次平均耗时。累计耗时长可能是执行次数多单次平均耗时高才是真正值得追的慢 SQL。每周固定看一次这张表再配合一份 AWR 报告对比快照间的 DB Time 变化基本能覆盖日常性能巡检的核心。我自己的习惯是每月做一次完整 AWR 对比日常只盯这个 20 行清单发现新面孔 SQL 出现就用第 2 章的方法取执行计划。这套做法的价值在于把性能优化从一次救火变成常态化的数据积累积累三个月后哪些表在膨胀、哪些 SQL 在退化对比历史记录一眼就能看出来。希望这套基于执行计划、AWR 和参数验证的思路能帮到你下次接到数据库慢的需求至少不会是从调 SGA 开始。本文还有配套的精品资源点击获取