“100 条命令”系列已经写了 38 篇，接下来继续写 RAC、Data Guard、GoldenGate 这些专题。想接着看，可以收藏 ORA100 · DBA100，微信里搜索小程序 「三笠的百令册」。

网站地址：
书接上文，前两篇从 +ARCH 被打满开始，一路查到了一个比较反常识的结果。

9 月 18 日，这套 Oracle 19c RAC 一天产生了 445.89GB Redo 和 450.57GB Archive，其中 redo size for lost write detection 就占了 384.51GB。继续往下追，当天全库 Physical Reads 达到了 78.31 亿次，而 SQL f71tmzgdz68g2 一条就贡献了 66.70 亿次，占全库大约 85%。

更离谱的是，它并不是什么大批量 DML，而是一条很普通的查询：

SELECT ...
FROM TASK_PARAM_DETAIL
WHERE TASK_PARAM_ID IN (...);

这条 SQL 一天执行 277 次，平均每次大约读取 2400 万个 block，数据库块大小是 8KB，粗略换算下来就是 184GB，而整张 TASK_PARAM_DETAIL 表实际也只有 205.54GB。

换句话说，这条 SQL 基本就是执行一次，把这张 205GB 的表扫一遍。

执行计划也确实如此：

TABLE ACCESS FULL
TASK_PARAM_DETAIL

继续查索引，发现这张 205GB 的表一个索引都没有，但是还有一个地方不对：Oracle 对这张表的估算只有：

Rows = 100
Cost = 30
Time = 00:00:01

再看统计信息：

NUM_ROWS       8079
BLOCKS           95
LAST_ANALYZED  27-NOV-24

也就是说，Oracle 手里拿着的还是 2024 年 11 月的统计信息，它认为这张表只有 8079 行、95 个 block。

于是准备重新收集统计信息，结果直接报错：

ORA-20005: object statistics are locked (stattype = ALL)

第三篇就从这个 ORA-20005 继续往下查。

统计信息为什么一直停在 2024 年？

既然 GATHER_TABLE_STATS 明确报了 object statistics are locked，那就先确认这张表的统计信息锁定状态，同时看一下是不是只有这一张表存在这个情况。

SELECT owner,
       table_name,
       stattype_locked,
       last_analyzed
FROM dba_tab_statistics
WHERE owner = 'APP'
  AND table_name = 'TASK_PARAM_DETAIL';

SELECT owner,
       COUNT(*) locked_count
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL
GROUP BY owner
ORDER BY locked_count DESC;

SELECT owner,
       table_name,
       stattype_locked,
       last_analyzed
FROM dba_tab_statistics
WHERE owner = 'APP'
  AND stattype_locked IS NOT NULL
ORDER BY table_name;

现场继续查以后发现，被锁的并不止这一张表。

这一点其实很重要，因为它说明这更像是这个 Schema 原来的统计信息管理策略，而不是某个人临时把 TASK_PARAM_DETAIL 锁了一下。

所以当时没有直接把整个 Schema 的统计信息全部解锁，当前事故 SQL 已经明确指向 TASK_PARAM_DETAIL，所以我先只处理这一张表。

解锁以后，8079 行变成了 18.5 亿

单独解锁这张表的统计信息：

BEGIN
    DBMS_STATS.UNLOCK_TABLE_STATS(
        ownname => 'APP',
        tabname => 'TASK_PARAM_DETAIL'
    );
END;
/

PL/SQL procedure successfully completed.

然后重新收集：

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => 'APP',
        tabname          => 'TASK_PARAM_DETAIL',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        degree           => 8,
        cascade          => TRUE,
        no_invalidate    => FALSE
    );
END;
/

PL/SQL procedure successfully completed.

这次正常完成，重新检查表统计信息：

SELECT owner,
       table_name,
       num_rows,
       blocks,
       avg_row_len,
       last_analyzed
FROM dba_tables
WHERE owner = 'APP'
  AND table_name = 'TASK_PARAM_DETAIL';

