Vontu's Oracle initialization parameters

book

Article ID: 159518

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

The following is a list of initialization parameters that Vontu specifies during Oracle installations, along with explanations.

Resolution

Oracle initializatiton values from the Vontu 9x database creation template (as of 10/21/2009)

 Parameter                                            Vontu Value       Default                       Comment                                                      
 pga_aggregate_target  419430400k  10M or 20% of SGA size  Specifies the target aggregate PGA memory available to all server processes attached to the instance.  This memory is reserved apart from SGA for use by memory-intensive SQL operators such as sort, group-by, bitmapping and bitmap indexes.
 fast_start_mttr_target  0  0 Enables you to specify the number of seconds the database takes to perform crash recovery of a single instance.  Not supported by Vontu.
 sort_area_size  524288  65536 Specifies (in bytes) the maximum amount of memory Oracle will use for a sort.  Adjusted to facilitate Vontu related sorts.
 sga_target  1153433600   0 Set to anything other than 0 enables SGA autotuning.
 sga_max_size  1153433600  initial size of the SGA at startup. Specifies the maximum size of the SGA for the lifetime of the instance.
 streams_pool_size  0  0 Sets the minimum value of the Streams pool when non-zero.  If zero, then this is managed by Oracle's Automatic Memory Management.
 shared_pool_size  0  0 If set to a non-zero number, this is used to specify the size of the shared pool (in bytes).  The shared pool contains shared cursors, stored procedures, control structures, and other structures.  Set to zero, Oracle manages this using Automatic Storage Management (ASM) 
 large_pool_size  0  0 Specifies (in bytes) the size of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers.  None of these are used or supported by Vontu.
 java_pool_size  0  0 Specifies (in bytes) the size of the Java pool, from which the Java memory manager allocates most Java state during runtime execution.  This is not used or supported by Vontu. 
 db_cache_size  0  0 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). Set to 0 to take advantage to SGA autotuning.
 session_max_open_files  20  10 Specifies the maximum number of BFILEs that can be opened in any session.
 processes  1000  40 Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.  Set to 1000 by Vontu to mitigate the possibility of running out of processes.
 aq_tm_processes  1  0 Specifies the number of processes used to monitor Advanced Queuing (AQ) messages.  Vontu does not use Advanced Queuing.  Vontu sets this value to 1 to eliminate "WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected." messages in the alert.log
 star_transformation_enabled  FALSE  FALSE Used to modify Cost Optimization to take into account a start schema.
 timed_statistics  TRUE  TRUE Specifies whether or not statistics related to time are collected.  This is extremely useful when troubleshooting performacne issues. 
 compatible  10.2.0.4.0  10.0.0 Allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.  Vontu makes no use of features not present in 10.2.0.4. 
 query_rewrite_enabled  FALSE  TRUE Allows you to enable or disable query rewriting globally for the database. If enabled, the optimizer will cost a query with and without rewriting it, picking which ever costs less.  Vontu does not use this feature. 
 disk_asynch_io  FALSE  TRUE Controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). 
 sessions  1500  1.1*PROCESSES+5 Specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system.  Vontu sets sessions high in conjunction with the number of processes.
 job_queue_processes  10  0 Specifies the maximum number of processes that can be created for the execution of jobs. Vontu required 10 to take advantage of dbms_jobs in version 9 and going forward.
 nls_length_semantics  CHAR  BYTE Enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.  Vontu uses CHAR to consistently manage character information in coordination with CLOBs and globalization.
 undo_retention  10800  900 Specifies (in seconds) the low threshold value of undo retention.  Because LOB infomation does not use undo, the default retention time for LOBs is according to the undo_retention parameter, which is why Vontu has set it to 180 minutes or 3 hours.
 open_cursors  300  50 Specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.  Vontu sets this to 300 to mitigate the potential of running out of cursors.
 db_block_size  8192  8192 This is the default blocksize for 32-bit installation.  Vontu does not support multiple block sizes, nor does it support any size other than 8192.
 remote_login_passwordfile  EXCLUSIVE  SHARED This allows Oracle to check for a password file providing a method to authenicate outside of the database. 
 undo_management  AUTO  MANUAL Specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.
 db_file_multiblock_read_count  16  16 It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
 optimizer_index_caching  90  0 Allows for the adjustment of cost-based optimization behavior to favor nested loops joins and IN-list iterators.  Vontu has adjusted this to maximize performance and insure consistent optimizer behavior.
 optimizer_index_cost_adj  25  100 Enables the tuning of optimizer behavior, specifically access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.  Vontu has adjusted this to maximize performance and insure consistent optimizer behavior.
 characterSet  AL32UTF8  WE8ISO8859P1 Vontu REQUIRES the use of AL32UTF8 for globalization.  WE8ISO8859P1 is not a sub set of AL32UTF8.
 nationalCharacterSet  AL16UTF16   AL16UTF16 Vontu REQUIRES the use of AL16UTF16 for globalization. 
 archiveLogMode  FALSE  FALSE Supported for this feature not provided by Vontu Technical support. 
 SPfile  TRUE  TRUE Vontu implementation of Oracle makes use of the SPFILE.  If an init.ora file is desired, it can be created from the existing spfile. 
 control_files  in comment  in comment Locations of files necessary to Oracle database operation.  By default three files are created and must be maintained.  Default locations are:("{ORACLE_BASE.EN_US}\oradata\{DB_UNIQUE_NAME.EN_US}\CONTROL01.CTL", "{ORACLE_BASE.EN_US}\oradata\{DB_UNIQUE_NAME.EN_US}\CONTROL02.CTL", "{ORACLE_BASE.EN_US}\oradata\{DB_UNIQUE_NAME.EN_US}\CONTROL03.CTL")
 background_dump_dest  in comment  in comment Specifies the location for all tracefiles generated by Oracle's background processes as well as the alert_<DB>.log.  By Default it is:{ORACLE_BASE.EN_US}\admin\{DB_UNIQUE_NAME.EN_US}\bdump
 user_dump_dest  in comment  in comment Specifies the location for all tracefiles generated by user sessions. By Default it is:{ORACLE_BASE.EN_US}\admin\{DB_UNIQUE_NAME.EN_US}\udump
 core_dump_dest  in comment  in comment Specifies the location for all core dumps genereated by the Oracle kernel. By Default it is:{ORACLE_BASE.EN_US}\admin\{DB_UNIQUE_NAME.EN_US}\cdump 
 initParamFileName  in comment  in comment Specifies the location for the initialization parameter file, also known as the init.ora file.  By Default it is: {ORACLE_BASE.EN_US}\admin\{DB_UNIQUE_NAME.EN_US}\pfile\init.ora

 

