Appearance

Oracle 性能排查 ​

Oracle 性能排查要围绕执行计划、等待事件、统计信息、索引、锁和资源使用展开。

学习目标 ​

学完本页，你要能做到：

目标能力
能定位问题从接口慢定位到 SQL_ID、会话、等待事件和执行计划
能读计划看懂 E-Rows、A-Rows、Buffers、Reads、Predicate Information、Peeked Binds
能理解优化器知道统计信息、直方图、绑定变量窥探、聚簇因子为什么会影响计划
能排等待能区分 IO、锁、日志同步、临时表空间、网络、并发竞争
能处理商业场景能处理资产列表、采集报表、批处理、热点更新、频繁提交这些常见问题
能面试回答不只说“看执行计划”，而是能讲完整诊断链路和原理

排查流程 ​

mermaid
flowchart TD
    A["系统慢"] --> B["定位 SQL"]
    B --> C["查看执行计划"]
    C --> D{"是否扫描过多"}
    D -- "是" --> E["索引 / 统计信息 / SQL 改写"]
    D -- "否" --> F{"是否等待事件"}
    F -- "是" --> G["分析 IO / 锁 / latch / 网络等待"]
    F -- "否" --> H["检查 CPU / PGA / 临时表空间"]

执行计划 ​

sql
explain plan for
select id, asset_no
from asset
where owner_id = 1001;

select * from table(dbms_xplan.display);

更真实的分析常用 dbms_xplan.display_cursor 查看实际执行过的 SQL。

常见问题 ​

问题表现
统计信息过期执行计划突然变化
索引选择错误走了低效索引或全表扫描
绑定变量窥探不同参数适合不同计划
锁等待SQL 本身快，并发时等待
临时表空间不足大排序、大 Hash、报表 SQL
Undo 不足长查询或大事务导致快照过旧

AWR 和 ASH ​

AWR 用于分析一段时间内数据库负载，ASH 用于分析活跃会话采样。它们是 Oracle 性能诊断常见工具，但需要相应授权。

零基础要先理解：Oracle 慢不等于 SQL 写错 ​

Oracle 慢通常有四类原因：

类型典型表现本质原因
SQL 访问路径错本来应该走索引，却全表扫描优化器估算、统计信息、谓词、索引设计不合适
等待资源SQL 单独跑很快，并发时很慢锁、IO、网络、latch、buffer busy、日志同步等待
内存和临时空间不足大排序、大 Hash、报表突然慢PGA 不够、临时表空间压力、执行计划需要大量中间结果
事务和 Undo 问题长查询报快照过旧，大事务拖慢系统Undo 保留不足、长事务占用版本、提交节奏不合理

所以 Oracle 排查不能只看 SQL 文本，要按“SQL -> 执行计划 -> 等待事件 -> 资源 -> 事务”的链路查。

商业排查闭环 ​

mermaid
flowchart TD
    A["用户反馈慢"] --> B["确认慢的是接口、批任务还是报表"]
    B --> C["定位 SQL_ID 和会话"]
    C --> D["查看真实执行计划"]
    D --> E{"计划是否合理"}
    E -- "不合理" --> F["检查统计信息、索引、谓词、绑定变量"]
    E -- "合理但仍慢" --> G["查看等待事件"]
    G --> H{"主要等待类型"}
    H -- "IO等待" --> I["查全表扫描、随机读、存储延迟"]
    H -- "锁等待" --> J["查阻塞会话和未提交事务"]
    H -- "日志等待" --> K["查提交频率、redo压力、磁盘"]
    H -- "临时空间" --> L["查排序、Hash Join、Temp使用"]
    F --> M["改 SQL、索引、统计信息或绑定变量策略"]
    I --> M
    J --> M
    K --> M
    L --> M
    M --> N["压测或灰度验证"]

第一步：定位 SQL_ID ​

Oracle 生产排查首先要拿到 SQL_ID，因为 SQL 文本可能很长、可能被格式化、可能被绑定变量替换。SQL_ID 是定位执行计划、历史统计、AWR 报告的入口。