OWNER  TABLE_NAME         NUM_ROWS       BLOCKS
-----  -----------------  -------------  ----------
APP    TASK_PARAM_DETAIL  1853767618     26914207

这个结果出来的时候，前面很多东西一下就解释通了。

之前 Oracle 认为：

NUM_ROWS     8,079
BLOCKS          95

重新收集统计信息之后发现：

NUM_ROWS     1,853,767,618
BLOCKS          26,914,207

不是八千多万，而是 18.54 亿行，原来的 NUM_ROWS 和实际数据量差了大约 22.9 万倍。

TASK_PARAM_ID 到底适不适合建索引？

重新收集以后，再看业务 SQL 查询条件 TASK_PARAM_ID 的列统计：

SELECT column_name,
       num_distinct,
       num_nulls,
       density,
       histogram,
       last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'APP'
  AND table_name = 'TASK_PARAM_DETAIL'
  AND column_name = 'TASK_PARAM_ID';

COLUMN_NAME   NUM_DISTINCT
------------- ------------
TASK_PARAM_ID      1851008

之前这个字段的：

NUM_DISTINCT = 938

现在已经变成：

NUM_DISTINCT ≈ 185 万

而整张表大约 18.54 亿行，业务 SQL 又恰好是：

WHERE TASK_PARAM_ID IN (...)

结合业务访问方式和类似明细表的索引设计，最终我们决定给 TASK_PARAM_ID 补一个单列索引。

第一次建索引，TEMP 先撑不住了

这是生产 RAC，业务还在运行，所以创建时使用 ONLINE PARALLEL 8：

CREATE INDEX APP.IDX_TASK_PARAM_DETAIL_TASKID
ON APP.TASK_PARAM_DETAIL(TASK_PARAM_ID)
TABLESPACE APP_TABLESPACE
ONLINE
PARALLEL 8;

这是一个 18.5 亿行、205GB 的表，肯定不是几秒钟就能完成，所以另外开窗口通过 GV$SESSION_LONGOPS 看进度：

SELECT inst_id,
       sid,
       serial#,
       opname,
       sofar,
       totalwork,
       ROUND(sofar/totalwork*100,2) pct,
       units,
       elapsed_seconds,
       time_remaining
FROM gv$session_longops
WHERE totalwork > 0
  AND sofar <> totalwork
ORDER BY elapsed_seconds DESC;

INST_ID SID  SERIAL# OPNAME       SOFAR     TOTALWORK   PCT    UNITS   ELAPSED_SECONDS TIME_REMAINING
------- ---- ------- ------------ --------- ---------- ------ -------- --------------- --------------
2       531  49889   Table Scan   3481351   26914207   12.93  Blocks                87            586

这里有一个数据很有意思：

TOTALWORK = 26,914,207 blocks

正好和重新收集后这张表的：

BLOCKS = 26,914,207

对上了。

进度后来跑到了 40%、59%，看起来一直在正常往前走，但是 1 分 45 秒以后，创建突然失败：

CREATE INDEX APP.IDX_TASK_PARAM_DETAIL_TASKID
ON APP.TASK_PARAM_DETAIL(TASK_PARAM_ID)
TABLESPACE APP_TABLESPACE
ONLINE
PARALLEL 8;

ERROR at line 1:
ORA-12801: error signaled in parallel query server P005
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Elapsed: 00:01:45.36

索引自然也是创建失败了：

SELECT index_name,
       status
FROM dba_indexes
WHERE owner = 'APP'
  AND index_name = 'IDX_TASK_PARAM_DETAIL_TASKID';

no rows selected

到这里，问题已经从“SQL 怎么优化”变成了一个很现实的生产实施问题：18.5 亿行的大表在线并行建索引，现有 TEMP 撑不住。

TEMP 32GB 为什么不够？

这是一个 CDB 架构的库，先确认是否切换到对应的 Container：

SHOW CON_NAME;

