简介Oracle数据库性能优化是保障系统稳定高效运行的核心环节尤其适合大数据量和高并发的业务环境。文档系统梳理了Oracle数据库调优的主要方面面向数据库管理员、开发人员及相关运维工程师帮助读者建立从内存配置到SQL写法再到系统层调优的完整知识框架。压缩包内为单个PDF文档整体仅128KB内容精炼便携方便随时查阅当前已有1500人学习下载。内容重点包括数据库服务器SGA内存调整讲解共享池、数据缓冲区、日志缓冲区的配置原理并给出系统内存为1G时共享池建议150M-200M等参考数值SQL语句优化部分说明基于规则的优化器中驱动表的选择、WHERE条件过滤顺序、避免SELECT *、优先用WHERE代替HAVING等技巧此外还覆盖索引建立、分区策略、回滚段管理、执行计划控制以及操作系统参数和网络性能调优。整体而言可作为DBA日常调优和排查性能瓶颈的系统参考。1. Oracle 数据库性能优化为什么先动 SGA 再改 SQLOracle 数据库的性能问题十有八九最后落在两个点上内存里放不下SQL 写得不划算。这份《Oracle数据库性能优化》PDF 就是围绕这两件事展开的——前面讲系统全局区SGA里共享池和数据缓冲区怎么取值后面讲驱动表、WHERE 顺序、SELECT * 这些改写技巧对解析成本的影响。我拿着线上库 CPU 高、慢查询扎堆的现场去对照读读完最大的收获是优化是有先后顺序的先定内存边界再改 SQL最后才轮到操作系统和网络。它适合正在带 Oracle 库的 DBA、运维转数据库方向的工程师也适合写 SQL 老觉得执行计划不听话的应用开发——案例是老了些但今天的坑大多还能在这份材料里找到原型。2. SGA 调整共享池 150–200M 起步缓冲区命中率别只看数字数据库服务器内存参数调整是 PDF 里点名的第一个调优方向。Oracle 实例启动后SGA 会按参数把内存分给固定区域、共享池、数据缓冲区、日志缓冲区等几个部分。应用发来的每条 SQL、每次数据读取都要经过这几块内存所以说 SGA 是性能的第一道闸门。2.1 共享池先看解析过程再定参数共享池存放最近使用过的 SQL、PL/SQL 代码和数据字典里的元数据。它由库高速缓存Library Cache和数据字典缓冲区Data Dictionary Cache组成。Oracle 收到一条 SQL先做语法分析、权限确认再交给优化器生成执行计划最后才真正执行。这个过程在共享池足够大的时候可以被跳过——相同文本的 SQL 再次进来直接命中缓存的执行计划省掉重复解析的时间。这就是为什么共享池是“对性能影响显著”的首选调整对象。PDF 给了一条很实用的经验值系统内存 1G 时共享池设 150M–200M内存每增加 1G它的值加 100M但上限不要超过 500M。这条经验我建议直接抄进自己的优化手册。它的依据是 LRU 算法共享池内部用最近最少使用算法淘汰旧条目缓存命中率与池子大小不是线性关系超过某个临界点后Oracle 维护内部结构free lists、lru chains的开销反而比省下来的解析时间更贵。另一个风险是共享池偏大会挤压操作系统的可用内存Oracle 进程一旦被换出到虚拟内存系统整体响应会断崖式下跌。在具体操作前先把当前占用和命中情况摸清楚-- 查看共享池各区域占用按字节倒序排 SELECT pool, name, ROUND(bytes / 1024 / 1024, 2) MB FROM v$sgastat WHERE pool shared pool ORDER BY bytes DESC; -- 查看库高速缓存命中情况 SELECT namespace, gets, gethitratio, pins, pinhitratio FROM v$librarycache;第一段 SQL 用来确认共享池里谁是“大户”正常情况下 library cache 和 sql area 占大头如果 free memory 长期很小说明池子偏紧。第二段 SQL 中gets 是解析请求次数gethitratio 表示该 namespace 下直接命中缓存的比率pins 是执行阶段引用次数pinhitratio 是执行期命中率。这里没有统一的“健康线”但 gethitratio 低于 90% 时值得考虑加大 shared_pool_size。2.2 数据缓冲区物理读与逻辑读的换算数据缓冲区DB Buffer Cache缓存从磁盘读出来的数据块。理解它很简单缓冲块越大第二次访问同一块数据就不用回磁盘直接命中内存。物理读少一批响应时间自然下来。不过缓冲区也服从边际收益递减。假设一个 20G 的库缓存 2G 时物理读明显下降但从 2G 加到 4G收益可能只有前一段的五分之一。PDF 原文强调“缓冲区越大存放的共享数据就越多”但没提醒你“大过头会挤压操作系统”。实际运维中我一般把 SGA 总和控制在物理内存的 60%–70%剩下留给 PGA 和操作系统文件缓存。想看当前全库的缓冲区命中率可以用这条经典 SQL-- 缓冲区命中率注意这只是总体参考值 SELECT 1 - (phy.value / (cur.value con.value)) AS buffer_hit_ratio FROM v$sysstat phy, v$sysstat cur, v$sysstat con WHERE phy.name physical reads AND cur.name db block gets AND con.name consistent gets;公式的含义是物理读次数除以逻辑读总数再用 1 减去得到命中率。db block gets 是当前模式读consistent gets 是一致性读两者相加是逻辑读总量。这条语句放在监控脚本里可以但别把它当成调优结论的唯一下判断依据——一个全表扫描如果数据全在内存里命中率照样很高可 CPU 一样被扫描动作吃满。2.3 用 v$db_cache_advice 判断加不加缓存比较稳妥的做法是让 Oracle 自己算一笔账。v$db_cache_advice 会为不同缓存大小预测物理读变化这是调优时少有的“先看预测再动手”手段-- 查看不同缓存大小对应的物理读预测 SELECT size_for_estimate, estd_physical_read_factor, estd_physical_reads FROM v$db_cache_advice WHERE name DEFAULT AND block_size 8192 ORDER BY size_for_estimate;size_for_estimate 是假设的缓存大小estd_physical_read_factor 是相对当前值的物理读比例estd_physical_reads 是估算出的物理读次数。如果 size_for_estimate 从 1G 涨到 2G 时 factor 从 1.0 掉到 0.6说明加大缓存回报不错如果 2G 到 3G 只降到 0.55说明增量收益已经很薄。我一般选曲线“膝盖”位置而不是追 100% 命中。2.4 参数落地alter system 与 scope 取舍跑完诊断真正改参数时先确认实例是从 spfile 启动还是 pfile 启动这决定了你改完能不能持久-- 确认参数文件方式VALUE 不为空说明用了 spfile SHOW PARAMETER spfile; -- 查看当前相关参数 SELECT name, value, isdefault FROM v$parameter WHERE name IN (shared_pool_size, db_cache_size, log_buffer);如果要从手动管理切到自动管理常见做法是先定 sga_target让 Oracle 自动调配各池如果库上跑的 SQL 特征非常稳定也可以继续用手动值-- 修改共享池希望立即生效并写入 spfile用 SCOPEBOTH ALTER SYSTEM SET shared_pool_size 300M SCOPE BOTH; -- 修改数据缓冲区接受重启生效用 SCOPESPFILE ALTER SYSTEM SET db_cache_size 1536M SCOPE SPFILE;SCOPE 三个取值要分清楚MEMORY 只改当前实例重启即丢SPFILE 只写服务器参数文件要重启才生效BOTH 是当前值和文件都改。注意静态参数不能带 BOTH比如 log_buffer 这类需要重启的参数带 SCOPEBOTH 会直接报错。日志缓冲区一般不建议手工乱调交给 Oracle 默认值在绝大多数场景下更稳。提示改 SGA 相关的关键参数前先执行CREATE PFILE/tmp/pfile_backup.ora FROM SPFILE;留一份后悔药。线上改参最怕改完想回头手里却没有原始配置。参数调整不是一锤子买卖。每次只动一个参数观察 5 到 10 分钟比较 AWR 报告里 physical reads、logical reads、db time 这三个指标的变化再决定下一步。一次改三个参数出了问题你根本不知道是哪个惹的祸。3. SQL 改写驱动表、WHERE 顺序、SELECT * 与 HAVING 的取舍应用代码最终都归结为数据库里的 SQL 执行。同样的业务SQL 写法不同执行成本可能差出几十倍。PDF 里对 SQL 优化的讲解是基于规则优化器RBO时代的习惯核心就四件事驱动表放哪、WHERE 条件怎么排序、要不要写 SELECT *、WHERE 和 HAVING 怎么分工。这些结论放到现在部分已经失效但思考方式完全能平移。3.1 驱动表from 子句的解析顺序与选择在基于规则的优化器里Oracle 对 FROM 子句按从右到左解析排在最后的表最先被处理这张表就是驱动表。驱动表决定连接的起点它产生多少行后续表就要被访问多少次。PDF 给了一条硬规则选择记录条数少的表作为驱动表放在 FROM 子句最后。FROM 里有 3 张以上表时把连接其他表的“交叉表”作为驱动表。这条规矩背后的逻辑很好懂。以最常见的嵌套循环连接为例驱动表是外层循环外层行数越少内层表被探访的次数就越少。如果把一张百万级大表放最后当驱动外层就是百万次循环哪怕每次探访很快总耗时也扛不住。-- 按 RBO 习惯小表 dept 放 FROM 最后先被处理 SELECT a.dept_name, COUNT(b.emp_id) AS emp_cnt FROM emp b, dept a WHERE a.dept_id b.dept_id GROUP BY a.dept_name;这里 emp 是大表、dept 是小表把 dept 放在最后它先进入内存作为外层再去逐行匹配 emp省掉重复扫描大表的开销。需要特别提醒这条规则只在 RBO 下成立。Oracle 10g 之后默认是基于代价的优化器CBO驱动表由统计信息和成本计算决定你调 FROM 顺序它不一定理你。跨表关联时我一般先看执行计划里的 NESTED LOOPS 哪一侧是 outer再判断驱动表选得对不对而不是盲目相信书写顺序。3.2 WHERE 子句自下而上解析与高选择性条件PDF 明确写WHERE 子句的执行顺序是自下而上。这意味着最后写的条件最先执行所以应该把能过滤掉大量数据的条件放在 WHERE 的最后。CBO 环境下优化器会做谓词评估顺序的调整书写顺序不代表执行顺序但“尽早过滤”这个原则永远不会过时。相比纠结条件先后更关键的是别让索引列在过滤前被函数包一层-- 反面对 hire_date 做函数转换索引失效还得先算出所有年份再筛 SELECT emp_id, emp_name FROM emp WHERE TO_CHAR(hire_date, YYYY) 2020 AND dept_id 30; -- 正面用范围条件命中索引且过滤能力最强的条件写后面 SELECT emp_id, emp_name FROM emp WHERE dept_id 30 AND hire_date DATE 2020-01-01 AND hire_date DATE 2021-01-01;两段 SQL 的结果一样开销完全不同。第一段对 hire_date 套了 TO_CHAR 函数Oracle 无法直接利用 hire_date 上的索引只能全表扫描后逐行转换第二段改成大于等于小于的范围写法既保留索引可用性又把查询范围锁死在一年内。这个例子在 PDF 基础上我多改了一步因为只调顺序不拆函数十次里有八次还是慢。3.3 SELECT * 的隐藏成本不止多查一次数据字典PDF 说得很直白SELECT * 虽然写起来简单但 Oracle 解析星号时需要查询数据字典完成列名转换比直接写列名多耗时间。这一步在 OLTP 频繁小查询场景下放大效应很明显。工程上我还会补两条。一是网络传输SELECT * 返回所有列多出的列如果应用根本不用白白增加客户端和数据库之间的数据传输量。二是 PL/SQL 里的隐患如果表结构后续加列SELECT * 的结果集变宽游标变量或记录类型的赋值可能直接报错属于典型的线上翻车点-- 反例返回全部列列变更时游标结构跟着变 SELECT * FROM emp WHERE dept_id 30; -- 正例只取需要的列结构稳定且解析更轻 SELECT emp_id, emp_name, hire_date FROM emp WHERE dept_id 30;这里没有惊天的理论纯粹是“能省则省”。一个一天执行几十万次的小查询省一次数据字典查询和省两列无用传输累计起来就是肉眼可见的负载差异。3.4 WHERE 替代 HAVING过滤时机不同WHERE 在分组聚合之前执行HAVING 在分组聚合之后执行。同样一个非聚合条件的过滤放进 HAVING意味着所有行都要先分组、先聚合再把不要的组丢掉放进 WHERE则在分组前就把行排除参与聚合的数据量直接变少。PDF 说的“用 WHERE 代替 HAVING”适用条件就在这里-- 反例dept_id 是非聚合列先进组再过滤白算了一堆没用的分组 SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id HAVING dept_id 30; -- 正例先排除 dept_id 非 30 的行再分组聚合 SELECT dept_id, COUNT(*) FROM emp WHERE dept_id 30 GROUP BY dept_id;注意不是所有 HAVING 都能改成 WHERE。过滤条件如果依赖聚合结果比如HAVING COUNT(*) 100那只能留在 HAVING 里因为 WHERE 执行时聚合还没算出来。判断标准只有一个条件是不是针对原始行的。是就用 WHERE不是才用 HAVING。3.5 用执行计划验证改写效果改写完不能靠猜要拿执行计划说话EXPLAIN PLAN FOR SELECT emp_id, emp_name FROM emp WHERE dept_id 30 AND hire_date DATE 2020-01-01 AND hire_date DATE 2021-01-01; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);重点看两列Operation 和 Rows。Operation 里出现TABLE ACCESS FULL说明在扫全表出现INDEX RANGE SCAN说明索引生效Rows 是优化器估算的行数驱动表那一步的 Rows 越小连接成本通常越低。改写前跑一次改写后跑一次两张计划对比谁好谁坏一眼就清楚。别只盯着 Cost 这一个数字Rows 估算偏差大时Cost 会骗人。4. 避坑指南共享池翻车、命中率错觉与参数改不生效优化做多了发现真正耽误时间的不是优化本身而是没识别出哪些“常见操作”其实是坑。以下五条都是我自己踩过或看同事踩过的按现象、原因、解决三步拆开写。4.1 共享池调到 1G 之后服务器开始“换页式”变慢现象某次为了缓存更多 SQL把 shared_pool_size 从 300M 直接调到 1G几小时后系统整体变慢CPU 使用率升高iowait 明显增长top 里看到大量 si/so 换页应用开始出现响应超时。原因SGA 总占用超过物理内存剩余量Linux 把 Oracle 的部分内存段换到 swap。共享内存被换出后任何一次访问都要先换入性能断崖式下跌。共享池并不是越大越好PDF 里那句“最大值不超过 500M”不是随口写的。解决立即把 shared_pool_size 调回 500M 以内SGA 加上 PGA 的总和控制在物理内存 60%–70%。如果内存确实有富余先确认 HugePages 已启用再考虑加大缓存否则越大的 SGA 越容易变成换页重灾区。4.2 缓冲区命中率 99.9%查询还是慢现象监控面板上 buffer hit ratio 高到 99.9%应用却持续报慢查询。查 v$sql 后发现某条 SQL 每次执行要逻辑读几十万个块执行频次又高CPU 全部耗在内存遍历上。原因命中率高只代表物理读少不代表逻辑读少。全表扫描数据全在内存时命中率照样漂亮但扫描动作本身的 CPU 开销一点没减。把命中率当健康指标是调优里最典型的错觉之一。解决丢弃全局命中率改用 v$sql 看单条 SQL 的逻辑读、物理读和执行次数-- 按逻辑读排序找出真正的“大户” SELECT sql_id, sql_text, executions, logical_reads, physical_reads, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec FROM v$sql WHERE executions 0 ORDER BY logical_reads DESC FETCH FIRST 10 ROWS ONLY;这里 logical_reads 是内存读块数physical_reads 是磁盘读块数elapsed_sec 是累计耗时。三者结合看才能定位“命中率没问题但慢”的真凶。4.3 把 RBO 的驱动表规则硬套到 CBO 上现象按 PDF 旧规则把记录少的小表放到 FROM 最后等着看执行计划变成嵌套循环结果计划纹丝不动有时甚至变得更差。原因10g 之后默认 CBO执行计划由统计信息和代价决定FROM 书写顺序对连接顺序几乎没影响。PDF 原文写得很清楚——“在基于规则的优化器中”适用边界只到 RBO。拿旧规则指挥新优化器等于往方向盘上贴了张过期的地图。解决先确认优化器模式SHOW PARAMETER optimizer_mode如果显示 ALL_ROWS 或 FIRST_ROWS就老老实实按 CBO 思路走保证统计信息新鲜必要时用 hint 明确指定连接顺序比如LEADING(dept) USE_NL(emp)而不是改 FROM 顺序期待优化器听你的。4.4 改完参数看着生效重启后打回原形现象执行ALTER SYSTEM SET shared_pool_size 300M;后v$parameter 里确实变成了 300M欣欣然下班。第二天数据库例行重启参数又变回 150M。原因实例从 pfile 启动时ALTER SYSTEM 的默认行为只改内存不写文件。pfile 是文本文件Oracle 不会帮你回写。从 spfile 启动时默认行为才是既改内存又写 spfile。问题出在“没确认启动方式”。解决改动前先执行SHOW PARAMETER spfile;如果 VALUE 为空说明走的是 pfile先创建 spfileCREATE SPFILE FROM PFILE; ALTER SYSTEM SET shared_pool_size 300M SCOPE SPFILE;从那以后我养成了习惯所有参数变更语句后面都显式写 SCOPE绝不依赖默认值。4.5 统计信息过期执行计划“漂移”现象同一条 SQL昨天走索引一百毫秒内返回今天突然全表扫描耗时飙到五秒。SQL 文本没动表结构没动应用没发新版本。原因表的数据量和数据分布变了但统计信息还是老的CBO 按过期的统计信息算出的成本失真做出了全表扫描的错误选择。数据倾斜越大这类漂移越常见。解决定期收集统计信息关注数据变化量大的表-- 查看哪些表被修改过但统计信息没更新 SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications ORDER BY inserts updates deletes DESC; -- 手动收集关键表统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname SCOTT, tabname EMP, cascade TRUE);生产环境建议在维护窗口统一收集核心大表单独安排频率。如果历史上执行计划已经漂移过多次还可以考虑用 SQL Plan ManagementSPM把稳定计划固定下来给优化器加一道保险。5. 操作系统与网络配合大页、SQL*Net 等待与 AWR 基线数据库的瓶颈不一定都在数据库内部。PDF 在引言里点过操作系统参数和网络性能调优这两个方向正文虽然只展开了前两项但真正落地时OS 和网络层不配合SGA 调得再好也会被外部因素拖住。5.1 Linux 大页HugePages与 SGA 的配合Linux 默认内存页是 4KBSGA 动辄几 GB意味着操作系统要维护上百万条页表项。CPU 的 TLB 缓存有限页表项一多TLB 命中率下降内存访问变慢。更麻烦的是SGA 这类共享内存段在系统内存压力大时可能被换出到磁盘触发灾难性的 swap 抖动。常见做法是给 Oracle 配置 HugePages让共享内存段使用 2MB 甚至 1GB 的大页。先看当前情况# 检查大页配置和实际使用 grep -i hugepages /proc/meminfo重点看 HugePages_Total 和 HugePages_Free。如果 Total 为 0说明没有启用。设置大致分三步先固定 SGA 相关参数再按 SGA 大小计算页数最后写入系统配置# 假设 SGA 为 8G使用 2MB 大页约需 4096 页留 10% 余量 sysctl -w vm.nr_hugepages4600 # 将设置写入 /etc/sysctl.conf 使其持久化 echo vm.nr_hugepages4600 /etc/sysctl.conf # 重启 Oracle 实例后确认共享内存段是否落在大页上 ipcs -m | grep oracle一个必须先说的前提Oracle 的自动内存管理memory_target与 HugePages 不兼容。启用大页前要把 memory_target 设为 0改用 sga_target 和 pga_aggregate_target 手动管理。否则实例可能启动失败或者看似启动了共享内存段根本没进大页。我踩过一次配好大页后 Oracle 起不来报错信息含糊最后排查到是 memory_target 残留在 spfile 里。5.2 网络层SQL*Net 等待不等于网络故障AWR 报告里如果看到 SQLNet message from client 排进 Top Events先别急着下“网络有问题”的结论。这个事件大部分时间表示客户端在思考或发呆是空闲等待。真正要警惕的是 SQLNet roundtrips to/from client 这类和往返次数强相关的事件它说明应用和数据库之间的交互次数过多。我常用的排查方法是“两端对照”在应用服务器上和数据库服务器上分别跑同一条 SQL对比耗时。如果 sqlplus 本机执行也慢问题在 SQL 或存储如果本机快、应用慢才往网络和应用层方向查。应用侧能做的优化是减少往返——把循环里一条条执行的 SQL 改成批量提交或者把多次查询合并成一条集合查询。网络参数层面常见做法是调整服务端和客户端之间的 TCP 超时与存活探测但这解决的是断连和僵死连接解决不了“交互次数太多”的根子问题。5.3 建立监控基线AWR 快照与报告阅读优化改完怎么知道有没有效果AWR 是最省事的工具。它可以手工打快照也可以设成固定间隔自动打-- 手动创建一次快照做变更前的基线记录 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;生成报告的标准动作是在 sqlplus 里调用 awrrpt 脚本sqlplus / as sysdba ?/rdbms/admin/awrrpt.sql按提示选择起始快照和结束快照再选 text 或 html 格式。报告生成后我一般只看三个区块Top 10 Foreground Events by Total Wait Time判断瓶颈是 CPU、I/O 还是网络SQL ordered by Elapsed Time定位最贵的 SQLInstance Activity 里的 physical reads、logical reads 和 db time对比优化前后曲线。变更前打一次快照变更后稳定运行几小时再打一次两次报告放一起比效果好过任何口头的“好像快了点”。没有基线就谈优化效果都是凭感觉。6. 优化落到日常留快照、比执行计划、记台账优化不是一次性工程它更像给数据库做长期健康管理。我自己的习惯是每次优化动作都走一套固定流程先记录环境和基线再定位最贵的 SQL然后用执行计划对比验证最后把结果写进台账。具体做法是固定三步。第一步做变更前快照并把这几个值记下来SGA 各池大小、PGA 大小、物理读和逻辑读数量、最贵的前三条 SQL。第二步对目标 SQL 做改写改完马上做一对对比实验-- 改写前打开详细统计后执行原 SQL ALTER SESSION SET statistics_level ALL; SELECT /* baseline */ emp_name, dept_name FROM emp e JOIN dept d ON e.dept_id d.dept_id WHERE e.hire_date DATE 2020-01-01; -- 查看刚才这条 SQL 的真实执行统计 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ALLSTATS LAST));记下输出里的 Rows、Time、A-Rows 和 Buffers再把改写后的 SQL 用同样流程跑一遍看 A-Rows 是否一致、Rows 估算是否更准、Elapsed Time 是否下降。执行计划显示的行数和实际返回行数差距越大说明统计信息越失真这时改 SQL 不如先收集统计信息。第三步把结果写进台账。我习惯用一张简单的表日期、SQL_ID、改动内容、改动前耗时、改动后耗时、执行计划变化。积累了半年的台账回头翻哪些改法在这个业务场景下稳定有效哪些换个表就不灵了一目了然。这份 PDF 里最有价值的东西不是那几个参数数字而是“优化前先分主次”的思路。从那以后我每次遇到性能问题都强制自己走一遍流程先拍快照记基线再查 v$sql 找最贵的再借助执行计划改 SQL改完用 ALLSTATS 对比验证。这套动作下来省掉了很多被“感觉”带偏的时间也希望帮到你。本文还有配套的精品资源点击获取 Oracle性能优化实战：SGA调整与SQL改写要点解析
简介Oracle数据库性能优化是保障系统稳定高效运行的核心环节尤其适合大数据量和高并发的业务环境。文档系统梳理了Oracle数据库调优的主要方面面向数据库管理员、开发人员及相关运维工程师帮助读者建立从内存配置到SQL写法再到系统层调优的完整知识框架。压缩包内为单个PDF文档整体仅128KB内容精炼便携方便随时查阅当前已有1500人学习下载。内容重点包括数据库服务器SGA内存调整讲解共享池、数据缓冲区、日志缓冲区的配置原理并给出系统内存为1G时共享池建议150M-200M等参考数值SQL语句优化部分说明基于规则的优化器中驱动表的选择、WHERE条件过滤顺序、避免SELECT *、优先用WHERE代替HAVING等技巧此外还覆盖索引建立、分区策略、回滚段管理、执行计划控制以及操作系统参数和网络性能调优。整体而言可作为DBA日常调优和排查性能瓶颈的系统参考。1. Oracle 数据库性能优化为什么先动 SGA 再改 SQLOracle 数据库的性能问题十有八九最后落在两个点上内存里放不下SQL 写得不划算。这份《Oracle数据库性能优化》PDF 就是围绕这两件事展开的——前面讲系统全局区SGA里共享池和数据缓冲区怎么取值后面讲驱动表、WHERE 顺序、SELECT * 这些改写技巧对解析成本的影响。我拿着线上库 CPU 高、慢查询扎堆的现场去对照读读完最大的收获是优化是有先后顺序的先定内存边界再改 SQL最后才轮到操作系统和网络。它适合正在带 Oracle 库的 DBA、运维转数据库方向的工程师也适合写 SQL 老觉得执行计划不听话的应用开发——案例是老了些但今天的坑大多还能在这份材料里找到原型。2. SGA 调整共享池 150–200M 起步缓冲区命中率别只看数字数据库服务器内存参数调整是 PDF 里点名的第一个调优方向。Oracle 实例启动后SGA 会按参数把内存分给固定区域、共享池、数据缓冲区、日志缓冲区等几个部分。应用发来的每条 SQL、每次数据读取都要经过这几块内存所以说 SGA 是性能的第一道闸门。2.1 共享池先看解析过程再定参数共享池存放最近使用过的 SQL、PL/SQL 代码和数据字典里的元数据。它由库高速缓存Library Cache和数据字典缓冲区Data Dictionary Cache组成。Oracle 收到一条 SQL先做语法分析、权限确认再交给优化器生成执行计划最后才真正执行。这个过程在共享池足够大的时候可以被跳过——相同文本的 SQL 再次进来直接命中缓存的执行计划省掉重复解析的时间。这就是为什么共享池是“对性能影响显著”的首选调整对象。PDF 给了一条很实用的经验值系统内存 1G 时共享池设 150M–200M内存每增加 1G它的值加 100M但上限不要超过 500M。这条经验我建议直接抄进自己的优化手册。它的依据是 LRU 算法共享池内部用最近最少使用算法淘汰旧条目缓存命中率与池子大小不是线性关系超过某个临界点后Oracle 维护内部结构free lists、lru chains的开销反而比省下来的解析时间更贵。另一个风险是共享池偏大会挤压操作系统的可用内存Oracle 进程一旦被换出到虚拟内存系统整体响应会断崖式下跌。在具体操作前先把当前占用和命中情况摸清楚-- 查看共享池各区域占用按字节倒序排 SELECT pool, name, ROUND(bytes / 1024 / 1024, 2) MB FROM v$sgastat WHERE pool shared pool ORDER BY bytes DESC; -- 查看库高速缓存命中情况 SELECT namespace, gets, gethitratio, pins, pinhitratio FROM v$librarycache;第一段 SQL 用来确认共享池里谁是“大户”正常情况下 library cache 和 sql area 占大头如果 free memory 长期很小说明池子偏紧。第二段 SQL 中gets 是解析请求次数gethitratio 表示该 namespace 下直接命中缓存的比率pins 是执行阶段引用次数pinhitratio 是执行期命中率。这里没有统一的“健康线”但 gethitratio 低于 90% 时值得考虑加大 shared_pool_size。2.2 数据缓冲区物理读与逻辑读的换算数据缓冲区DB Buffer Cache缓存从磁盘读出来的数据块。理解它很简单缓冲块越大第二次访问同一块数据就不用回磁盘直接命中内存。物理读少一批响应时间自然下来。不过缓冲区也服从边际收益递减。假设一个 20G 的库缓存 2G 时物理读明显下降但从 2G 加到 4G收益可能只有前一段的五分之一。PDF 原文强调“缓冲区越大存放的共享数据就越多”但没提醒你“大过头会挤压操作系统”。实际运维中我一般把 SGA 总和控制在物理内存的 60%–70%剩下留给 PGA 和操作系统文件缓存。想看当前全库的缓冲区命中率可以用这条经典 SQL-- 缓冲区命中率注意这只是总体参考值 SELECT 1 - (phy.value / (cur.value con.value)) AS buffer_hit_ratio FROM v$sysstat phy, v$sysstat cur, v$sysstat con WHERE phy.name physical reads AND cur.name db block gets AND con.name consistent gets;公式的含义是物理读次数除以逻辑读总数再用 1 减去得到命中率。db block gets 是当前模式读consistent gets 是一致性读两者相加是逻辑读总量。这条语句放在监控脚本里可以但别把它当成调优结论的唯一下判断依据——一个全表扫描如果数据全在内存里命中率照样很高可 CPU 一样被扫描动作吃满。2.3 用 v$db_cache_advice 判断加不加缓存比较稳妥的做法是让 Oracle 自己算一笔账。v$db_cache_advice 会为不同缓存大小预测物理读变化这是调优时少有的“先看预测再动手”手段-- 查看不同缓存大小对应的物理读预测 SELECT size_for_estimate, estd_physical_read_factor, estd_physical_reads FROM v$db_cache_advice WHERE name DEFAULT AND block_size 8192 ORDER BY size_for_estimate;size_for_estimate 是假设的缓存大小estd_physical_read_factor 是相对当前值的物理读比例estd_physical_reads 是估算出的物理读次数。如果 size_for_estimate 从 1G 涨到 2G 时 factor 从 1.0 掉到 0.6说明加大缓存回报不错如果 2G 到 3G 只降到 0.55说明增量收益已经很薄。我一般选曲线“膝盖”位置而不是追 100% 命中。2.4 参数落地alter system 与 scope 取舍跑完诊断真正改参数时先确认实例是从 spfile 启动还是 pfile 启动这决定了你改完能不能持久-- 确认参数文件方式VALUE 不为空说明用了 spfile SHOW PARAMETER spfile; -- 查看当前相关参数 SELECT name, value, isdefault FROM v$parameter WHERE name IN (shared_pool_size, db_cache_size, log_buffer);如果要从手动管理切到自动管理常见做法是先定 sga_target让 Oracle 自动调配各池如果库上跑的 SQL 特征非常稳定也可以继续用手动值-- 修改共享池希望立即生效并写入 spfile用 SCOPEBOTH ALTER SYSTEM SET shared_pool_size 300M SCOPE BOTH; -- 修改数据缓冲区接受重启生效用 SCOPESPFILE ALTER SYSTEM SET db_cache_size 1536M SCOPE SPFILE;SCOPE 三个取值要分清楚MEMORY 只改当前实例重启即丢SPFILE 只写服务器参数文件要重启才生效BOTH 是当前值和文件都改。注意静态参数不能带 BOTH比如 log_buffer 这类需要重启的参数带 SCOPEBOTH 会直接报错。日志缓冲区一般不建议手工乱调交给 Oracle 默认值在绝大多数场景下更稳。提示改 SGA 相关的关键参数前先执行CREATE PFILE/tmp/pfile_backup.ora FROM SPFILE;留一份后悔药。线上改参最怕改完想回头手里却没有原始配置。参数调整不是一锤子买卖。每次只动一个参数观察 5 到 10 分钟比较 AWR 报告里 physical reads、logical reads、db time 这三个指标的变化再决定下一步。一次改三个参数出了问题你根本不知道是哪个惹的祸。3. SQL 改写驱动表、WHERE 顺序、SELECT * 与 HAVING 的取舍应用代码最终都归结为数据库里的 SQL 执行。同样的业务SQL 写法不同执行成本可能差出几十倍。PDF 里对 SQL 优化的讲解是基于规则优化器RBO时代的习惯核心就四件事驱动表放哪、WHERE 条件怎么排序、要不要写 SELECT *、WHERE 和 HAVING 怎么分工。这些结论放到现在部分已经失效但思考方式完全能平移。3.1 驱动表from 子句的解析顺序与选择在基于规则的优化器里Oracle 对 FROM 子句按从右到左解析排在最后的表最先被处理这张表就是驱动表。驱动表决定连接的起点它产生多少行后续表就要被访问多少次。PDF 给了一条硬规则选择记录条数少的表作为驱动表放在 FROM 子句最后。FROM 里有 3 张以上表时把连接其他表的“交叉表”作为驱动表。这条规矩背后的逻辑很好懂。以最常见的嵌套循环连接为例驱动表是外层循环外层行数越少内层表被探访的次数就越少。如果把一张百万级大表放最后当驱动外层就是百万次循环哪怕每次探访很快总耗时也扛不住。-- 按 RBO 习惯小表 dept 放 FROM 最后先被处理 SELECT a.dept_name, COUNT(b.emp_id) AS emp_cnt FROM emp b, dept a WHERE a.dept_id b.dept_id GROUP BY a.dept_name;这里 emp 是大表、dept 是小表把 dept 放在最后它先进入内存作为外层再去逐行匹配 emp省掉重复扫描大表的开销。需要特别提醒这条规则只在 RBO 下成立。Oracle 10g 之后默认是基于代价的优化器CBO驱动表由统计信息和成本计算决定你调 FROM 顺序它不一定理你。跨表关联时我一般先看执行计划里的 NESTED LOOPS 哪一侧是 outer再判断驱动表选得对不对而不是盲目相信书写顺序。3.2 WHERE 子句自下而上解析与高选择性条件PDF 明确写WHERE 子句的执行顺序是自下而上。这意味着最后写的条件最先执行所以应该把能过滤掉大量数据的条件放在 WHERE 的最后。CBO 环境下优化器会做谓词评估顺序的调整书写顺序不代表执行顺序但“尽早过滤”这个原则永远不会过时。相比纠结条件先后更关键的是别让索引列在过滤前被函数包一层-- 反面对 hire_date 做函数转换索引失效还得先算出所有年份再筛 SELECT emp_id, emp_name FROM emp WHERE TO_CHAR(hire_date, YYYY) 2020 AND dept_id 30; -- 正面用范围条件命中索引且过滤能力最强的条件写后面 SELECT emp_id, emp_name FROM emp WHERE dept_id 30 AND hire_date DATE 2020-01-01 AND hire_date DATE 2021-01-01;两段 SQL 的结果一样开销完全不同。第一段对 hire_date 套了 TO_CHAR 函数Oracle 无法直接利用 hire_date 上的索引只能全表扫描后逐行转换第二段改成大于等于小于的范围写法既保留索引可用性又把查询范围锁死在一年内。这个例子在 PDF 基础上我多改了一步因为只调顺序不拆函数十次里有八次还是慢。3.3 SELECT * 的隐藏成本不止多查一次数据字典PDF 说得很直白SELECT * 虽然写起来简单但 Oracle 解析星号时需要查询数据字典完成列名转换比直接写列名多耗时间。这一步在 OLTP 频繁小查询场景下放大效应很明显。工程上我还会补两条。一是网络传输SELECT * 返回所有列多出的列如果应用根本不用白白增加客户端和数据库之间的数据传输量。二是 PL/SQL 里的隐患如果表结构后续加列SELECT * 的结果集变宽游标变量或记录类型的赋值可能直接报错属于典型的线上翻车点-- 反例返回全部列列变更时游标结构跟着变 SELECT * FROM emp WHERE dept_id 30; -- 正例只取需要的列结构稳定且解析更轻 SELECT emp_id, emp_name, hire_date FROM emp WHERE dept_id 30;这里没有惊天的理论纯粹是“能省则省”。一个一天执行几十万次的小查询省一次数据字典查询和省两列无用传输累计起来就是肉眼可见的负载差异。3.4 WHERE 替代 HAVING过滤时机不同WHERE 在分组聚合之前执行HAVING 在分组聚合之后执行。同样一个非聚合条件的过滤放进 HAVING意味着所有行都要先分组、先聚合再把不要的组丢掉放进 WHERE则在分组前就把行排除参与聚合的数据量直接变少。PDF 说的“用 WHERE 代替 HAVING”适用条件就在这里-- 反例dept_id 是非聚合列先进组再过滤白算了一堆没用的分组 SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id HAVING dept_id 30; -- 正例先排除 dept_id 非 30 的行再分组聚合 SELECT dept_id, COUNT(*) FROM emp WHERE dept_id 30 GROUP BY dept_id;注意不是所有 HAVING 都能改成 WHERE。过滤条件如果依赖聚合结果比如HAVING COUNT(*) 100那只能留在 HAVING 里因为 WHERE 执行时聚合还没算出来。判断标准只有一个条件是不是针对原始行的。是就用 WHERE不是才用 HAVING。3.5 用执行计划验证改写效果改写完不能靠猜要拿执行计划说话EXPLAIN PLAN FOR SELECT emp_id, emp_name FROM emp WHERE dept_id 30 AND hire_date DATE 2020-01-01 AND hire_date DATE 2021-01-01; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);重点看两列Operation 和 Rows。Operation 里出现TABLE ACCESS FULL说明在扫全表出现INDEX RANGE SCAN说明索引生效Rows 是优化器估算的行数驱动表那一步的 Rows 越小连接成本通常越低。改写前跑一次改写后跑一次两张计划对比谁好谁坏一眼就清楚。别只盯着 Cost 这一个数字Rows 估算偏差大时Cost 会骗人。4. 避坑指南共享池翻车、命中率错觉与参数改不生效优化做多了发现真正耽误时间的不是优化本身而是没识别出哪些“常见操作”其实是坑。以下五条都是我自己踩过或看同事踩过的按现象、原因、解决三步拆开写。4.1 共享池调到 1G 之后服务器开始“换页式”变慢现象某次为了缓存更多 SQL把 shared_pool_size 从 300M 直接调到 1G几小时后系统整体变慢CPU 使用率升高iowait 明显增长top 里看到大量 si/so 换页应用开始出现响应超时。原因SGA 总占用超过物理内存剩余量Linux 把 Oracle 的部分内存段换到 swap。共享内存被换出后任何一次访问都要先换入性能断崖式下跌。共享池并不是越大越好PDF 里那句“最大值不超过 500M”不是随口写的。解决立即把 shared_pool_size 调回 500M 以内SGA 加上 PGA 的总和控制在物理内存 60%–70%。如果内存确实有富余先确认 HugePages 已启用再考虑加大缓存否则越大的 SGA 越容易变成换页重灾区。4.2 缓冲区命中率 99.9%查询还是慢现象监控面板上 buffer hit ratio 高到 99.9%应用却持续报慢查询。查 v$sql 后发现某条 SQL 每次执行要逻辑读几十万个块执行频次又高CPU 全部耗在内存遍历上。原因命中率高只代表物理读少不代表逻辑读少。全表扫描数据全在内存时命中率照样漂亮但扫描动作本身的 CPU 开销一点没减。把命中率当健康指标是调优里最典型的错觉之一。解决丢弃全局命中率改用 v$sql 看单条 SQL 的逻辑读、物理读和执行次数-- 按逻辑读排序找出真正的“大户” SELECT sql_id, sql_text, executions, logical_reads, physical_reads, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec FROM v$sql WHERE executions 0 ORDER BY logical_reads DESC FETCH FIRST 10 ROWS ONLY;这里 logical_reads 是内存读块数physical_reads 是磁盘读块数elapsed_sec 是累计耗时。三者结合看才能定位“命中率没问题但慢”的真凶。4.3 把 RBO 的驱动表规则硬套到 CBO 上现象按 PDF 旧规则把记录少的小表放到 FROM 最后等着看执行计划变成嵌套循环结果计划纹丝不动有时甚至变得更差。原因10g 之后默认 CBO执行计划由统计信息和代价决定FROM 书写顺序对连接顺序几乎没影响。PDF 原文写得很清楚——“在基于规则的优化器中”适用边界只到 RBO。拿旧规则指挥新优化器等于往方向盘上贴了张过期的地图。解决先确认优化器模式SHOW PARAMETER optimizer_mode如果显示 ALL_ROWS 或 FIRST_ROWS就老老实实按 CBO 思路走保证统计信息新鲜必要时用 hint 明确指定连接顺序比如LEADING(dept) USE_NL(emp)而不是改 FROM 顺序期待优化器听你的。4.4 改完参数看着生效重启后打回原形现象执行ALTER SYSTEM SET shared_pool_size 300M;后v$parameter 里确实变成了 300M欣欣然下班。第二天数据库例行重启参数又变回 150M。原因实例从 pfile 启动时ALTER SYSTEM 的默认行为只改内存不写文件。pfile 是文本文件Oracle 不会帮你回写。从 spfile 启动时默认行为才是既改内存又写 spfile。问题出在“没确认启动方式”。解决改动前先执行SHOW PARAMETER spfile;如果 VALUE 为空说明走的是 pfile先创建 spfileCREATE SPFILE FROM PFILE; ALTER SYSTEM SET shared_pool_size 300M SCOPE SPFILE;从那以后我养成了习惯所有参数变更语句后面都显式写 SCOPE绝不依赖默认值。4.5 统计信息过期执行计划“漂移”现象同一条 SQL昨天走索引一百毫秒内返回今天突然全表扫描耗时飙到五秒。SQL 文本没动表结构没动应用没发新版本。原因表的数据量和数据分布变了但统计信息还是老的CBO 按过期的统计信息算出的成本失真做出了全表扫描的错误选择。数据倾斜越大这类漂移越常见。解决定期收集统计信息关注数据变化量大的表-- 查看哪些表被修改过但统计信息没更新 SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications ORDER BY inserts updates deletes DESC; -- 手动收集关键表统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname SCOTT, tabname EMP, cascade TRUE);生产环境建议在维护窗口统一收集核心大表单独安排频率。如果历史上执行计划已经漂移过多次还可以考虑用 SQL Plan ManagementSPM把稳定计划固定下来给优化器加一道保险。5. 操作系统与网络配合大页、SQL*Net 等待与 AWR 基线数据库的瓶颈不一定都在数据库内部。PDF 在引言里点过操作系统参数和网络性能调优这两个方向正文虽然只展开了前两项但真正落地时OS 和网络层不配合SGA 调得再好也会被外部因素拖住。5.1 Linux 大页HugePages与 SGA 的配合Linux 默认内存页是 4KBSGA 动辄几 GB意味着操作系统要维护上百万条页表项。CPU 的 TLB 缓存有限页表项一多TLB 命中率下降内存访问变慢。更麻烦的是SGA 这类共享内存段在系统内存压力大时可能被换出到磁盘触发灾难性的 swap 抖动。常见做法是给 Oracle 配置 HugePages让共享内存段使用 2MB 甚至 1GB 的大页。先看当前情况# 检查大页配置和实际使用 grep -i hugepages /proc/meminfo重点看 HugePages_Total 和 HugePages_Free。如果 Total 为 0说明没有启用。设置大致分三步先固定 SGA 相关参数再按 SGA 大小计算页数最后写入系统配置# 假设 SGA 为 8G使用 2MB 大页约需 4096 页留 10% 余量 sysctl -w vm.nr_hugepages4600 # 将设置写入 /etc/sysctl.conf 使其持久化 echo vm.nr_hugepages4600 /etc/sysctl.conf # 重启 Oracle 实例后确认共享内存段是否落在大页上 ipcs -m | grep oracle一个必须先说的前提Oracle 的自动内存管理memory_target与 HugePages 不兼容。启用大页前要把 memory_target 设为 0改用 sga_target 和 pga_aggregate_target 手动管理。否则实例可能启动失败或者看似启动了共享内存段根本没进大页。我踩过一次配好大页后 Oracle 起不来报错信息含糊最后排查到是 memory_target 残留在 spfile 里。5.2 网络层SQL*Net 等待不等于网络故障AWR 报告里如果看到 SQLNet message from client 排进 Top Events先别急着下“网络有问题”的结论。这个事件大部分时间表示客户端在思考或发呆是空闲等待。真正要警惕的是 SQLNet roundtrips to/from client 这类和往返次数强相关的事件它说明应用和数据库之间的交互次数过多。我常用的排查方法是“两端对照”在应用服务器上和数据库服务器上分别跑同一条 SQL对比耗时。如果 sqlplus 本机执行也慢问题在 SQL 或存储如果本机快、应用慢才往网络和应用层方向查。应用侧能做的优化是减少往返——把循环里一条条执行的 SQL 改成批量提交或者把多次查询合并成一条集合查询。网络参数层面常见做法是调整服务端和客户端之间的 TCP 超时与存活探测但这解决的是断连和僵死连接解决不了“交互次数太多”的根子问题。5.3 建立监控基线AWR 快照与报告阅读优化改完怎么知道有没有效果AWR 是最省事的工具。它可以手工打快照也可以设成固定间隔自动打-- 手动创建一次快照做变更前的基线记录 EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;生成报告的标准动作是在 sqlplus 里调用 awrrpt 脚本sqlplus / as sysdba ?/rdbms/admin/awrrpt.sql按提示选择起始快照和结束快照再选 text 或 html 格式。报告生成后我一般只看三个区块Top 10 Foreground Events by Total Wait Time判断瓶颈是 CPU、I/O 还是网络SQL ordered by Elapsed Time定位最贵的 SQLInstance Activity 里的 physical reads、logical reads 和 db time对比优化前后曲线。变更前打一次快照变更后稳定运行几小时再打一次两次报告放一起比效果好过任何口头的“好像快了点”。没有基线就谈优化效果都是凭感觉。6. 优化落到日常留快照、比执行计划、记台账优化不是一次性工程它更像给数据库做长期健康管理。我自己的习惯是每次优化动作都走一套固定流程先记录环境和基线再定位最贵的 SQL然后用执行计划对比验证最后把结果写进台账。具体做法是固定三步。第一步做变更前快照并把这几个值记下来SGA 各池大小、PGA 大小、物理读和逻辑读数量、最贵的前三条 SQL。第二步对目标 SQL 做改写改完马上做一对对比实验-- 改写前打开详细统计后执行原 SQL ALTER SESSION SET statistics_level ALL; SELECT /* baseline */ emp_name, dept_name FROM emp e JOIN dept d ON e.dept_id d.dept_id WHERE e.hire_date DATE 2020-01-01; -- 查看刚才这条 SQL 的真实执行统计 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ALLSTATS LAST));记下输出里的 Rows、Time、A-Rows 和 Buffers再把改写后的 SQL 用同样流程跑一遍看 A-Rows 是否一致、Rows 估算是否更准、Elapsed Time 是否下降。执行计划显示的行数和实际返回行数差距越大说明统计信息越失真这时改 SQL 不如先收集统计信息。第三步把结果写进台账。我习惯用一张简单的表日期、SQL_ID、改动内容、改动前耗时、改动后耗时、执行计划变化。积累了半年的台账回头翻哪些改法在这个业务场景下稳定有效哪些换个表就不灵了一目了然。这份 PDF 里最有价值的东西不是那几个参数数字而是“优化前先分主次”的思路。从那以后我每次遇到性能问题都强制自己走一遍流程先拍快照记基线再查 v$sql 找最贵的再借助执行计划改 SQL改完用 ALLSTATS 对比验证。这套动作下来省掉了很多被“感觉”带偏的时间也希望帮到你。本文还有配套的精品资源点击获取