Initialization Parameters Configuration

The following table describes the initialization parameters set in the performance lab for testing medium and large systems. The SGA and PGA requirements for performance payload was 24G and 10G respectively.

Initialization Parameters
Parameter Value Description 
db-block_size
 8192 Specifies (in bytes) the size of Oracle database blocks. Typical values are 4096 and 8192. The value of this parameter must be a multiple of the physical block size at the device level. This parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. 
db_cache_size
 10G Specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).The value must be at least 4M * number of cpus * granule size (smaller values are automatically rounded up to this value). 
db_file_multiblock_read_count
 16 Minimizes disk input/output during table scans by specifying the maximum number of blocks read in one input/output operation during a sequential scan. 
db_writer_processes
 10 Useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance. 
job_queue_processes
 10 Specifies the maximum number of processes that can be created for the execution of jobs. 
open_cursors
 3024 Specifies the maximum number of open cursors (handles private SQL areas) a session can have at once. This parameter prevents a session from opening an excessive number of cursors. It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. 
parallel_threads_per_cpu
 8 Specifies the default degree of parallelism for the instance and determines the parallel adaptive and load balancing algorithms. The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution. 
processes
 1000 Specifies the maximum number of operating system user processes that can simultaneously connect to an Oracle server. This value accommodates all background processes such as Job Queue (SNP) and parallel execution (Pnnn) processes. 
sessions
 2000 Specifies the total number of user and system sessions. 
optimizer_adaptive_features
 true Enables or disables all of the adaptive optimizer features, including adaptive plan (adaptive join methods and bitmap plans), automatic re-optimization, SQL plan directives, and adaptive distribution methods.
Note: The optimizer_adaptive_features property (default value is set to true) may produce a huge negative impact on the performance, especially with the RAC database. If negative performance is observed, change the property on RAC database with Oracle 12C.