一、优化器的概念：它不找最优，只找最便宜

一条 SQL 到出结果，中间分三步：解析、优化、执行。 解析负责查语法、查对象、查权限；优化负责「列出候选计划、算成本、挑最便宜的」； 执行按挑出来的计划取数据。 早期 Oracle 有过两个优化器：RBO 按规则挑（比如「有索引就走索引」），CBO 按成本挑。 9i 之后 RBO 只剩一个兼容开关，19c 里基本见不到了。 所以现在说「优化器」，默认就是 CBO。 成本是三块加起来的：单块读、多块读、CPU 时间。 单块读是「一次读一个块」，走索引就是这个；多块读是「一次读一批」，全表扫就是这个。 多块读一次能读好几个块，所以同样是读一万块，全扫比走索引便宜得多。 把两条式子并排放，取舍就清楚了： 走索引 ≈ 索引层数 × 单块读 + 命中行数 × 回表单块读；全表扫 ≈ 表块数 ÷ 多块读倍数。 命中行数一多，左边那项就压过右边——这就是「选择性低就别走索引」的算术来源。 经验上的分界点在 5% 左右：命中不到 5% 的行，走索引划算；超过就全扫便宜。 但这是经验，不是规则——它取决于行长、块大小、多块读倍数，同一张表换个块大小，分界点就挪了。 算成本要有原料，原料就是统计信息： DBA_TABLES.NUM_ROWS 是行数、BLOCKS 是块数，DBA_TAB_COLUMNS.NUM_DISTINCT 是不同值个数， HISTOGRAM 是直方图。 统计信息过时，等于让优化器闭着眼算账——这是生产上「突然变慢」的第一大原因。

二、运行 EXPLAIN PLAN：三条命令

Oracle 里看计划最常用的三条，一条命令配一句解释： ① EXPLAIN PLAN FOR 语句——把计划写进 PLAN_TABLE。 ② SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)——把它排版出来。 ③ SET AUTOTRACE ON EXPLAIN——执行完自动带出计划。

SQL> EXPLAIN PLAN FOR
  2  SELECT e.ename, e.sal FROM emp e WHERE e.deptno = 30;

已解释。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------
Plan hash value: 1234567890

| Id  | Operation                    | Name  | Rows  | Cost |
-------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     6 |    3 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMP   |     6 |    3 |
|*  2 |   INDEX RANGE SCAN           | I_DNO |     6 |    1 |
-------------------------------------------------------------

Predicate Information (identified by operation id):
   2 - access("E"."DEPTNO"=30)

但 EXPLAIN PLAN 有个坑：它只是「让优化器算一遍」，并不真执行。 所以绑定变量是猜的、WHERE 里的函数是不求值的、它看到的也可能是你当前会话的参数设置， 而不是业务真正的环境。 11g 之后更推荐 DBMS_XPLAN.DISPLAY_CURSOR：它看的是【真实执行过】的那个游标。 加 FORMAT => 'ALLSTATS LAST' 还会带出实际行数和实际耗时—— 这才是「这条 SQL 当时到底怎么跑的」。 读计划有四条规矩，按顺序用： ① 从右往左读——最右边最内层最先执行。 ② 缩进表示父子——缩进深的那层是被调用的。 ③ 同一层从上往下依次执行。 ④ 先看 Rows（估算行数），再看 Cost（成本），最后才看操作名。 第 ④ 条最容易被跳过，但它最值钱：操作名告诉你「用了什么手段」， 行数告诉你「这个手段合不合适」。 只看操作名，你只能说出「走了索引」；加上行数，你才能判断「该不该走索引」。

三、读计划最容易看错的三个地方

第一处：把缩进当成「先做上面的」。 计划是树，不是流水线。缩进最深的那行是最先跑的，它上面的行是「调用者」。 所以上面那段计划里，INDEX RANGE SCAN 先跑，回表第二，SELECT 最后。 第二处：把 INDEX RANGE SCAN 当成「已经拿到数据了」。 索引里只有索引列和 rowid，没有其他列。 要 ename、sal 就得拿 rowid 回表——这一层叫 TABLE ACCESS BY INDEX ROWID。 回表的代价才是走索引的大头，也是最容易被忽略的一项。 第三处：把 Cost 当成毫秒。 Cost 是个相对数值，单位是「相当于多少次单块读」，跟秒数没有换算关系。 同一个计划在两台机器上 Cost 一样，耗时可能差十倍。 真正要盯的是「估算行数和实际行数差多少」。 怎么比这两个数： DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST') 会同时给出 E-Rows（估算）和 A-Rows（实际）。 两者差十倍以上，就不要改 SQL 了——问题在统计信息，不在写法。 先 DBMS_STATS.GATHER_TABLE_STATS 收一遍，再看计划有没有变。

四、Oracle 中 SQL 执行计划的管理