CON_NAME
------------------------------
CDB$ROOT

SELECT tablespace_name,
       file_id,
       ROUND(bytes/1024/1024/1024,2) size_gb,
       autoextensible,
       ROUND(maxbytes/1024/1024/1024,2) max_gb
FROM dba_temp_files
ORDER BY file_id;

TABLESPACE_NAME  FILE_ID  SIZE_GB  AUT  MAX_GB
--------------- -------- --------- ---- -------
TEMP                   1       .67 YES       32

但事故表并不在 Root，所以不能拿这里的 TEMP 判断，切到对应 PDB 再看：

ALTER SESSION SET CONTAINER=APP_PDB;

Session altered.

SHOW CON_NAME;

CON_NAME
------------------------------
APP_PDB

SELECT file_id,
       ROUND(bytes/1024/1024/1024,2) size_gb,
       autoextensible,
       ROUND(maxbytes/1024/1024/1024,2) max_gb
FROM dba_temp_files
ORDER BY file_id;

FILE_ID  SIZE_GB  AUT  MAX_GB
------- --------- ---- -------
      4     32.00 YES       32

可以发现当前 PDB TEMP 当时只有 32GB，继续增加 tempfile，最终 PDB TEMP 总容量扩到 182GB：

SELECT ROUND(SUM(bytes)/1024/1024/1024,2) total_gb
FROM dba_temp_files
WHERE tablespace_name='TEMP';

TOTAL_GB
--------
182

TEMP 够了，还要确认永久表空间

TEMP 扩完以后还不能直接放心重跑，索引排序使用 TEMP，但最终生成的索引段还是要写入表空间。

这张表本身已经有 205GB，而且当时还不知道最终索引会做到多大，所以重新创建之前又检查了一遍目标表空间：

SELECT file_id,
       file_name,
       ROUND(bytes/1024/1024/1024,2) size_gb,
       autoextensible,
       ROUND(maxbytes/1024/1024/1024,2) max_gb,
       ROUND((maxbytes-bytes)/1024/1024/1024,2) growable_gb
FROM dba_data_files
WHERE tablespace_name='APP_TABLESPACE'
ORDER BY file_id;

FILE_ID  SIZE_GB  AUT  MAX_GB  GROWABLE_GB
-------  -------  ---  ------  -----------
     38       32  YES      32            0
     68       30  YES      30            0
     69       30  YES      30            0
     70       30  YES      30            0
     71       30  YES      30            0
     81       32  YES      32            0
     82       32  YES      32            0
     83       32  YES      32            0
     ...

再看整个表空间当前还有多少剩余空间：

SELECT ROUND(SUM(bytes)/1024/1024/1024,2) free_gb
FROM dba_free_space
WHERE tablespace_name='APP_TABLESPACE';

   FREE_GB
----------
    195.25

当时还有大约 195GB 可用，底层 +DATA 还有 3TB 多空间：

ASMCMD> lsdg

State    Type    Total_MB  Free_MB   Name
-------- ------- --------- --------- -----
MOUNTED  EXTERN    6294240   3637144 DATA/

从这两个结果看，ASM 本身没有压力，表空间当前剩余容量也足够继续创建这个单列索引，确认完这些以后，才重新执行。

再次创建索引：

CREATE INDEX APP.IDX_TASK_PARAM_DETAIL_TASKID
ON APP.TASK_PARAM_DETAIL(TASK_PARAM_ID)
TABLESPACE APP_TABLESPACE
ONLINE
PARALLEL 8;

这次 TEMP 已经扩容，索引创建正常往下跑。

为了确认当前到底是哪条 SQL 在执行，又从 GV$SQL 找到了创建索引对应的 SQL_ID：

SET LONG 5000
SET LONGCHUNKSIZE 5000
SET LINES 300

SELECT inst_id,
       sql_id,
       child_number,
       parsing_schema_name,
       executions,
       SUBSTR(sql_text,1,500) sql_text
