Oracle 优化器（CBO）是什么，如何做基数（Cardinality）估算？

原创于 2025-09-10 12:43:39 发布 · 700 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们将对 Oracle 数据字典（Data Dictionary）进行一场极其深入和全面的剖析。数据字典是 Oracle 数据库的“大脑”和“元数据中心”，理解其内部原理是进行高级管理、性能优化和故障排查的基石。

第一部分：官方技术详解

一、核心概念与作用

1. 什么是数据字典？
Oracle 数据字典是一组只读的表和视图的集合，它描述了数据库自身的结构和状态。它包含了所有数据库对象的元数据（Metadata），即“关于数据的数据”。例如：

逻辑结构信息：用户、表、视图、索引、序列、同义词、存储过程、函数、包的定义。
物理结构信息：表空间、数据文件、控制文件、重做日志文件的位置和状态。
空间分配信息：段（Segments）、区（Extents）、块（Blocks）的分配和使用情况。
权限与约束信息：用户权限、角色、系统权限、对象权限、完整性约束（主键、外键等）。
运行状态信息：动态性能视图（V$ 视图）提供了实时的性能和状态数据。

2. 核心作用

系统可靠性基石：确保所有数据库操作引用的对象是有效、一致且经过授权的。任何 SQL 或 PL/SQL 语句的执行都离不开数据字典的查询。
优化器决策基础：SQL 查询优化器（CBO）严重依赖数据字典中的统计信息（如 NUM_ROWS, BLOCKS, LAST_ANALYZED）来计算成本并生成最高效的执行计划。
数据库管理的接口：DBA 和开发者通过查询数据字典视图来监控、管理和维护数据库。
依赖关系维护：记录对象间的依赖关系。例如，当一个基表的结构被修改时，依赖于它的所有视图和程序单元会被标记为 INVALID。

二、内部结构：基表、视图与内部机制

数据字典在物理上和逻辑上分为多个层次，其访问机制精巧而复杂。

1. 核心内部结构

基表 (Base Tables)：
- 定义：数据字典最底层的、存储原始元数据的表。例如 OBJ$（所有对象）、TAB$（所有表）、COL$（所有列）、USER$（所有用户）、IND$（所有索引）等。
- 存储：这些基表及其索引存储在 SYSTEM 表空间中，由 SYS 用户所有。
- 特性：基表的结构非常晦涩（列名如 NAME, TYPE#, OBJ#），直接查询极其困难且强烈不推荐。它们的更改由 SQL DDL 语句（如 CREATE, ALTER, DROP, GRANT）自动触发和维护。
- 初始化：在数据库创建时（CREATE DATABASE），由 sql.bsq 核心脚本创建这些基表并插入初始数据。
数据字典视图 (Data Dictionary Views)：
- 定义：为了便于用户和 DBA 查询，Oracle 在基表之上创建了多组易于理解的视图。这些视图对基表的信息进行了转换、连接、过滤和格式化，提供了人类可读的接口。
- 前缀与作用域：
  - DBA_*：显示数据库中所有对象的信息，无论所有者是谁。例如 DBA_TABLES。查询需要 SELECT ANY DICTIONARY 或 DBA 角色权限。
  - ALL_*：显示当前用户有权限访问的所有对象的信息。例如 ALL_TABLES，它包括用户自己拥有的对象和已被授权访问的其他用户的对象。
  - USER_*：显示当前用户所拥有的对象的信息。例如 USER_TABLES。这是最常用、最快速的视图集，因为它不需要进行权限过滤。
- 静态视图 (Static Views)：上述 DBA_/ALL_/USER_ 视图主要基于基表，其内容相对持久，直到下一次 DDL 发生。
动态性能视图 (Dynamic Performance Views) - V$ 视图：
- 定义：虽不属于严格意义上的数据字典（因其不存储在 SYSTEM 表空间），但它们是元数据生态的核心部分。它们提供实时的、内存中的数据库运行状态信息。
- 存储：其数据来源于数据库实例的控制文件和SGA 内存结构。它们是实例状态的“虚拟”窗口。
- 特性：这些视图在实例启动时动态创建，在实例关闭时消失。其内容持续变化，反映了系统的瞬时状态。
- 底层：V$ 视图通常基于 GV$ (Global V$) 视图，后者在 RAC 环境中提供所有实例的聚合信息。

