请求SQL调优帮助：优化生产查询并解决改写后性能下降问题

看起来你遇到了一个典型的物化子查询后性能退化的问题——明明逻辑完全一致，把嵌套子查询转成临时表后作业反而变慢了。结合Oracle的调优经验，我整理了几个核心原因和对应的解决办法：

1. 临时表统计信息缺失是首要嫌疑 #

Oracle的临时表默认不会自动收集统计信息，优化器没有办法准确判断临时表的数据量、字段分布情况，只能依赖默认的基数估计，这会直接导致执行计划走偏（比如选错连接方式、扫描顺序）。

解决办法：
在创建临时表并插入数据后，手动收集统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => '你的用户名',
    tabname => 'TMP_TMP2',
    cascade => TRUE, -- 同时收集关联索引的统计信息
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => '你的用户名',
    tabname => 'TMP_TMP1_B',
    cascade => TRUE,
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

2. 并行度设置没有继承到临时表 #

原查询的子查询里加了PARALLEL(A,10)的提示，但是临时表本身可能没有设置并行属性，查询临时表时也没加并行提示，导致原本的并行扫描变成单线程执行，速度自然大幅下降。

解决办法：

• 方式一：创建临时表时直接指定并行度：

CREATE GLOBAL TEMPORARY TABLE TMP_TMP2 (
    -- 你的字段定义
) PARALLEL 10 TABLESPACE TEMP;

• 方式二：在查询临时表时补充并行提示：

SELECT /*+ PARALLEL(A,10) PARALLEL(B,10) */ ...
FROM TMP_TMP2 A, TMP_TMP1_B B 
WHERE A.DOCUMENT_NUMBER = B.DOCUMENT_NUMBER(+);

3. 原查询的子查询折叠优化被破坏 #

Oracle优化器具备子查询折叠能力，原查询的嵌套子查询可能被它自动合并到主查询中，生成更高效的执行计划（比如把多次全表扫描合并成一次，或者调整连接顺序减少IO）。但你把子查询物化到临时表后，优化器没办法再做这种合并，只能先扫描临时表再做连接，额外增加了IO和内存开销。

解决办法：
对比原查询和临时表版本的执行计划，找出差异点：

-- 生成原查询的执行计划
EXPLAIN PLAN FOR 
-- 替换为原查询的完整语句
SELECT ... FROM (SELECT /*+ FULL(A) FULL(B) PARALLEL(A,10) PARALLEL(B,10) */ ...) A, (...) B WHERE ...;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

-- 生成临时表版本的执行计划
EXPLAIN PLAN FOR 
SELECT ... FROM TMP_TMP2 A, TMP_TMP1_B B WHERE A.DOCUMENT_NUMBER = B.DOCUMENT_NUMBER(+);

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

重点关注连接方式（哈希连接/嵌套循环/合并连接）、扫描行数、并行度的差异，针对性调整执行计划。

4. 用结果缓存替代临时表，平衡性能与内存占用 #

如果你想要低内存占用的缓存效果，临时表未必是最优选择——Oracle的RESULT_CACHE提示可以把子查询的结果缓存到SGA中，内存占用可控，且重复查询时直接从缓存读取，比物化临时表更高效。

尝试方案：
给原查询的子查询加上RESULT_CACHE提示，示例如下：

FROM ( SELECT /*+ RESULT_CACHE FULL(A) FULL(B) PARALLEL(A,10) PARALLEL(B,10) */ 
               A.*, B.CCNA, B.CCNA_NAME 
       FROM TMP2 A, CNA B 
       WHERE A.DOCUMENT_NUMBER = B.DOCUMENT_NUMBER(+) ) A, 
     (SELECT /*+ RESULT_CACHE FULL(A) FULL(B) PARALLEL(A,10) PARALLEL(B,10) */ 
               A.*, B.STATE 
      FROM ( SELECT /*+ RESULT_CACHE FULL(A) FULL(B) PARALLEL(A,10) PARALLEL(B,10) */ 
                     A.*, B.USOC 
             FROM TMP1 A, COSS B 
             WHERE A.SERV_ITEM_ID = B.SERV_ITEM_ID(+) ) A, 
           STATE B 
      WHERE A.SERV_ITEM_ID = B.SERV_ITEM_ID(+)) B 
WHERE A.DOCUMENT_NUMBER = B.DOCUMENT_NUMBER(+)

这种方式不需要手动维护临时表，缓存由Oracle自动管理，内存占用可以通过RESULT_CACHE_MAX_SIZE参数调整，更符合你“高效运行且缓存内存占用低”的需求。

内容的提问来源于stack exchange，提问作者JCBA