Home -> Mailing Lists -> Oracle-L -> RE: Tuning Advice

RE: Tuning Advice

From: Sheehan, Jeremy <JEREMY.SHEEHAN_at_fpl.com>
Date: Fri, 27 Apr 2018 17:26:48 +0000
Message-ID: <8e09f77f7b00461781c573fa15b3fd58_at_fpl.com> 








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"@"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<mailto: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


Received on Fri Apr 27 2018 - 19:26:48 CEST












Original text of this message