2. 内部管理与访问机制

递归 SQL (Recursive SQL)：
- 这是理解数据字典访问的关键。当用户发出一条 SQL 语句时，数据库在解析和执行过程中，自己会在后台发出大量的 SQL 语句来访问数据字典。这些 SQL 就是递归 SQL。
- 过程举例（一次硬解析）：
  1. 用户执行 SELECT * FROM scott.emp WHERE empno = 7788。
  2. 数据库在共享池中找不到此语句的执行计划，开始硬解析。
  3. 硬解析过程中，数据库会发出大量递归SQL：
    - 对象解析：SELECT OBJ# FROM OBJ$ WHERE NAME='EMP' AND OWNER# = (SELECT USER# FROM USER$ WHERE NAME='SCOTT') — 确认 scott.emp 存在并获取其内部对象ID。
    - 权限检查：SELECT PRIVILEGE FROM SYSAUTH$ WHERE GRANTEE# = :current_user_id AND OBJ# = :emp_obj# — 检查当前用户是否有 SELECT 权限。
    - 结构获取：SELECT NAME, COL#, TYPE# FROM COL$ WHERE OBJ#=:emp_obj# ORDER BY COL# — 获取 emp 表的所有列信息。
    - 统计信息获取：SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM TAB_STATS$ WHERE OBJ#=:emp_obj# — 为优化器获取表的统计信息。
  4. 基于收集到的元数据，优化器生成执行计划。
  5. 执行查询，返回结果。
- 大量的硬解析会导致大量的递归 SQL，显著增加系统负载（CPU 和闩锁争用）。
引导阶段 (Bootstrap)：
- 这是一个“自举”过程，解决元数据依赖的悖论：数据库如何读取 SYSTEM 表空间中的数据字典基表，如果连描述这些基表结构的元数据都还没有被读取？
- 解决方案：在实例启动挂载数据库后，会执行一个引导过程。最核心的、最小化的元数据信息（如 OBJ$, COL$, TAB$ 等基表自身的定义和块地址）是“硬编码”在二进制代码中的。通过这部分信息，实例可以找到并加载其他的数据字典基表。这个过程是完全内部的、自动的。
数据字典缓存 (Dictionary Cache / Row Cache)：
- 为了避免频繁的物理 I/O 访问 SYSTEM 表空间，数据字典的信息被缓存在 SGA 的共享池 (Shared Pool) 中的一个特定区域，称为字典缓存或行缓存 (Row Cache)。
- 它缓存的是从数据字典基表中取出的数据行（不同于缓冲区缓存（Buffer Cache）缓存的是用户数据块）。
- 字典缓存被进一步划分为多个子缓存（dc_tablespaces, dc_objects, dc_users, dc_sequences 等），每个子缓存负责特定类型的元数据。

三、数据字典的管理

保护：SYSTEM 表空间是数据字典的物理家园，必须确保其高度可用和完整。绝不能将非字典的用户对象放入 SYSTEM 表空间，这会引发空间管理问题、I/O 竞争，并增加备份恢复的复杂性。
统计信息：数据字典基表自身也有统计信息。陈旧的统计信息会导致优化器为递归 SQL 生成低效的计划，进而拖慢整个数据库。统计信息通过：
- 自动维护任务 GATHER_STATS_JOB（或 auto optimizer stats collection）自动收集。
- 手动调用 DBMS_STATS.GATHER_DICTIONARY_STATS 或 GATHER_SCHEMA_STATS('SYS')。
损坏与恢复：数据字典的损坏是灾难性的。恢复通常需要从备份中还原 SYSTEM 表空间，并应用归档日志到最新状态。

第二部分：场景、争用、排查与解决

四、性能争用与排查

数据字典的争用几乎总是发生在内存访问层（字典缓存），因为物理 I/O 访问字典基表是极力要避免的。

1. 场景：行缓存争用 (Row Cache / Dictionary Cache Contention)

