1 2 3 Previous Next 34 Replies Latest reply: Sep 21, 2009 5:42 AM by Centinul Go to original post RSS
      • 15. Re: Optimizer choosing different plans when ROWNUM filter is applied
        Tubby
        Centinul wrote:
        Looking at the trace for the non-hinted plan it seems the optimizer is making incorrect estimates at multiple steps once the K Mode operations have started. For example, it's predicting a cardinality of 1 for the PRODUCT table, but in reality it should be on the order of about 77. However there is a frequency histogram on this column. The value in the predicate does exist in the histogram. The CBO Trace says it's using 1 for the density and by my calculations the density*num_rows should give a cardinality of 78.
        I think (though it's possible i'm mistaken) you will find this is an inherited value from the ROWNUM predicate. Here's a simple example to demonstrate.
        ME_XE?create table some_table
          2  (
          3     col1     number primary key
          4  );
        
        Table created.
        
        Elapsed: 00:00:00.01
        ME_XE?
        ME_XE?insert into some_table
          2  select level from dual connect by level <= 10000;
        
        10000 rows created.
        
        Elapsed: 00:00:00.07
        ME_XE?
        ME_XE?exec dbms_stats.gather_table_stats(USER, 'SOME_TABLE', cascade => TRUE);
        
        PL/SQL procedure successfully completed.
        
        Elapsed: 00:00:00.03
        ME_XE?
        ME_XE?variable a number;
        ME_XE?variable b number;
        ME_XE?
        --Standard .0025 selectivity*
        ME_XE?explain plan for
          2  select col1
          3  from
          4  (
          5     select *
          6     from some_table
          7     where col1 between :A and :B
          8     order by col1 asc
          9  );
        
        Explained.
        
        Elapsed: 00:00:00.00
        ME_XE?select * from table(dbms_xplan.display(NULL, NULL, 'ALL'));
        
        PLAN_TABLE_OUTPUT
        --------------------------------------------------------------------------------------
        Plan hash value: 3038920053
        
        ----------------------------------------------------------------------------------
        | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
        ----------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT  |              |    25 |    75 |     2   (0)| 00:00:01 |
        |*  1 |  FILTER           |              |       |       |            |          |
        |*  2 |   INDEX RANGE SCAN| SYS_C0026367 |    25 |    75 |     2   (0)| 00:00:01 |
        ----------------------------------------------------------------------------------
        
        Query Block Name / Object Alias (identified by operation id):
        -------------------------------------------------------------
        
           1 - SEL$F5BB74E1
           2 - SEL$F5BB74E1 / SOME_TABLE@SEL$2
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           1 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
           2 - access("COL1">=TO_NUMBER(:A) AND "COL1"<=TO_NUMBER(:B))
               filter("COL1">=TO_NUMBER(:A) AND "COL1"<=TO_NUMBER(:B))
        
        Column Projection Information (identified by operation id):
        -----------------------------------------------------------
        
           1 - "COL1"[NUMBER,22]
           2 - "COL1"[NUMBER,22]
        
        28 rows selected.
        
        Elapsed: 00:00:00.07
        ME_XE?
        --selectivity based on the first_rows_N hint the optimizer will add in based on the ROWNUM predicate*
        ME_XE?explain plan for
          2  select col1
          3  from
          4  (
          5     select *
          6     from some_table
          7     where col1 between :A and :B
          8     order by col1 asc
          9  )
         10  where rownum <= 7;
        
        Explained.
        
        Elapsed: 00:00:00.01
        ME_XE?
        ME_XE?select * from table(dbms_xplan.display(NULL, NULL, 'ALL'));
        
        PLAN_TABLE_OUTPUT
        -------------------------------------------------------------------------------------------------------
        Plan hash value: 705848945
        
        ------------------------------------------------------------------------------------
        | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT    |              |     7 |    91 |     2   (0)| 00:00:01 |
        |*  1 |  COUNT STOPKEY      |              |       |       |            |          |
        |   2 |   VIEW              |              |     8 |   104 |     2   (0)| 00:00:01 |
        |*  3 |    FILTER           |              |       |       |            |          |
        |*  4 |     INDEX RANGE SCAN| SYS_C0026364 |     8 |    24 |     2   (0)| 00:00:01 |
        ------------------------------------------------------------------------------------
        
        Query Block Name / Object Alias (identified by operation id):
        -------------------------------------------------------------
        
           1 - SEL$1
           2 - SEL$2 / from$_subquery$_001@SEL$1
           3 - SEL$2
           4 - SEL$2 / SOME_TABLE@SEL$2
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           1 - filter(ROWNUM<=7)
           3 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
           4 - access("COL1">=TO_NUMBER(:A) AND "COL1"<=TO_NUMBER(:B))
        
        Column Projection Information (identified by operation id):
        -----------------------------------------------------------
        
           1 - "COL1"[NUMBER,22]
           2 - "COL1"[NUMBER,22]
           3 - "COL1"[NUMBER,22]
           4 - "COL1"[NUMBER,22]
        
        34 rows selected.
        
        Elapsed: 00:00:00.07
        ME_XE?
        --selectivity based on the first_rows_N hint the optimizer will add in based on the ROWNUM predicate*
        ME_XE?explain plan for
          2  select col1
          3  from
          4  (
          5     select *
          6     from some_table
          7     where col1 between :A and :B
          8     order by col1 asc
          9  )
         10  where rownum <= 2;
        
        Explained.
        
        Elapsed: 00:00:00.01
        ME_XE?
        ME_XE?select * from table(dbms_xplan.display(NULL, NULL, 'ALL'));
        
        PLAN_TABLE_OUTPUT
        -------------------------------------------------------------------------------------------------------
        Plan hash value: 705848945
        
        ------------------------------------------------------------------------------------
        | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT    |              |     2 |    26 |     2   (0)| 00:00:01 |
        |*  1 |  COUNT STOPKEY      |              |       |       |            |          |
        |   2 |   VIEW              |              |     3 |    39 |     2   (0)| 00:00:01 |
        |*  3 |    FILTER           |              |       |       |            |          |
        |*  4 |     INDEX RANGE SCAN| SYS_C0026364 |     3 |     9 |     2   (0)| 00:00:01 |
        ------------------------------------------------------------------------------------
        
        Query Block Name / Object Alias (identified by operation id):
        -------------------------------------------------------------
        
           1 - SEL$1
           2 - SEL$2 / from$_subquery$_001@SEL$1
           3 - SEL$2
           4 - SEL$2 / SOME_TABLE@SEL$2
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           1 - filter(ROWNUM<=2)
           3 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
           4 - access("COL1">=TO_NUMBER(:A) AND "COL1"<=TO_NUMBER(:B))
        
        Column Projection Information (identified by operation id):
        -----------------------------------------------------------
        
           1 - "COL1"[NUMBER,22]
           2 - "COL1"[NUMBER,22]
           3 - "COL1"[NUMBER,22]
           4 - "COL1"[NUMBER,22]
        
        34 rows selected.
        
        Elapsed: 00:00:00.07
        ME_XE?
        • 16. Re: Optimizer choosing different plans when ROWNUM filter is applied
          Centinul
          Timur Akhmadeev wrote:
          Interesting. From what is reported in CBO trace it seems like CBO tries to cost an access to product table as if Oracle won't require too many rows from that table. I've tried to reproduce your scenario in 11.1.0.7 with optimizer_features_enable='10.2.0.4' (and my intention was to test this assumption by using different first_rows_n hints), but no luck. What happens with cardinality estimates when you use FIRST_ROWS_N hints with different N?
          In my testing I found that FIRST_ROWS_N hints <=68 generated the NL version of the plan, a.k.a. the slow plan. Anything greater than 68 produced the hash join plan.
          Can you build a reproducible test case on your database?
          I will see if I can produce a generic test case today...
          • 17. Re: Optimizer choosing different plans when ROWNUM filter is applied
            Centinul
            Tubby wrote:
            I think (though it's possible i'm mistaken) you will find this is an inherited value from the ROWNUM predicate. Here's a simple example to demonstrate.
            Based on your example I can completely understand why the optimizer would take that route. I say that because there is no aggregation done between the inner and outer queries so there is a 1:1 relationship between the number of rows in the subquery compared to the ROWNUM restriction in the outer query.

            However in my case, I have aggregates in the subquery so that 1:1 relationship does not apply any longer. It appears that the CBO is blindly assuming a 1:1 relationship in all cases forcing a FIRST_ROWS optimization when that is not the most efficient execution path.

            I currently have a work-around (I re-wrote the query to use ROW_NUMBER() instead), but I would like to get to the route of the issues, of which their is two:

            1. Is this a bug, and should I report it to Oracle
            2. How do I get the optimizer to correctly estimate the cardinality of the INDEX RANGE SCAN step? Currently it is using a 5% * 5% estimate which gets an estimate that is 2 order of magnitude off. It looks like I may have to fudge statistics, but the problem with that is that this table is increasing in size every single day, and is not stable.
            • 18. Re: Optimizer choosing different plans when ROWNUM filter is applied
              Timur Akhmadeev
              1. Is this a bug, and should I report it to Oracle
              Well, you should contact Oracle support, definitely, before you define is this a bug or not.
              I've searched ML for FIRST_ROWS_K / ROWNUM optimizations - there are plenty of bugs. Funnily, some them addresses issues which seems to be mutually exclusive.
              • 19. Re: Optimizer choosing different plans when ROWNUM filter is applied
                Centinul
                Timur Akhmadeev wrote:
                Can you build a reproducible test case on your database?
                I just built a test case with a data distribution similar to mine and have reproduced the same results. In a little while I'll post it here once I get it cleaned up.
                • 20. Re: Optimizer choosing different plans when ROWNUM filter is applied
                  Centinul
                  Here is my test case:

                  DDL/DML
                  CREATE TABLE TEST_AGGR
                  (
                       ID NUMBER
                  ,      VAL DATE
                  ,      PAD VARCHAR2(500)
                  ,      ID2 NUMBER
                  );
                  
                  CREATE TABLE TEST_JOIN
                  (
                          ID2 NUMBER
                  ,       PAD VARCHAR2(5)
                  ,       PROD VARCHAR2(15)
                  );
                  
                  CREATE SEQUENCE TEST_SEQUENCE;
                  
                  INSERT INTO TEST_AGGR
                  SELECT     TEST_SEQUENCE.NEXTVAL
                  ,     SYSDATE + DBMS_RANDOM.VALUE*100
                  ,     RPAD('X',499,'0')
                  ,     MOD(ROWNUM,78)
                  FROM     DUAL
                  CONNECT BY ROWNUM <= 195000;
                  
                  INSERT INTO TEST_JOIN
                  SELECT      ID2
                  ,      
                       (CASE
                            WHEN ROWNUM IN (3,5) THEN 'Y'
                            ELSE RPAD('X',4,'0') 
                       END)
                  ,     (CASE 
                            WHEN ROWNUM = 1 THEN 'test_prod' 
                            WHEN ROWNUM BETWEEN 2 AND 3 THEN 'DUPLICATE A1'
                            WHEN ROWNUM BETWEEN 4 AND 5 THEN 'A DUPLICATE A2'
                            ELSE UPPER(DBMS_RANDOM.STRING('A',15)) END)
                  FROM      (SELECT DISTINCT ID2 FROM TEST_AGGR);
                  
                  CREATE INDEX TEST_AGGR_IDX ON TEST_AGGR(ID,VAL,ID2);
                  CREATE UNIQUE INDEX TEST_JOIN_UIDX ON TEST_JOIN(PROD,PAD);
                  Statistics Gathering
                  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_AGGR',cascade=>true);
                  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_JOIN',cascade=>true);
                  Bind Variable Setup
                  var a VARCHAR2(25);
                  var b VARCHAR2(25);
                  
                  exec :a := '08/01/2009';
                  exec :b := '08/27/2009';
                  Non-hinted Query
                  SELECT /*repeatable_case_nohint*/ PROD
                  FROM
                  (
                       SELECT     TEST_JOIN.PROD
                       ,     COUNT(*) CNT
                       FROM     TEST_AGGR
                       JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                       WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                       AND     PROD <> 'TEST_PROD'
                       GROUP BY TEST_JOIN.PROD
                       ORDER BY CNT DESC
                  )
                  WHERE ROWNUM <= 1
                  Non-hinted XPLAN Output
                  PLAN_TABLE_OUTPUT
                  -----------------------------------------------------------------------------------------------------------------------------------------------
                  
                  SQL_ID  9smb486cd31b2, child number 0
                  -------------------------------------
                  SELECT /*repeatable_case_nohint*/ PROD FROM (  SELECT TEST_JOIN.PROD  , COUNT(*) CNT  FROM TEST_AGGR  JOIN TEST_JOIN ON
                  TEST_JOIN.ID2 = TEST_AGGR.ID2  WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')  AND PROD <> 'TEST_PROD'
                  GROUP BY TEST_JOIN.PROD  ORDER BY CNT DESC ) WHERE ROWNUM <= 1
                  
                  Plan hash value: 301623847
                  
                  -----------------------------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
                  -----------------------------------------------------------------------------------------------------------------------------------------
                  |*  1 |  COUNT STOPKEY                    |                |      1 |        |      1 |00:00:07.33 |   57334 |       |       |          |
                  |   2 |   VIEW                            |                |      1 |      2 |      1 |00:00:07.33 |   57334 |       |       |          |
                  |*  3 |    FILTER                         |                |      1 |        |      1 |00:00:07.33 |   57334 |       |       |          |
                  |   4 |     SORT ORDER BY                 |                |      1 |      2 |      1 |00:00:07.33 |   57334 | 73728 | 73728 |          |
                  |   5 |      SORT GROUP BY NOSORT         |                |      1 |      2 |     76 |00:00:07.33 |   57334 |       |       |          |
                  |   6 |       NESTED LOOPS                |                |      1 |      2 |  50637 |00:00:07.24 |   57334 |       |       |          |
                  |   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |    2 |          |       |          |
                  |*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |    1 |          |       |          |
                  |*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      2 |  50637 |00:00:07.15 |   57332 |       |       |          |
                  -----------------------------------------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     1 - filter(ROWNUM<=1)
                     3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                     8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                     9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
                                "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
                         filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
                                "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
                  Hinted Query
                  SELECT /*+ ALL_ROWS repeatable_case_hint*/ PROD
                  FROM
                  (
                       SELECT     TEST_JOIN.PROD
                       ,     COUNT(*) CNT
                       FROM     TEST_AGGR
                       JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                       WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                       AND     PROD <> 'TEST_PROD'
                       GROUP BY TEST_JOIN.PROD
                       ORDER BY CNT DESC
                  )
                  WHERE ROWNUM <= 1
                  Hinted XPLAN Output
                  PLAN_TABLE_OUTPUT
                  -----------------------------------------------------------------------------------------------------------------------------------------------
                  
                  SQL_ID  283wx8s0d04kn, child number 0
                  -------------------------------------
                  SELECT /*+ ALL_ROWS repeatable_case_hint*/ PROD FROM (  SELECT TEST_JOIN.PROD  , COUNT(*) CNT  FROM TEST_AGGR  JOIN
                  TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2  WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')  AND
                  PROD <> 'TEST_PROD'  GROUP BY TEST_JOIN.PROD  ORDER BY CNT DESC ) WHERE ROWNUM <= 1
                  
                  Plan hash value: 828673535
                  
                  ---------------------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
                  ---------------------------------------------------------------------------------------------------------------------------------
                  |*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.34 |     772 |       |       |          |
                  |   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.34 |     772 |       |       |          |
                  |*  3 |    FILTER                  |               |      1 |        |      1 |00:00:00.34 |     772 |       |       |          |
                  |   4 |     SORT ORDER BY          |               |      1 |     75 |      1 |00:00:00.34 |     772 | 73728 | 73728 |          |
                  |   5 |      HASH GROUP BY         |               |      1 |     75 |     76 |00:00:00.34 |     772 |   808K|   808K|     1/0/0|
                  |*  6 |       HASH JOIN            |               |      1 |  50069 |  50637 |00:00:00.26 |     772 |   842K|   842K|     1/0/0|
                  |*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
                  |*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  50737 |  50637 |00:00:00.10 |     769 |       |       |          |
                  ---------------------------------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     1 - filter(ROWNUM<=1)
                     3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                     6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
                     7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                     8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))
                  Observations

                  The cardinality estimates in the non-hinted plan are way, way off. However, in the hinted plan the cardinalities are nearly correct. In the non-hinted plan the cardinality estimates are incorrect at steps 8 and 9.

                  I tried to work out the cardinality estimate for the access of TEST_JOIN_UIDX by first calculating the "Effective Index Selectivity" based on the following data:
                  TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS
                  ------------------------------ ------------------------------ ------------ ---------- ----------
                  TEST_JOIN                      PROD                                     76 .013157895          0
                  Since in step 8 of the plan we are filtering based on a != condition we want ALL the rows except that one. Since the number of rows in this roughly equal the number of distinct values I calculated a rough effective index selectivity estimate of:
                  (1-0.13157895)*78 = 67.7368419
                  This is right in line with a logical understanding of the data.

                  Based on the following data:
                  TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
                  ------------------------------ ------------------------------ ------------
                  TEST_AGGR                      VAL                                  194588
                  I would expect the following cardinality out of the TEST_AGGR_IDX index:
                  SQL > SELECT (TO_DATE(:b,'MM/DD/YYYY')-TO_DATE(:a,'MM/DD/YYYY'))/(MAX(VAL)-MIN(VAL))+2/194588 AS SELECTIVITY FROM TEST_AGGR;
                  
                  SELECTIVITY
                  -----------
                   .260014672
                  
                  SQL > SELECT .260014672*195128 AS CARDINALITY FROM DUAL;
                  
                  CARDINALITY
                  -----------
                   50736.1429
                  This almost exactly matches the HINTED plan (error due to rounding).

                  Based on the estimates in the non-hinted plan I would have expected a 5% * 5% to occur but my calculations don't match:
                  SQL > select .05*.05*(1/78)*195128 AS CARDINALITY from dual;
                  
                  CARDINALITY
                  -----------
                   6.25410256
                  Thanks all for the help!
                  • 21. Re: Optimizer choosing different plans when ROWNUM filter is applied
                    Timur Akhmadeev
                    Looks like [Bug 6845871 - Suboptimal plan from ROWNUM predicate|https://metalink2.oracle.com/metalink/plsql/f?p=130:14:64481831320631190::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,6845871.8,1,1,1,helvetica].

                    WA suggested in the note works:
                    SQL> explain plan for
                      2  SELECT /*+ */ PROD
                      3  FROM
                      4  (
                      5       SELECT     TEST_JOIN.PROD
                      6       ,     COUNT(*) CNT
                      7       FROM     TEST_AGGR
                      8       JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                      9       WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                     10       AND     PROD != 'TEST_PROD'
                     11       GROUP BY TEST_JOIN.PROD
                     12       ORDER BY CNT DESC
                     13  )
                     14  WHERE ROWNUM <= 1;
                     
                    Explained
                    SQL> select * from table(dbms_xplan.display);
                     
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                    Plan hash value: 2964352717
                    --------------------------------------------------------------------------------
                    | Id  | Operation                         | Name           | Rows  | Bytes | Cos
                    --------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT                  |                |     1 |     9 |
                    |*  1 |  COUNT STOPKEY                    |                |       |       |
                    |   2 |   VIEW                            |                |     2 |    18 |
                    |*  3 |    SORT ORDER BY STOPKEY          |                |     2 |    60 |
                    |   4 |     SORT GROUP BY NOSORT          |                |     2 |    60 |
                    |*  5 |      FILTER                       |                |       |       |
                    |   6 |       NESTED LOOPS                |                |     2 |    60 |
                    |   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |    77 |  1463 |
                    |*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |     1 |       |
                    |*  9 |        INDEX FAST FULL SCAN       | TEST_AGGR_IDX  |     2 |    22 |
                    --------------------------------------------------------------------------------
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                     
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                       1 - filter(ROWNUM<=1)
                       3 - filter(ROWNUM<=1)
                       5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                       8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                       9 - filter("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
                                  "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"=
                     
                    26 rows selected
                     
                    SQL> alter session set "_first_k_rows_dynamic_proration"=false;
                     
                    Session altered
                     
                    SQL> 
                    SQL> explain plan for
                      2  SELECT /*+ */ PROD
                      3  FROM
                      4  (
                      5       SELECT     TEST_JOIN.PROD
                      6       ,     COUNT(*) CNT
                      7       FROM     TEST_AGGR
                      8       JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                      9       WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                     10       AND     PROD != 'TEST_PROD'
                     11       GROUP BY TEST_JOIN.PROD
                     12       ORDER BY CNT DESC
                     13  )
                     14  WHERE ROWNUM <= 1;
                     
                    Explained
                    SQL> select * from table(dbms_xplan.display);
                     
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                    Plan hash value: 1445505494
                    --------------------------------------------------------------------------------
                    | Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)
                    --------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT           |               |     1 |     9 |   209   (3)
                    |*  1 |  COUNT STOPKEY             |               |       |       |
                    |   2 |   VIEW                     |               |    75 |   675 |   209   (3)
                    |*  3 |    SORT ORDER BY STOPKEY   |               |    75 |  2250 |   209   (3)
                    |   4 |     HASH GROUP BY          |               |    75 |  2250 |   209   (3)
                    |*  5 |      FILTER                |               |       |       |
                    |*  6 |       HASH JOIN            |               |   481 | 14430 |   208   (3)
                    |*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |    77 |  1463 |     3   (0)
                    |*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |   488 |  5368 |   205   (3)
                    --------------------------------------------------------------------------------
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                       1 - filter(ROWNUM<=1)
                     
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                       3 - filter(ROWNUM<=1)
                       5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                       6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
                       7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                       8 - filter("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
                                  "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
                     
                    26 rows selected
                    • 22. Re: Optimizer choosing different plans when ROWNUM filter is applied
                      Charles Hooper
                      Centinul,

                      Nice test case.

                      I have seen this "problem" before. I started looking at the 10053 trace files you posted a couple days ago, and then became sidetracked with other things.

                      The 10053 trace file you posted 2 days ago showed something like this, which was also found in the 10053 trace file generated by your test case:
                      FPD: Following transitive predicates are generated in SEL$64EAE176 (#2) :
                      TO_DATE(:B1,'MM/DD/YYYY')<=TO_DATE(:B2,'MM/DD/YYYY')

                      The above lead me down the path... what if additional predicates are being generated, causing the cardinality estimates to be thrown off? Then I remembered seeing this before - ROWNUM<=1 is being pushed into the inline view. Take a look:
                      SELECT /*+ gather_plan_statistics */ PROD
                      FROM
                      (
                           SELECT     TEST_JOIN.PROD
                           ,     COUNT(*) CNT
                           FROM     TEST_AGGR
                           JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                           WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                           AND     PROD <> 'TEST_PROD'
                           GROUP BY TEST_JOIN.PROD
                           ORDER BY CNT DESC
                      )
                      WHERE ROWNUM <= 1;
                      
                      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                      
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      |*  1 |  COUNT STOPKEY                    |                |      1 |        |      1 |00:00:02.84 |   57178 |       |       |          |
                      |   2 |   VIEW                            |                |      1 |      2 |      1 |00:00:02.84 |   57178 |       |       |          |
                      |*  3 |    FILTER                         |                |      1 |        |      1 |00:00:02.84 |   57178 |       |       |          |
                      |   4 |     SORT ORDER BY                 |                |      1 |      2 |      1 |00:00:02.84 |   57178 | 73728 | 73728 |          |
                      |   5 |      SORT GROUP BY NOSORT         |                |      1 |      2 |     76 |00:00:02.80 |   57178 |       |       |          |
                      |   6 |       NESTED LOOPS                |                |      1 |      2 |  50596 |00:00:03.04 |   57178 |       |       |          |
                      |   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
                      |*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
                      |*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      2 |  50596 |00:00:02.88 |   57176 |       |       |          |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                         1 - filter(ROWNUM<=1)
                         3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                         8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                         9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
                                    "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
                             filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
                                    "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
                      
                      
                      SELECT /*+ gather_plan_statistics */ PROD
                      FROM
                      (
                           SELECT     TEST_JOIN.PROD
                           ,     COUNT(*) CNT
                           FROM     TEST_AGGR
                           JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                           WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                           AND     PROD <> 'TEST_PROD'
                           GROUP BY TEST_JOIN.PROD
                           ORDER BY CNT DESC
                      )
                      WHERE ROWNUM <= 2;
                      
                      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                      
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      |*  1 |  COUNT STOPKEY                    |                |      1 |        |      2 |00:00:02.70 |   57178 |       |       |          |
                      |   2 |   VIEW                            |                |      1 |      3 |      2 |00:00:02.70 |   57178 |       |       |          |
                      |*  3 |    FILTER                         |                |      1 |        |      2 |00:00:02.70 |   57178 |       |       |          |
                      |   4 |     SORT ORDER BY                 |                |      1 |      3 |      2 |00:00:02.70 |   57178 |  9216 |  9216 | 8192  (0)|
                      |   5 |      SORT GROUP BY NOSORT         |                |      1 |      3 |     76 |00:00:02.73 |   57178 |       |       |          |
                      |   6 |       NESTED LOOPS                |                |      1 |      3 |  50596 |00:00:02.88 |   57178 |       |       |          |
                      |   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
                      |*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
                      |*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      3 |  50596 |00:00:02.73 |   57176 |       |       |          |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                         1 - filter(ROWNUM<=2)
                         3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                         8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                         9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
                                    "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
                             filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
                                    "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
                      
                      
                      SELECT /*+ gather_plan_statistics */ PROD
                      FROM
                      (
                           SELECT     TEST_JOIN.PROD
                           ,     COUNT(*) CNT
                           FROM     TEST_AGGR
                           JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                           WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                           AND     PROD <> 'TEST_PROD'
                           GROUP BY TEST_JOIN.PROD
                           ORDER BY CNT DESC
                      )
                      WHERE ROWNUM <= 3;
                      
                      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                      
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      |*  1 |  COUNT STOPKEY                    |                |      1 |        |      3 |00:00:02.71 |   57178 |       |       |          |
                      |   2 |   VIEW                            |                |      1 |      4 |      3 |00:00:02.71 |   57178 |       |       |          |
                      |*  3 |    FILTER                         |                |      1 |        |      3 |00:00:02.71 |   57178 |       |       |          |
                      |   4 |     SORT ORDER BY                 |                |      1 |      4 |      3 |00:00:02.71 |   57178 |  9216 |  9216 | 8192  (0)|
                      |   5 |      SORT GROUP BY NOSORT         |                |      1 |      4 |     76 |00:00:02.67 |   57178 |       |       |          |
                      |   6 |       NESTED LOOPS                |                |      1 |      4 |  50596 |00:00:02.93 |   57178 |       |       |          |
                      |   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
                      |*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
                      |*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |      4 |  50596 |00:00:02.83 |   57176 |       |       |          |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                         1 - filter(ROWNUM<=3)
                         3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                         8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                         9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
                                    "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
                             filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
                                    "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
                      
                      
                      SELECT /*+ gather_plan_statistics */ PROD
                      FROM
                      (
                           SELECT     TEST_JOIN.PROD
                           ,     COUNT(*) CNT
                           FROM     TEST_AGGR
                           JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                           WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                           AND     PROD <> 'TEST_PROD'
                           GROUP BY TEST_JOIN.PROD
                           ORDER BY CNT DESC
                      )
                      WHERE ROWNUM <= 50;
                      
                      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                      
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      |*  1 |  COUNT STOPKEY                    |                |      1 |        |     50 |00:00:02.72 |   57178 |       |       |          |
                      |   2 |   VIEW                            |                |      1 |     51 |     50 |00:00:02.72 |   57178 |       |       |          |
                      |*  3 |    FILTER                         |                |      1 |        |     50 |00:00:02.72 |   57178 |       |       |          |
                      |   4 |     SORT ORDER BY                 |                |      1 |     51 |     50 |00:00:02.72 |   57178 |  9216 |  9216 | 8192  (0)|
                      |   5 |      SORT GROUP BY NOSORT         |                |      1 |     51 |     76 |00:00:02.65 |   57178 |       |       |          |
                      |   6 |       NESTED LOOPS                |                |      1 |     51 |  50596 |00:00:02.88 |   57178 |       |       |          |
                      |   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |     78 |00:00:00.01 |       2 |       |       |          |
                      |*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |     78 |00:00:00.01 |       1 |       |       |          |
                      |*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |     51 |  50596 |00:00:02.78 |   57176 |       |       |          |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                         1 - filter(ROWNUM<=50)
                         3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                         8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                         9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
                                    "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
                             filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
                                    "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
                      
                      
                      SELECT /*+ gather_plan_statistics */ PROD
                      FROM
                      (
                           SELECT     TEST_JOIN.PROD
                           ,     COUNT(*) CNT
                           FROM     TEST_AGGR
                           JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                           WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                           AND     PROD <> 'TEST_PROD'
                           GROUP BY TEST_JOIN.PROD
                           ORDER BY CNT DESC
                      )
                      WHERE ROWNUM <= 74;
                      
                      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                      
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      |*  1 |  COUNT STOPKEY                    |                |      1 |        |   74 |00:00:02.71 |   57178 |       |       |          |
                      |   2 |   VIEW                            |                |      1 |     75 |   74 |00:00:02.71 |   57178 |       |       |          |
                      |*  3 |    FILTER                         |                |      1 |        |   74 |00:00:02.71 |   57178 |       |       |          |
                      |   4 |     SORT ORDER BY                 |                |      1 |     75 |   74 |00:00:02.71 |   57178 |  9216 |  9216 | 8192  (0)|
                      |   5 |      SORT GROUP BY NOSORT         |                |      1 |     75 |   76 |00:00:02.66 |   57178 |       |       |          |
                      |   6 |       NESTED LOOPS                |                |      1 |     75 |50596 |00:00:02.93 |   57178 |       |       |          |
                      |   7 |        TABLE ACCESS BY INDEX ROWID| TEST_JOIN      |      1 |     77 |   78 |00:00:00.01 |       2 |       |       |          |
                      |*  8 |         INDEX FULL SCAN           | TEST_JOIN_UIDX |      1 |      1 |   78 |00:00:00.01 |       1 |       |       |          |
                      |*  9 |        INDEX FULL SCAN            | TEST_AGGR_IDX  |     78 |     75 |50596 |00:00:02.83 |   57176 |       |       |          |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                         1 - filter(ROWNUM<=74)
                         3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                         8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                         9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND
                                    "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
                             filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND
                                    "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
                      
                      SELECT /*+ gather_plan_statistics */ PROD
                      FROM
                      (
                           SELECT     TEST_JOIN.PROD
                           ,     COUNT(*) CNT
                           FROM     TEST_AGGR
                           JOIN     TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2
                           WHERE     VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')
                           AND     PROD <> 'TEST_PROD'
                           GROUP BY TEST_JOIN.PROD
                           ORDER BY CNT DESC
                      )
                      WHERE ROWNUM <= 75;
                      
                      select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                      
                      ------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                      ------------------------------------------------------------------------------------------------------------------------------
                      |*  1 |  COUNT STOPKEY          |               |      1 |        |     75 |00:00:00.06 |     741 |       |       |          |
                      |   2 |   VIEW                  |               |      1 |     75 |     75 |00:00:00.06 |     741 |       |       |          |
                      |*  3 |    FILTER               |               |      1 |        |     75 |00:00:00.06 |     741 |       |       |          |
                      |   4 |     SORT ORDER BY       |               |      1 |     75 |     75 |00:00:00.06 |     741 |  9216 |  9216 | 8192  (0)|
                      |   5 |      HASH GROUP BY      |               |      1 |     75 |     76 |00:00:00.06 |     741 |   963K|   963K| 2375K (0)|
                      |*  6 |       HASH JOIN         |               |      1 |  50074 |  50596 |00:00:00.30 |     741 |  1035K|  1035K| 1164K (0)|
                      |*  7 |        TABLE ACCESS FULL| TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       7 |       |       |          |
                      |*  8 |        INDEX FULL SCAN  | TEST_AGGR_IDX |      1 |  50742 |  50596 |00:00:00.10 |     734 |       |       |          |
                      ------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                         1 - filter(ROWNUM<=75)
                         3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))
                         6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")
                         7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
                         8 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY'))
                             filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))
                      Notice that the plan switched when ROWNUM<=75 was used.

                      Charles Hooper
                      IT Manager/Oracle DBA
                      K&M Machine-Fabricating, Inc.
                      • 23. Re: Optimizer choosing different plans when ROWNUM filter is applied
                        Timur Akhmadeev
                        Charles,

                        Could you clarify your point, please? What do you mean by "additional predicates are being generated"? I can't get it from the provided plans.
                        • 24. Re: Optimizer choosing different plans when ROWNUM filter is applied
                          Charles Hooper
                          Timur Akhmadeev wrote:
                          Charles,

                          Could you clarify your point, please? What do you mean by "additional predicates are being generated"? I can't get it from the provided plans.
                          Timur,

                          Centinul mentioned in this thread that he is curious about the cardinality estimates in the plan. He has apparently studied some very well written documents regarding cardinality estimates with bind variables. The book "Cost-Based Oracle Fundamentals" states something similar to this:
                          "When bind peeking is not used, the selectivity of BETWEEN :BIND1 AND :BIND2 is 0.0025, which is 5% (0.05) for the first bind variable and 5% (0.05) for the second, with a total estimated selectivity of 0.05 * 0.05, thus the estimated cadinality is 0.0025 * TableRows."

                          With the above in mind, I saw this text (slightly reformatted) in the 10053 trace file for Centinul's test case, and something similar also appeared in the 10053 trace file that he posted in this thread:
                          FPD: Considering simple filter push in SEL$1 (#1)
                          FPD:   Current where clause predicates in SEL$1 (#1) :
                                   ROWNUM<=1
                          
                          kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#1)
                          predicates with check contraints: 1>=ROWNUM
                          after transitive predicate generation: ROWNUM<=1
                          finally: ROWNUM<=1
                          FPD: Considering simple filter push in SEL$64EAE176 (#2)
                          
                          FPD:   Current where clause predicates in SEL$64EAE176 (#2) :
                           "TEST_AGGR"."VAL">=TO_DATE(:B1,'MM/DD/YYYY')
                           AND "TEST_AGGR"."VAL"<=TO_DATE(:B2,'MM/DD/YYYY')
                           AND "TEST_JOIN"."PROD"<>'TEST_PROD'
                           AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"
                          
                          kkogcp: try to generate transitive predicate from check constraints for SEL$64EAE176 (#2)
                          predicates with check contraints: 
                          "TEST_AGGR"."VAL">=TO_DATE(:B1,'MM/DD/YYYY')
                           AND "TEST_AGGR"."VAL"<=TO_DATE(:B2,'MM/DD/YYYY')
                           AND "TEST_JOIN"."PROD"<>'TEST_PROD'
                           AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"
                           AND TO_DATE(:B3,'MM/DD/YYYY')>=TO_DATE(:B4,'MM/DD/YYYY')
                          
                          after transitive predicate generation: 
                           "TEST_AGGR"."VAL">=TO_DATE(:B1,'MM/DD/YYYY')
                           AND "TEST_AGGR"."VAL"<=TO_DATE(:B2,'MM/DD/YYYY')
                           AND "TEST_JOIN"."PROD"<>'TEST_PROD'
                           AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"
                           AND TO_DATE(:B3,'MM/DD/YYYY')<=TO_DATE(:B4,'MM/DD/YYYY')
                          
                          finally:
                           "TEST_AGGR"."VAL">=TO_DATE(:B1,'MM/DD/YYYY')
                           AND "TEST_AGGR"."VAL"<=TO_DATE(:B2,'MM/DD/YYYY')
                           AND "TEST_JOIN"."PROD"<>'TEST_PROD'
                           AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"
                           AND TO_DATE(:B3,'MM/DD/YYYY')<=TO_DATE(:B4,'MM/DD/YYYY')
                          
                          FPD:   Following transitive predicates are generated in SEL$64EAE176 (#2) :
                                   TO_DATE(:B1,'MM/DD/YYYY')<=TO_DATE(:B2,'MM/DD/YYYY')
                          From the above, the optimizer added "TO_DATE(:B1,'MM/DD/YYYY')<=TO_DATE(:B2,'MM/DD/YYYY')" to the WHERE clause. This happened because a BETWEEN clause was used, therefore the first value must be less than the second value. When I saw that, I incorrectly calculated - there are now 4 bind variables in the WHERE clause, not just 2. That would mean that the selectivity would now be 0.05 * 0.05 * 0.05 * 0.05 = 0.00000625. That would make the estimated cadinality 0.00000625 * TableRows, which would be a very small cardinality estimate (much less than the 2 reported). The execution plans that I posted show that this is not happening. It is actually the ROWNUM <= syntax that is pushing into the inline view (note in the 10053 trace: Considering simple filter push in SEL$1 (#1)) which is driving the cardinality estimate from the TEST_AGGR_IDX index, because Oracle will stop the execution of the plan once that number of rows is retrieved.

                          Hopefully, the above makes sense. Two days ago before I had to set this thread aside, I was thrown into several weird calculations due to the additional predicates that the optimizer created. I think that I last noticed this ROWNUM pushing into an inline view behavior a year or two ago, and I had to experiment with the test case to actually recall the earlier test cases that I performed.

                          Charles Hooper
                          IT Manager/Oracle DBA
                          K&M Machine-Fabricating, Inc.
                          • 25. Re: Optimizer choosing different plans when ROWNUM filter is applied
                            Centinul
                            Timur Akhmadeev wrote:
                            Looks like [Bug 6845871 - Suboptimal plan from ROWNUM predicate|https://metalink2.oracle.com/metalink/plsql/f?p=130:14:64481831320631190::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,6845871.8,1,1,1,helvetica].
                            Thank you for posting this and providing an example. I haven't had a chance to read through it yet, but I'll definitely look at it tomorrow.
                            • 26. Re: Optimizer choosing different plans when ROWNUM filter is applied
                              Centinul
                              Charles Hooper wrote:
                              Centinul,

                              Nice test case.
                              Thank you for the compliments.
                              I have seen this "problem" before. I started looking at the 10053 trace files you posted a couple days ago, and then became sidetracked with other things.

                              The 10053 trace file you posted 2 days ago showed something like this, which was also found in the 10053 trace file generated by your test case:
                              FPD: Following transitive predicates are generated in SEL$64EAE176 (#2) :
                              TO_DATE(:B1,'MM/DD/YYYY')<=TO_DATE(:B2,'MM/DD/YYYY')

                              The above lead me down the path... what if additional predicates are being generated, causing the cardinality estimates to be thrown off? Then I remembered seeing this before - ROWNUM<=1 is being pushed into the inline view. Take a look:
                              I hadn't even thought of that, nice catch. I'm still not very proficient in reading the 10053 trace files (maybe someday they'll make sense).
                              Notice that the plan switched when ROWNUM<=75 was used.
                              I noticed very similar behavior when using the FIRST_ROWS() hints. Using my real data the change point was a ROWNUM <= 68 (see earlier thread message by me).
                              • 27. Re: Optimizer choosing different plans when ROWNUM filter is applied
                                Centinul
                                Charles Hooper wrote:
                                Hopefully, the above makes sense. Two days ago before I had to set this thread aside, I was thrown into several weird calculations due to the additional predicates that the optimizer created. I think that I last noticed this ROWNUM pushing into an inline view behavior a year or two ago, and I had to experiment with the test case to actually recall the earlier test cases that I performed.
                                I wanted to express my thanks for you sticking with me during this investigative process. While it wasn't a deal breaker from a programming/business perspective (I just recoded using ROW_NUMBER() instead) it was a great learning opportunity for me and I like to capitalize on them as much as possible. Your insightful responses help me understand the idiosyncrasies of the optimizer much better.

                                Situations like this almost make me wish I had a blog so I could post this stuff for other relatively new folks like me to learn from :) I suppose I could always start one :P

                                Now I can finally put this issue to rest...
                                • 28. Re: Optimizer choosing different plans when ROWNUM filter is applied
                                  Centinul
                                  I just had a chance to setup a base 11.2 install and I wanted to test this scenario. Surprisingly (or not depending on your perspective) the results were different (produced better plan) in 11.2. I tested with the default optimizer features, and with the same OPTIMIZER_INDEX_* parameters as identified in my original post and they both produced the same optimizer plans.

                                  Test Case

                                  See: {message:id=3660477}

                                  Tests Bind Data (because it was dependent on the date):
                                  SQL> print a
                                  
                                  A
                                  --------------------------------
                                  09/21/2009
                                  
                                  SQL> print b
                                  
                                  B
                                  --------------------------------
                                  10/14/2009
                                  Optmizer Features
                                  SQL> show parameter optimizer
                                  
                                  NAME                                 TYPE        VALUE
                                  ------------------------------------ ----------- ------------------------------
                                  optimizer_capture_sql_plan_baselines boolean     FALSE
                                  optimizer_dynamic_sampling           integer     2
                                  optimizer_features_enable            string      11.2.0.1
                                  optimizer_index_caching              integer     0
                                  optimizer_index_cost_adj             integer     100
                                  optimizer_mode                       string      ALL_ROWS
                                  optimizer_secure_view_merging        boolean     TRUE
                                  optimizer_use_invisible_indexes      boolean     FALSE
                                  optimizer_use_pending_statistics     boolean     FALSE
                                  optimizer_use_sql_plan_baselines     boolean     TRUE
                                  Plan:
                                  PLAN_TABLE_OUTPUT                                                                                                                 
                                  ----------------------------------------------------------------------------------------------------------------------------------
                                  SQL_ID  b3bkduwzcpzhg, child number 0                                                                                             
                                  -------------------------------------                                                                                             
                                  SELECT /*+ gather_plan_statistics TEST1 */ PROD FROM (  SELECT                                                                    
                                  TEST_JOIN.PROD  , COUNT(*) CNT  FROM TEST_AGGR  JOIN TEST_JOIN ON                                                                 
                                  TEST_JOIN.ID2 = TEST_AGGR.ID2  WHERE VAL BETWEEN                                                                                  
                                  TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY')  AND PROD <>                                                                
                                  'TEST_PROD'  GROUP BY TEST_JOIN.PROD  ORDER BY CNT DESC ) WHERE ROWNUM                                                            
                                  <= 1                                                                                                                              
                                                                                                                                                                    
                                  Plan hash value: 1445505494                                                                                                       
                                                                                                                                                                    
                                  --------------------------------------------------------------------------------------------------------------------------------- 
                                  | Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | 
                                  --------------------------------------------------------------------------------------------------------------------------------- 
                                  |   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:01.12 |     751 |       |       |          | 
                                  |*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:01.12 |     751 |       |       |          | 
                                  |   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:01.09 |     751 |       |       |          | 
                                  |*  3 |    SORT ORDER BY STOPKEY   |               |      1 |     75 |      1 |00:00:01.09 |     751 | 73728 | 73728 |          | 
                                  |   4 |     HASH GROUP BY          |               |      1 |     75 |     76 |00:00:01.10 |     751 |   964K|   964K|     1/0/0| 
                                  |*  5 |      FILTER                |               |      1 |        |  44926 |00:00:25.70 |     751 |       |       |          | 
                                  |*  6 |       HASH JOIN            |               |      1 |  44262 |  44926 |00:00:14.13 |     751 |  1035K|  1035K|     1/0/0| 
                                  |*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       7 |       |       |          | 
                                  |*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  44852 |  44926 |00:00:02.04 |     744 |       |       |          | 
                                  --------------------------------------------------------------------------------------------------------------------------------- 
                                                                                                                                                                    
                                  Predicate Information (identified by operation id):                                                                               
                                  ---------------------------------------------------                                                                               
                                                                                                                                                                    
                                     1 - filter(ROWNUM<=1)                                                                                                          
                                     3 - filter(ROWNUM<=1)                                                                                                          
                                     5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY'))                                                                 
                                     6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2")                                                                                
                                     7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')                                                                                    
                                     8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))    
                                  As you can see the plan produced the Hash Join that results in a speedy execution. I haven't looked at a 10053 trace to see what is different between 10.2 and 11.2 but I wanted to put this out there for anyone that is interested.

                                  Thanks!

                                  Edited by: Centinul on Sep 20, 2009 8:59 PM

                                  Added emphasis on OPTIMIZER_INDEX parameters.
                                  • 29. Re: Optimizer choosing different plans when ROWNUM filter is applied
                                    Jonathan Lewis
                                    Centinul wrote:
                                    SQL> show parameter optimizer
                                    
                                    NAME                                 TYPE        VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    optimizer_capture_sql_plan_baselines boolean     FALSE
                                    optimizer_dynamic_sampling           integer     2
                                    optimizer_features_enable            string      11.2.0.1
                                    optimizer_index_caching              integer     0
                                    optimizer_index_cost_adj             integer     100
                                    optimizer_mode                       string      ALL_ROWS
                                    optimizer_secure_view_merging        boolean     TRUE
                                    optimizer_use_invisible_indexes      boolean     FALSE
                                    optimizer_use_pending_statistics     boolean     FALSE
                                    optimizer_use_sql_plan_baselines     boolean     TRUE
                                    I guess I didn't have time to look at this the first time around - or maybe I decided it was in good hands and didn't need any comment from me. One thing that strikes me about the 11.2 result, though, is that you have the defaults for optimizer_index_cost_adj and optimizer_index_caching.

                                    In the 10.2 environment, they were set to 30% and 90% respectively, which would make an index full scan appear very cheap - which could explain why the optimizer chose a nested loop join until you cued it with a fairly large number in the rownum predicate.

                                    Regards
                                    Jonathan Lewis
                                    http://jonathanlewis.wordpress.com
                                    http://www.jlcomp.demon.co.uk

                                    "Science is more than a body of knowledge; it is a way of thinking"
                                    Carl Sagan