Archived Database Upgrade Blog

Parameter Recommendations for Oracle Database 12c - Part I

Mike Dietrich
Master Product Manager

Best Practice Hint

 A few weeks ago we've published some parameter recommendations including several underscores but based on an internal discussion (still ongoing) we decided to remove this entry and split up the tasks. The optimizer team will take over parts of it and I'll post an update as soon as something is published.



Please be advised - the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.

We strongly recommend SQL Performance Analyzer to verify the effect of any of those parameters. 

How to read this blog post?

Never ever blindly set any underscore or hidden parameters because "somebody said" or "somebody wrote on a blog" (including this blog!) or "because our country has the best tuning experts worldwide" ... Only trust Oracle Support if it's written into a MOS Note or an official Oracle White Paper or if you work with a particular support or consulting engineer for quite a long time who understands your environment.

Important Parameter Settings

    • _kks_obsolete_dump_threshold
      • What it does?
        • Introduced in Oracle as an enhancement  to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and its child cursors after the parent cursor has been obsoleted N times. 
      • Problem:
        • Trace files can grow like giant mushrooms due to cursor invalidations
      • Solution:
      • Patches:
        • Fix included in DBBP
        • Fix on-top of
        • Since Feb 13, 2016 there's a one-off available but on Linux only - and only on top of a fresh 
      • Remarks:
        • The underlying cursor sharing problem needs to be investigated - always
          If you have cursor sharing issues you may set this parameter higher therefore not every invalidation causes a dump, then investigate and solve the issue, and finally switch the parameter to 0 once the issue is taken care of. 
          Please be aware that switching the parameter to 0 will lead to a lack of diagnostics information in case of cursor invalidations.

    • _use_single_log_writer
    • memory_target
      • What it does?
      • Problem:
        • Unexpected failing database upgrades with settings of  memory_target < 1GB where equal settings ofsga_target and pga_aggregate_target didn't cause issues 
        • It prevents the important use of HugePages
      • Solution:
        • Avoid memory_target by any chance
        • Better use sga_target and pga_aggregate_target instead

    • pga_aggregate_limit

    Essential MOS Notes for Oracle Database


    View Comments

    Comments ( 4 )
    Commenting is closed on this post.

    • Kais Thursday, March 31, 2016

      Hi Mike,
      How be sure of not having side effects after setting these hidden parameters ?

    • Mike Thursday, March 31, 2016


      you won't be surprised by my answer I'd guess ;-)
      Please see the Preface above:


      Please be advised - the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle Support. Always use proper testing mechanisms.

      We strongly recommend SQL Performance Analyzer to verify the effect of any of those parameters.


    • guest Thursday, February 2, 2017

      do you have any parameter recommendation for ? we have applied Jan2017 patch..for GI and DATABASE.

    • Mike Wednesday, February 8, 2017

      Sure - please browse through the blog as Roy and I gave a lot of recommendations within the past 12 months.

      In addition this one is the most important one meaning you'll have to apply two fixes on top:https://blogs.oracle.com/UPGRADE/entry/enabling_adaptive_features_of_oracle

      Apart from that we can't give "blindly" recommendations without knowing anything about your environment. A good rule is to have not too many parameter set. The more simple the better.



    Integrated Cloud Applications & Platform Services