Oracle SQL 优化实战：明明走了索引，SQL 还是慢？

举报
Lucifer三思而后行 发表于 2026/09/17 14:27:25 2026/09/17
【摘要】 “100 条命令”系列已经写了 38 篇，接下来继续写 RAC、Data Guard、GoldenGate 这些专题。想接着看，可以收藏 ORA100 · DBA100，微信里搜索小程序 「三笠的百令册」。网站地址： 前言早上业务那边发了条 SQL 过来，说执行已经走索引但还是比较慢，环境是 Oracle 19c RAC PDB，我看了一下就是一个简单的查询语句，问题本身并不复杂，应用场景也...

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

网站地址：

前言

早上业务那边发了条 SQL 过来，说执行已经走索引但还是比较慢，环境是 Oracle 19c RAC PDB，我看了一下就是一个简单的查询语句，问题本身并不复杂，应用场景也比较简单：根据两个工单号和一批产品批次号查询生产过程数据。

但排查过程中我发现其实 这个 SQL 不是缺索引，而是没有走对索引， 最终问题出在统计信息长期被锁。表从约 148 万行增长到 6742 万行后，优化器仍然使用近一年前的统计信息进行成本估算，最终选错了索引。

这里我分享一下我的整个分析过程以及解决方案。

1. 问题 SQL

SQL 脱敏后如下：

SELECT ID,
       WORKORDER_NO,
       PRODUCT_BATCH_NO,
       PRODUCT_BATCH_ID,
       BATCH_TRACE_ID,
       CONTAINER_BARCODE,
       PRODUCT_CODE,
       PRODUCT_NAME,
       PROCESS_CODE,
       PROCESS_NAME,
       EQUIPMENT_CODE,
       EQUIPMENT_NAME,
       FIRST_MOVEIN_TIME,
       FIRST_MOVEOUT_TIME,
       CREATE_TIME,
       UPDATE_TIME,
       ...
FROM PE_BATCH_PROCESS
WHERE WORKORDER_NO IN ('WO_A','WO_B')
  AND PRODUCT_BATCH_NO IN (
      'BATCH_001',
      'BATCH_002',
      'BATCH_003',
      ...
      'BATCH_050'
  )
  AND PROCESS_CODE = 'PROC_X';

初步看一遍发现，其实 SQL 的过滤条件还是比较清晰的：

WORKORDER_NO      IN 2 个值
PRODUCT_BATCH_NO  IN 50 个值
PROCESS_CODE      = 1 个值

2. 定位 SQL ID

由于业务提供的是 SQL 文本，为了方便我们去分析，最好能找到生产环境中这条 SQL 对应的 SQL_ID：

-- RAC 环境，直接从目标 PDB 查询 GV$SQL
SELECT inst_id,
       sql_id,
       plan_hash_value,
       executions,
       ROUND(elapsed_time / 1000000, 2) elapsed_sec,
       buffer_gets,
       disk_reads,
       rows_processed,
       TO_CHAR(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active_time,
       sql_text
FROM gv$sql
WHERE sql_text LIKE '%BATCH_001%'
ORDER BY last_active_time DESC;

在节点 2 找到两条符合的 SQL：

SQL Executions Elapsed Buffer Gets Disk Reads Rows
SQL_ID_A 2 4.69s 1,599,216 0 100
SQL_ID_B 2 57.72s 1,694,842 326,214 60

这里其实有两个问题：

  1. 为什么看起来完全一样的业务 SQL 有两个 SQL_ID？
  2. 为什么其中一条累计只需要 4.69 秒，另一条却需要 57.72 秒？

后面逐一分析。

3. 分析执行计划

查看两个 SQL ID 对应的 SQL：

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 32767

SELECT inst_id,
       sql_id,
       sql_fulltext
FROM gv$sql
WHERE sql_id IN ('SQL_ID_A','SQL_ID_B')
  AND inst_id = 2;

两条 SQL 的 SQL_FULLTEXT 肉眼看基本一致，但 SQL_ID 不同，说明文本层面仍存在差异，可能是空格、换行或其他不可见字符。这个问题暂时不是重点，因为两条 SQL 的执行计划完全一致。

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        'SQL_ID_A',
        NULL,
        'ALLSTATS LAST +PREDICATE'
    )
);

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        'SQL_ID_B',
        NULL,
        'ALLSTATS LAST +PREDICATE'
    )
);

