Parameter Recommendations for Oracle Database 12c – Part II

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
  • See the Oracle Documentation – starts up a minimum number of parallel query slaves
  • 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
      
  • This change got introduced to allow more parallel query workers being present to avoid the overhead of starting them up once a parallel operation is requested
  • See also MOS Note:1678111.1
    PARALLEL_MIN_SERVERS Does Not Limit The Number Of Parallel Processes At Startup in 12c
    .
    .

• job_queue_processes
  • 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
  • 1000
  • Set it to a rough equivalent of 2 * CPU cores
  • 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 12.1.0.1, job_queue_process was a Container Database (CDB) modifiable parameter (ie. at a global level). However, in 12.1.0.2, 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.
    But please see Patrick’s test which demonstrates that the parameter is modifiable on PDB level – but if it’s set to zero in CDB$ROOT and in all PDBS no jobs will be executed:
    https://hkpatora.com/2017/03/30/do-not-set-job_queue_processes-at-pdb-level-in-12-1-0-2/
  • https://mikedietrichde.com/2015/10/06/new-in-oracle-12c-_optimizer_gather_stats_on_load/
  • https://blogs.oracle.com/optimizer/entry/gathering_optimizer_statistics_is_one
  • Ask Tom: http://tinyurl.com/job-queue-processes 
  • MOS Note 2118028.1 – JOB_QUEUE_PROCESSES Parameter, its Significance and
    Recommended value
  • 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
  • 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
  • PURGE command
  • Using FLASHBACK DROP and managing the recycle bin

.
.

• 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