sql
select sql_id, status, event, wait_class, seconds_in_wait
from v$session
where username = 'APP_USER'
  and status = 'ACTIVE';

如果你只知道模块或机器，可以结合 machine、program、module、action 查。商业系统建议应用连接池设置模块名，方便定位来源。

第二步：看真实执行计划 ​

explain plan 只是优化器根据当前环境推测的计划，不一定等于线上刚刚执行的真实计划。排查线上问题更建议看 cursor 中实际执行过的计划。

sql
select *
from table(dbms_xplan.display_cursor(
  sql_id => '你的SQL_ID',
  cursor_child_no => null,
  format => 'ALLSTATS LAST +PEEKED_BINDS'
));

重点看：

字段怎么看
E-Rows优化器估算行数
A-Rows实际返回行数
Buffers逻辑读，越大代表访问数据块越多
Reads物理读，说明从磁盘读
Predicate Information条件是访问条件还是过滤条件
Peeked Binds绑定变量被窥探到的值

如果 E-Rows 和 A-Rows 差距很大，通常说明统计信息不准、数据倾斜、谓词选择性估错或绑定变量窥探导致计划不适合当前参数。

常见执行计划现象 ​

现象说明优化方向
TABLE ACCESS FULL全表扫描，不一定错，大表小比例查询通常不合适看条件选择性、索引、分区裁剪
INDEX RANGE SCAN走索引范围扫描看是否回表过多
TABLE ACCESS BY INDEX ROWID通过 ROWID 回表查询字段多或返回行多会变慢
NESTED LOOPS外层少、内层有索引时好外层结果大时可能灾难
HASH JOIN大表 Join 常见需要内存，可能打临时表空间
SORT ORDER BY排序可用合适索引减少排序，或限制返回行

统计信息为什么会影响性能 ​

Oracle 优化器是成本优化器。它不是“看到索引就用”，而是估算不同计划的成本后选择一个成本最低的计划。估算依赖统计信息：

  1. 表有多少行。
  2. 列有多少不同值。
  3. 数据是否倾斜。
  4. 索引高度、叶子块数量、聚簇因子。
  5. 分区统计和直方图。

如果统计信息过期，优化器可能以为某个条件只返回 10 行，实际返回 100 万行，于是选 Nested Loops；或者以为条件返回很多行，实际很少，于是放弃索引。

sql
begin
  dbms_stats.gather_table_stats(
    ownname => 'APP',
    tabname => 'ASSET',
    cascade => true,
    method_opt => 'for all columns size auto'
  );
end;
/

不要在业务高峰随意全量收集大表统计信息。生产中要结合数据变化窗口、分区、采样和发布策略。

绑定变量窥探是什么 ​

Oracle 为了复用 SQL，常使用绑定变量：

sql
select * from asset where org_id = :orgId and status = :status;

第一次硬解析时，优化器可能“窥探”绑定变量值，并基于这个值生成执行计划。如果第一次传的是小机构 org_id=1，只命中几十行，计划可能适合索引；后面传超级大机构 org_id=999，命中百万行，继续复用小机构计划就可能很慢。

这不是“绑定变量不好”，而是数据倾斜下同一 SQL 的不同参数适合不同计划。解决方向包括直方图、SQL Plan Baseline、Adaptive Cursor Sharing、SQL 改写、分区或按业务拆 SQL。

等待事件怎么读 ​

等待事件告诉你数据库会话“卡在哪里”。不要只看 CPU 使用率，Oracle 很多慢问题本质是等待。

等待类别常见含义排查方向
User I/O数据块读取慢SQL 是否读太多块、存储延迟、缓存命中
Concurrencylatch、buffer busy 等并发竞争热块、热点索引、并发更新
Application应用层锁等待查阻塞事务、未提交会话
Commit提交等待日志落盘提交过于频繁、redo 磁盘压力
Network网络传输等待大结果集、应用拉取慢、网络

