Oracle-11g和12c中相同的查询但是不同的计划

Question

这个问题与this question有关。这是我尝试在12c中使用的代码

  SELECT * FROM DMProgDate_00001
  WHERE 1=1
  AND ProgressOID IN ( 
    SELECT P.OID FROM (
      SELECT OID FROM (
        SELECT A.OID, ROWNUM as seqNum FROM (
          SELECT OID FROM DMProgress_00001 
            WHERE 1=1
            AND Project = 'Moho'
            AND Phase = 'Procurement'
            AND Displine = 'Q340'
            ORDER BY actCode
          ) A
          WHERE ROWNUM <= 20
      ) WHERE seqNum > 0
    ) P
  );

• 结果 11g :1秒以下 12c :超过8秒

这是11g中的查询计划。

​

这是12c中的查询计划。

​

当我取出所有分页代码时(如下所示)。12c中的查询速度足以达到11g，但需要分页查询。

  SELECT  * FROM DMProgDate_00001
  WHERE 1=1
  AND ProgressOID IN ( 
    SELECT P.OID FROM (
          SELECT OID FROM DMProgress_00001 
            WHERE 1=1
            AND Project = 'Moho'
            AND Phase = 'Procurement'
            AND Displine = 'Q340'
            ORDER BY actCode
    ) P
  );

这是12c中的查询(不分页)计划。

​

我试过了..。关键词(只支持12c )和optimizer_features_enable('11.2.0.4')，但结果与上面相同(超过8秒)。

我们需要同时支持11g和12c，我知道解决这个问题需要绕道(在my pre-question中)，但不希望将其保留为相同的查询代码。是否有任何选项或设置可以解决此问题？

将查询计划添加为文本(它们是不同的表名，但表结构和内容相同)

12c - over 3 sec
Plan hash value: 3742986389

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |     1 |   153 |   204   (0)| 00:00:01 |
|*  1 |  FILTER              |                  |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | DMPROGDATE_00001 |     1 |   153 |   102   (0)| 00:00:01 |
|*  3 |   FILTER             |                  |       |       |            |          |
|*  4 |    COUNT STOPKEY     |                  |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| DMPROGRESS_00001 |    26 |  2288 |   102   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (<not feasible>)
   3 - filter("OID"=:B1)
   4 - filter(ROWNUM<=20)
   5 - filter("PROJECT"='Moho' AND "PHASE"='Procurement' AND "DISPLINE"='Q340')

Note
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement





11g - 0.01 sec

Plan hash value: 833434956
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |    13 |  1157 |    57   (2)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|                  |    13 |  1157 |    57   (2)| 00:00:01 |
|   2 |   VIEW               | VW_NSO_1         |     3 |    81 |    34   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY     |                  |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| DMPROGRESS_00037 |     3 |    99 |    34   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | DMPROGDATE_00037 |  7388 |   447K|    22   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PROGRESSOID"="OID")
   3 - filter(ROWNUM<=20)
   4 - filter("DISPLINE"='Q340' AND "PHASE"='Procurement' AND "PROJECT"='Moho')

Answer 1

所选择的优化器所选择的执行计划即使在相同版本的两个环境中也会有很大差异，而不仅仅是11g和12c。这取决于许多因素，但主要是：

• 表中的行数(envs之间是否相似？)
• 存在哪些索引(两个dbs都有完全相同的索引吗？)
• 表和索引是否有统计数据，以及它们是如何更新的？如果不是最新的，请收集表和索引的统计数据.

如果你能张贴这些细节，我可以提供一个更有帮助的答案。

此外，代码看起来像自动生成的，如果您可以随意修改它们，根据详细信息，我们可以建议重写查询和/或提示。

如果无法修改代码，则可以强制使用SQL计划管理(SPM) -从运行速度更快的数据库导出计划，并将其导入另一个db。

更新

使用此简化版本的SQL生成计划：

SELECT * FROM DMProgDate_00001
 WHERE ProgressOID IN ( 
   SELECT OID FROM DMProgress_00001 
    WHERE Project = 'Moho'
      AND Phase = 'Procurement'
      AND Displine = 'Q340'
      AND ROWNUM <= 20
    ORDER BY actCode
  );