Re: Tuning Advice

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_at_fpl.com> 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_at_gmail.com]
> *Sent:* Friday, April 27, 2018 4:29 PM
> *To:* Sheehan, Jeremy <JEREMY.SHEEHAN_at_fpl.com>
> *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_at_fpl.com>
> wrote:
>
> Excellent! I will give this a try and see how it works!
>
>
>
> Thanks for the help Chris!
>
>
>
> Jeremy
>
>
>
> *From:* Chris Taylor [mailto:christopherdtaylor1994_at_gmail.com]
> *Sent:* Friday, April 27, 2018 12:22 PM
> *To:* Sheehan, Jeremy <JEREMY.SHEEHAN_at_fpl.com>
> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
> *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(_at_"SEL$1")
>
> FULL(_at_"SEL$1" "N"_at_"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(_at_"SEL$1")
>
> FULL(_at_"SEL$1" "N"_at_"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_at_fpl.com>
> 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
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l