I wonder if there is an 18c equivalent of the 12.1
optimizer_adaptive_features=TRUE that Oracle hasn't discovered yet !
Hemant K Chitale
On Sun, Apr 29, 2018 at 12:56 AM, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
Glad we could help. OPTIMIZER_ADAPTIVE_FEATURES=TRUE strikes again!
For anyone interested, here is the metalink document about that parameter
in 12.1. Such a PITA.
Recommendations for Adaptive Features in Oracle Database 12c Release 1
(Adaptive Features, Adaptive Statistics and 12c SQL Performance) (Doc ID
2312911.1)
Chris
On Fri, Apr 27, 2018 at 3:46 PM, Sheehan, Jeremy <JEREMY.SHEEHAN@xxxxxxx>
wrote:
Chris,
I disabled the parameter and it went right to the plan that it uses for
selects! HUZZAH! Thanks so much!
Jeremy
*From:* Chris Taylor [mailto:christopherdtaylor1994@xxxxxxxxx]
*Sent:* Friday, April 27, 2018 4:29 PM
*To:* Sheehan, Jeremy <JEREMY.SHEEHAN@xxxxxxx>
*Subject:* Re: Tuning Advice
Also check to see if you have optimizer_adaptive_features=TRUE in your
db. Can cause wonky behavior in the execution plans. I think Oracle
recommends disabling it entirely on 12.1 and we did.
There's a metalink document about it.
Chris
On Fri, Apr 27, 2018 at 12:26 PM, Sheehan, Jeremy <JEREMY.SHEEHAN@xxxxxxx>
wrote:
Excellent! I will give this a try and see how it works!
Thanks for the help Chris!
Jeremy
*From:* Chris Taylor [mailto:christopherdtaylor1994@xxxxxxxxx]
*Sent:* Friday, April 27, 2018 12:22 PM
*To:* Sheehan, Jeremy <JEREMY.SHEEHAN@xxxxxxx>
*Cc:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*Subject:* Re: Tuning Advice
CAUTION - EXTERNAL EMAIL
Questions:
1. Does the inserted table have constraints - especially FK contraints
back to a parent table?
2. Is there anything unusual about the table being inserted such as being
an IOT , partitioned etc?
Tips:
1. Execute your SELECT sql (without the INSERT) and then immediately
execute :
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED
IOSTATS LAST'))
/
From that output you can grab everything under the OUTLINE section - it's
a big hint and you can add that to your SELECT statement and use it in your
insert.
For example let's say your output under OUTLINE DATA looks something like:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_fix_control' '20355502:8')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "N"@"SEL$1")
END_OUTLINE_DATA
*/
Then your INSERT would become:
INSERT into my_table (col1, col2, col3, colN)
SELECT /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_fix_control' '20355502:8')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "N"@"SEL$1")
END_OUTLINE_DATA
*/
col1, col2, col3, colN from my_other_table;
HTH
Chris
On Fri, Apr 27, 2018 at 10:50 AM, Sheehan, Jeremy <JEREMY.SHEEHAN@xxxxxxx>
wrote:
Hello Gurus,
Oracle 12.1.0.2
AIX 7.1
I have a query that runs fairly well. Takes about 8 minutes to run,
nothing terrible about it (no huge FTS, joins seem to be in place
correctly). When I try to do an insert into TABLE as SELECT, it picks a
completely different execution plan and never finishes. The really odd
thing is that it works great when using CTAS or initial creation of a MV.
Any suggestion on how I can have the optimizer not use a specific
execution plan or any session level parameters that would have it use a
different execution plan?
Thanks in advance!
Jeremy
