Re: Tuning Advice

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_at_gmail.com> 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_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