Re: 11gR2 - optimizer_dynamic_sampling issue
From: Tim Gorman <tim_at_evdbt.com>
Date: Sat, 30 Oct 2010 15:45:00 -0600
Message-ID: <4CCC91DC.9050500_at_evdbt.com>
On 10/30/2010 2:28 PM, Robert Freeman wrote:
Date: Sat, 30 Oct 2010 15:45:00 -0600
Message-ID: <4CCC91DC.9050500_at_evdbt.com>
Perhaps ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 7 was executed by the user session, either as part of the application code or in an AFTER LOGON trigger?
On 10/30/2010 2:28 PM, Robert Freeman wrote:
-- http://www.freelists.org/webpage/oracle-l Received on Sat Oct 30 2010 - 16:45:00 CDTLooking 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