11gR2 - optimizer_dynamic_sampling issue

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Sat, 30 Oct 2010 13:28:50 -0700 (PDT)
Message-ID: <459091.88522.qm_at_web113211.mail.gq1.yahoo.com>



Looking for some thoughts. I have a query. the default setting for ODS is:

SQL> show parameter dynamic

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2

yet I have a SQL statement where the execution plan indicates that ODS being used is 7:

Outline Data


  /*+

      BEGIN_OUTLINE_DATA
      GBY_PUSHDOWN(_at_"SEL$1")
      SWAP_JOIN_INPUTS(_at_"SEL$1" "C"_at_"SEL$1")
      PX_JOIN_FILTER(_at_"SEL$1" "C"_at_"SEL$1")
      PQ_DISTRIBUTE(_at_"SEL$1" "C"_at_"SEL$1" HASH HASH)
      PX_JOIN_FILTER(_at_"SEL$1" "L"_at_"SEL$1")
      PQ_MAP(_at_"SEL$1" "L"_at_"SEL$1")
      PQ_DISTRIBUTE(_at_"SEL$1" "L"_at_"SEL$1" BROADCAST NONE)
      USE_HASH(_at_"SEL$1" "C"_at_"SEL$1")
      USE_HASH(_at_"SEL$1" "L"_at_"SEL$1")
      LEADING(_at_"SEL$1" "H"_at_"SEL$1" "L"@"SEL$1" "C"@"SEL$1")
      FULL(_at_"SEL$1" "C"_at_"SEL$1")
      FULL(_at_"SEL$1" "L"_at_"SEL$1")
      FULL(_at_"SEL$1" "H"_at_"SEL$1")
      OUTLINE_LEAF(_at_"SEL$1")
      ALL_ROWS
      OPT_PARAM('optimizer_dynamic_sampling' 7)
      OPT_PARAM('query_rewrite_enabled' 'false')
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA

  */

Note


  • dynamic sampling used for this statement (level=7)

I've checked there are no outlines in dba_outlines

SQL> select name from dba_outlines;

no rows selected

and there are no plan_baselines:

SQL> select sql_handle, plan_name
  2 from dba_sql_plan_baselines;

no rows selected

Would appreciate any thoughts.....

RF

Robert G. Freeman
Master Principal Consultant, Oracle Corporation, Oracle ACE Author of various books on RMAN, New Features and this shorter signature line. Blog: http://robertgfreeman.blogspot.com
--

http://www.freelists.org/webpage/oracle-l Received on Sat Oct 30 2010 - 15:28:50 CDT

Original text of this message