Oracle 核心全过程原理

Oracle 不能只背“实例、SGA、PGA、Undo、Redo、SCN”。这些词如果不串到一条 SQL 的执行过程里，遇到慢 SQL、锁等待、归档日志暴涨、snapshot too old、执行计划变化时还是会懵。

这一页按商业项目视角，把 Oracle 的核心链路讲清楚：

客户端连接进来后由谁处理。
一条 select 怎么解析、优化、读块、做一致性读。
一条 update 怎么产生 Undo、Redo、锁和 SCN。
B-tree 索引为什么通过 ROWID 回表。
Shared Pool、Buffer Cache、Redo Log Buffer、PGA 分别在什么时候参与。
提交时为什么不是直接把所有数据块写到磁盘。
慢 SQL 和锁等待怎么按层排查。

学习目标

问题	学完后要能说清
实例和数据库区别	实例是内存和进程，数据库是物理文件
SGA/PGA 怎么参与 SQL	SGA 共享缓存 SQL、数据块和 redo，PGA 保存会话私有排序 Hash 等
`select` 怎么执行	解析、优化、执行、读块、必要时一致性读
`update` 怎么执行	找行、加锁、写 Undo、改 Buffer Cache、写 Redo、提交
Undo 和 Redo 区别	Undo 保存旧值用于回滚和一致性读，Redo 记录变化用于恢复
SCN 解决什么	标识一致性时间点、提交顺序和恢复位置
B-tree 索引怎么定位行	root/branch/leaf 找 key，leaf 里拿 ROWID，再访问表块
性能怎么排查	执行计划、等待事件、Buffer Gets、物理读、锁、统计信息

Oracle 总体结构

Oracle 要先分清两个概念：实例和数据库。

mermaid

flowchart TD
    A["客户端会话"] --> B["Server Process"]
    B --> C["Oracle Instance"]
    C --> D["SGA 共享内存"]
    C --> E["后台进程"]
    D --> F["Shared Pool"]
    D --> G["Database Buffer Cache"]
    D --> H["Redo Log Buffer"]
    B --> I["PGA 私有内存"]
    E --> J["DBWR / LGWR / CKPT / SMON / PMON"]
    C --> K["Oracle Database"]
    K --> L["Data Files"]
    K --> M["Control Files"]
    K --> N["Redo Log Files"]

部分	作用	类比
Server Process	处理用户会话 SQL	专门服务员
SGA	多会话共享内存	公共工作区
Shared Pool	缓存 SQL、执行计划、数据字典	SQL 和元数据缓存
Buffer Cache	缓存数据块和索引块	数据页缓存
Redo Log Buffer	暂存 redo 记录	日志缓冲
PGA	会话私有内存，排序、Hash、会话状态	私人草稿纸
DBWR	把脏块写回数据文件	刷数据块
LGWR	把 redo 写到 redo log 文件	刷日志
CKPT	协调检查点	标记恢复位置

一条 SELECT 的全过程

示例：

sql

select id, asset_no
from asset
where owner_id = 1001
order by created_at desc
fetch first 20 rows only;

执行流程：

mermaid

flowchart TD
    A["客户端发送 SQL"] --> B["Server Process 接收"]
    B --> C["检查 Shared Pool 是否已有可复用游标"]
    C --> D{"能否软解析"}
    D -- "是" --> E["复用执行计划"]
    D -- "否" --> F["硬解析：语法、语义、权限检查"]
    F --> G["优化器生成执行计划"]
    G --> H["执行计划开始执行"]
    E --> H
    H --> I["通过索引或全表扫描读取块"]
    I --> J["Buffer Cache 命中则读内存"]
    I --> K["未命中则物理读数据文件"]
    J --> L["一致性读检查"]
    K --> L
    L --> M["PGA 中排序或取 TopN"]
    M --> N["返回结果"]

软解析和硬解析

解析方式	做什么	性能影响
软解析	SQL 和执行计划可复用	成本较低
硬解析	语法语义检查、权限检查、优化器生成计划	成本高，消耗 CPU 和 Shared Pool

为什么商业项目强调绑定变量：

sql

-- 不推荐：每个值都是不同 SQL 文本，容易硬解析多
select * from asset where owner_id = 1001;
select * from asset where owner_id = 1002;

-- 推荐：SQL 文本稳定，变量变化
select * from asset where owner_id = :owner_id;

如果高并发系统里大量拼接字面量 SQL，会造成硬解析压力、Shared Pool 抖动和 Library Cache 竞争。