FROM gv$sql
WHERE sql_text LIKE
      'CREATE INDEX APP.IDX_TASK_PARAM_DETAIL_TASKID%';

   INST_ID SQL_ID        CHILD_NUMBER PARSING_SCHEMA_NAME EXECUTIONS
---------- ------------- ------------ ------------------- ----------
         1 5hcqj6j6gkp57            0 SYS                          0

SQL_TEXT
--------------------------------------------------------------------------------
CREATE INDEX APP.IDX_TASK_PARAM_DETAIL_TASKID
ON APP.TASK_PARAM_DETAIL(TASK_PARAM_ID)
TABLESPACE APP_TABLESPACE ONLINE PARALLEL 8

索引创建对应的 SQL_ID 就是 5hcqj6j6gkp57，再看执行计划：

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        '5hcqj6j6gkp57',
        NULL,
        'ALL'
    )
);

Plan hash value: 2062379604

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                | Name                         | Rows  | Bytes |    TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|  0 | CREATE INDEX STATEMENT   |                              |       |       |       |      |            |
|  1 |  PX COORDINATOR          |                              |       |       |       |      |            |
|  2 |   PX SEND QC (ORDER)     | :TQ10001                    | 1853M |   34G | Q1,01 | P->S | QC (ORDER) |
|  3 |    INDEX BUILD NON UNIQUE| IDX_TASK_PARAM_DETAIL_TASKID |       |       | Q1,01 | PCWP |            |
|  4 |     SORT CREATE INDEX    |                              | 1853M |   34G | Q1,01 | PCWP |            |
|  5 |      PX RECEIVE          |                              | 1853M |   34G | Q1,01 | PCWP |            |
|  6 |       PX SEND RANGE      | :TQ10000                    | 1853M |   34G | Q1,00 | P->P | RANGE      |
|  7 |        PX BLOCK ITERATOR |                              | 1853M |   34G | Q1,00 | PCWC |            |
|* 8 |         TABLE ACCESS FULL| TASK_PARAM_DETAIL            | 1853M |   34G | Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------

这里最值得看的不是 TABLE ACCESS FULL，因为创建 B-tree 索引本来就需要读取基表，而是执行计划里的：

Rows = 1853M

它已经和重新收集统计信息后的：

NUM_ROWS = 1,853,767,618

基本一致。

而修复统计信息之前，同一张表在业务 SQL 执行计划里的估算只有：

Rows = 100
Cost = 30

这个对比其实比单纯看到 NUM_ROWS 从 8079 变成 18.5 亿更直观。

GV$SESSION_LONGOPS 里的 Table Scan 到底是谁？

索引创建过程中还发现了一个问题，当时一直通过 GV$SESSION_LONGOPS 看进度，但是里面同时出现了多个：

Table Scan
Rowid Range Scan

而业务 SQL f71tmzgdz68g2 此时也还在执行，所以只看到 OPNAME=Table Scan 并不能判断它是业务 SQL 的 Full Scan，还是 CREATE INDEX 正在并行扫描基表。

最直接的办法就是把 SQL_ID 对上：

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.event,
       s.program
FROM gv$session s
WHERE s.sql_id IN (
          'f71tmzgdz68g2',
          '5hcqj6j6gkp57'
      )
ORDER BY s.sql_id,
         s.inst_id,
         s.sid;

这里：

f71tmzgdz68g2  → 业务 SELECT
5hcqj6j6gkp57  → CREATE INDEX

再单独看创建索引的会话：

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.event,
       s.program
FROM gv$session s
WHERE s.sql_id='5hcqj6j6gkp57'
ORDER BY s.inst_id,s.sid;

-- 现场共看到 17 个相关会话
-- 1 个 PX Coordinator
-- 16 个 PX Slave
--
-- 部分 Slave：
-- direct path read temp
-- direct path write
--
-- 另外一些：
-- PX Deq: Execution Msg

