Oracle 从零到生产级掌握

Oracle 不能只背“实例、SGA、PGA、Undo、Redo、SCN”。这些词只有放到建模、SQL 执行、事务提交、崩溃恢复、锁等待和性能排查里，才真正有用。

先用一句话建立主线：

Oracle 是面向大型商业系统的关系型数据库。它用实例内存和后台进程承载 SQL 执行，用 Undo 支持回滚和一致性读，用 Redo 保证恢复，用 SCN 标记一致性时间点，用优化器和统计信息决定 SQL 怎么跑。

学习目标

学完这一页，你要能做到：

区分实例、数据库、表空间、段、区、块这些层次。
从零建用户、表、序列、索引、约束，并写出常见业务 SQL。
解释一条查询从 Shared Pool 解析、优化器生成计划，到 Buffer Cache 读块和 Undo 一致性读的全过程。
解释一条更新为什么会产生 Undo、Redo、行锁和提交 SCN。
解释 commit 为什么关键是 LGWR 刷 Redo，而不是立即刷所有数据块。
看懂常见执行计划：全表扫描、索引范围扫描、ROWID 回表、Nested Loops、Hash Join、Sort。
排查慢 SQL、硬解析、锁等待、归档日志暴涨、snapshot too old、临时表空间不足。

如果你已经读完主线，但还不知道怎么把 Oracle 原理用于项目，继续做：Oracle 商业场景训练营。它把资产表建模、ROWID 回表、一致性读、Undo、SCN、锁等待、snapshot too old、Redo 和恢复串成可验证训练。

Oracle 学习路线

mermaid

flowchart TD
    A["基础概念<br/>实例、数据库、用户、表空间"] --> B["SQL 和对象<br/>表、序列、约束、索引"]
    B --> C["存储结构<br/>表空间、段、区、块"]
    C --> D["SQL 执行<br/>解析、优化、执行计划"]
    D --> E["内存结构<br/>SGA、Shared Pool、Buffer Cache、PGA"]
    E --> F["事务机制<br/>Undo、Redo、SCN、锁"]
    F --> G["备份恢复<br/>归档、RMAN、Data Guard"]
    G --> H["生产排查<br/>等待事件、AWR、ASH、执行计划"]

不要一开始就背 AWR、RAC、Data Guard。先把单机里一条 SQL 怎么执行、一条 update 怎么提交理解透，再去学高可用和调优。

第一步：实例、数据库、用户和表空间

Oracle 里“实例”和“数据库”不是一个概念。

概念	解释	初学者常见误解
Instance	内存结构 + 后台进程	误以为等同于数据文件
Database	数据文件、控制文件、Redo 文件等物理文件	误以为只是一个 schema
User/Schema	用户拥有对象时也形成同名 schema	误以为 user 和 schema 完全无关
Tablespace	逻辑存储容器，对应数据文件	误以为表直接随便存磁盘
Segment	表、索引等对象占用的空间	误以为表就是一个文件
Block	Oracle 读写的基本数据单位	误以为按行读磁盘

总体关系：

mermaid

flowchart TD
    A["Oracle Instance<br/>SGA + 后台进程"] --> B["Oracle Database<br/>物理文件集合"]
    B --> C["Tablespace 表空间"]
    C --> D["Data File 数据文件"]
    C --> E["Segment 段<br/>表段、索引段"]
    E --> F["Extent 区"]
    F --> G["Block 块"]

创建业务用户示例：

sql

create user app_user identified by "ChangeMe_123"
default tablespace users
temporary tablespace temp;

grant create session to app_user;
grant create table, create sequence, create view to app_user;

alter user app_user quota unlimited on users;

为什么要理解表空间：

表和索引最终要占数据文件空间。
临时排序、Hash、索引创建可能使用临时表空间。
归档、备份、恢复都和数据文件、控制文件、Redo 文件有关。
表空间满了不是 Java 报错那么简单，而是数据库对象无法继续扩展。

第二步：从业务建模开始

以医疗资产平台为例，Oracle 表设计不能只有字段，还要考虑约束、序列、索引和状态合法性。

sql