三个视图管三件事：V$SQL 一条 SQL 一行（带 SQL_ID），V$SQL_PLAN 存计划行， V$SQLSTATS 是聚合统计。 查慢 SQL 一般从 V$SQL 按 ELAPSED_TIME 倒排开始。 同一个 SQL_ID 出现好几个 CHILD_NUMBER，说明这条 SQL 有好几套计划。 为什么分叉？查 V$SQL_SHARED_CURSOR，它一列一个原因，值为 Y 的就是原因。 常见的是绑定变量长度不同、优化器环境不同、或者 ACS 主动分了岔。 固定计划有三层手段，从轻到重： ① SQL Plan Baseline——把好计划存进基线，以后想换先跟基线比，比不过就不换。 ② SQL Profile——不改 SQL，只给优化器补一组「修正系数」，让估算变准。 ③ hint——直接写死「走这个索引、用这个连接方式」。 三层的区别在「谁在控制」：基线是「优化器自己挑，但只能在允许的集合里挑」； Profile 是「告诉优化器你算错了，往这个方向修」；hint 是「你别想了，照我说的做」。 越往后越硬，也越难适应数据变化——所以顺序是从轻到重，能不改就不改。 基线的两个常用操作： DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 从游标缓存里把计划收进基线； DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 拿新计划跟基线比，更好就收进去。 收完之后查 DBA_SQL_PLAN_BASELINES，ENABLED 为 YES 才生效。 最后给个排查顺序，遇到「突然变慢」照这个走。 第一步看统计信息是不是过时（最常见），第二步看计划是不是换了（比 V$SQL_PLAN）， 第三步看绑定变量是不是碰上不挑的值，最后才考虑 hint。 顺序反了会走很多弯路：上来就加 hint，把问题盖住，下次数据一变又崩。

五、动手 Demo：优化器靠统计信息吃饭，这一课把它跑出来

这一篇的 Demo 做三件事：看统计信息长什么样、把「数据分布差 500 倍」跑成一张表、 再看计划会变的后果。 先说清本沙箱的限制：SQLite 的代价模型用硬编码常数，基本不看数据分布。 所以「全表扫更快」那一行是我加 NOT INDEXED 强制出来的。 这一点在输出里也如实标了出来，别把它当成「优化器自己选的」。

# 优化器怎么选路：同一个查询，统计信息一变计划就变
import os, sqlite3, tempfile
p = os.path.join(tempfile.gettempdir(), "t154.db")
if os.path.exists(p):
    os.remove(p)
c = sqlite3.connect(p)
c.execute("create table big (id int primary key, deptno int, amt int)")
c.executemany("insert into big values (?,?,?)",
              [(i, i % 12 + 1, i % 1000) for i in range(1, 50001)])
c.commit()

def plan(sql):                      # 只取计划，不执行 SQL
    return " | ".join(r[3] for r in
                       c.execute("explain query plan " + sql).fetchall())

q = "select count(*) from big where deptno = 3"
print("没索引、没统计：", plan(q))
c.execute("create index i_d on big(deptno)")
print("有索引、没统计：", plan(q))
c.execute("analyze")               # 收统计信息
print("有索引、有统计：", plan(q))

for r in c.execute("select tbl, idx, stat from sqlite_stat1"):
    print("  ", r)

实跑输出：

--------------------------------------------------------------------
第六十七课：优化器——它靠统计信息吃饭
--------------------------------------------------------------------

  **优化器不是「懂 SQL」，它是「按统计信息算成本，挑最便宜的那条路」。**
  **所以统计信息一变，执行计划就变——这一课把这件事跑出来。**

  一、先看统计信息长什么样
  --------------------------------------------------------------------
    dept                   sqlite_autoindex_dept_1          12 1
    big                    i_da                             200000 16667 17
    big                    i_name                           200000 4
    big                    i_big_deptno                     200000 16667
    big                    sqlite_autoindex_big_1           200000 1
  **每行的意思是「这张表／这个索引大约有多少条，以及索引列上有多少不同值」。**
  **比如 `200000 16667` 就是「20 万行，索引列有 16667 个不同值」**——
  **平均一个值对应 12 行，很挑。**
  **Oracle 里对应的东西更细：`DBA_TABLES.NUM_ROWS` 是行数，**
  **`DBA_TAB_COLUMNS.NUM_DISTINCT` 是不同值个数。**
  **还有 `DBA_HISTOGRAMS`，也就是直方图，记录「值怎么分布」。**
  **直方图是 Oracle 比 SQLite 多出来的那一块：**
  **它记的是分布，不只是「有多少种」。**

  二、数据分布差 500 倍，计划该不该变
  --------------------------------------------------------------------
  **造一张极不均匀的表：20 万行里 19.9 万行的 `v` 都是 1，剩下 2000 行散在 900 个值上。**
  **同一条 SQL 换个值，命中率从 99.5% 变成 0.1%。**

  取值                 走索引    强制全表扫
  -----------------------------------------
  `v = 1`（占 99.5%）  0.0414 秒 0.0306 秒
  `v = 500`（占 0.1%） 0.0001 秒 0.0186 秒
  已选择 2 行
  **看第一行：取 99.5% 的行，全表扫反而快 1.4 倍。**
  **看第二行：取 0.1% 的行，走索引快 305 倍。**
  **所以「要不要走索引」的答案，完全取决于【这个值在表里占多少】。**
  **统计信息的作用就是把这件事告诉优化器。**
  **但这里有个诚实的结论要说：本沙箱的优化器【两条都选了索引】。**
  **上面那个「全表扫更快」，是加 `NOT INDEXED` 强制出来的。**
  **原因是 SQLite 的代价模型用了硬编码常数，基本不看数据分布。**
  **Oracle 不一样：它有直方图，知道 99.5% 这个数，这种情况会选全表扫。**

  三、这就引出了「绑定变量」的麻烦
  --------------------------------------------------------------------
  **上面那张表就是【绑定变量窥探】问题的原型。**
  **同一条带绑定变量的 SQL，第一次传「很挑」的值，优化器就以为它一直很挑，一直走索引。**
  **等哪天传进来一个「不挑」的值，这条计划就变成灾难。**
  **19c 的解法是【自适应游标共享】（ACS）：一个 SQL 允许多套计划，按参数值自动切换。**
  **所以看到 `V$SQL` 里同一个 `SQL_ID` 有好几个 `CHILD_NUMBER`，不用奇怪。**
  **还有一招是从根上避开的：让「不挑」的值别走索引**——**比如给这种列建直方图、或者干脆不建索引。**

  四、计划会变，怎么办
  --------------------------------------------------------------------
    统计信息按时收集       别等它过时，也别频繁收集
    关键 SQL 固定计划       SQL Plan Baseline（SPM）
    出问题先比计划          别急着改 SQL
  **第一条的分寸：大表每周一次，小表每月一次，别天天收集**——
  **收集本身要扫全表，而且会让计划突然变。**
  **第二条是 11g 之后最重要的一个功能：把「好的计划」存成基线。**
  **以后优化器想换计划，先跟基线比，比不过就不换。**
  **这是治「昨天还好今天突然慢」的根子。**

