9 Replies Latest reply on Apr 20, 2014 9:55 AM by Aman....

    optmizer_feature_enable and hints

    Roger22

      Hi,

      I have Oracle Database 11.2.0.2.0.

      It seems that sometimes, the optimizer_features_enable must be set to a version that supports the hints used...; don't know whether this affirmation is correct, but this is what I observed:

       

      -- setting at session or system level (same behaviour)

      SQL> alter session set optimizer_features_enable='8.1.0';

      Session altered

       

      SQL> show parameter optimizer

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      optimizer_capture_sql_plan_baselines boolean     FALSE

      optimizer_dynamic_sampling           integer     0

      optimizer_features_enable            string      8.1.0

      optimizer_index_caching              integer     0

      optimizer_index_cost_adj             integer     100

      optimizer_mode                       string      CHOOSE

      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

       

      SQL> explain plan for

        2 

        2  select /*+ result_cache*/cust_last_name, sum(nvl2(o.customer_id, 0, 1)) "Count"

        3      from oe.customers c, oe.orders o

        4      where c.credit_limit>1000

        5      and c.customer_id = o.customer_id(+)

        6      group by cust_last_name

        7  ;

      Explained

       

      SQL> select * from table(dbms_xplan.display);

      PLAN_TABLE_OUTPUT

      --------------------------------------------------------------------------------

      Plan hash value: 3417764714

      --------------------------------------------------------------------------------

      | Id  | Operation            | Name                       | Rows  | Bytes | Cost

      --------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT     |                            |   168 |  8568 |    1

      |   1 |  RESULT CACHE        | gd83j1337c0yb0tb0vstqtw7zz |       |       |

      |   2 |   SORT GROUP BY      |                            |   168 |  8568 |    1

      |   3 |    NESTED LOOPS OUTER|                            |   260 | 13260 |

      |*  4 |     TABLE ACCESS FULL| CUSTOMERS                  |   260 |  9880 |

      |*  5 |     INDEX RANGE SCAN | ORD_CUSTOMER_IX            |     1 |    13 |

      --------------------------------------------------------------------------------

      Predicate Information (identified by operation id):

      ---------------------------------------------------

         4 - filter("C"."CREDIT_LIMIT">1000)

         5 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID"(+))

      Result Cache Information (identified by operation id):

      PLAN_TABLE_OUTPUT

      --------------------------------------------------------------------------------

      ------------------------------------------------------

         1 - column-count=2; dependencies=(OE.ORDERS, OE.CUSTOMERS); attributes=(order

          from oe.customers c, oe.orders o

          where c"

      Note

      -----

         - cpu costing is off (consider enabling it)

      27 rows selected

       


      I don't understand why setting the optimizer_features_enable to a lower version, still uses the result cache mechanism, even if if the RESULT_CACHE parameter was added in Oracle 11. Shouldn't the statement above NOT being cached, since I run with 8.1.0 optimizer settings?

      I tried with another parameter, and, for the same query, I tried with the opt_param hint ( select /*+ opt_param('optimizer_index_cost_adj',20)*/ ........ from ....). The first plan, running with optimizer features to 8.1.0 was different than the plan with optimizer features set to 11.2.0.2 (in this latter case, indexes were used).

       

      So: optimizer_features_enable must be set to a version that supports the hints used? or not really

       

      Thank you!