这也解释了为什么虽然语句写的是 PARALLEL 8，现场却能看到 P000 ~ P00F 共 16 个 PX Slave：这个执行计划使用了两组 PX Server Set，并不是说 PARALLEL 8 就只会在操作系统层面看到 8 个并行进程。

索引终于建出来了

TEMP 和永久表空间都确认以后，这次创建没有再失败，最终 SQL*Plus 返回：

CREATE INDEX APP.IDX_TASK_PARAM_DETAIL_TASKID
ON APP.TASK_PARAM_DETAIL(TASK_PARAM_ID)
TABLESPACE APP_TABLESPACE
ONLINE
PARALLEL 8;

Index created.

Elapsed: 00:06:47.24

整个 18.5 亿行表的在线并行索引创建最终用了 6 分 47 秒。

创建完成以后马上把索引并行属性改回来：

ALTER INDEX APP.IDX_TASK_PARAM_DETAIL_TASKID
NOPARALLEL;

Index altered.

Elapsed: 00:00:00.00

PARALLEL 8 是为了缩短这次生产实施时间，不需要让索引长期保持并行属性，再次检查索引状态、统计信息和实际 Segment 大小：

SELECT i.index_name,
       i.status,
       i.degree,
       i.num_rows,
       i.distinct_keys,
       i.leaf_blocks,
       i.clustering_factor,
       ROUND(s.bytes/1024/1024/1024,2) index_gb,
       TO_CHAR(i.last_analyzed,'YYYY-MM-DD HH24:MI') last_analyzed
FROM dba_indexes i
JOIN dba_segments s
  ON s.owner = i.owner
 AND s.segment_name = i.index_name
WHERE i.owner='APP'
  AND i.index_name='IDX_TASK_PARAM_DETAIL_TASKID';

INDEX_NAME                    STATUS DEGREE NUM_ROWS    DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR INDEX_GB LAST_ANALYZED
----------------------------- ------ ------ ----------- ------------- ----------- ----------------- -------- ----------------
IDX_TASK_PARAM_DETAIL_TASKID  VALID  1      1853966951       1853079     8025835          28739764    61.23 2026-09-19 00:15

最终索引大小是 61.23GB，这也说明前面提前检查永久表空间是有必要的：205GB 的基表，最终建出来的单列索引也有 61GB，并不是一个可以完全忽略容量的小对象。

索引建好了，但旧 SQL 还在跑

索引创建成功以后，还有一个很容易忽略的问题：已经开始执行的 Full Table Scan，不会因为中途突然多了一个索引就自动改走索引。

当时业务侧还有两个旧执行正在跑：

SELECT inst_id,
       sid,
       serial#,
       sql_id,
       sql_child_number,
       status,
       event,
       ROUND(last_call_et/60,2) running_min,
       machine
FROM gv$session
WHERE sql_id='f71tmzgdz68g2';

INST_ID SID   SERIAL# SQL_ID        SQL_CHILD_NUMBER STATUS EVENT                      RUNNING_MIN
------- ----- ------- ------------- ---------------- ------ -------------------------- -----------
      2 436     47221 f71tmzgdz68g2                0 ACTIVE db file scattered read           9.67
      2 2933    27071 f71tmzgdz68g2                1 ACTIVE gc cr multi block request        4.62

一个已经跑了接近 10 分钟，一个跑了 4 分多钟，而且等待事件仍然是：

db file scattered read
gc cr multi block request

和前面的 Full Scan 特征一致。

确认业务影响以后，把这两个旧执行终止，让后续请求重新解析：

ALTER SYSTEM KILL SESSION '436,47221,@2' IMMEDIATE;

ORA-00031: session marked for kill

ALTER SYSTEM KILL SESSION '2933,27071,@2' IMMEDIATE;

ORA-00031: session marked for kill

这里的 ORA-00031 不是 Kill 失败，而是 Session 已经被标记为 Kill。

再次检查：

SELECT inst_id,
       sid,
       serial#,
       sql_id,
       sql_child_number,
       status,
       event,
       machine
