Oracle 面试

Oracle 实例和数据库区别

Oracle 实例是内存结构和后台进程的集合，包括 SGA、PGA 相关进程等；数据库是物理文件集合，包括数据文件、控制文件和 redo log 文件。实例启动后加载数据库，用户连接实例执行 SQL。

原理：架构与存储、Undo、Redo 与 SCN 原理。

Oracle、MySQL、PostgreSQL、SQL Server 怎么横向比较

横向比较时，要从企业级事务能力、存储结构、日志恢复、优化器和运维工具看。Oracle 常见于金融、政企、运营商等核心系统，强调实例、SGA/PGA、表空间、段、区、块、Undo、Redo、SCN、Data Guard、RAC、AWR/ASH 等能力。MySQL InnoDB 常见于互联网业务，重点是 B+Tree、Buffer Pool、undo、redo、binlog；PostgreSQL 强在复杂 SQL、扩展索引、JSONB/GIS、tuple MVCC、VACUUM 和 WAL；SQL Server 强在微软生态、聚集索引、Transaction Log、TempDB、RCSI 和参数嗅探。Oracle 的优势不是某个语法点，而是围绕大规模核心交易、恢复、高可用和诊断形成的一整套体系。

追问：

1. Oracle Undo、Redo、SCN 分别解决什么？
2. Oracle commit 为什么只需要保证 Redo 落盘？
3. Oracle B-tree 叶子里的 ROWID 有什么作用？
4. AWR/ASH 和普通慢 SQL 日志有什么区别？

原理：主流关系库核心差异与选型、关系型数据库底层原理、Undo、Redo 与 SCN 原理。

同一条 SQL 在不同数据库为什么性能不同

因为 SQL 文本只是逻辑请求，真正性能取决于表组织、索引叶子层、统计信息、执行计划、可见性判断和回表方式。Oracle B-tree 索引叶子块保存 key 和 ROWID，命中索引后通常通过 ROWID 访问表块，并基于 Undo + SCN 做一致性读；如果返回行太多，ROWID 回表会产生大量随机读，可能不如全表扫描。MySQL InnoDB 二级索引可能回聚簇索引，PostgreSQL 通过 TID 回 heap 并检查可见性，SQL Server 可能 Key Lookup。所以“走索引”只是路径，不等于一定快。

追问：

1. TABLE ACCESS BY INDEX ROWID 次数太多说明什么？
2. E-Rows 和 A-Rows 差距大可能是什么原因？
3. Oracle 为什么有时会选择全表扫描而不是索引？

原理：同一条列表查询在四种数据库里的差异、为什么走了索引还是慢、索引与优化器。

Oracle 怎么从零学到生产可用

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

原理：从零到生产级掌握、核心全过程原理。

Oracle 一条 SQL 怎么执行

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

原理：核心全过程原理、架构与存储、性能排查。

Oracle Undo 和 Redo 区别

Undo 保存数据修改前的旧值，用于事务回滚和一致性读；Redo 记录数据块变化，用于崩溃恢复。查询一致性读时，如果当前数据版本晚于查询 SCN，可以通过 Undo 构造旧版本；事务提交后即使数据块没刷盘，也可以通过 Redo 恢复提交后的修改。

原理：事务与锁、Undo、Redo 与 SCN 原理。

Oracle update 和 commit 过程

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

原理：核心全过程原理、Undo、Redo 与 SCN 原理。

Oracle B-tree 索引怎么工作

Oracle B-tree 索引通常由 root、branch、leaf block 组成，leaf block 保存 key 和 ROWID。查询时从 root 定位到 leaf，找到 key 对应的 ROWID，再根据 ROWID 访问表数据块。B-tree 适合高选择性字段、范围查询和排序；Bitmap 索引更适合低基数字段和数据仓库场景，不适合高并发 OLTP 频繁更新。

原理：索引与优化器。

Oracle 为什么推荐绑定变量

绑定变量可以让 SQL 文本保持稳定，提升游标和执行计划复用率，减少硬解析。高并发系统如果大量拼接字面量 SQL，会导致 Shared Pool 压力、Library Cache 竞争和 CPU 消耗增加。当然绑定变量也可能带来不同参数共用计划的问题，需要结合直方图、绑定变量窥探和 SQL 调优综合判断。

原理：核心全过程原理、索引与优化器。

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

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

原理：从零到生产级掌握、核心全过程原理。

Oracle sequence 为什么会跳号

