同一查询同执行计划在不同Oracle环境中性能差异分析

我有两个数据量一致的Oracle环境（transactions表约400万行），执行同一查询时执行计划完全一致，但性能差距极大：一个耗时约500ms，另一个约30秒。

查询语句 #

SELECT t.*
FROM 
    (SELECT t.id, t.transaction_date
     FROM transactions t 
     ORDER BY t.transaction_date DESC, t.id DESC
     FETCH NEXT 11 ROWS ONLY) transactions_table 
JOIN 
    transactions t ON transactions_table.id = t.id
ORDER BY 
    t.transaction_date DESC, t.id DESC;

索引信息 #

ID是该表的主键，已创建索引：

CREATE INDEX transaction_date_idx ON transactions (transaction_date DESC, id);

执行计划 #

PLAN_TABLE_OUTPUT
Plan hash value: 3772986339
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |    11 |   167K|       | 35981   (2)| 00:00:02 |
|   1 |  SORT ORDER BY                |                                   |    11 |   167K|       | 35981   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                |                                   |    11 |   167K|       | 35980   (2)| 00:00:02 |
|   3 |    NESTED LOOPS               |                                   |    11 |   167K|       | 35980   (2)| 00:00:02 |
|*  4 |     VIEW                      |                                   |    11 |   286 |       | 35958   (2)| 00:00:02 |
|*  5 |      WINDOW SORT PUSHED RANK  |                                   |  4345K|   107M|   150M| 35958   (2)| 00:00:02 |
|   6 |       INDEX FAST FULL SCAN    | TRANSACTIONS_TRANSACTION_DATE_IDX |  4345K|   107M|       |  3593   (2)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | PK_TRANSACTIONS                   |     1 |       |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| TRANSACTIONS                      |     1 | 15582 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   4 - filter(""from$_subquery$_003"".""rowlimit_$$_rownumber""<=11)"
"   5 - filter(ROW_NUMBER() OVER ( ORDER BY SYS_OP_DESCEND(""TRANSACTION_DATE""),INTERNAL_FUNCTION(""T"".""ID"") DESC "
              )<=11)
"   7 - access(""T2"".""ID""=""from$_subquery$_003"".""ID"")"
 
Note
----- 
   - dynamic statistics used: dynamic sampling (level=2)

两个环境的执行计划唯一差异：快速返回的环境没有上述Note中的动态采样提示。单独运行子查询：

SELECT t.id, t.transaction_date
FROM transactions t 
ORDER BY t.transaction_date DESC, t.id DESC
FETCH NEXT 11 ROWS ONLY

在两个环境中性能几乎一致（均较快），推测性能瓶颈出在JOIN环节，请问原因是什么？

1. 动态采样引发的隐性执行逻辑差异 #

虽然执行计划文本一致，但慢环境启用了动态采样（level=2），这会导致Oracle在执行时的隐性逻辑差异：

• 动态采样会在运行时临时收集表/索引的统计数据，若采样过程效率低，或采样结果让Oracle对JOIN的行数预估出现偏差（即使计划结构不变），会直接影响NESTED LOOPS的执行效率。
• 快环境无动态采样，说明它依赖的是已收集的准确统计信息，Oracle能精准预判JOIN仅需处理11行，因此NESTED LOOPS的执行是高效的。

2. JOIN环节的实际执行开销差异 #

从计划看，JOIN采用NESTED LOOPS，依赖子查询返回的11个ID关联主表：

• 快环境中，Oracle明确知晓子查询仅返回11行，因此直接用这11个ID做索引唯一扫描+表ROWID访问，全程无额外开销。
• 慢环境中，动态采样可能导致Oracle对JOIN行数的预估波动，或执行时触发不必要的统计校验，让NESTED LOOPS的每一轮迭代都产生额外耗时；甚至因统计信息不准确，Oracle没有真正按“仅处理11行”的逻辑优化，做了多余操作。

3. 子查询与完整查询的性能差异根源 #

单独运行子查询时，逻辑简单（仅索引扫描+排序取前11行），动态采样的开销可忽略；但加入JOIN后，JOIN逻辑对行数预估的敏感度远高于简单排序取数，动态采样的影响被放大，最终导致整体性能骤降。

解决建议 #

• 对慢环境的transactions表重新收集统计信息，确保统计数据准确，避免Oracle触发动态采样：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'TRANSACTIONS', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
  

• 检查两个环境的Oracle参数差异，尤其是与统计信息、动态采样相关的参数（如OPTIMIZER_DYNAMIC_SAMPLING），保持参数配置一致。
• 优化查询逻辑，去掉冗余JOIN：直接在子查询中返回t.*，无需额外关联主表，优化后查询如下：

  SELECT t.*
  FROM transactions t 
  ORDER BY t.transaction_date DESC, t.id DESC
  FETCH NEXT 11 ROWS ONLY;
  

内容的提问来源于stack exchange，提问作者Hasan Can Saral