Template for Vontu 8 (as of 9/26/2007):

pga_aggregate_target          419430400
fast_start_mttr_target        0
sort_area_size                524288
sga_max_size                  1153433600
sga_target                    1153433600
streams_pool_size             0
shared_pool_size              0
large_pool_size               0
java_pool_size                0
db_cache_size                 0
fast_start_mttr_target        0
session_max_open_files        20
processes                     1000
aq_tm_processes               1
star_transformation_enabled   FALSE
control_files        ('{ORACLE_BASE.EN_US}/oradata/{DB_UNIQUE_NAME.EN_US}/CONTROL01.CTL',
                      '{ORACLE_BASE.EN_US}/oradata/{DB_UNIQUE_NAME.EN_US}/CONTROL02.CTL',
                      '{ORACLE_BASE.EN_US}/oradata/{DB_UNIQUE_NAME.EN_US}/CONTROL03.CTL')
timed_statistics              TRUE
compatible                    10.2.0.3.0
query_rewrite_enabled         FALSE
disk_asynch_io                FALSE
sessions                      1500
background_dump_dest          {ORACLE_BASE.EN_US}/admin/{DB_UNIQUE_NAME.EN_US}/bdump
job_queue_processes           10
db_name                       {deliberately left blank}
nls_length_semantics          CHAR
user_dump_dest                {ORACLE_BASE.EN_US}/admin/{DB_UNIQUE_NAME.EN_US}/udump
dispatchers                   (PROTOCOL=TCP) (SERVICE={SID.EN_US}XDB)
audit_file_dest               {ORACLE_BASE.EN_US}/admin/{DB_UNIQUE_NAME.EN_US}/adump
db_domain                     {deliberately left blank}
undo_retention                10800
open_cursors                  300
db_block_size                 8192
undo_tablespace               UNDOTBS1
core_dump_dest                {ORACLE_BASE.EN_US}/admin/{DB_UNIQUE_NAME.EN_US}/cdump
remote_login_passwordfile     EXCLUSIVE
undo_management               AUTO
db_file_multiblock_read_count 16
optimizer_index_caching       90
optimizer_index_cost_adj      25