两条 SQL 的 Plan Hash 都是 2464540259，进一步通过 DBMS_XPLAN.DISPLAY_CURSOR 检查，两条 SQL 的执行计划完全相同：

Plan hash value: 2464540259

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

Predicate 是问题的关键：

3 - access(
      WORKORDER_NO = 'WO_A'
      OR
      WORKORDER_NO = 'WO_B'
    )

2 - filter(
      PROCESS_CODE = 'PROC_X'
      AND PRODUCT_BATCH_NO IN (...)
    )

也就是说 Oracle 实际执行的是：

SQL 虽然“走了索引”，但索引的选择性其实并不好。

其次，我们可以发现这两个 SQL 的逻辑读非常接近：

SQL_ID_A
Buffer Gets = 1,599,216

SQL_ID_B
Buffer Gets = 1,694,842

而且平均每次执行都接近 80 万 Buffer Gets / execution，但是物理读差异很大：

SQL_ID_A
Disk Reads = 0

SQL_ID_B
Disk Reads = 326,214

从现有统计看，这与缓存状态差异高度吻合，但仅凭 V$SQL 的累计值还不足以证明两次执行之间存在严格的“前一次预热、后一次命中”关系。

所以，我们也不能简单把问题归结为“磁盘慢”，真正需要解决的是：为什么查询几十行数据，需要约 80 万次逻辑读？

4. 检查索引

接下来，检查一下查询表上的索引：

SELECT index_name,
       column_position,
       column_name
FROM dba_ind_columns
WHERE table_owner = 'APP_SCHEMA'
  AND table_name = 'PE_BATCH_PROCESS'
ORDER BY index_name, column_position;

这张表上的索引比较多，我筛了一下，跟这条 SQL 关系最大的三个索引如下：

  1. IDX_WORKORDER_REPORT (WORKORDER_NO,REPORT_NO)
  2. IDX_WORKORDER_PROCESS (WORKORDER_NO,PROCESS_CODE)
  3. IDX_BATCH_PROCESS (PRODUCT_BATCH_NO,PROCESS_CODE)

这条 SQL 当前执行计划选择的是：

IDX_WORKORDER_REPORT (WORKORDER_NO, REPORT_NO)

但实际上 SQL 中根本没有 REPORT_NO，因此这个索引实际只利用了 WORKORDER_NO，而数据库中其实已经存在合适的索引：

IDX_BATCH_PROCESS (PRODUCT_BATCH_NO, PROCESS_CODE)

为什么 SQL 执行没有采用这个索引呢？

5. 验证索引

为了验证索引的可行性，我们可以给 SQL 增加 Hint 进行测试：

/*+ INDEX(PE_BATCH_PROCESS IDX_BATCH_PROCESS)
           GATHER_PLAN_STATISTICS */

在 sqlplus 执行：

SELECT /*+ INDEX(PE_BATCH_PROCESS IDX_BATCH_PROCESS)
           GATHER_PLAN_STATISTICS */
       ...
FROM PE_BATCH_PROCESS
WHERE WORKORDER_NO IN ('WO_A','WO_B')
  AND PRODUCT_BATCH_NO IN (...)
  AND PROCESS_CODE = 'PROC_X';

执行后查询这条 SQL 对应的 SQL ID：

SELECT sql_id,
       executions,
       buffer_gets,
       disk_reads,
       rows_processed,
       ROUND(elapsed_time/1e6,3) elapsed_sec
FROM v$sql
WHERE sql_text LIKE '%GATHER_PLAN_STATISTICS%'
  AND sql_text LIKE '%PE_BATCH_PROCESS%'
  AND sql_text NOT LIKE '%v$sql%'
ORDER BY last_active_time DESC;

然后根据新的 SQL ID 查看执行计划：

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    '<新SQL_ID>',
    NULL,
    'ALLSTATS LAST +PREDICATE'
  )
);

可以看到新的 Plan Hash 3904258984 的执行路径变成：

实际执行统计：

-----------------------------------------------------------------------
Operation                              Starts  E-Rows A-Rows Buffers
-----------------------------------------------------------------------
SELECT STATEMENT                            1             50      198
INLIST ITERATOR                             1             50      198
TABLE ACCESS BY INDEX ROWID BATCHED        50       1     50      198
INDEX RANGE SCAN                           50       1     50      159
-----------------------------------------------------------------------

