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(@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "C"@"SEL$1")
PX_JOIN_FILTER(@"SEL$1" "C"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "C"@"SEL$1" HASH HASH)
PX_JOIN_FILTER(@"SEL$1" "L"@"SEL$1")
PQ_MAP(@"SEL$1" "L"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "L"@"SEL$1" BROADCAST NONE)
USE_HASH(@"SEL$1" "C"@"SEL$1")
USE_HASH(@"SEL$1" "L"@"SEL$1")
LEADING(@"SEL$1" "H"@"SEL$1" "L"@"SEL$1" "C"@"SEL$1")
FULL(@"SEL$1" "C"@"SEL$1")
FULL(@"SEL$1" "L"@"SEL$1")
FULL(@"SEL$1" "H"@"SEL$1")
OUTLINE_LEAF(@"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