Oracle sequence 用来生成唯一递增值，但不保证连续。每次调用 nextval 都会消耗一个值，事务回滚不会回退 sequence；开启 cache 后，实例异常也可能丢失缓存中的号段。所以 sequence 跳号不是 Bug，它适合作为技术主键取号，不适合作为要求连续的业务单号。业务订单号、票据号如果要求连续，要单独设计规则和审计。

追问：

1. sequence cache 为什么能提升性能？
2. 事务回滚后 sequence 为什么不回退？
3. 技术主键和业务编号为什么要分开？

原理：基础 SQL：Sequence。

Oracle date 类型为什么查询某一天不能直接等值

Oracle date 包含年月日时分秒，不只是日期。`date '2026-07-06'` 表示 2026-07-06 00:00:00，只能匹配刚好这个时间点的数据。查询某一天应该写范围条件，例如 created_at >= date '2026-07-06' and created_at < date '2026-07-07'。不要对列使用 to_char 再比较字符串，否则普通索引可能无法直接使用。

追问：

1. date 和 timestamp 有什么区别？
2. 为什么 to_char(created_at) 可能影响索引？
3. 查询某月数据怎么写？

原理：基础 SQL：日期查询、函数包列影响索引。

Oracle rownum 分页有什么坑

rownum 是 Oracle 给结果行分配的伪列，但它和 order by 的执行顺序容易让人误解。如果先写 where rownum <= 20 再 order by，可能是先取任意前 20 行再排序，而不是排序后的前 20 行。老版本分页通常要使用嵌套查询，先在内层 order by，再在外层用 rownum 截取。12c 后可以使用 offset fetch。

追问：

1. 为什么 rownum <= 20 order by 可能结果不对？
2. 12c 后分页怎么写？
3. 深分页为什么仍然慢？

原理：基础 SQL：分页、老版本 rownum 分页。

Oracle 为什么普通查询不阻塞更新

Oracle 通过多版本一致性读实现普通查询和更新之间的并发。查询开始时获得一个一致性读 SCN，如果读到的数据块版本晚于这个 SCN，就通过 Undo 构造旧版本。因此普通查询通常不需要等待正在更新的事务提交。代价是长查询需要足够 Undo 支持，如果 Undo 被覆盖，可能出现 snapshot too old。

追问：

1. Undo 在一致性读里做什么？
2. SCN 在一致性读里做什么？
3. 为什么长查询可能报 ORA-01555？

原理：架构与存储：Undo一致性读、Undo、Redo 与 SCN 原理。

Oracle Shared Pool 解决什么问题

Shared Pool 用于缓存 SQL 游标、执行计划和数据字典信息。相同 SQL 如果能复用已有游标，就是软解析；不能复用则要硬解析，包括语法、语义、权限检查和优化器生成计划。高并发系统大量拼接字面量 SQL 会导致硬解析增加、Shared Pool 压力和 Library Cache 竞争，所以 Oracle 推荐使用绑定变量。

追问：

1. 软解析和硬解析区别是什么？
2. 为什么绑定变量能减少硬解析？
3. 绑定变量有没有副作用？

原理：架构与存储：Shared Pool、核心全过程原理。

Oracle ROWID 有什么用

ROWID 可以理解为 Oracle 定位一行物理位置的地址信息。B-tree 索引叶子块通常保存 key 和 ROWID，查询通过索引找到 ROWID 后，再根据 ROWID 回表读取数据块。返回少量行时这种方式很快；如果返回大量行，频繁 ROWID 回表可能产生很多随机访问，不一定比全表扫描快。

追问：

1. Oracle B-tree 叶子块保存什么？
2. 为什么索引返回行太多可能反而慢？
3. ROWID 和主键是什么关系？

原理：架构与存储：ROWID、索引与优化器。

Oracle Checkpoint 是什么

Checkpoint 会推动脏块写盘，并更新控制文件和数据文件头中的检查点信息，用来缩短实例恢复时需要重做的日志范围。它不是事务提交本身，commit 主要依赖 LGWR 刷 Redo；Checkpoint 更多是控制恢复成本和脏块写回进度。Checkpoint 过于频繁会增加运行时写盘压力，过少可能让恢复时间变长。

追问：

1. Checkpoint 和 commit 有什么区别？
2. DBWR 和 LGWR 分别写什么？
3. Checkpoint 为什么能缩短恢复时间？

原理：架构与存储：Checkpoint。

Oracle 性能排查先看什么

Oracle 性能排查先定位 SQL_ID 和会话，再看真实执行计划、估算行数和实际行数、逻辑读、物理读、等待事件、锁、统计信息和临时表空间。不能只看 SQL 文本或只说加索引。统计信息不准、绑定变量窥探、等待事件、Undo 不足、PGA/临时表空间压力都可能导致慢。