同时我们从 V$SQL 中看到：

Executions      1
Buffer Gets     216
Disk Reads      0
Rows Processed  50
Elapsed         0.005s

前后差距已经非常明显：

而且这次测试没有新增任何索引，数据库里本来就有合适的索引，只是 CBO 没有选择它。

6. 为什么 CBO 会放着更合适的索引不用？

这里虽然我们找到了合适的索引，但是还是要搞清楚为什么 CBO 选择了错误的索引。

检查表的统计信息：

SELECT owner,
       table_name,
       num_rows,
       blocks,
       stale_stats,
       last_analyzed
FROM dba_tab_statistics
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'PE_BATCH_PROCESS'
  AND partition_name IS NULL;

NUM_ROWS      = 1,480,408
BLOCKS        = 80,896
LAST_ANALYZED = 2025-10-14
STALE_STATS   = YES

再看三个条件列的统计：

SELECT column_name,
       num_distinct,
       density,
       num_nulls,
       histogram,
       last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'PE_BATCH_PROCESS'
  AND column_name IN (
       'WORKORDER_NO',
       'PRODUCT_BATCH_NO',
       'PROCESS_CODE'
  );

WORKORDER_NO
NUM_DISTINCT = 33

PRODUCT_BATCH_NO
NUM_DISTINCT = 182,368

再检查三个相关索引统计信息：

SET LINES 250
COL INDEX_NAME FOR A70

SELECT index_name,
       status,
       visibility,
       num_rows,
       distinct_keys,
       leaf_blocks,
       clustering_factor,
       last_analyzed
FROM dba_indexes
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'PE_BATCH_PROCESS'
  AND index_name IN (
       'IDX_WORKORDER_REPORT',
       'IDX_WORKORDER_PROCESS',
       'IDX_BATCH_PROCESS'
  );

索引统计同样停留在 2025 年，更值得让人注意的是 DBA_TAB_MODIFICATIONS：

SELECT table_owner,
       table_name,
       inserts,
       updates,
       deletes,
       timestamp
FROM dba_tab_modifications
WHERE table_owner = 'APP_SCHEMA'
  AND table_name = 'PE_BATCH_PROCESS';

INSERTS = 67,420,911
UPDATES = 70,665,878
DELETES = 982

这里的 modification 数量不能直接理解为当前表有这么多新增行，因为同一行可能经历多次 DML。

但它已经足以证明：自上一次统计信息采集以后，这张表发生了巨量变化。

跟业务那边协商之后，找了一个时间准备重新收集统计信息：

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

但是执行的时候报错了，这个其实我是真没想到：ORA-20005: object statistics are locked (stattype = ALL)，这个报错很明确，对象的统计信息被锁了。

检查这个对象的统计信息状态：

SELECT owner,
       table_name,
       stattype_locked,
       stale_stats,
       num_rows,
       last_analyzed
FROM dba_tab_statistics
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'PE_BATCH_PROCESS'
  AND partition_name IS NULL;

STATTYPE_LOCKED = ALL
STALE_STATS     = YES
NUM_ROWS        = 1,480,408
LAST_ANALYZED   = 2025-10-14

怀疑不止这一个表被锁，继续检查整个 Schema，发现整个业务 Schema 有大量表被锁定：

SELECT owner,
       table_name,
       stattype_locked,
       stale_stats,
       num_rows,
       last_analyzed
FROM dba_tab_statistics
WHERE owner = 'APP_SCHEMA'
  AND stattype_locked IS NOT NULL
  AND partition_name IS NULL
ORDER BY table_name;

STATTYPE_LOCKED = ALL

其中不少表已经：STALE_STATS = YES，但是这里我们不能直接执行：

DBMS_STATS.UNLOCK_SCHEMA_STATS(...)

因为一次性解锁整个生产 Schema 并更新大量统计信息，可能导致大量 SQL 同时发生执行计划变化，本次只针对问题表处理。

7. 先备份旧统计信息

生产环境修改统计信息前，先保留回退能力：

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE(
    ownname => 'APP_SCHEMA',
    stattab => 'PE_BATCH_PROCESS_STATS_BAK'
  );
END;
/

导出：