FROM gv$session
WHERE sql_id='f71tmzgdz68g2';

no rows selected

旧的 Full Scan 执行已经消失。

接下来真正要确认的是：业务重新发起 SQL 以后，Oracle 到底会不会使用刚刚创建的索引。

执行计划终于变了

业务重新发起以后，先看 Cursor：

SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       executions,
       invalidations,
       is_obsolete
FROM gv$sql
WHERE sql_id='f71tmzgdz68g2';

INST_ID SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS INVALIDATIONS I
------- ------------- ------------ --------------- ---------- ------------- -
      1 f71tmzgdz68g2            0      2091707026          1             1 N

原来的 Plan Hash 是：

3287208169

现在变成：

2091707026

再直接看新执行计划：

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        'f71tmzgdz68g2',
        0,
        'ALL'
    )
);

Plan hash value: 2091707026

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                         | E-Rows |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                              |        |
|   1 |  INLIST ITERATOR                     |                              |        |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TASK_PARAM_DETAIL            |   200K |
|*  3 |    INDEX RANGE SCAN                  | IDX_TASK_PARAM_DETAIL_TASKID |   200K |
---------------------------------------------------------------------------------------------

原来的：

TABLE ACCESS FULL

已经变成：

INLIST ITERATOR
    ↓
INDEX RANGE SCAN
    ↓
TABLE ACCESS BY INDEX ROWID BATCHED

这正是这条 WHERE TASK_PARAM_ID IN (...) 查询希望看到的访问路径。

不看第一次执行，继续让真实业务跑

新计划刚出来的时候，第一次执行的数据确实已经非常漂亮：

SELECT inst_id,
       child_number,
       plan_hash_value,
       executions,
       buffer_gets,
       disk_reads,
       rows_processed,
       ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec,
       ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec
FROM gv$sql
WHERE sql_id='f71tmzgdz68g2'
ORDER BY inst_id,child_number;

INST_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS ROWS_PROCESSED GETS_PER_EXEC READS_PER_EXEC
------- ------------ --------------- ---------- ----------- ---------- -------------- ------------- --------------
      1            0      2091707026          1        9152        780          40950          9152            780

从原来单次 2400 多万次 Disk Reads，直接降到 780，看起来问题似乎已经解决了。

但我们不能拿这一次执行的数据直接下结论，需要继续监控观察 🔎。

单次执行很容易受到 Bind 值、返回行数以及缓存状态的影响，尤其这条 SQL 本身就是一个很长的 IN 条件，不同批次传进来的 TASK_PARAM_ID 并不完全一样。所以这里没有继续做其他调整，而是让真实业务正常跑了一段时间，再回来重新看累计数据。

几个小时以后，再次查询：

SELECT inst_id,
       child_number,
       plan_hash_value,
       executions,
       buffer_gets,
       disk_reads,
       rows_processed,
       ROUND(buffer_gets/NULLIF(executions,0)) gets_per_exec,
       ROUND(disk_reads/NULLIF(executions,0)) reads_per_exec
FROM gv$sql
WHERE sql_id='f71tmzgdz68g2'
ORDER BY inst_id,child_number;

INST_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS ROWS_PROCESSED GETS_PER_EXEC READS_PER_EXEC
------- ------------ --------------- ---------- ----------- ---------- -------------- ------------- --------------
      1            0      2091707026         64     2843000     241969       12824243         44422           3781
      2            0      2091707026         65     2886257     245558       13016933         44404           3778

这时候两个 RAC 实例已经累计执行了 129 次，而且全部稳定使用新的 PLAN_HASH_VALUE=2091707026，这比刚建完索引以后只看一次执行有意义得多。

此时我们再把事故当天旧计划的数据拿出来对比。

旧计划 9 月 18 日累计：

EXECUTIONS      277
BUFFER_GETS     7,139,118,098
DISK_READS      6,669,632,022

平均每次大约：

BUFFER_GETS / EXEC ≈ 25,773,892
DISK_READS  / EXEC ≈ 24,078,094

