1 2 3 Previous Next 34 Replies Latest reply: Sep 21, 2009 5:42 AM by Centinul Go to original post RSS
      • 30. Re: Optimizer choosing different plans when ROWNUM filter is applied
        Charles Hooper
        Thanks for posting the updated execution on Oracle 11.2.0.1. I also performed the test with 11.2.0.1. I think that one of the important changes is the cardinality estimate. Oracle 10.2.0.x set the estimated cardinality returned by the view at one greater than the number specified for the ROWNUM restriction, while 11.2.0.1 apparently set the estimated cardinality returned by the view to 75.

        My results on 11.2.0.1 with different values for optimizer_index_cost_adj and optimizer_index_caching:
        ALTER SESSION SET optimizer_index_caching=0;
        ALTER SESSION SET optimizer_index_cost_adj=100;
        ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test100';
        ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
        
        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 |
        ---------------------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
        |*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
        |   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.03 |     747 |       |       |          |
        |*  3 |    SORT ORDER BY STOPKEY   |               |      1 |     75 |      1 |00:00:00.03 |     747 | 73728 | 73728 |          |
        |   4 |     HASH GROUP BY          |               |      1 |     75 |     76 |00:00:00.03 |     747 |   964K|   964K| 2491K (0)|
        |*  5 |      FILTER                |               |      1 |        |  44832 |00:00:00.06 |     747 |       |       |          |
        |*  6 |       HASH JOIN            |               |      1 |  44262 |  44832 |00:00:00.05 |     747 |  1035K|  1035K| 1265K (0)|
        |*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
        |*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  44852 |  44832 |00:00:00.02 |     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')))
        
        
        ALTER SESSION SET optimizer_index_caching=90;
        ALTER SESSION SET optimizer_index_cost_adj=30;
        ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test30';
        
        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 |
        ---------------------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
        |*  1 |  COUNT STOPKEY             |               |      1 |        |      1 |00:00:00.03 |     747 |       |       |          |
        |   2 |   VIEW                     |               |      1 |     75 |      1 |00:00:00.03 |     747 |       |       |          |
        |*  3 |    SORT ORDER BY STOPKEY   |               |      1 |     75 |      1 |00:00:00.03 |     747 | 73728 | 73728 |          |
        |   4 |     HASH GROUP BY          |               |      1 |     75 |     76 |00:00:00.03 |     747 |   964K|   964K| 2501K (0)|
        |*  5 |      FILTER                |               |      1 |        |  44832 |00:00:00.06 |     747 |       |       |          |
        |*  6 |       HASH JOIN            |               |      1 |  44262 |  44832 |00:00:00.05 |     747 |  1035K|  1035K| 1286K (0)|
        |*  7 |        TABLE ACCESS FULL   | TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
        |*  8 |        INDEX FAST FULL SCAN| TEST_AGGR_IDX |      1 |  44852 |  44832 |00:00:00.02 |     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')))
        
        
        ALTER SESSION SET optimizer_index_caching=100;
        ALTER SESSION SET optimizer_index_cost_adj=1;
        ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test1';
        
        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 |
        ------------------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT        |               |      1 |        |      1 |00:00:01.16 |   57034 |       |       |          |
        |*  1 |  COUNT STOPKEY          |               |      1 |        |      1 |00:00:01.16 |   57034 |       |       |          |
        |   2 |   VIEW                  |               |      1 |     75 |      1 |00:00:01.16 |   57034 |       |       |          |
        |*  3 |    SORT ORDER BY STOPKEY|               |      1 |     75 |      1 |00:00:01.16 |   57034 | 73728 | 73728 |          |
        |   4 |     HASH GROUP BY       |               |      1 |     75 |     76 |00:00:01.16 |   57034 |   964K|   964K| 2506K (0)|
        |*  5 |      FILTER             |               |      1 |        |  44832 |00:00:01.17 |   57034 |       |       |          |
        |   6 |       NESTED LOOPS      |               |      1 |  44262 |  44832 |00:00:01.16 |   57034 |       |       |          |
        |*  7 |        TABLE ACCESS FULL| TEST_JOIN     |      1 |     77 |     78 |00:00:00.01 |       3 |       |       |          |
        |*  8 |        INDEX FULL SCAN  | TEST_AGGR_IDX |     78 |    575 |  44832 |00:00:01.14 |   57031 |       |       |          |
        ------------------------------------------------------------------------------------------------------------------------------
        
        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'))
           7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD')
           8 - 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"))
        Charles Hooper
        IT Manager/Oracle DBA
        K&M Machine-Fabricating, Inc.
        • 31. Re: Optimizer choosing different plans when ROWNUM filter is applied
          Centinul
          Jonathan --

          I also tested with the same parameters. I mentioned that in the first paragraph of the post today, but I should have probably separated it out and made it more clear. Testing with the same 10.2 parameters, resulted in the HASH JOIN plan in 11.2.0.1

          Thanks!
          • 32. Re: Optimizer choosing different plans when ROWNUM filter is applied
            Centinul
            Charles --

            Thanks for the follow-on testing. Your results corroborate exactly with mine. I tested with the 10.2 OPTIMIZER_INDEX_* parameters as well and still received the HASH JOIN plan (added emphasis to my post to identify that).

            I did notice the difference in estimation that you identified, however i currently cannot explain it. I am curious to what would have changed in the CBO code to now to have this plan cost less than the NESTED LOOP that was identified as the cheapest in 10.2.0.4. If I get a chance I'll dig into the trace files.

            Thanks again!
            • 33. Re: Optimizer choosing different plans when ROWNUM filter is applied
              Jonathan Lewis
              Centinul wrote:
              Jonathan --

              I also tested with the same parameters. I mentioned that in the first paragraph of the post today, but I should have probably separated it out and made it more clear. Testing with the same 10.2 parameters, resulted in the HASH JOIN plan in 11.2.0.1

              Thanks!
              Centinul,

              You're right - I didn't read that paragraph to the end.

              I think Charles' comments fairly early on in the thread give us the correct answer - the 1/78 pro-rating code for "first K rows" optimisation has gone wrong in 10g. It's decided to push all the way through to the initial selection and join (i.e. what's the cost of getting one row from test_join than scanning the index once to aggregate all (or 1/78th of ?) the data for that one row without sorting).

              But every row from test_join has to be aggregated because the order by is on the count, not on the value from test_join - so the 1/78 push down should not have applied to descendent of the "sort order by" operation.

              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
              • 34. Re: Optimizer choosing different plans when ROWNUM filter is applied
                Centinul
                Jonathan (and Charles) --

                Thank you both very much. I have learned a lot from this thread. If I had any more "Helpful" points I'd give them out!

                Thanks again.
                1 2 3 Previous Next