追问：

1. 为什么 explain plan 不一定等于真实执行计划？
2. E-Rows 和 A-Rows 差距大说明什么？
3. 等待事件能告诉我们什么？

原理：性能排查、核心全过程原理。

Oracle 为什么走索引还是慢

Oracle 走索引只是说明访问路径使用了索引，不代表一定快。B-tree 叶子块保存 key 和 ROWID，命中索引后通常还要通过 ROWID 回表。如果返回行很多，回表会产生大量随机读，可能比全表扫描更慢。还要看统计信息是否低估了返回行数、聚簇因子是否差、查询字段是否过多、是否存在锁等待或 IO 等待。排查时要看 TABLE ACCESS BY INDEX ROWID、Buffers、Reads、E-Rows 和 A-Rows。

追问：

1. TABLE ACCESS BY INDEX ROWID 代表什么？
2. 为什么返回行多时全表扫描可能更快？
3. 聚簇因子对索引回表有什么影响？

原理：性能排查：走索引还是慢、索引与优化器。

Oracle 绑定变量窥探是什么

绑定变量窥探是指 SQL 硬解析时优化器可能查看第一次传入的绑定变量值，并基于这个值生成执行计划。绑定变量能减少硬解析，但如果数据严重倾斜，不同参数适合不同计划，后续参数复用第一次计划就可能变慢。解决方向包括直方图、Adaptive Cursor Sharing、SQL Plan Baseline、分区、拆分大客户和普通客户 SQL，而不是简单改成拼接 SQL。

追问：

1. 为什么绑定变量能减少硬解析？
2. 数据倾斜为什么会让同一 SQL 需要不同计划？
3. SQL Plan Baseline 解决什么问题？

原理：性能排查：绑定变量窥探和执行计划抖动、核心全过程原理。

Oracle log file sync 是什么

log file sync 表示用户会话提交时等待 LGWR 把提交相关 Redo 写入 redo log 文件。它常见于 commit 过于频繁、Redo 磁盘写入慢、批处理每行提交、日志设备压力大等场景。优化方向不是盲目调参数，而是减少无意义小事务、合理批量提交、检查 Redo IO、归档压力和事务设计。

追问：

1. commit 为什么要等 Redo 落盘？
2. 每行 commit 为什么慢？
3. 批量提交是不是越大越好？

原理：性能排查：频繁commit导致log file sync、Undo、Redo 与 SCN 原理。

Oracle 临时表空间打满怎么排查

临时表空间常被大排序、Hash Join、Group By、报表查询消耗。当 PGA 内存不足以容纳中间结果时，Oracle 会把部分数据写入临时表空间，等待事件可能出现 direct path read temp 或 direct path write temp。排查时要定位使用 temp 的会话和 SQL，查看执行计划中的 Sort、Hash、全表扫描和返回行数。优化方向包括缩小查询范围、预聚合、分区裁剪、合理索引、报表走数仓或只读库。

追问：

1. PGA 和临时表空间有什么关系？
2. 哪些 SQL 容易用 temp？
3. 为什么报表不建议直接压核心交易库？

原理：性能排查：大排序打满临时表空间。

AWR 和 ASH 分别看什么

AWR 是一段时间内数据库负载的汇总报告，可以看 Top 等待事件、Top SQL、IO、CPU 和实例效率；ASH 是活跃会话采样，可以看某个时间窗口里哪些会话在执行什么 SQL、等待什么事件。AWR 更适合看整体负载趋势和 Top SQL，ASH 更适合还原慢时段的会话现场。它们提供证据，但不能替代理解执行计划、等待事件和业务链路。

追问：

1. AWR Top Timed Events 能说明什么？
2. ASH 为什么能看到活跃会话现场？
3. AWR/ASH 能不能自动告诉你怎么改 SQL？

原理：性能排查：AWR和ASH怎么用。

Oracle Data Guard 和 RAC 区别

Data Guard 是主备容灾方案，核心是主库生成 Redo，备库接收并应用 Redo，用于机房故障和数据库级容灾。RAC 是多个实例访问同一个数据库存储，主要提升实例层可用性和部分并发能力。Data Guard 不能替代备份，因为误删数据也会同步到备库；RAC 也不能解决误删、慢 SQL 和共享存储故障。生产上常把 RMAN、Data Guard、RAC 按不同目标组合使用。

追问：

1. Data Guard 为什么不能替代备份？
2. RAC 解决什么，不解决什么？
3. Switchover 和 Failover 区别是什么？

原理：备份与高可用、Data Guard原理、RAC边界。