Archived Database Upgrade Blog

Parameter Recommendations for Oracle Database 12c - Part II

By: Mike Dietrich | Master Product Manager

Best Practice Hint

Time for a new round on Parameter Recommendations for Oracle Database The focus of this blog post settles on very well known parameters with interesting behavior. This can be a behavior change or simply something we'd like to point out. And even if you still work on Oracle Database 11g some of the below recommendations may apply to your environment as well.


Again, 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 Real Application Testing, especially the SQL Performance Analyzer but also Database Replay to verify the effect of any of those parameters. 

Known Parameters - Interesting Behavior

  • parallel_min_servers
    • What it does?
    • Default:
    • Behavior Change:
      • Setting it to a value below the default will let the database ignore it.
      • In Oracle Database 11g the default was 0
      • Compare vs on the same box:
        • 11g:
          SQL> show parameter parallel_min_servers
          NAME                  TYPE     VALUE
          --------------------- -------- ------
          parallel_min_servers  integer  0

        • 12c:
          SQL> show parameter parallel_min_servers
          NAME                  TYPE     VALUE
          --------------------- -------- ------
          parallel_min_servers  integer  8
    • Explanation:

  • job_queue_processes
    • What it does?
      • See the Oracle Documentation - value specifies the maximum number of job slaves to be created to execute jobs started by either DBMS_JOBS or DBMS_SCHEDULER
    • Default:
      • 1000
    • Recommendation:
      • Set it to a rough equivalent of 2 * CPU cores
    • Explantion:
      • In Oracle Database 12c we introduced the automatic stats gathering during CTAS and IAS (into an empty table only) operations. This can potentially lead to too many jobs doing the stats gathering. Furthermore issues can happen due to the default of concurrent stats gathering.
        Therefore a limitation of this parameter seems to be a good idea. 
      • Be aware when switching it to 0 - this will block all recompilation attempts. Furthermore generally no jobs can be executed anymore with  DBMS_JOBS or DBMS_SCHEDULER.
      • Multitenant behavior change:
        In, job_queue_process was a Container Database (CDB) modifiable parameter (ie. at a global level). However, in, the job_queue_process parameter is not CDB modifiable; instead it's PDB modifiable which means each PDB can have its own job_queue_process value.  
    • More Information:
    • Annotation:
      I've had an email exchange with Stefan Köhler about the stats behavior for CTAS. As I couldn't myself reproduce the behavior we say at two customer with job_queue_processes=1000 and an heavy CTAS activity (which could be remedied by setting JQP to a lower value) I would put a question mark behind my above statement.

  • recyclebin
    • What it does?
      • See the Oracle Documentation - controls whether the Flashback Drop capability is turned on or off. If the parameter is set to OFF, then dropped tables do not go into the recycle bin. If this parameter is set to ON, then dropped tables go into the recycle bin and can be recovered.
    • Default:
      • ON
    • Recommendation:
      • If the recyclebin is ON (the default) in your environment then empty it at least once per week. Create a default job in all your environments emptying the recycle bin every Sunday morning at 3am for instance:
        SQL> purge DBA_RECYCLEBIN;
    • Explantion:
      • The recycle bin is on in every database by default since Oracle 10g. The danger is that it may not be emptied but especially on developer databases many objects may be created and dropped again. As a result the dropped objects and its dependents still stay in the database until the space needs to be reclaimed. That means, they exist in the data dictionary as well, for instance in TAB$. Their name is different now starting with "BIN$..." instead of "EMP" - but they will blow up your dictionary. And emptying it not often enough may introduce a performance dip to your system as the cleanup of many objects can be quite resource intense
      • Check your current recycle bins:
        ------------- ---------------------------- ----------- -------------------
        TEST_RBIN     BIN$2e51YTaSK8TL/mPy+FuA==$0 TABLE       2010-05-27:15:23:45
        TEST_RBIN     BIN$5dF60S3GSEOSSYREaqCg==$0 TABLE       2010-05-27:15:23:43
        TEST_RBIN     BIN$JHCDN9YwQRXjXGOJcCIg==$0 TABLE       2010-05-27:15:23:42
    • More Information:

  • deferred_segment_creation
    • What it does?
      • See the Oracle Documentation - set to the default (TRUE), then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table
    • Default:
      • TRUE
    • Recommendation:
      • Set it to FALSE unless you plan to create a larger number of tables/indexes knowing that you won't populate many of them.
    • Explantion/Risk:
      • If my understanding is correct this parameter got introduced with Oracle Database 11.2 in order to save space when applications such as EBS, Siebel or SAP create tons of tables and indexes which never may get used as you don't work with the matching module of the software
      • The risk can be that certain query check DBA_SEGMENTS and/or DBA_EXTENTS - and if there's no segment allocated you won't find an indication about the existence of the object in there - but it actually exists. Furthermore we have seen issues with Data Pump workers getting contention, and some other things. 
    • More Information:
      • The documentation has become now pretty conservative as well since Oracle and I'll second that:
        Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.

View Comments

Comments ( 2 )
Commenting is closed on this post.

  • guest Tuesday, March 8, 2016

    Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog if you don?t mind! Because I am sure my visitors will find that very useful!

    <a href="https://twitter.com/">Twitter</a>

  • Mike Tuesday, March 8, 2016

    Sure - please do so.
    And thanks for your feedback.