Buffer Cache 怎么参与读取

Oracle 的数据不是一行一行从磁盘读，而是以 block 为基本单位。

mermaid

flowchart TD
    A["执行器需要某个数据块"] --> B{"Buffer Cache 是否有该块"}
    B -- "有" --> C["逻辑读 buffer get"]
    B -- "没有" --> D["从数据文件物理读"]
    D --> E["放入 Buffer Cache"]
    E --> C
    C --> F["读取行数据"]

指标	含义
Buffer Gets	逻辑读，从 Buffer Cache 访问块的次数
Physical Reads	物理读，从磁盘读取块
Consistent Gets	一致性读，需要按查询 SCN 读取一致版本
Current Gets	当前读，通常用于更新、锁定等需要当前版本的场景

SQL 慢不一定是物理读高。一个 SQL 即使都命中缓存，如果 Buffer Gets 特别高，也说明扫描块太多，CPU 和缓存压力仍然大。

B-tree 索引和 ROWID 回表

Oracle B-tree 索引一般由 root block、branch block、leaf block 组成。leaf block 中保存 key 和 ROWID。

mermaid

flowchart TD
    A["Root Block"] --> B["Branch Block"]
    B --> C["Leaf Block"]
    C --> D["Key + ROWID"]
    D --> E["根据 ROWID 访问表数据块"]
    E --> F["读取完整行"]

ROWID 可以理解为 Oracle 定位一行物理位置的地址信息。索引查找分两步：

先在索引树里找到 key 对应的 ROWID。
再根据 ROWID 去表数据块读取完整行。

如果查询字段都在索引里，可能避免访问表块；如果需要其他列，就会通过 ROWID 回表。少量回表没问题，大量回表会造成大量随机访问。

一致性读和 SCN

Oracle 的一致性读依赖 SCN 和 Undo。

SCN 可以理解为 Oracle 内部的一致性时间点。查询开始时会确定一个查询 SCN，读取时要看到这个时间点已经提交的数据。

mermaid

flowchart TD
    A["select 开始"] --> B["确定查询 SCN"]
    B --> C["读取数据块"]
    C --> D{"块中行版本是否晚于查询 SCN"}
    D -- "否" --> E["直接返回当前块中的版本"]
    D -- "是" --> F["根据 Undo 构造旧版本"]
    F --> G["返回查询 SCN 可见版本"]

为什么需要 Undo：

场景	Undo 的作用
事务回滚	用旧值撤销修改
一致性读	构造查询开始时可见的旧版本
闪回能力	在一定范围内查询历史状态

如果 Undo 中需要的旧版本已经被覆盖，可能出现：

text

ORA-01555: snapshot too old

这通常和长查询、Undo 保留不足、更新量大有关。

一条 UPDATE 的全过程

示例：

sql

update asset
set status = 'USED',
    updated_at = sysdate
where asset_no = 'A202607050001'
  and status = 'IDLE';

过程：

mermaid

flowchart TD
    A["定位目标行"] --> B["对目标行加锁"]
    B --> C["生成 Undo 旧值"]
    C --> D["在 Buffer Cache 修改数据块"]
    D --> E["生成 Redo 记录"]
    E --> F["Redo 进入 Redo Log Buffer"]
    F --> G["事务提交"]
    G --> H["LGWR 写 redo 到日志文件"]
    H --> I["返回提交成功"]
    I --> J["DBWR 后续写脏块到数据文件"]

每一步为什么需要：

步骤	作用
定位目标行	通过索引或扫描找到要改的数据
加锁	防止其他事务同时修改同一行
生成 Undo	支持回滚和一致性读
修改 Buffer Cache	先改内存块，提高性能
生成 Redo	记录修改，用于崩溃恢复
LGWR 写日志	提交时保证 redo 持久化
DBWR 写脏块	数据块可稍后刷盘，不阻塞每次提交

重点：提交成功不要求数据块马上写入数据文件，但要求相关 Redo 能恢复这次修改。

Undo 和 Redo 的区别

对比	Undo	Redo
保存内容	修改前的旧值或反向操作信息	数据块变化记录
主要作用	回滚、一致性读、闪回	崩溃恢复、介质恢复
读场景	查询旧版本时可能用	恢复时重放
写场景	DML 修改时产生	几乎所有数据块变化都会产生
类比	后悔药	事故恢复录像

面试不要只说“Undo 回滚，Redo 恢复”。要补一句：