BEGIN
  DBMS_STATS.EXPORT_TABLE_STATS(
    ownname => 'APP_SCHEMA',
    tabname => 'PE_BATCH_PROCESS',
    stattab => 'PE_BATCH_PROCESS_STATS_BAK',
    statid  => 'BEFORE_OPT',
    cascade => TRUE
  );
END;
/

然后只解锁这一张表：

BEGIN
  DBMS_STATS.UNLOCK_TABLE_STATS(
    ownname => 'APP_SCHEMA',
    tabname => 'PE_BATCH_PROCESS'
  );
END;
/

重新收集：

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

完成统计信息收集后重新检查：

SELECT owner,
       table_name,
       stale_stats,
       num_rows,
       blocks,
       last_analyzed
FROM dba_tab_statistics
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'PE_BATCH_PROCESS'
  AND partition_name IS NULL;

NUM_ROWS      = 67,418,965
BLOCKS        = 3,548,934
STALE_STATS   = NO
LAST_ANALYZED = 当前日期

重新收集后，NUM_ROWS 从原来的 148 万变成了 6742 万，差了 45 倍。统计信息都已经偏差到这个程度了，前面的执行计划为什么会选成这样，基本也就能解释了。

关键列统计也发生巨大变化：

SELECT column_name,
       num_distinct,
       density,
       num_nulls,
       histogram,
       last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'APP_SCHEMA'
  AND table_name = 'PE_BATCH_PROCESS'
  AND column_name IN (
       'WORKORDER_NO',
       'PRODUCT_BATCH_NO',
       'PROCESS_CODE'
  );

WORKORDER_NO
NUM_DISTINCT = 101
HISTOGRAM    = FREQUENCY

PRODUCT_BATCH_NO
NUM_DISTINCT = 5,618,688
HISTOGRAM    = HYBRID

PROCESS_CODE
NUM_DISTINCT = 40
HISTOGRAM    = FREQUENCY

特别是：PRODUCT_BATCH_NO NDV ≈ 562 万，其选择性显然远高于 WORKORDER_NO，这也进一步解释了为什么：

(PRODUCT_BATCH_NO, PROCESS_CODE)

才是这条 SQL 更合理的入口。

8. 最后验证

其实到这里，问题已经基本解决了，还差最后一步，也是最关键的一步：验证一下真实的 SQL 执行效率。

更新统计信息以后，CBO 自己会不会选对？

重新执行 SQL，这次完全不指定索引，只保留：

/*+ GATHER_PLAN_STATISTICS */

执行后：

Plan hash value: 3904258984

Oracle 自动选择：

INDEX RANGE SCAN
IDX_BATCH_PROCESS
(PRODUCT_BATCH_NO, PROCESS_CODE)

实际行源：

-----------------------------------------------------------------------
Operation                              Starts E-Rows A-Rows Buffers
-----------------------------------------------------------------------
SELECT STATEMENT                            1            50      198
INLIST ITERATOR                             1            50      198
TABLE ACCESS BY INDEX ROWID BATCHED        50      2     50      198
INDEX RANGE SCAN                           50     15     50      159
-----------------------------------------------------------------------

Predicate：

INDEX RANGE SCAN

access:
    PRODUCT_BATCH_NO IN (...)
    AND PROCESS_CODE = 'PROC_X'

TABLE ACCESS

filter:
    WORKORDER_NO IN ('WO_A','WO_B')

最终可以看到：

返回行数：50
Buffers：198
执行时间：约 0.01 秒

至此可以确认：不需要 Hint，不需要新增索引，也不需要修改业务 SQL。 只需要重新获得正确统计信息以后，CBO 自己选择了正确访问路径。

至于为什么整个 Schema 的统计信息会被锁，目前还没有确认。初步怀疑跟之前的迁移或升级有关，这部分还需要跟业务负责人和厂商确认，所以这里先不下结论。

总结

最后用两张图来做一个总结：

处理以后：

所以很多时候优化并不复杂，只需要找到原因，对症下药即可。

这次没有新建索引，也没有修改业务 SQL。真正的处理动作，只是让 CBO 重新看到了这张表现在真实的数据规模和分布。

所以后面再碰到“明明走了索引，但 SQL 还是慢”的情况，我们可以先看三个东西：

  • 访问谓词到底用了哪些列
  • 实际逻辑读有多大
  • 统计信息是否还能代表当前数据

索引走了，不代表走对了，优化器做出的选择，也只能建立在它当时掌握的信息之上。

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

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

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
Oracle SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入