常用查看当前会话等待：

sql
select sid, serial#, username, event, wait_class, seconds_in_wait
from v$session
where status = 'ACTIVE'
order by seconds_in_wait desc;

锁等待怎么排 ​

锁等待的特点是 SQL 逻辑不一定慢，但被别的事务挡住。比如库存更新、订单状态更新、审批流抢任务都可能发生。

sql
select
  s.sid,
  s.serial#,
  s.username,
  s.blocking_session,
  s.event,
  s.sql_id
from v$session s
where s.blocking_session is not null;

排查时要问：

  1. 谁阻塞了谁。
  2. 阻塞事务已经执行多久。
  3. 阻塞事务在等应用提交，还是 SQL 本身卡住。
  4. 是否有大事务批量更新没有分批提交。
  5. 是否缺少状态条件导致更新范围过大。

生产处理不能上来就 kill session。先确认业务影响、事务是否可回滚、是否会造成更大补偿成本。

商业场景：资产采集报表突然变慢 ​

场景：医疗资产平台每天采集设备数据，报表查询某院区某时间段的资产变化。上线初期很快，半年后慢到 30 秒。

sql
select asset_no, dept_id, status, updated_at
from asset_collect_record
where hospital_id = :hospitalId
  and updated_at between :beginTime and :endTime
order by updated_at desc;

排查链路：

  1. 查 SQL_ID，确认慢 SQL。
  2. 看真实执行计划，发现走全表扫描，A-Rows 远大于 E-Rows。
  3. 查统计信息，发现表每天大量写入但统计信息很久没更新。
  4. 看索引，只有 hospital_id 单列索引，无法同时支持时间范围和排序。
  5. 新增或调整联合索引：(hospital_id, updated_at)。
  6. 大表按时间分区后，查询可以分区裁剪。
  7. 收集统计信息，并在灰度环境验证执行计划。
sql
create index idx_collect_hospital_time
on asset_collect_record(hospital_id, updated_at);

如果查询字段较多，不要盲目把所有字段都塞进索引。Oracle 通过 ROWID 回表，返回行数少时没问题；返回几十万行时，即使走索引也可能慢，应该分页、归档、分区或改报表链路。

商业场景：订单状态更新慢但 SQL 很简单 ​

有时慢 SQL 看起来非常简单：

sql
update payment_order
set status = 'SUCCESS',
    paid_at = sysdate
where pay_no = :payNo
  and status = 'PAYING';

单独执行很快，但线上偶发卡几十秒。这种问题通常不是“SQL 写得复杂”，而是等待。

排查：

sql
select sid,
       serial#,
       username,
       event,
       wait_class,
       blocking_session,
       sql_id,
       seconds_in_wait
from v$session
where status = 'ACTIVE'
  and username = 'APP';

如果看到 enq: TX - row lock contention，说明会话在等行锁。常见原因：

  1. 另一个事务已经更新了同一行，但迟迟不提交。
  2. 上游接口把远程调用放进数据库事务，导致锁持有时间变长。
  3. 批处理扫到了在线交易正在更新的行。
  4. 缺少状态条件或唯一条件，更新范围比预期更大。

正确设计：

sql
update payment_order
set status = 'SUCCESS',
    paid_at = sysdate
where pay_no = :payNo
  and status = 'PAYING';

然后在应用里判断影响行数：

java
int rows = jdbcTemplate.update(sql, payNo);
if (rows == 0) {
    // 可能订单不存在、状态已变化、重复回调，走幂等查询和业务判断
}

为什么要带 status = 'PAYING'？因为它把“只有待支付才能改成功”交给数据库原子判断，避免两个并发请求后提交覆盖先提交。

商业场景：频繁 commit 导致 log file sync ​

批量导入时有人这样写：

java
for (AssetRow row : rows) {
    assetRepository.insert(row);
    connection.commit();
}

