Use > Database > Deploying the Oracle Server Database > Oracle Server Configuration and Sizing Guidelines > Oracle Parameter Settings

Oracle Parameter Settings

The following table describes the recommended values for a number of Oracle database initialization parameters, when working with the Universal CMDB database server:

Parameter Name Universal CMDB Deployment Remarks
Small Large
DB_BLOCK_SIZE 8K 8K-16K Should be a multiple of the operating system block size.
DB_CACHE_ADVICE ON ON For gathering statistics when tuning is required .
SGA_TARGET 1 GB 4 GB and higher See remarks below the table.
MEMORY_TARGET 2 GB 5 GB and higher See remarks below the table.
LOG_BUFFER 1 MB 5 MB  
DB_FILE_MULTIBLOCK_READ_COUNT Oracle default value Oracle default value  
PROCESSES 200 400 Add an additional 100 as a safety net
SESSIONS 225 445 (1.1 * PROCESSES) + 5
OPTIMIZER_ INDEX_COST_ ADJ parameter value 100 100 Affects performance
TIMED_ STATISTICS True True  
LOG_ CHECKPOINT_ INTERVAL 0 0  
LOG_ CHECKPOINT_ TIMEOUT 0; or greater than or equal to 1800 0; or greater than or equal to 1800  
OPTIMIZER_ MODE ALL_ROWS ALL_ROWS  
CURSOR_ SHARING Exact Exact  
OPEN_CURSORS 800 800  
COMPATIBLE The same as the release installed The same as the release installed  
SQL_TRACE False, True False  
UNDO_ MANAGEMENT Auto Auto  
UNDO_ RETENTION Oracle default value Oracle default value  
RECYCLEBIN Off Off  
NLS_LENGTH_SEMANTICS BYTE BYTE This parameter controls the length definition of character type columns.
NLS_COMP BINARY BINARY Using a different value for this parameter may cause severe performance problems.
NLS_SORT BINARY BINARY Using a different value for this parameter may cause severe performance problems.
WORKAREA_SIZE_ POLICY AUTO AUTO  
PGA_AGGREGATE_ TARGET 400 MB 1 GB and higher  
STATISTICS_LEVEL TYPICAL TYPICAL Enables tuning if required.
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES FALSE FALSE Controls Automatic Plan Capture as part of Oracle 11g SQL Management Base (SMB).
AUDIT_TRAIL NONE NONE In Oracle 11g, the default is changed from none to db, which means that out-of-the-box database auditing is written to the SYS.AUD$ audit trail table. It is advisable to change this value to none to avoid growth of the system tablespace.
CURSOR_ SPACE_FOR_ TIME False False  
USE_STORED_ OUTLINES False False Oracle Default
BLANK_ TRIMMING False False  
FIXED_DATE Not set Not set Universal CMDB uses the SYSDATE function for generating system time as part of the application process.
_PARTITION_LARGE_EXTENTS FALSE FALSE Relevant for Oracle 11.2.0.2 only. When this hidden parameter is set to TRUE, it affects the size of partitions in native partitioned tables. The initial extent allocated for each partition is very large, thus causing unwanted growth of database data files.

Note the following:

  • SGA_TARGET. Setting this parameter configures Oracle to automatically determine the size of the buffer cache (db_cache_size), shared pool (shared_pool_size), large pool (large_pool_size), java pool (java_pool_size), and streams pool (streams_pool_size).

    The value configured for SGA_TARGET sets the total size of the SGA components.

    When SGA_TARGET is set (that is, it's value is not 0), and one of the above pools is also set to a non-zero value, the pool value is used as the minimum value for that pool.

  • MEMORY_TARGET. In Oracle 11g, Automatic Memory Management enables the entire instance memory to be automatically managed and tuned by the instance. The instance memory contains the System Global Area (SGA) and the Program Global Area (PGA). In Oracle 11g, MEMORY_TARGET is the only required memory parameter to set, however it is recommended to set SGA_TARGET or PGA_AGGREGATE_TARGET as well to avoid frequent resizing of the SGA and PGA components. The values entered for SGA_TARGET and PGA_AGGREGATE_TARGET serve as minimum values.

Note  

  • It is not recommended to collect statistics for any temporary tables.

  • It is recommended to have the statistics gathering enabled and this way the user will not need to run them manually. Statistics gathering relies on the modification monitoring feature of stale statistics. For more information, see the Oracle® Database Performance Tuning Guide.

Send Help Center feedback