init.ora Parameters

The init<Oracle SID>.ora file is the startup parameter file that Oracle uses to size the SGA, determine ARCHIVELOG mode, and so on. The rest of this section is a list of the more important init.ora parameters and a brief description of what each affects and how it should be set. Listing 24.3 is an example init.ora file from an Oracle 8.0.3.

Listing 24.3. Example init.ora parameter file.

db_name = oracle

db_files = 20

control_files = (/ora8/v8.0.3/dbs/initORA8.ora,

/disk2/ora8_control_file/initORA8.ora)



db_file_multiblock_read_count =  8



db_block_buffers =  200



shared_pool_size =  50000



log_checkpoint_interval = 10000



processes =  150



dml_locks =  200



log_buffer =  8192



sequence_cache_entries =  10



sequence_cache_hash_buckets =  10

# audit_trail = true t

# timed_statistics = true



# log_archive_start = true

background_dump_dest=/ora8/v8.0.3/trace

user_dump_dest=/ora8/v8.0.3/trace



db_block_size = 2048

BACKGROUND_DUMP_DEST is the directory location where the alert.log and any trace files will be created.

CHECKPOINT_PROCESS is set to true or false depending on whether checkpointing is desired. Checkpointing creates a quiet point in the database when all buffered infor-mation is written to disk. A mark is placed in the archive log files that indicates a checkpoint happened. Checkpointing happens at archive log file switches or when the number of blocks defined in log_checkpoint_interval is reached.

CONTROL_FILES lists the Oracle-maintained control files. I highly recommend listing at least three, each on separate disk drives. To create additional control files, simply copy the original to the new locations with the Oracle database in a shutdown state. List all control files with their full path and they will be automatically maintained by the Oracle RDBMS.

DB_BLOCK_BUFFERS is the size of the buffer cache in the SGA.

DB_BLOCK_SIZE is the size of the database blocks. It is used in the CREATE DATABASE SQL statement and appears here basically for reference.

DB_FILES determines how many operating-system files Oracle can have access to at any one time. The online redo logs are part of this number as well. This parameter does cause SGA space to be used.

DB_FILE_MULTI_BLOCK_READ_COUNT is how many blocks will be read with each read request. This value×DB_BLOCK_SIZE should equal 64KB.

HASH_JOIN_ENABLE defaults to true, enabling memory hash joins (discussed in the “SQL-Level Tuning” section later in this chapter).

HASH_AREA_SIZE defaults to twice the SORT_AREA_SIZE.

HASH_MULTI_BLOCK_IO_COUNT defaults to DB_FILE_MULTI_BLOCK_READ_COUNT.

LARGE_POOL_SIZE defaults to 0 and can be specified in KB or MB.

LARGE_POOL_MIN_ALLOC is the minimum size of the large pool.

LOG_ARCHIVE_BUFFERS and LOG_ARCHIVE_BUFFER_SIZE deal with the writing of the online redo logs to the archive logs. These parameters may need to be increased only if archiving directly to tape devices and the redo log buffers are waiting on archive log buffers to become available.

LOG_ARCHIVE_DEST is where the archive logs are created if log archive mode is enabled.

LOG_ARCHIVE_FORMAT determines the naming convention used for the archive logs created.

LOG_ARCHIVE_START enables or disables the log archive mode.

LOG_BUFFER determines the size of the redo log buffer in the SGA.

LOG_CHECKPOINT_INTERVAL is used to determine the frequency of checkpointing. This number is set to the number of redo buffer blocks processed before taking a checkpoint.

MAX_ROLLBACK_SEGMENTS identifies the maximum number of rollback segments that can be kept available for user processes.

OPTIMIZER_GOAL is used by the cost-based optimizer and can globally force an applications default behavior by specifying FIRST_ROWS or ALL_ROWS.

OPTIMIZER_MODE defaults to COST and will use the cost-based optimizer if there are collected statistics. Changing this parameter to RULE will use the rule based optimizer unless SQL hints are specified. If CHOOSE is specified and any of the objects involved have statistics, Oracle will use the cost-based optimizer.

ROLLBACK_SEGMENTS identifies rollback segments by name, assigning them to this Oracle environment.

SHARED_POOL_SIZE is the size of the shared pool in the SGA.

SHARED_POOL_RESERVED_SIZE is used for packages.

SHARED_POOL_RESERVED_MIN_ALLOC is the minimum memory allotment.

SORT_AREA_SIZE is the size of the sort area in the SGA.

SQL_TRACE is used to collect tuning statistics from the Oracle environment. It is set to false by default.

TIMED_STATISTICS is used by some v$ table statistics that are relative to clock time. It is set to false by default.