每插一行提交一次，Oracle 会频繁等待 LGWR 把 Redo 写入 redo log 文件。等待事件常见为 log file sync。

mermaid
flowchart TD
    A["应用每行 insert"] --> B["每行 commit"]
    B --> C["会话等待 LGWR"]
    C --> D["LGWR 写 redo log"]
    D --> E["返回 commit 成功"]
    E --> F["下一行继续重复"]

问题：

问题解释
提交次数太多每次 commit 都要等待提交相关 Redo 持久化
吞吐下降大量小事务把顺序写优势打碎
Redo 压力上升日志写入和归档压力增加
业务恢复困难每行成功失败分散，要额外记录状态

更合理的做法是按批提交，例如每 500 或 1000 行一个事务，并记录批次状态：

java
int batchSize = 500;
for (List<AssetRow> batch : partition(rows, batchSize)) {
    transactionTemplate.executeWithoutResult(status -> {
        for (AssetRow row : batch) {
            assetRepository.insert(row);
        }
        importLogRepository.markBatchSuccess(batch);
    });
}

批大小不是越大越好。太小会 log file sync 频繁，太大会 Undo/Redo 暴涨、锁持有久、失败回滚慢。要根据单行大小、索引数量、数据库压力和恢复要求压测确定。

商业场景：大排序打满临时表空间 ​

报表常见 SQL：

sql
select hospital_id, dept_id, count(*) cnt
from asset_collect_record
where updated_at >= :beginTime
  and updated_at < :endTime
group by hospital_id, dept_id
order by cnt desc;

如果过滤范围很大，Oracle 可能需要 Hash Group By、Sort Order By，并使用 PGA。PGA 不够时会把中间结果写到临时表空间，表现为：

  1. SQL 耗时突然变长。
  2. 临时表空间使用率升高。
  3. 等待事件出现 direct path read temp、direct path write temp。

查看临时空间使用方向：

sql
select s.sid,
       s.serial#,
       s.username,
       u.tablespace,
       u.blocks
from v$sort_usage u
join v$session s
  on u.session_addr = s.saddr;

优化方向：

方向说明
缩小时间范围不要一次扫多年数据
预聚合把日报、月报提前汇总
分区裁剪按时间分区，让查询只扫目标分区
合理索引让过滤先减少数据量
独立报表库核心交易库不要承担重报表
调整 PGA需要 DBA 结合全库负载评估，不是单 SQL 随意调

绑定变量窥探和执行计划抖动 ​

绑定变量能减少硬解析，但数据倾斜时可能带来计划复用问题。比如：

sql
select *
from asset_collect_record
where hospital_id = :hospitalId
  and status = :status;

如果 hospital_id=1 只有 1000 行，hospital_id=999 有 5000 万行，两者适合的计划可能完全不同：

参数更可能适合
小医院走索引，按 ROWID 回表
超大医院分区裁剪、全表/分区扫描、并行或报表链路

执行计划抖动常见现象：

  1. 同一 SQL_ID 有多个 child cursor。
  2. 某些参数很快，某些参数很慢。
  3. 发布后或统计信息刷新后，计划突然变化。
  4. E-Rows 和 A-Rows 差距大。

排查方向：

sql
select sql_id, child_number, plan_hash_value, executions, elapsed_time
from v$sql
where sql_id = '你的SQL_ID'
order by child_number;

处理方向：

方案适用
收集直方图列值明显倾斜，优化器需要知道热门值
Adaptive Cursor Sharing不同绑定值适合不同计划
SQL Plan Baseline核心 SQL 需要稳定计划
SQL 拆分大客户和普通客户走不同 SQL 或不同链路
分区让大范围查询可裁剪

不要一看到绑定变量窥探就改成拼接 SQL。拼接字面量会导致硬解析暴增、Shared Pool 压力变大，通常是把一个问题换成另一个问题。

AWR 和 ASH 怎么用才不是背名词 ​

AWR 和 ASH 是 Oracle 性能诊断的重要证据，但它们不是“自动调优魔法”。