create table medical_asset (
  id number primary key,
  asset_no varchar2(64) not null,
  hospital_id number not null,
  department_id number,
  asset_name varchar2(128) not null,
  status varchar2(20) not null,
  created_at date default sysdate not null,
  updated_at date default sysdate not null,
  constraint uk_medical_asset_no unique (asset_no),
  constraint ck_medical_asset_status
    check (status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

create sequence seq_medical_asset start with 1 increment by 1;

插入数据：

sql

insert into medical_asset (
  id, asset_no, hospital_id, department_id, asset_name, status
) values (
  seq_medical_asset.nextval, 'A001', 1001, 10, 'CT-001', 'IDLE'
);

commit;

约束为什么重要：

约束	作用	不用会怎样
主键	每行唯一身份	后续更新、关联、审计困难
唯一约束	防止业务重复	并发导入可能重复资产号
非空	保证关键字段完整	报表和接口出现不可解释空值
check	限制状态合法	脏状态进入库后业务分支失控

数据库约束不是和 Java 校验二选一。Java 校验负责用户体验，数据库约束负责最终一致性边界。

第三步：索引和 ROWID

Oracle B-tree 索引叶子块里保存索引 key 和 ROWID。ROWID 可以定位到表数据块里的行。

mermaid

flowchart TD
    A["SQL 条件 asset_no = :assetNo"] --> B["访问唯一索引"]
    B --> C["Root Block"]
    C --> D["Branch Block"]
    D --> E["Leaf Block<br/>key + ROWID"]
    E --> F["TABLE ACCESS BY INDEX ROWID"]
    F --> G["读取表块中的完整行"]

常见索引：

sql

create unique index uk_asset_no on medical_asset(asset_no);

create index idx_asset_hospital_status_time
on medical_asset(hospital_id, status, created_at);

索引设计原则：

查询	合适索引
按资产编号精确查	`asset_no` 唯一索引
某医院某状态按时间分页	`(hospital_id, status, created_at)`
低更新、低基数字段报表统计	可能考虑 Bitmap 索引
高频 OLTP 更新字段	谨慎使用 Bitmap 索引

为什么 Bitmap 索引不适合高并发 OLTP 高频更新：

Bitmap 索引一段位图可能对应很多行。
更新一行可能锁住更大范围的位图片段。
并发更新容易互相影响。
它更适合数据仓库、低更新、高统计查询场景。

第四步：一条 SELECT 怎么执行

Oracle 查询执行不是直接读表，而是先解析和优化。

mermaid

flowchart TD
    A["客户端发送 SQL"] --> B["Server Process"]
    B --> C["Shared Pool 查找游标"]
    C --> D{"能否软解析"}
    D -- "能" --> E["复用执行计划"]
    D -- "不能" --> F["硬解析<br/>语法、语义、权限"]
    F --> G["优化器基于统计信息选计划"]
    E --> H["执行计划"]
    G --> H
    H --> I["访问索引或全表扫描"]
    I --> J["Buffer Cache 读数据块"]
    J --> K["按 SCN 做一致性读"]
    K --> L["PGA 排序/Hash/聚合"]
    L --> M["返回结果"]

软解析和硬解析区别：

类型	发生什么	影响
软解析	SQL 文本和环境匹配，复用游标	成本低
硬解析	重新检查语义、权限、生成计划	消耗 CPU 和 Shared Pool

绑定变量示例：

sql

select id, asset_no, asset_name
from medical_asset
where hospital_id = :hospital_id
  and status = :status;

为什么绑定变量重要：

SQL 文本稳定，游标复用率高。
减少硬解析。
降低 Shared Pool 和 Library Cache 压力。
高并发系统减少 CPU 抖动。

但也要知道另一面：不同参数分布差异很大时，绑定变量可能复用不合适的计划，需要结合统计信息、直方图、SQL Plan Baseline 等手段处理。

第五步：SGA、PGA 分别在什么时候用

SGA 是共享内存，PGA 是进程私有内存。

内存	组件	作用
SGA	Shared Pool	缓存 SQL、游标、执行计划、数据字典
SGA	Database Buffer Cache	缓存表块和索引块
SGA	Redo Log Buffer	暂存 Redo 记录
PGA	Sort Area	排序
PGA	Hash Area	Hash Join、Hash 聚合
PGA	Session Memory	会话私有状态

读取数据块过程：

mermaid

flowchart TD
    A["执行计划需要数据块"] --> B{"Buffer Cache 是否命中"}
    B -- "命中" --> C["逻辑读 Buffer Gets"]
    B -- "未命中" --> D["物理读 Data File"]
    D --> E["放入 Buffer Cache"]
    E --> C
    C --> F["返回行或继续过滤"]

如果 SQL 的 Buffer Gets 很高，即使物理读不高，也说明它在内存里扫描了很多块，会消耗 CPU 和缓存资源。

第六步：一致性读、Undo 和 SCN

Oracle 普通查询要看到“查询开始时”的一致结果。这个时间点用 SCN 表示。

mermaid

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

Undo 的两个核心作用：

作用	解释
回滚	事务失败时用旧值撤销修改
一致性读	查询需要旧版本时用 Undo 构造

snapshot too old 的本质：

长查询开始时拿到一个较早 SCN。
查询过程中，其他事务大量更新同一批数据。
Oracle 需要从 Undo 构造旧版本。
但所需 Undo 已被覆盖或不可用。
查询报 ORA-01555: snapshot too old。

处理方向：

方向	说明
缩短长查询	分页、分批、减少一次查询时间
增加 Undo 保留	调整 Undo 表空间和保留策略
降低更新冲突	避开高更新时段跑报表
优化 SQL	减少扫描时间，降低需要旧版本的窗口

第七步：UPDATE 和 COMMIT 全过程

资产状态更新：

sql

update medical_asset
set status = 'USED',
    updated_at = sysdate
where asset_no = :asset_no
  and status = 'IDLE';

执行过程：

mermaid

flowchart TD
    A["通过索引定位行"] --> B["检查 status 条件"]
    B --> C["对行加锁"]
    C --> D["写 Undo 保存旧值"]
    D --> E["修改 Buffer Cache 中的数据块"]
    E --> F["生成 Redo 到 Redo Log Buffer"]
    F --> G["commit"]
    G --> H["LGWR 刷 Redo 到日志文件"]
    H --> I["生成提交 SCN 并返回成功"]
    I --> J["DBWR 后台后续刷脏块"]

为什么 commit 不刷所有数据块：

如果 commit 刷数据块	后果
每次提交随机写数据文件	延迟高
同一热点块反复刷	浪费 IO
高并发吞吐下降	系统抖动

Oracle 的选择是：提交时保证 Redo 落盘。数据块稍后刷盘。宕机后用 Redo 重放恢复。

Undo、Redo、SCN 的关系：

机制	作用
Undo	保存旧值，支持回滚和一致性读
Redo	记录变化，支持崩溃恢复
SCN	标记提交顺序、一致性读时间点和恢复位置

第八步：执行计划怎么看

查看执行计划：

sql

explain plan for
select id, asset_no, asset_name
from medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc;

select * from table(dbms_xplan.display);

常见执行计划节点：

节点	含义	可能问题
TABLE ACCESS FULL	全表扫描	大表高频查询可能慢
INDEX UNIQUE SCAN	唯一索引定位	通常较好
INDEX RANGE SCAN	索引范围扫描	范围过大仍慢
TABLE ACCESS BY INDEX ROWID	根据 ROWID 回表	回表多会慢
NESTED LOOPS	嵌套循环	外层行数大时灾难
HASH JOIN	哈希连接	PGA/临时表空间压力
SORT ORDER BY	排序	大排序可能写 TEMP

优化顺序：

mermaid

flowchart TD
    A["发现慢 SQL"] --> B["看执行计划"]
    B --> C["看访问路径"]
    C --> D["看估算行数"]
    D --> E["看谓词是否命中索引"]
    E --> F["看 Join 顺序和方法"]
    F --> G["看排序、回表、临时空间"]
    G --> H["更新统计信息或调整 SQL/索引"]

更新统计信息：

sql

begin
  dbms_stats.gather_table_stats(
    ownname => user,
    tabname => 'MEDICAL_ASSET',
    cascade => true
  );
end;
/

统计信息不准时，优化器可能低估或高估行数，导致选错 Join 顺序、选错索引或全表扫描。

第九步：锁等待怎么排查

Oracle 行锁在事务提交或回滚前不会释放。

mermaid

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

常见等待：

等待	解释
`enq: TX - row lock contention`	行锁等待
`library cache lock/pin`	解析或对象相关竞争
`db file sequential read`	单块读，常见索引访问
`db file scattered read`	多块读，常见全表扫描
`log file sync`	commit 等待 LGWR 刷日志
`direct path read temp`	临时表空间读，常见大排序/Hash

排查方向：

找等待会话。
找阻塞会话。
看阻塞会话正在执行或最后执行的 SQL。
判断是业务事务过长、批量更新、条件无索引还是应用未提交。
优先修事务边界，不要简单杀会话当长期方案。

第十步：商业项目设计边界

Oracle 常见于核心交易、政企、金融、运营商系统。设计时要避免把所有问题都交给一个大事务。

场景	推荐做法
资产主数据	主表 + 唯一约束 + B-tree 索引
资产变更历史	流水表，按时间归档或分区
大批量导入	分批提交，记录批次和错误明细
报表查询	避开交易高峰，必要时读库或数仓
高频状态更新	精确索引定位，缩短事务
审计追踪	单独审计表或日志链路
对外同步	本地消息表或 CDC，避免远程调用放事务里

错误做法：

错误	后果
一个事务处理几十万行	Undo/Redo 暴涨，锁持有久
报表直接扫交易大表	影响核心交易
不使用绑定变量	硬解析压力大
高频更新字段建 Bitmap 索引	并发更新冲突高
没有归档策略	表和索引越来越大

完整 Demo：医疗资产状态流转

建表：

sql

create table medical_asset (
  id number primary key,
  asset_no varchar2(64) not null,
  hospital_id number not null,
  asset_name varchar2(128) not null,
  status varchar2(20) not null,
  created_at date default sysdate not null,
  updated_at date default sysdate not null,
  constraint uk_medical_asset_no unique (asset_no),
  constraint ck_medical_asset_status
    check (status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

create sequence seq_medical_asset start with 1 increment by 1;

create index idx_asset_hospital_status_time
on medical_asset(hospital_id, status, created_at);

插入：

sql

insert into medical_asset (
  id, asset_no, hospital_id, asset_name, status
) values (
  seq_medical_asset.nextval, 'A001', 1001, 'CT-001', 'IDLE'
);

commit;

查询：

sql

select id, asset_no, asset_name
from medical_asset
where hospital_id = :hospital_id
  and status = :status
order by created_at desc
fetch first 20 rows only;

状态流转：

sql

update medical_asset
set status = 'USED',
    updated_at = sysdate
where asset_no = :asset_no
  and status = 'IDLE';

commit;

这个 Demo 对应的原理：

唯一约束防重复资产编号。
联合索引支撑医院 + 状态 + 时间列表查询。
update 通过唯一索引定位行，减少扫描和锁持有时间。
update 产生 Undo 和 Redo。
commit 时 LGWR 刷 Redo。
查询按 SCN 做一致性读。

线上排查总流程

mermaid

flowchart TD
    A["Oracle 线上问题"] --> B{"表现是什么"}
    B -- "SQL 慢" --> C["看执行计划和等待事件"]
    C --> D["访问路径、统计信息、Join、排序"]
    B -- "接口卡住" --> E["查锁等待"]
    E --> F["找阻塞会话和未提交事务"]
    B -- "提交慢" --> G["查 log file sync"]
    G --> H["看 LGWR、Redo、磁盘延迟"]
    B -- "归档暴涨" --> I["查大事务和批量 DML"]
    I --> J["拆批、归档空间、减少无效更新"]
    B -- "snapshot too old" --> K["查长查询和 Undo"]
    K --> L["优化查询、扩 Undo、错峰报表"]

常见处理：

问题	优先看
慢 SQL	执行计划、统计信息、等待事件
硬解析高	绑定变量、Shared Pool、SQL 文本
行锁等待	阻塞会话、事务边界、更新条件索引
commit 慢	`log file sync`、Redo 日志、磁盘
TEMP 爆	大排序、Hash Join、PGA、临时表空间
Undo 不够	长事务、长查询、批量更新

面试标准回答

Oracle 怎么从零学到生产可用

text

Oracle 要按实例和数据库、用户和表空间、表和索引、SQL 执行、SGA/PGA、Undo/Redo/SCN、事务锁、备份恢复和性能排查这条线学习。先理解实例是内存和后台进程，数据库是数据文件、控制文件和 redo 文件；再掌握表空间、段、区、块这些存储层次。SQL 执行时会经过 Shared Pool 解析和计划复用，优化器根据统计信息选择计划，数据块优先从 Buffer Cache 读取，排序和 Hash 使用 PGA。更新会产生 Undo 和 Redo，提交时 LGWR 刷 Redo 并生成提交 SCN，数据块后续由 DBWR 刷盘。生产排查要结合执行计划、等待事件、锁、统计信息、Undo、Redo 和临时表空间。

为什么 Oracle commit 不等于刷数据块

text

如果每次 commit 都把所有修改过的数据块刷到数据文件，会产生大量随机 IO，提交延迟很高。Oracle 的做法是事务修改先发生在 Buffer Cache 中，同时生成 Redo。commit 时 LGWR 把相关 Redo 刷到 redo log 文件并生成提交 SCN，只要 Redo 持久化，宕机后就能重放恢复已提交修改。数据块本身可以由 DBWR 后台择机写回数据文件。

关联知识点

知识点	说明
Oracle 总览	专栏入口和学习顺序
核心全过程原理	SQL、Undo、Redo、SCN、执行计划全链路
Undo、Redo 与 SCN 原理	一致性读、提交和恢复
架构与存储	实例、SGA、PGA、表空间、段区块
索引与优化器	B-tree、Bitmap、执行计划
事务与锁	行锁、隔离、一致性读
性能排查	慢 SQL、等待事件、AWR/ASH 思路
Oracle 面试	标准回答和追问

本章小结

Oracle 从零到生产级掌握，关键是把“对象和存储”“SQL 和内存”“事务和日志”“排查和等待”串起来。表空间、段、区、块解释数据怎么放；Shared Pool、Buffer Cache、PGA 解释 SQL 怎么跑；Undo、Redo、SCN 解释为什么能回滚、一致性读和恢复；执行计划和等待事件解释为什么慢。理解这些过程后，你才能真正回答“为什么这样设计，不这样会怎样”。

JDK 8/11/17/21 LTS演进

线程池

Oracle 从零到生产级掌握 ​

学习目标 ​

Oracle 学习路线 ​

第一步：实例、数据库、用户和表空间 ​

第二步：从业务建模开始 ​

第三步：索引和 ROWID ​

第四步：一条 SELECT 怎么执行 ​

第五步：SGA、PGA 分别在什么时候用 ​

第六步：一致性读、Undo 和 SCN ​

第七步：UPDATE 和 COMMIT 全过程 ​

第八步：执行计划怎么看 ​

第九步：锁等待怎么排查 ​

第十步：商业项目设计边界 ​

完整 Demo：医疗资产状态流转 ​

线上排查总流程 ​

面试标准回答 ​

Oracle 怎么从零学到生产可用 ​

为什么 Oracle commit 不等于刷数据块 ​

关联知识点 ​

本章小结 ​

Oracle 从零到生产级掌握

学习目标

Oracle 学习路线

第一步：实例、数据库、用户和表空间

第二步：从业务建模开始

第三步：索引和 ROWID

第四步：一条 SELECT 怎么执行

第五步：SGA、PGA 分别在什么时候用

第六步：一致性读、Undo 和 SCN

第七步：UPDATE 和 COMMIT 全过程

第八步：执行计划怎么看

第九步：锁等待怎么排查

第十步：商业项目设计边界

完整 Demo：医疗资产状态流转

线上排查总流程

面试标准回答

Oracle 怎么从零学到生产可用

为什么 Oracle commit 不等于刷数据块

关联知识点

本章小结