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:
*/
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