原理：
- 在高并发、大量硬解析的场景下，无数会话需要访问相同的字典信息（如检查权限、解析对象名），会导致对字典缓存中“行”的访问争用。
- 为了保护缓存中数据结构的一致性，Oracle 使用闩锁（Latch） 和互斥锁（Mutex） 这种轻量级的串行化设备。
等待事件：
- latch: row cache objects：这是最常见的字典缓存争用事件。当大量进程试图同时搜索或读取字典缓存信息时，会产生对这个闩锁的争用。获取闩锁失败会导致会话等待。
- row cache lock：这是一个更高级的、保护字典缓存条目内容本身的锁。当会话正在修改字典缓存中的某一行（通常在执行 DDL 时）时，其他会话想要读取这一行就会被阻塞，等待此事件。
排查：
1. 确认等待事件：查询 V$SESSION 或 V$ACTIVE_SESSION_HISTORY，查找会话是否在等待 latch: row cache objects 或 row cache lock。
2. 分析字典缓存效率：查询 V$ROWCACHE 视图，这是诊断的关键。
```
-- 检查各子缓存的命中率和修改频率
SELECT parameter, 
       gets, 
       getmisses,
       ROUND((1 - (getmisses / NULLIF(gets, 0))) * 100, 2) "Hit Ratio (%)",
       modifications,
       ROUND((getmisses / NULLIF(gets, 0)) * 100, 2) "Miss Ratio (%)"
FROM v$rowcache
WHERE gets > 0
ORDER BY gets DESC;
```
  - 高 GETS：表示该子缓存被频繁访问。
  - 高 GETMISSES 和 高 MISS RATIO：表示缓存未命中率高，可能因为缓存大小不足或条目被频繁失效。
  - 高 MODIFICATIONS：表示该缓存下的元数据被频繁修改（如大量 DDL），会导致缓存条目失效，从而增加 misses。
3. 关联 SQL：使用 V$SQL 或 AWR/ASH 报告，查找正在经历大量硬解析的 SQL 语句。
解决：
- 根本方法：减少硬解析：使用绑定变量（Bind Variables）。这是最有效的手段。将 SELECT * FROM emp WHERE empno = 1234 和 SELECT * FROM emp WHERE empno = 5678 变为 SELECT * FROM emp WHERE empno = :val，极大减少对字典缓存的访问。
- 调整共享池：确保 SHARED_POOL_SIZE 足够大，以减少字典缓存条目被换出（age out）的风险。但盲目增大并非良策，应先解决解析问题。
- 避免频繁DDL：在生产高峰期间，避免执行 TRUNCATE, DROP, GRANT 等 DDL 操作，因为它们会无效化（invalidate）相关的字典缓存条目并可能获取 row cache lock。
- 保持字典统计信息最新：确保 SYS 模式的统计信息是最新的，以保证递归 SQL 自身的高效执行。

2. 场景：库缓存争用 (Library Cache Contention)

原理：虽然库缓存主要缓存执行计划、SQL 文本、PL/SQL 代码等，但它与数据字典紧密相关。在解析 SQL 时，需要获取对象上的库缓存锁（Library Cache Lock） 和句柄（Handle） 来防止对象被并发修改。
等待事件：
- library cache lock：通常由并发 DDL 和 DML 引起。例如，一个会话正在查询一个大表，另一个会话试图 DROP 该表，查询会话就会等待此事件。
- library cache: mutex X：更轻量级的保护机制，但在高并发解析下也会出现争用。
排查与解决：与行缓存争用根源相似，在于硬解析和 DDL。解决方法同样是使用绑定变量和避免高峰 DDL。

五、常用查询与管理 SQL

查看数据字典缓存命中率：

SELECT (1 - (SUM(getmisses) / NULLIF(SUM(gets), 0))) * 100 "Overall Row Cache Hit Ratio (%)"
FROM v$rowcache;
-- 理想值应高于95%，如果低于90%，则可能存在争用或缓存不足。

查找具体的行缓存争用源头：

SELECT parameter, gets, getmisses, 
       ROUND((getmisses / NULLIF(gets, 0)) * 100, 2) miss_ratio,
       modifications