工具看什么粒度
AWR一段时间内数据库负载、Top SQL、等待事件、IO、实例效率时间段汇总
ASH活跃会话采样，某个时刻谁在等什么、执行什么 SQL活跃会话采样

一次真实排查可以这样用：

mermaid
flowchart TD
    A["确认慢的时间窗口"] --> B["看 AWR Top Timed Events"]
    B --> C["找主要等待类型"]
    C --> D["看 Top SQL by Elapsed/CPU/Reads"]
    D --> E["拿 SQL_ID 看执行计划"]
    E --> F["用 ASH 看慢时段会话等待"]
    F --> G["结合业务发布、批任务、数据增长判断根因"]

例子：

AWR 现象可能方向
db file sequential read 高大量索引回表随机读，可能返回行太多
db file scattered read 高全表扫描或多块读多
log file sync 高commit 频繁或 redo 写慢
enq: TX - row lock contention 高行锁等待
direct path read/write temp 高排序、Hash、报表落临时空间
CPU Time 高硬解析、函数计算、低效 SQL、并发过高

注意：AWR/ASH 涉及授权，学习时知道原理和诊断方向；生产使用要遵守企业授权和 DBA 规范。

Oracle 性能排查清单 ​

现象优先检查常见处理
单条 SQL 慢SQL_ID、真实计划、E-Rows/A-Rows、Buffers索引、统计信息、SQL 改写、分区
某些参数慢绑定变量、直方图、child cursor、数据倾斜直方图、ACS、拆 SQL、Baseline
并发时慢等待事件、锁、阻塞会话缩短事务、修更新条件、分批
批处理拖垮系统Undo/Redo、commit 频率、锁、临时空间分批、限速、错峰、独立批处理库
报表慢Temp、PGA、全表扫描、分区裁剪预聚合、数仓、分区、报表库
计划突然变统计信息刷新、发布、索引变化、绑定变量固定计划、回滚变更、重收统计

面试追问：为什么走索引还是慢 ​

标准回答不能只说“索引失效”。Oracle 走索引仍然慢，常见原因是：

  1. 条件选择性差，返回行太多。
  2. 索引扫描后大量 TABLE ACCESS BY INDEX ROWID 回表，随机读成本高。
  3. 查询字段太多，无法只从索引拿结果。
  4. 聚簇因子差，相邻索引键对应的数据块分散。
  5. 统计信息估错，让优化器低估了回表成本。
  6. 并发下不是扫描慢，而是在等锁或 IO。

可以这样回答：

text
Oracle 走索引只是说明访问路径使用了索引，不代表一定快。B-tree 叶子块保存 key 和 ROWID，命中索引后通常还要根据 ROWID 回表。如果返回行很多，回表会产生大量随机读，可能比全表扫描更慢。排查时要看执行计划中的 TABLE ACCESS BY INDEX ROWID、Buffers、Reads、E-Rows 和 A-Rows。如果估算行数远小于实际行数，优化器可能低估回表成本；如果等待事件是锁或 IO，慢也不一定是索引设计本身。

常见误区 ​

误区为什么错
有索引一定快返回行太多、回表太多、统计信息不准时可能更慢
全表扫描一定错小表或返回大部分数据时全表扫描可能更合理
AWR 能自动解决问题AWR 只是证据，仍要理解 SQL、等待和业务
慢就加并行并行会消耗更多资源，可能拖垮整库
慢就 kill 会话可能导致长时间回滚，也可能破坏业务补偿链路

面试标准回答 ​

text
Oracle 性能排查要先定位慢 SQL，再看执行计划、统计信息、索引选择和等待事件。统计信息过期可能导致优化器选择错误计划；大排序和 Hash 可能消耗 PGA 或临时表空间；锁等待会导致 SQL 单独执行快但并发慢；长查询和 Undo 保留不足可能出现快照过旧。生产中常结合执行计划、AWR、ASH、等待事件和会话信息综合分析。