新计划经过两个 RAC 实例 129 次真实业务执行以后，两个实例的数据基本一致：

BUFFER_GETS / EXEC ≈ 44,400
DISK_READS  / EXEC ≈ 3,780

放在一起就很直观了：

                         旧计划                新计划
-------------------- ------------------- ----------------
Plan Hash               3287208169          2091707026
访问方式                TABLE ACCESS FULL    INDEX RANGE SCAN
Buffer Gets / Exec      ≈ 25,773,892        ≈ 44,400
Disk Reads / Exec       ≈ 24,078,094        ≈ 3,780

单次 Buffer Gets 从大约 2577 万降到了 4.44 万，下降约 580 倍；而和这次故障关系最直接的 Disk Reads，则从单次大约 2408 万降到了 3780 左右，下降约 6370 倍。

到这里，我们才可以比较有把握地说：这条 SQL 的 Full Table Scan 已经真正处理掉了，而且不是一次偶然执行，而是在两个 RAC 实例持续跑了 129 次以后仍然稳定使用新计划。

最后还得回到最开始的 +ARCH

SQL 优化到这里已经验证完成，但这次事故最开始并不是因为“SQL 跑得慢”把我叫起来的，而是 +ARCH 这个大约 1TB 的归档磁盘组只剩下了 432MB。

所以最后还有一个问题必须确认：SQL 修完以后，归档增长到底有没有恢复正常？

索引创建期间我一直在另外一个窗口监控 +ARCH：

while true
do
    date
    asmcmd lsdg | grep ARCH
    sleep 60
done

Sat Sep 19 00:12:37 +07 2026
MOUNTED  EXTERN  N  512  512  4096  4194304  1049040  299920  0  299920  0  N  ARCH/

Sat Sep 19 00:13:37 +07 2026
MOUNTED  EXTERN  N  512  512  4096  4194304  1049040  278972  0  278972  0  N  ARCH/

Sat Sep 19 00:14:38 +07 2026
MOUNTED  EXTERN  N  512  512  4096  4194304  1049040  259136  0  259136  0  N  ARCH/

Sat Sep 19 00:15:39 +07 2026
MOUNTED  EXTERN  N  512  512  4096  4194304  1049040  239732  0  239732  0  N  ARCH/

Sat Sep 19 00:16:39 +07 2026
MOUNTED  EXTERN  N  512  512  4096  4194304  1049040  234056  0  234056  0  N  ARCH/

从 00:12 到 00:16，+ARCH 的 Free Space 从 299920MB 降到了 234056MB，短短几分钟少了大约 64GB。

这个时间段正好对应在线创建索引的过程，而最终创建出来的索引本身就有 61.23GB，所以 00 点这个小时的归档量会受到维护操作明显影响，不能拿来代表修复后的正常业务水平。

等统计信息收集、TEMP 扩容、在线建索引这些操作全部结束以后，再从数据库里重新统计每小时归档量。

还是按照 THREAD# + SEQUENCE# 去重，避免 RAC 环境下直接查询 GV$ARCHIVED_LOG 带来的重复统计：

SELECT hour,
       ROUND(SUM(bytes)/1024/1024/1024,2) arch_gb,
       COUNT(*) arch_count
FROM (
    SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') hour,
           thread#,
           sequence#,
           MAX(blocks*block_size) bytes
    FROM gv$archived_log
    WHERE first_time >= DATE '2026-09-19'
      AND dest_id = 1
      AND archived = 'YES'
    GROUP BY TO_CHAR(first_time,'YYYY-MM-DD HH24'),
             thread#,
             sequence#
)
GROUP BY hour
ORDER BY hour;

HOUR             ARCH_GB ARCH_COUNT
------------- ---------- ----------
2026-09-19 00      69.22         91
2026-09-19 01       2.88          4
2026-09-19 02       2.87          4
2026-09-19 03       3.61          5
2026-09-19 04       2.17          3
2026-09-19 05       3.58          5
2026-09-19 06       2.61          4
2026-09-19 07       3.62          5
2026-09-19 08       2.74          4
2026-09-19 09       5.44          8
2026-09-19 10       2.16          3

