Parameter Recommendations for Oracle Database 12c – Part II

Best Practice Hint
See also:

Parameter Recommendations for Oracle Database 12c – Part I

Time for a new round on Parameter Recommendations for Oracle Database 12.1.0.2. 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.

Preface

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
    • CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2
    • Setting it to a value below the default will let the database ignore it.
    • In Oracle Database 11g the default was 0
    • Compare 11.2.0.4 vs 12.1.0.2 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
  • recyclebin
    • 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.
    • ON
    • 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;
    • 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:
      SQL > SHOW RECYCLEBIN;
      ORIGINAL NAME RECYCLEBIN NAME              OBJECT TYPE DROP TIME
      ————- —————————- ———– ——————-
      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

.
.

  • deferred_segment_creation
    • 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
    • TRUE
    • Set it to FALSE unless you plan to create a larger number of tables/indexes knowing that you won’t populate many of them.
    • 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.
    • The documentation has become now pretty conservative as well since Oracle 11.2.0.4 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.
      ..

–Mike

6 thoughts on “Parameter Recommendations for Oracle Database 12c – Part II

  1. Pingback: Parameter Recommendations for Oracle Database 12c – Part I | Upgrade your Database - NOW!

  2. Hi Mike,
    one addition to job_queue_processes and stats gathering: 11g introduced the “CONCURRENT” option to DBMS_STATS. When you set it to TRUE, then DBMS_STATS spawns several scheduler jobs to concurrently gather stats in batches. The number of spawned jobs seems to correlate to job_queue_processes which, at the default of 1000, may render your DB server almost unusable due to overload.
    Yet another reason to choose a suitable setting for job_queue_processes.

    Cheers,
    Uwe

Leave a Reply

Your email address will not be published. Required fields are marked *