简介这份Oracle数据库性能优化PDF文档面向数据库管理员、后端开发及运维人员针对大数据量、高并发场景下系统响应变慢、性能持续下降等实际问题梳理了一套可落地的调优思路。内容围绕数据库服务器内存参数调整与SQL语句优化两大主线展开涵盖系统全局区中共享池、数据缓冲区、日志缓冲区的合理配置以及驱动表选择、WHERE子句条件顺序、避免SELECT *、用WHERE替代HAVING等具体技巧并延伸至索引管理、分区策略、回滚段优化与执行计划控制等方向。资源包共1个PDF文件约128KB篇幅精炼适合作为日常调优的速查参考。目前已有1501人学习下载读者可从中获得内存参数设定区间、SQL改写规则与整体优化框架便于结合自身系统负载持续监控与调整。1. Oracle 性能优化不是调参玄学从一份 PDF 标题说开去很多人第一次接触 Oracle 数据库性能优化是从一份名为《oracle数据库性能优化.pdf》的文档开始的。它可能来自前辈的分享、培训班的资料或者某个技术群里流传的“内部手册”。但真正翻开之后你会发现里面讲的往往是零散的等待事件、索引建议、SGA 参数缺少一条从“定位问题”到“验证效果”的完整链路。这篇文章要做的就是把这个标题背后的东西拆开Oracle 性能优化到底在优化什么、用什么工具定位、参数怎么改、哪些坑一踩就翻车。适合已经能写 SQL、管过一两个实例但面对“系统变慢”时还靠猜的从业者。下面按“先能看见问题再动手改最后能验证”的顺序讲每一步都落到可执行的命令和参数上。2. 先让数据库开口说话AWR、ASH 与等待事件定位法性能优化最怕的不是问题难而是不知道问题在哪。Oracle 自带的诊断工具已经足够回答“谁在等、等什么、等多久”这三个问题。这一章先把观测手段立住后面所有调整才有依据。2.1 用 AWR 报告锁定 Top 等待事件与 SQLAWRAutomatic Workload Repository是 Oracle 自带的历史性能快照仓库默认每小时采集一次保留 8 天。生成一份 AWR 报告只需要两个快照 ID命令如下-- 先查最近可用的快照确定起止 snap_id SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 20 ROWS ONLY; -- 在 SQL*Plus 中生成 AWR 报告需 SYSDBA 或 SELECT_CATALOG_ROLE -- 假设起止快照为 1201 和 1202 ?/rdbms/admin/awrrpt.sql -- 交互中输入 report_type htmlbegin_snap 1201end_snap 1202生成后重点看三块Top 10 Foreground Events 按 DB Time 占比排序如果db file sequential read排第一说明大量单块读索引或 SQL 访问路径可能有问题如果log file sync靠前说明提交过于频繁SQL ordered by Elapsed Time 则直接列出最耗时的语句拿到 SQL_ID 后可以用SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id))看历史执行计划。参数上statistics_level必须为 TYPICAL 或 ALL否则很多等待事件统计不到。检查命令是SHOW PARAMETER statistics_level。如果实例是 11g 及以上默认就是 TYPICAL一般不用改。AWR 的采样间隔由dbms_workload_repository.modify_snapshot_settings控制生产库不建议把 interval 调到 30 分钟以下否则快照本身会带来额外 I/O。2.2 ASH 抓瞬时卡顿与阻塞链AWR 是小时级粒度遇到“每天下午三点卡五分钟”这种问题AWR 可能只看到一个平均值。ASHActive Session History按秒采样活动会话更适合抓瞬时峰值。查最近 30 分钟内的等待分布SELECT session_state, event, COUNT(*) AS samples FROM v$active_session_history WHERE sample_time SYSDATE - 30/1440 GROUP BY session_state, event ORDER BY samples DESC;如果看到大量enq: TX - row lock contention说明有行锁阻塞。进一步用blocking_session字段追阻塞源SELECT sample_time, session_id, blocking_session, event, sql_id FROM v$active_session_history WHERE blocking_session IS NOT NULL AND sample_time SYSDATE - 30/1440 ORDER BY sample_time DESC;拿到blocking_session后去v$session里查它的sql_id、machine、program基本就能定位到是哪台应用、哪条语句持锁不放。这一步是排查“数据库突然变慢”最有效的入口比盲目看 AWR 快得多。2.3 执行计划里最容易看错的三个点拿到 SQL_ID 之后DBMS_XPLAN.DISPLAY_CURSOR是必用工具SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(9f8d3k2m1p0q, NULL, ALLSTATS LAST));看执行计划时三个地方最容易误判。第一Rows是估算值A-Rows才是实际返回行数两者差几个数量级说明统计信息过期或绑定变量窥探导致计划偏差。第二Buffers列反映逻辑读如果某个步骤逻辑读远高于其他步骤即使它不在最内层也可能是真正的瓶颈。第三Note部分如果出现dynamic sampling used说明优化器对表统计信息不信任临时采样会消耗额外资源需要检查DBMS_STATS的收集策略。统计信息收集的常见做法是对变化量超过 10% 的表单独收集而不是全库每晚跑一遍。命令是EXEC DBMS_STATS.GATHER_TABLE_STATS(SCHEMA_NAME,TABLE_NAME, cascadeTRUE);。cascadeTRUE会同时收集索引统计省一步操作。3. 索引与 SQL 改写把逻辑读降下来才是硬道理定位到问题 SQL 之后下一步是改。Oracle 性能优化里索引和 SQL 改写带来的收益通常远大于调 SGA 参数。这一章讲怎么选索引、怎么写 SQL、怎么避免常见的改写翻车。3.1 联合索引的列顺序由谓词选择性决定建联合索引时列顺序不是拍脑袋定的。原则是等值谓词列放前面范围谓词列放后面选择性高的列优先。比如下面这条查询SELECT order_id, customer_id, order_date, amount FROM orders WHERE customer_id 10086 AND order_date DATE 2024-01-01 AND status PAID;假设customer_id有 10 万种取值status只有 5 种取值。那么索引应该建为(customer_id, status, order_date)而不是(status, customer_id, order_date)。因为customer_id等值过滤后剩余数据量最小status再过滤最后order_date做范围扫描。建索引语句CREATE INDEX idx_orders_cust_status_date ON orders (customer_id, status, order_date) TABLESPACE indx_ts COMPUTE STATISTICS;COMPUTE STATISTICS在 10g 之后已不推荐改用DBMS_STATS.GATHER_INDEX_STATS单独收集。另外如果orders表更新频繁索引会带来维护成本DML密集的表索引数量控制在 5 个以内比较稳妥。3.2 用绑定变量但别让窥探毁掉计划绑定变量能减少硬解析但 Oracle 的绑定变量窥探bind peeking在 11g 之前只窥探第一次传入的值如果第一次传的是极端值后续所有执行都沿用那个计划。11g 引入自适应游标共享ACS后有所缓解但并不是万能。常见做法是对数据分布均匀的列用绑定变量对分布倾斜严重的列比如status只有几个值但某值占 90%考虑用字面量或/* BIND_AWARE */提示。检查是否发生窥探导致计划偏差可以查v$sql的is_bind_sensitive和is_bind_aware字段SELECT sql_id, plan_hash_value, is_bind_sensitive, is_bind_aware, executions FROM v$sql WHERE sql_text LIKE %orders%customer_id% AND rownum 10;如果is_bind_sensitive为 Y 而is_bind_aware为 N说明这个游标对绑定值敏感但没有启用自适应共享可能需要手动干预。3.3 SQL 改写的三个安全边界改写 SQL 时有三条边界不能碰。第一不要为了走索引而在列上做函数运算比如WHERE TO_CHAR(order_date,YYYY)2024会让order_date上的索引失效改成WHERE order_date DATE 2024-01-01 AND order_date DATE 2025-01-01。第二NOT IN遇到 NULL 值会返回空结果改用NOT EXISTS或确保列上有 NOT NULL 约束。第三UNION会排序去重如果业务允许重复行用UNION ALL能省掉大量排序开销。一个实际改写例子原语句用SELECT * FROM (SELECT ... ORDER BY ...) WHERE ROWNUM 20做分页在 12c 之前这是标准写法但内层排序会处理全部结果集。如果表很大改成先过滤再排序或者用ROW_NUMBER() OVER配合索引扫描逻辑读能从几十万降到几千。4. 内存与 I/O 参数SGA、PGA 和临时表空间的调整尺度SQL 和索引改完之后如果还有瓶颈才轮到内存和 I/O 参数。这一章讲三个最常调的参数以及调错之后的典型症状。4.1 SGA_TARGET 与 MEMORY_TARGET 的取舍Oracle 11g 之后支持自动内存管理AMM用MEMORY_TARGET统一管理 SGA 和 PGA。但 AMM 依赖/dev/shm在部分 Linux 发行版上需要额外配置而且一旦设置不当可能触发 ORA-00845 错误。生产环境更常见的做法是只用 ASMM自动共享内存管理即设置SGA_TARGET和PGA_AGGREGATE_TARGET不设MEMORY_TARGET。查看当前 SGA 各组件分配SELECT component, current_size/1024/1024 AS mb, min_size/1024/1024 AS min_mb FROM v$sga_dynamic_components ORDER BY current_size DESC;如果DEFAULT buffer cache远大于shared pool而 AWR 里library cache等待事件靠前说明共享池偏小可以适当调大SHARED_POOL_SIZE。但不要超过 SGA 总量的 40%否则 buffer cache 被挤压物理读会上升。调整命令ALTER SYSTEM SET SGA_TARGET 8G SCOPE SPFILE; ALTER SYSTEM SET SHARED_POOL_SIZE 2G SCOPE SPFILE; -- 重启实例生效SCOPESPFILE表示只改参数文件重启后生效。如果当前实例支持在线调整可以用SCOPEBOTH但SGA_TARGET的调整通常需要重启才能重新分配内存。4.2 PGA_AGGREGATE_TARGET 与排序溢出PGA 主要给排序、哈希连接、位图索引使用。如果PGA_AGGREGATE_TARGET设得太小大量排序会溢出到临时表空间表现为direct path write temp等待事件。查 PGA 使用情况SELECT name, value/1024/1024 AS mb FROM v$pgastat WHERE name IN (aggregate PGA target parameter, aggregate PGA auto target, total PGA inuse, total PGA allocated, over allocation count, cache hit percentage);cache hit percentage低于 90% 说明 PGA 不够over allocation count大于 0 说明曾经超分配。调整PGA_AGGREGATE_TARGET一般设为物理内存的 20% 左右但也要看并发连接数。如果单个会话需要大排序可以临时用ALTER SESSION SET SORT_AREA_SIZE或WORKAREA_SIZE_POLICYMANUAL单独控制但不要全局改成手动否则容易 OOM。4.3 临时表空间组与临时文件扩展临时表空间不足时排序会报 ORA-01652。查临时表空间使用率SELECT tablespace_name, bytes_used/1024/1024 AS used_mb, bytes_free/1024/1024 AS free_mb FROM v$temp_space_header ORDER BY bytes_used DESC;如果使用率长期超过 70%建议加临时文件而不是扩单个文件因为临时文件可以并行写。命令ALTER TABLESPACE temp ADD TEMPFILE /u01/app/oracle/oradata/temp02.dbf SIZE 4G AUTOEXTEND ON NEXT 1G MAXSIZE 16G;AUTOEXTEND ON要设MAXSIZE否则可能把文件系统撑满。另外如果实例有多个临时表空间可以建临时表空间组让不同会话分散 I/OCREATE TEMPORARY TABLESPACE temp_grp1 TEMPFILE ... SIZE 4G; CREATE TEMPORARY TABLESPACE temp_grp2 TEMPFILE ... SIZE 4G; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp1; -- 把 temp_grp2 加入组 ALTER TABLESPACE temp_grp2 TABLESPACE GROUP temp_group;5. 避坑与排查五个让优化前功尽弃的典型操作性能优化最怕的不是没效果而是改完之后出了新问题。下面五条是实际运维中反复出现的翻车场景每条按现象、原因、解决写清楚。5.1 收集统计信息后计划反而变差现象刚跑完DBMS_STATS.GATHER_SCHEMA_STATS原本正常的 SQL 突然走全表扫描。原因统计信息刷新后优化器认为全表扫描成本更低或者直方图变化导致选择性估算偏差。解决先别急着回滚统计信息用DBMS_STATS.LOCK_TABLE_STATS锁定问题表的统计信息再用SQL PLAN BASELINE固定执行计划。命令-- 锁定统计信息 EXEC DBMS_STATS.LOCK_TABLE_STATS(SCHEMA_NAME,ORDERS); -- 从游标缓存加载计划基线 DECLARE l_plans PLS_INTEGER; BEGIN l_plans : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id 9f8d3k2m1p0q); END; /5.2 索引建了但没被用上现象明明建了索引执行计划还是全表扫描。原因列上有隐式类型转换比如WHERE customer_id 10086而customer_id是 NUMBER 类型Oracle 会把列转成字符串索引失效。解决检查v$sql_plan的filter_predicates字段看是否有TO_CHAR或TO_NUMBER包裹列。改 SQL 让绑定值类型与列类型一致。5.3 并行度调高后 CPU 打满现象为了加快批量任务把PARALLEL_DEGREE_POLICY设为 AUTO结果多个会话同时并行CPU 使用率飙到 100%正常交易也变慢。原因并行度没有限制多个大查询争抢 CPU。解决用资源管理器Resource Manager限制并行消费者组或者对特定 SQL 用/* PARALLEL(4) */显式指定并行度不要全局开 AUTO。查当前并行会话SELECT sid, serial#, degree, sql_id FROM v$px_session WHERE qcsid IS NOT NULL;5.4 调整 SGA 后实例起不来现象改完SGA_TARGET重启报 ORA-27102 out of memory。原因SGA 超过了操作系统共享内存限制或者/dev/shm空间不足。解决检查df -h /dev/shm和ipcs -lm的max seg size确保 SGA 不超过两者最小值。如果是 AMM 模式还要确认MEMORY_TARGET不超过MEMORY_MAX_TARGET。5.5 清理监听日志导致监听无法启动现象手动删了listener.log后lsnrctl start报错。原因Linux 下文件被删除但进程仍持有句柄直接删文件会导致监听写入失败。解决不要直接rm用lsnrctl set log_status off关闭日志再清空文件内容 listener.log最后lsnrctl set log_status on。如果已经删了重启监听进程即可恢复。6. 用 SQL 计划基线和实时监控把优化效果锁住优化做完不是终点能不能稳住才是。Oracle 的 SQL Plan Baseline 和实时 SQL 监控是两个被低估的工具。前者把验证过的执行计划固化下来后者让你在 SQL 还在跑的时候就能看到它卡在哪一步。先看计划基线的用法。假设你已经确认某个 SQL_ID 的计划是好的想让它以后都走这个计划-- 从游标缓存加载计划基线 DECLARE l_plans PLS_INTEGER; BEGIN l_plans : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id 9f8d3k2m1p0q, plan_hash_value 1234567890 ); DBMS_OUTPUT.PUT_LINE(Loaded plans: || l_plans); END; /加载后优化器会优先选择基线中的计划。如果后续统计信息变化导致新计划出现新计划会先进入“未验证”状态不会立即生效。查基线SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE %orders%;fixed为 YES 表示该计划被固定优化器不会再选其他计划。accepted为 YES 表示已验证。如果发现某个基线导致性能下降可以用DBMS_SPM.ALTER_SQL_PLAN_BASELINE把它设为enabledNO而不是直接删掉。再看实时 SQL 监控。对于执行时间超过 5 秒的 SQLOracle 会自动开启实时监控前提是statistics_levelTYPICAL且control_management_pack_access不为 NONE。查正在跑的 SQLSELECT sql_id, status, elapsed_time/1000000 AS elapsed_sec, cpu_time/1000000 AS cpu_sec, buffer_gets, disk_reads FROM v$sql_monitor WHERE status EXECUTING ORDER BY elapsed_time DESC;拿到sql_id后可以看它的执行计划每个步骤的实际行数和时间SELECT plan_line_id, plan_operation, plan_options, output_rows, elapsed_time/1000000 AS elapsed_sec FROM v$sql_monitor WHERE sql_id 9f8d3k2m1p0q ORDER BY plan_line_id;这个视图的好处是SQL 还在跑的时候就能看到哪个步骤最慢不用等它跑完。如果发现某个TABLE ACCESS FULL步骤的output_rows远大于估算说明统计信息或绑定变量有问题可以当场决定是否 kill 掉重来。最后说一个我自己的习惯每次优化完把 AWR 快照 ID、SQL_ID、调整前后的逻辑读和响应时间记在一个表格里过一周再拉一次 AWR 对比。如果逻辑读没降但响应时间降了可能是缓存命中率变化如果两者都降了说明优化真正生效。这个习惯帮我避免了很多“感觉快了”的误判。希望帮到你。本文还有配套的精品资源点击获取 Oracle性能优化实战：从AWR诊断到SQL计划基线
简介这份Oracle数据库性能优化PDF文档面向数据库管理员、后端开发及运维人员针对大数据量、高并发场景下系统响应变慢、性能持续下降等实际问题梳理了一套可落地的调优思路。内容围绕数据库服务器内存参数调整与SQL语句优化两大主线展开涵盖系统全局区中共享池、数据缓冲区、日志缓冲区的合理配置以及驱动表选择、WHERE子句条件顺序、避免SELECT *、用WHERE替代HAVING等具体技巧并延伸至索引管理、分区策略、回滚段优化与执行计划控制等方向。资源包共1个PDF文件约128KB篇幅精炼适合作为日常调优的速查参考。目前已有1501人学习下载读者可从中获得内存参数设定区间、SQL改写规则与整体优化框架便于结合自身系统负载持续监控与调整。1. Oracle 性能优化不是调参玄学从一份 PDF 标题说开去很多人第一次接触 Oracle 数据库性能优化是从一份名为《oracle数据库性能优化.pdf》的文档开始的。它可能来自前辈的分享、培训班的资料或者某个技术群里流传的“内部手册”。但真正翻开之后你会发现里面讲的往往是零散的等待事件、索引建议、SGA 参数缺少一条从“定位问题”到“验证效果”的完整链路。这篇文章要做的就是把这个标题背后的东西拆开Oracle 性能优化到底在优化什么、用什么工具定位、参数怎么改、哪些坑一踩就翻车。适合已经能写 SQL、管过一两个实例但面对“系统变慢”时还靠猜的从业者。下面按“先能看见问题再动手改最后能验证”的顺序讲每一步都落到可执行的命令和参数上。2. 先让数据库开口说话AWR、ASH 与等待事件定位法性能优化最怕的不是问题难而是不知道问题在哪。Oracle 自带的诊断工具已经足够回答“谁在等、等什么、等多久”这三个问题。这一章先把观测手段立住后面所有调整才有依据。2.1 用 AWR 报告锁定 Top 等待事件与 SQLAWRAutomatic Workload Repository是 Oracle 自带的历史性能快照仓库默认每小时采集一次保留 8 天。生成一份 AWR 报告只需要两个快照 ID命令如下-- 先查最近可用的快照确定起止 snap_id SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 20 ROWS ONLY; -- 在 SQL*Plus 中生成 AWR 报告需 SYSDBA 或 SELECT_CATALOG_ROLE -- 假设起止快照为 1201 和 1202 ?/rdbms/admin/awrrpt.sql -- 交互中输入 report_type htmlbegin_snap 1201end_snap 1202生成后重点看三块Top 10 Foreground Events 按 DB Time 占比排序如果db file sequential read排第一说明大量单块读索引或 SQL 访问路径可能有问题如果log file sync靠前说明提交过于频繁SQL ordered by Elapsed Time 则直接列出最耗时的语句拿到 SQL_ID 后可以用SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id))看历史执行计划。参数上statistics_level必须为 TYPICAL 或 ALL否则很多等待事件统计不到。检查命令是SHOW PARAMETER statistics_level。如果实例是 11g 及以上默认就是 TYPICAL一般不用改。AWR 的采样间隔由dbms_workload_repository.modify_snapshot_settings控制生产库不建议把 interval 调到 30 分钟以下否则快照本身会带来额外 I/O。2.2 ASH 抓瞬时卡顿与阻塞链AWR 是小时级粒度遇到“每天下午三点卡五分钟”这种问题AWR 可能只看到一个平均值。ASHActive Session History按秒采样活动会话更适合抓瞬时峰值。查最近 30 分钟内的等待分布SELECT session_state, event, COUNT(*) AS samples FROM v$active_session_history WHERE sample_time SYSDATE - 30/1440 GROUP BY session_state, event ORDER BY samples DESC;如果看到大量enq: TX - row lock contention说明有行锁阻塞。进一步用blocking_session字段追阻塞源SELECT sample_time, session_id, blocking_session, event, sql_id FROM v$active_session_history WHERE blocking_session IS NOT NULL AND sample_time SYSDATE - 30/1440 ORDER BY sample_time DESC;拿到blocking_session后去v$session里查它的sql_id、machine、program基本就能定位到是哪台应用、哪条语句持锁不放。这一步是排查“数据库突然变慢”最有效的入口比盲目看 AWR 快得多。2.3 执行计划里最容易看错的三个点拿到 SQL_ID 之后DBMS_XPLAN.DISPLAY_CURSOR是必用工具SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(9f8d3k2m1p0q, NULL, ALLSTATS LAST));看执行计划时三个地方最容易误判。第一Rows是估算值A-Rows才是实际返回行数两者差几个数量级说明统计信息过期或绑定变量窥探导致计划偏差。第二Buffers列反映逻辑读如果某个步骤逻辑读远高于其他步骤即使它不在最内层也可能是真正的瓶颈。第三Note部分如果出现dynamic sampling used说明优化器对表统计信息不信任临时采样会消耗额外资源需要检查DBMS_STATS的收集策略。统计信息收集的常见做法是对变化量超过 10% 的表单独收集而不是全库每晚跑一遍。命令是EXEC DBMS_STATS.GATHER_TABLE_STATS(SCHEMA_NAME,TABLE_NAME, cascadeTRUE);。cascadeTRUE会同时收集索引统计省一步操作。3. 索引与 SQL 改写把逻辑读降下来才是硬道理定位到问题 SQL 之后下一步是改。Oracle 性能优化里索引和 SQL 改写带来的收益通常远大于调 SGA 参数。这一章讲怎么选索引、怎么写 SQL、怎么避免常见的改写翻车。3.1 联合索引的列顺序由谓词选择性决定建联合索引时列顺序不是拍脑袋定的。原则是等值谓词列放前面范围谓词列放后面选择性高的列优先。比如下面这条查询SELECT order_id, customer_id, order_date, amount FROM orders WHERE customer_id 10086 AND order_date DATE 2024-01-01 AND status PAID;假设customer_id有 10 万种取值status只有 5 种取值。那么索引应该建为(customer_id, status, order_date)而不是(status, customer_id, order_date)。因为customer_id等值过滤后剩余数据量最小status再过滤最后order_date做范围扫描。建索引语句CREATE INDEX idx_orders_cust_status_date ON orders (customer_id, status, order_date) TABLESPACE indx_ts COMPUTE STATISTICS;COMPUTE STATISTICS在 10g 之后已不推荐改用DBMS_STATS.GATHER_INDEX_STATS单独收集。另外如果orders表更新频繁索引会带来维护成本DML密集的表索引数量控制在 5 个以内比较稳妥。3.2 用绑定变量但别让窥探毁掉计划绑定变量能减少硬解析但 Oracle 的绑定变量窥探bind peeking在 11g 之前只窥探第一次传入的值如果第一次传的是极端值后续所有执行都沿用那个计划。11g 引入自适应游标共享ACS后有所缓解但并不是万能。常见做法是对数据分布均匀的列用绑定变量对分布倾斜严重的列比如status只有几个值但某值占 90%考虑用字面量或/* BIND_AWARE */提示。检查是否发生窥探导致计划偏差可以查v$sql的is_bind_sensitive和is_bind_aware字段SELECT sql_id, plan_hash_value, is_bind_sensitive, is_bind_aware, executions FROM v$sql WHERE sql_text LIKE %orders%customer_id% AND rownum 10;如果is_bind_sensitive为 Y 而is_bind_aware为 N说明这个游标对绑定值敏感但没有启用自适应共享可能需要手动干预。3.3 SQL 改写的三个安全边界改写 SQL 时有三条边界不能碰。第一不要为了走索引而在列上做函数运算比如WHERE TO_CHAR(order_date,YYYY)2024会让order_date上的索引失效改成WHERE order_date DATE 2024-01-01 AND order_date DATE 2025-01-01。第二NOT IN遇到 NULL 值会返回空结果改用NOT EXISTS或确保列上有 NOT NULL 约束。第三UNION会排序去重如果业务允许重复行用UNION ALL能省掉大量排序开销。一个实际改写例子原语句用SELECT * FROM (SELECT ... ORDER BY ...) WHERE ROWNUM 20做分页在 12c 之前这是标准写法但内层排序会处理全部结果集。如果表很大改成先过滤再排序或者用ROW_NUMBER() OVER配合索引扫描逻辑读能从几十万降到几千。4. 内存与 I/O 参数SGA、PGA 和临时表空间的调整尺度SQL 和索引改完之后如果还有瓶颈才轮到内存和 I/O 参数。这一章讲三个最常调的参数以及调错之后的典型症状。4.1 SGA_TARGET 与 MEMORY_TARGET 的取舍Oracle 11g 之后支持自动内存管理AMM用MEMORY_TARGET统一管理 SGA 和 PGA。但 AMM 依赖/dev/shm在部分 Linux 发行版上需要额外配置而且一旦设置不当可能触发 ORA-00845 错误。生产环境更常见的做法是只用 ASMM自动共享内存管理即设置SGA_TARGET和PGA_AGGREGATE_TARGET不设MEMORY_TARGET。查看当前 SGA 各组件分配SELECT component, current_size/1024/1024 AS mb, min_size/1024/1024 AS min_mb FROM v$sga_dynamic_components ORDER BY current_size DESC;如果DEFAULT buffer cache远大于shared pool而 AWR 里library cache等待事件靠前说明共享池偏小可以适当调大SHARED_POOL_SIZE。但不要超过 SGA 总量的 40%否则 buffer cache 被挤压物理读会上升。调整命令ALTER SYSTEM SET SGA_TARGET 8G SCOPE SPFILE; ALTER SYSTEM SET SHARED_POOL_SIZE 2G SCOPE SPFILE; -- 重启实例生效SCOPESPFILE表示只改参数文件重启后生效。如果当前实例支持在线调整可以用SCOPEBOTH但SGA_TARGET的调整通常需要重启才能重新分配内存。4.2 PGA_AGGREGATE_TARGET 与排序溢出PGA 主要给排序、哈希连接、位图索引使用。如果PGA_AGGREGATE_TARGET设得太小大量排序会溢出到临时表空间表现为direct path write temp等待事件。查 PGA 使用情况SELECT name, value/1024/1024 AS mb FROM v$pgastat WHERE name IN (aggregate PGA target parameter, aggregate PGA auto target, total PGA inuse, total PGA allocated, over allocation count, cache hit percentage);cache hit percentage低于 90% 说明 PGA 不够over allocation count大于 0 说明曾经超分配。调整PGA_AGGREGATE_TARGET一般设为物理内存的 20% 左右但也要看并发连接数。如果单个会话需要大排序可以临时用ALTER SESSION SET SORT_AREA_SIZE或WORKAREA_SIZE_POLICYMANUAL单独控制但不要全局改成手动否则容易 OOM。4.3 临时表空间组与临时文件扩展临时表空间不足时排序会报 ORA-01652。查临时表空间使用率SELECT tablespace_name, bytes_used/1024/1024 AS used_mb, bytes_free/1024/1024 AS free_mb FROM v$temp_space_header ORDER BY bytes_used DESC;如果使用率长期超过 70%建议加临时文件而不是扩单个文件因为临时文件可以并行写。命令ALTER TABLESPACE temp ADD TEMPFILE /u01/app/oracle/oradata/temp02.dbf SIZE 4G AUTOEXTEND ON NEXT 1G MAXSIZE 16G;AUTOEXTEND ON要设MAXSIZE否则可能把文件系统撑满。另外如果实例有多个临时表空间可以建临时表空间组让不同会话分散 I/OCREATE TEMPORARY TABLESPACE temp_grp1 TEMPFILE ... SIZE 4G; CREATE TEMPORARY TABLESPACE temp_grp2 TEMPFILE ... SIZE 4G; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp1; -- 把 temp_grp2 加入组 ALTER TABLESPACE temp_grp2 TABLESPACE GROUP temp_group;5. 避坑与排查五个让优化前功尽弃的典型操作性能优化最怕的不是没效果而是改完之后出了新问题。下面五条是实际运维中反复出现的翻车场景每条按现象、原因、解决写清楚。5.1 收集统计信息后计划反而变差现象刚跑完DBMS_STATS.GATHER_SCHEMA_STATS原本正常的 SQL 突然走全表扫描。原因统计信息刷新后优化器认为全表扫描成本更低或者直方图变化导致选择性估算偏差。解决先别急着回滚统计信息用DBMS_STATS.LOCK_TABLE_STATS锁定问题表的统计信息再用SQL PLAN BASELINE固定执行计划。命令-- 锁定统计信息 EXEC DBMS_STATS.LOCK_TABLE_STATS(SCHEMA_NAME,ORDERS); -- 从游标缓存加载计划基线 DECLARE l_plans PLS_INTEGER; BEGIN l_plans : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id 9f8d3k2m1p0q); END; /5.2 索引建了但没被用上现象明明建了索引执行计划还是全表扫描。原因列上有隐式类型转换比如WHERE customer_id 10086而customer_id是 NUMBER 类型Oracle 会把列转成字符串索引失效。解决检查v$sql_plan的filter_predicates字段看是否有TO_CHAR或TO_NUMBER包裹列。改 SQL 让绑定值类型与列类型一致。5.3 并行度调高后 CPU 打满现象为了加快批量任务把PARALLEL_DEGREE_POLICY设为 AUTO结果多个会话同时并行CPU 使用率飙到 100%正常交易也变慢。原因并行度没有限制多个大查询争抢 CPU。解决用资源管理器Resource Manager限制并行消费者组或者对特定 SQL 用/* PARALLEL(4) */显式指定并行度不要全局开 AUTO。查当前并行会话SELECT sid, serial#, degree, sql_id FROM v$px_session WHERE qcsid IS NOT NULL;5.4 调整 SGA 后实例起不来现象改完SGA_TARGET重启报 ORA-27102 out of memory。原因SGA 超过了操作系统共享内存限制或者/dev/shm空间不足。解决检查df -h /dev/shm和ipcs -lm的max seg size确保 SGA 不超过两者最小值。如果是 AMM 模式还要确认MEMORY_TARGET不超过MEMORY_MAX_TARGET。5.5 清理监听日志导致监听无法启动现象手动删了listener.log后lsnrctl start报错。原因Linux 下文件被删除但进程仍持有句柄直接删文件会导致监听写入失败。解决不要直接rm用lsnrctl set log_status off关闭日志再清空文件内容 listener.log最后lsnrctl set log_status on。如果已经删了重启监听进程即可恢复。6. 用 SQL 计划基线和实时监控把优化效果锁住优化做完不是终点能不能稳住才是。Oracle 的 SQL Plan Baseline 和实时 SQL 监控是两个被低估的工具。前者把验证过的执行计划固化下来后者让你在 SQL 还在跑的时候就能看到它卡在哪一步。先看计划基线的用法。假设你已经确认某个 SQL_ID 的计划是好的想让它以后都走这个计划-- 从游标缓存加载计划基线 DECLARE l_plans PLS_INTEGER; BEGIN l_plans : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id 9f8d3k2m1p0q, plan_hash_value 1234567890 ); DBMS_OUTPUT.PUT_LINE(Loaded plans: || l_plans); END; /加载后优化器会优先选择基线中的计划。如果后续统计信息变化导致新计划出现新计划会先进入“未验证”状态不会立即生效。查基线SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE %orders%;fixed为 YES 表示该计划被固定优化器不会再选其他计划。accepted为 YES 表示已验证。如果发现某个基线导致性能下降可以用DBMS_SPM.ALTER_SQL_PLAN_BASELINE把它设为enabledNO而不是直接删掉。再看实时 SQL 监控。对于执行时间超过 5 秒的 SQLOracle 会自动开启实时监控前提是statistics_levelTYPICAL且control_management_pack_access不为 NONE。查正在跑的 SQLSELECT sql_id, status, elapsed_time/1000000 AS elapsed_sec, cpu_time/1000000 AS cpu_sec, buffer_gets, disk_reads FROM v$sql_monitor WHERE status EXECUTING ORDER BY elapsed_time DESC;拿到sql_id后可以看它的执行计划每个步骤的实际行数和时间SELECT plan_line_id, plan_operation, plan_options, output_rows, elapsed_time/1000000 AS elapsed_sec FROM v$sql_monitor WHERE sql_id 9f8d3k2m1p0q ORDER BY plan_line_id;这个视图的好处是SQL 还在跑的时候就能看到哪个步骤最慢不用等它跑完。如果发现某个TABLE ACCESS FULL步骤的output_rows远大于估算说明统计信息或绑定变量有问题可以当场决定是否 kill 掉重来。最后说一个我自己的习惯每次优化完把 AWR 快照 ID、SQL_ID、调整前后的逻辑读和响应时间记在一个表格里过一周再拉一次 AWR 对比。如果逻辑读没降但响应时间降了可能是缓存命中率变化如果两者都降了说明优化真正生效。这个习惯帮我避免了很多“感觉快了”的误判。希望帮到你。本文还有配套的精品资源点击获取 RELATED READING
延伸阅读
更多一线实战笔记与深度复盘，助您持续精进