Oracle 一致性读也依赖 Undo。如果当前块中的版本太新，Oracle 会根据 Undo 构造查询 SCN 对应的旧版本。

Commit 到底做了什么

很多人以为 commit 会把所有修改的数据块刷盘。实际更关键的是 redo。

mermaid

flowchart TD
    A["事务执行 DML"] --> B["修改 Buffer Cache 中的块"]
    B --> C["Redo 进入 Redo Log Buffer"]
    C --> D["commit"]
    D --> E["LGWR 写 redo 到 redo log file"]
    E --> F["生成提交 SCN"]
    F --> G["返回 commit 成功"]
    G --> H["DBWR 后台择机刷脏块"]

为什么这样设计：

如果 commit 刷所有数据块	问题
大量随机 IO	提交性能很差
同一块可能被反复刷	浪费
高并发事务延迟高	吞吐下降

Oracle 选择先保证 redo 落盘。宕机后如果数据块没刷，也可以通过 redo 重放恢复。

锁和阻塞怎么理解

Oracle 更新一行会对行加锁。其他事务如果也要更新同一行，会等待。

mermaid

sequenceDiagram
    participant A as 事务A
    participant B as 事务B
    A->>A: update asset set status='USED' where id=1
    A->>A: 持有行锁，未提交
    B->>B: update same row
    B-->>A: 等待事务A提交或回滚
    A->>A: commit
    B->>B: 继续执行

常见阻塞原因：

原因	后果
事务长时间不提交	其他更新等待
更新条件没走索引	扫描和锁范围变大
批量大事务	Undo/Redo 压力大，锁持有久
应用异常未关闭事务	会话挂住，锁一直不释放

执行计划怎么看

Oracle 执行计划要关注访问路径、连接方式、估算行数和实际行数。

常见访问方式：

访问方式	含义	关注点
TABLE ACCESS FULL	全表扫描	大表高频 SQL 要警惕
INDEX RANGE SCAN	索引范围扫描	范围是否过大
INDEX UNIQUE SCAN	唯一索引定位	通常较好
TABLE ACCESS BY INDEX ROWID	通过 ROWID 回表	回表次数是否过多
NESTED LOOPS	嵌套循环连接	外层行数大时可能慢
HASH JOIN	哈希连接	PGA、临时空间可能压力大
SORT ORDER BY	排序	大排序可能用临时表空间

查看执行计划：

sql

explain plan for
select id, asset_no
from asset
where owner_id = 1001
order by created_at desc;

select *
from table(dbms_xplan.display);

如果要看实际执行情况，生产常结合 AWR、ASH、SQL Monitor 或 dbms_xplan.display_cursor。

商业场景：资产状态更新

医疗资产平台中，资产状态从空闲改为使用中：

sql

update asset
set status = 'USED',
    used_by = :user_id,
    updated_at = sysdate
where asset_no = :asset_no
  and status = 'IDLE';

推荐索引：

sql

create unique index uk_asset_no on asset(asset_no);
create index idx_asset_owner_time on asset(owner_id, created_at);

这个 SQL 背后的过程：

通过 uk_asset_no 快速定位资产行。
检查 status = 'IDLE' 是否满足。
对该行加锁。
写 Undo 保存旧状态。
修改 Buffer Cache 中的数据块。
写 Redo 到 Redo Log Buffer。
commit 时 LGWR 刷 redo。
返回成功。
后台 DBWR 后续刷脏块。

如果没有 asset_no 唯一索引，更新可能扫描更多块，持锁更久，Redo/Undo 更多，其他事务等待概率也更高。

线上排查流程

慢 SQL

mermaid

flowchart TD
    A["SQL 慢"] --> B["看执行计划"]
    B --> C{"访问路径合理吗"}
    C -- "全表扫描大表" --> D["检查索引和谓词"]
    C -- "回表很多" --> E["考虑覆盖或减少返回列"]
    C -- "排序重" --> F["检查 order by 和 PGA/临时表空间"]
    C -- "Join 重" --> G["检查驱动表、统计信息、连接列索引"]
    D --> H["更新统计信息并验证计划"]

锁等待

mermaid

flowchart TD
    A["接口卡住"] --> B["查等待事件"]
    B --> C{"是否 enq: TX row lock contention"}
    C -- "是" --> D["找阻塞会话"]
    D --> E["看未提交事务 SQL"]
    E --> F["提交、回滚或修复应用事务边界"]
    C -- "否" --> G["继续查 IO、CPU、临时空间、日志等待"]

Redo 或归档暴涨