第一段：sqlite_stat1 每行的意思是「大约多少行、索引列有多少不同值」。 比如 200000 16667 就是「20 万行，索引列有 16667 个不同的值」，平均一个值对应 12 行。 Oracle 里对应的是 DBA_TABLES.NUM_ROWS 和 DBA_TAB_COLUMNS.NUM_DISTINCT，另外多一样东西： DBA_HISTOGRAMS 直方图。 直方图记的不是「有多少种值」，而是「值怎么分布」—— 这一样是本沙箱没有的，也是下面那段诚实的结论的由来。 第二段是全篇最值钱的一跑：20 万行里 19.9 万行的 v 都是 1，同一条 SQL 换个值， 命中率从 99.5% 掉到 0.1%。 结果一个快一个慢，差了几百倍。 所以「要不要走索引」根本没有统一答案，答案在「这个值占多少行」里。 第三段把这个现象接到绑定变量上：第一次传「很挑」的值，优化器就以为它一直很挑。 19c 的解法是自适应游标共享，一个 SQL 允许多套计划—— 所以 V$SQL 里一个 SQL_ID 挂好几个 CHILD_NUMBER 是正常的。 第四段是结论：计划会变是常态，要能管住它。 三个动作——按时收统计信息、关键 SQL 收基线、出问题先比计划再改 SQL。 第三条最省钱：多数「变慢」不是 SQL 写坏了，是计划换了。

30 秒实验

1. 把第二段那张表的 v = 1 改成 v = 2（只占 2000 行里的几行），再跑一次。 看耗时会不会掉到和 v = 500 一个量级——这就是「值占多少行」在起作用。
2. 用 EXPLAIN QUERY PLAN 看第一段三条语句的计划，确认「没索引时是 SCAN， 有索引时变成 SEARCH」。再看一眼 analyze 前后计划有没有变。
3. 查 DBA_SQL_PLAN_BASELINES（如果手边有 Oracle 环境），看 ENABLED、ACCEPTED、 FIXED 三列分别是什么含义。FIXED 为 YES 就是彻底锁死。

常见误区

1. 「优化器总能挑到最快的计划」。 它只能按统计信息挑估算最便宜的。 统计信息错，它就一定挑错，而且挑得很有信心。
2. 「Cost 小就一定快」。 Cost 是相对单位，不是耗时。 真正要盯的是估算行数与实际行数的差距。
3. 「走索引一定比全表扫快」。 命中行数一多，回表比全扫贵得多。 15.3 量过：取 99.5% 的行，全扫快 1.3 倍。
4. 「出问题先加 hint」。 hint 是把优化器的判断写死。 写死之后数据一变，它再也没法自己调整——问题只是被盖住了。

下一篇讲怎么【验证】改动到底有没有用。这一篇说计划会变，那怎么知道变好还是变坏？ 下一篇讲两种重演：数据库重演把整个负载放一遍，SQL 重演只比单条 SQL。

• ☐ （本篇完，下一篇 15.5 数据库和 SQL 重演）

相关笔记

• 合理使用索引
• 常规 SQL 语句优化
• 系统全局区（SGA）优化
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引