FROM v$rowcache
WHERE gets > 0
ORDER BY miss_ratio DESC, gets DESC;
-- 重点关注 `dc_objects`, `dc_tablespaces`, `dc_users` 等子缓存的高 miss_ratio。

收集数据字典统计信息：

-- 推荐方式：使用 GATHER_DICTIONARY_STATS
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- 或明确收集SYS schema的统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

查看共享池中字典缓存的内存分配：

SELECT * FROM v$sgastat 
WHERE pool = 'shared pool' 
AND name LIKE '%row%';

查找正在等待字典相关事件的会话：

SELECT sid, serial#, event, state, seconds_in_wait, blocking_session, sql_id
FROM v$session
WHERE event LIKE '%row cache%' OR event LIKE '%library cache%';

检查是否使用了绑定变量（查找类似SQL）：

SELECT sql_id, sql_text, executions, parse_calls
FROM v$sql 
WHERE sql_text LIKE '%SELECT * FROM emp WHERE empno = %'
AND sql_text NOT LIKE '%:b%';
-- 如果找到多条只有字面值差异的SQL，说明未使用绑定变量。

第三部分：通俗易懂的解释

想象一下 Oracle 数据库是一座巨大的、管理极其严密的图书馆。

数据字典基表 (OBJ$, TAB$, COL$)：就是图书馆总管理员的终极 master 清单。这张清单用只有管理员才懂的简写和代码写成（OBJ#, TYPE#），记录了：“书A（obj#=101）放在3楼B区2架，作者是X，共500页，属于科幻区…” 这张清单本身也存放在图书馆里的一个特殊保险柜里（SYSTEM 表空间）。
数据字典视图 (DBA_, ALL_, USER_)：就是图书馆为不同读者准备的查询台。
- USER_ 查询台：你走过去，它只显示你本人借阅和捐赠的所有书籍的清单，用的是你能看懂的正常语言。
- ALL_ 查询台：显示整个图书馆所有你被允许阅读的书籍的清单（包括别人借给你看的）。
- DBA_ 查询台：只有图书管理员能用，显示图书馆里的一切，包括那些禁书区和内部档案。
动态性能视图 (V$)：这是图书馆大门口的实时监控显示屏。它不关心书本身，它显示的是：“当前入馆人数：153人，最拥挤区域：科幻区，最近一小时借阅量：300本…” 这些信息是实时变化的。
递归 SQL：当你想找一本《三体》时，你不会直接去翻 master 清单（基表）。你会去问查询台（数据字典视图）：“请问《三体》在哪？” 而查询台的工作人员，为了回答你的问题，他需要背后去查阅好几次那个 master 清单。他为你做的这几步查询，就是“递归 SQL”。如果你每次问“《三体》在哪？”都要他现去查（硬解析），那他就很忙。但如果他记得答案（缓存），他就能马上告诉你（软解析）。
行缓存争用 (latch: row cache objects)：在借书高峰期（高并发），无数人同时挤在查询台前问问题：“《三体》在哪？”“《哈利波特》呢？”。查询台工作人员只有一个，他一次只能回答一个人的问题。其他人必须排队等待，这就产生了争用。解决方案是：1) 让大多数人通过记忆（缓存）自己知道答案，别老来问（使用绑定变量减少解析）；2) 让工作人员记住热门书的答案（调整共享池）。
row cache lock：你正在查询《三体》的信息，这时图书管理员推着车过来要把这本书下架回收 (DROP TABLE)。为了保证操作的一致性，他会在书上挂个“正在盘点，请稍候”的牌子（row cache lock）。你这时的查询就会被这个牌子挡住，必须等他操作完。

总结：
Oracle 数据字典是数据库自我认知和自我管理的核心系统。它的高效运作依赖于共享池中字典缓存和库缓存的命中率。DBA 的核心任务就是通过强制使用绑定变量和审慎进行DDL操作来最大限度地减少对数据字典的递归访问和争用，确保这个“图书馆查询系统”畅通无阻，从而让整个“图书馆”（数据库）高效运转。任何性能调查，在深入底层之前，都应先检查是否源于字典缓存或库缓存的争用。

欢迎关注我的公众号《IT小Chen》