可能原因	排查方向
批量更新/删除	是否大事务，能否分批
索引过多	DML 同时维护大量索引
大字段频繁更新	是否拆表或减少更新
归档目的地慢	检查磁盘和归档进程

常见坑

坑	后果	正确做法
拼接字面量 SQL	硬解析多，Shared Pool 压力大	使用绑定变量
事务不及时提交	锁等待、Undo 占用	缩短事务
更新条件没索引	扫描多、锁持有久	高频更新条件建合适索引
只看是否有索引	大量回表仍慢	看执行计划和行数
误以为 commit 刷数据块	无法理解 redo 恢复	commit 关键是 redo 持久化
长查询加高频更新	可能 snapshot too old	调整 Undo、缩短查询、降低冲突
Bitmap 索引用在 OLTP 高频更新	锁和维护成本高	Bitmap 更适合低更新的数据仓库

面试标准回答

Oracle 一条 SQL 怎么执行

text

Oracle 客户端连接通常由 Server Process 处理。SQL 进入后会先在 Shared Pool 中查找是否可以复用游标和执行计划，能复用就是软解析，不能复用就要硬解析，包括语法、语义、权限检查和优化器生成执行计划。执行时会通过索引或全表扫描访问数据块，优先从 Database Buffer Cache 读取，未命中才物理读。排序、Hash 等会使用 PGA。查询还要根据 SCN 和 Undo 做一致性读。

Oracle update 和 commit 过程

text

Oracle 更新一行时，会先定位目标行并加锁，然后生成 Undo 保存旧值，用于回滚和一致性读；再修改 Buffer Cache 中的数据块，同时生成 Redo 记录写入 Redo Log Buffer。提交时 LGWR 把相关 Redo 刷到 redo log 文件并生成提交 SCN，然后返回提交成功。数据块本身不一定在 commit 时立即写入数据文件，后续由 DBWR 后台刷盘。宕机后可以通过 Redo 恢复已提交修改。

Undo、Redo、SCN 的关系

text

Undo 保存修改前的旧值，用于事务回滚和一致性读；Redo 记录数据块变化，用于崩溃恢复；SCN 是 Oracle 内部的一致性时间点，用来标识提交顺序、查询可见性和恢复位置。查询开始时基于某个 SCN 做一致性读，如果当前数据块中的版本太新，Oracle 可以利用 Undo 构造查询 SCN 对应的旧版本。

关联知识点

本章小结

Oracle 的核心链路可以概括为：Server Process 执行 SQL，Shared Pool 负责解析和计划复用，Buffer Cache 缓存数据块，PGA 支撑会话私有计算，Undo 支持回滚和一致性读，Redo 保证崩溃恢复，SCN 串起一致性时间点。真正理解这些过程后，再看执行计划、锁等待、归档日志、snapshot too old、硬解析和慢 SQL，就不再是背概念，而是能定位问题发生在哪一层。

JDK 8/11/17/21 LTS演进

线程池

Oracle 核心全过程原理 ​

学习目标 ​

Oracle 总体结构 ​

一条 SELECT 的全过程 ​

软解析和硬解析 ​

Buffer Cache 怎么参与读取 ​

B-tree 索引和 ROWID 回表 ​

一致性读和 SCN ​

一条 UPDATE 的全过程 ​

Undo 和 Redo 的区别 ​

Commit 到底做了什么 ​

锁和阻塞怎么理解 ​

执行计划怎么看 ​

商业场景：资产状态更新 ​

线上排查流程 ​

慢 SQL ​

锁等待 ​

Redo 或归档暴涨 ​

常见坑 ​

面试标准回答 ​

Oracle 一条 SQL 怎么执行 ​

Oracle update 和 commit 过程 ​

Undo、Redo、SCN 的关系 ​

关联知识点 ​

本章小结 ​

Oracle 核心全过程原理

学习目标

Oracle 总体结构

一条 SELECT 的全过程

软解析和硬解析

Buffer Cache 怎么参与读取

B-tree 索引和 ROWID 回表

一致性读和 SCN

一条 UPDATE 的全过程

Undo 和 Redo 的区别

Commit 到底做了什么

锁和阻塞怎么理解

执行计划怎么看

商业场景：资产状态更新

线上排查流程

慢 SQL

锁等待

Redo 或归档暴涨

常见坑

面试标准回答

Oracle 一条 SQL 怎么执行

Oracle update 和 commit 过程

Undo、Redo、SCN 的关系

关联知识点

本章小结