00 点的 69.22GB 包含了这次维护操作，所以先排除。真正值得看的是凌晨 1 点以后：每小时归档已经基本稳定在 2 ～ 5GB。

再和事故当天相同时间段对比：

时间      09-18 事故当天      09-19 修复后
------   ----------------    --------------
01:00          33.72GB             2.88GB
02:00          33.14GB             2.87GB
03:00          36.16GB             3.61GB
04:00          34.06GB             2.17GB
05:00          33.72GB             3.58GB
06:00          33.97GB             2.61GB
07:00          35.17GB             3.62GB
08:00          42.05GB             2.74GB
09:00          56.82GB             5.44GB
10:00          43.92GB             2.16GB

事故当天同一时间段基本维持在 30 ～ 50GB/h，修复以后已经回到 2 ～ 5GB/h，而且不是只观察了十几分钟，而是连续几个小时都维持在这个水平。

到这里，从 SQL 执行计划到 Physical Reads，再到最开始把数据库搞宕机的归档增长，整个链路才算真正闭环。

回看这次问题

三篇写到这里，这次事故已经完整闭环。最开始看到的只是一个很普通的 Oracle 故障：+ARCH 满了，数据库无法继续归档。如果当时只是清理归档、释放空间，业务确实很快就能恢复，但按照当时每天 450GB 左右的增长速度，问题很可能很快再次出现。

后面真正花时间的，是从异常归档继续追 Redo，再从 Lost Write Detection Redo 追到 Physical Reads，最后锁定一条占全库约 85% Disk Reads 的 SELECT。

继续往下才发现，这条 SQL 每次都在扫描一张 205GB 的表，而 Oracle 手里的统计信息还停留在 8079 行、95 个 block。

解锁统计信息以后，真实数据量变成了 18.5 亿行。后面又经历 TEMP 不足、在线并行建索引失败、扩容和重新创建，最终执行计划从 Full Table Scan 变成 Index Range Scan。

经过两个 RAC 实例 129 次真实业务执行验证，单次 Disk Reads 从约 2408 万降到 3780，归档也从 30 ～ 50GB/h 回到了 2 ～ 5GB/h。

这时候才真正解决了问题。

最后

这次故障里有一个地方我觉得很值得单独记录：DB_LOST_WRITE_PROTECT=AUTO 并不是这次需要“修复”的故障点。

如果排查到 384.51GB redo size for lost write detection 以后，直接把注意力放在“怎么关掉 Lost Write Protection、怎么减少这部分 Redo”上，确实可能很快把表面上的 Redo 压下来，但真正的问题——那条一天制造 66.7 亿次 Physical Reads 的 Full Scan —— 仍然存在。

这次真正异常的是读取。

Lost Write Detection 只是让这些异常的 Buffer Cache Block Reads 在当前 Data Guard 环境下进一步体现成大量 Redo，最后又通过 +ARCH 被打满把问题暴露出来。

所以整个过程中我们没有为了快速降低 Redo 去修改 DB_LOST_WRITE_PROTECT，而是继续顺着：

LWD Redo
→ Block Read Records
→ Physical Reads
→ Top SQL
→ Full Table Scan
→ 缺少索引
→ 统计信息失真

一直找到真正产生异常读取的地方，最后 Full Scan 被处理掉以后，Physical Reads、SQL 执行效率和归档增长一起恢复正常。

这也是这次故障从凌晨 +ARCH 只剩 432MB，一直追到 18.5 亿行大表的完整过程。

排障最怕的不是问题复杂，而是太早相信自己的判断；让数据决定下一步查什么，答案往往就在下一层。

更多数据库命令，见 ORA100 · DBA100：

也可以在微信搜索小程序 「三笠的百令册」。