optimization - oracle - same query but different plan in 11g and 12c -

this question relative this question. code try use in 12c

  select * dmprogdate_00001   1=1   , progressoid in (      select p.oid (       select oid (         select a.oid, rownum seqnum (           select oid dmprogress_00001              1=1             , project = 'moho'             , phase = 'procurement'             , displine = 'q340'             order actcode           )           rownum <= 20       ) seqnum > 0     ) p   ); 

• result

  11g : under 1 sec

  12c : on 8 sec

this query plan in 11g

this query plan in 12c

when take out pagination code (like below). query in 12c fast enough 11g need pagination query.

  select  * dmprogdate_00001   1=1   , progressoid in (      select p.oid (           select oid dmprogress_00001              1=1             , project = 'moho'             , phase = 'procurement'             , displine = 'q340'             order actcode     ) p   ); 

this query (without pagination) plan in 12c

i tried offset .. key word ( 12c support) , optimizer_features_enable('11.2.0.4') same result above ( on 8 sec).

we need support both 11g , 12c , know detour fix problem ( in my pre-question) don't want keep same query code. there option or setting can fix qeustion?

---

added query plan text (they different table name same table structure , contents)

12c - on 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 operation id): ---------------------------------------------------    1 - filter( exists (<not feasible>)    3 - filter("oid"=:b1)    4 - filter(rownum<=20)    5 - filter("project"='moho' , "phase"='procurement' , "displine"='q340')  note    - dynamic statistics used: dynamic sampling (level=2)    - 1 sql plan directive used 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 operation id): ---------------------------------------------------     1 - access("progressoid"="oid")    3 - filter(rownum<=20)    4 - filter("displine"='q340' , "phase"='procurement' , "project"='moho') 

the execution plan chosen optimizer chosen can vary between 2 environments of same version, not 11g , 12c. depends on many factors, mostly:

• number of rows in table (are similar between envs?)
• what indexes present (do both dbs have exact same indexes?)
• are there stats table , indexes, , how date they? if not up-to-date, please gather stats tables and the indexes.

if can post these details can provide more helpful answer.

also, code looks auto generated, if @ liberty modify them, based on details, can suggest rewritten query and/or hints.

if cannot modify code, can force plan sql plan management (spm) - export db runs faster , import other db.

update

use simplified version of sql generate plan:

select * dmprogdate_00001  progressoid in (     select oid dmprogress_00001      project = 'moho'       , phase = 'procurement'       , displine = 'q340'       , rownum <= 20     order actcode   );