INIT.ORA PARAMETER NOTES

init.ora

INIT.ORA PARAMETER NOTES:

Parameter - Init.ora parameter name

Value - Current value

Is Default - Whether the parameter value is the default

Session Modifiable - TRUE=the parameter can be changed with ALTER SESSION / FALSE=the parameter cannot be changed with ALTER SESSION

System Modifiable - IMMEDIATE=the parameter can be changed with ALTER SYSTEM / DEFERRED=the parameter cannot be changed until the next session / FALSE=the parameter cannot be changed with ALTER SYSTEM

Is Modified - Indicates how the parameter was modified. If an ALTER SESSION was performed, the value will be MODIFIED. If an ALTER SYSTEM (which will cause all the currently logged in sessions' values to be modified) was performed the value will be SYS_MODIFIED.

ALWAYS_ANTI_JOIN - Set the parameter to HASH to allow parallel processing of the NOT IN clause (a real resource hog). If you set the parameter to NESTED_LOOPS, the NOT IN is evaluated the same way it was as Oracle7.2 and earlier (which will not please you). MERGE uses the SORT_MERGE algorithm to process the NOT IN, which is faster than NESTED_LOOPS but not as fast as HASH.
DEFAULT: NULL
VALUES: NESTED_LOOPS, MERGE, HASH

COMPATIBLE - This parameter specifies the release with which the Oracle Server must maintain compatibility. Set to the release of Oracle RDBMS that you are currently running to take full advantage of any new features. Allows you to use the current version while at the same time guaranteeing backward compatibility with an earlier release. This is in case it becomes necessary to revert to the earlier release. Some features of the current release may be restricted. Oracle 7.1.5 introduced the ability to bypass the buffer cache (direct reads) for table scans and sorts (COMPATIBLE=7.1.5). Oracle 7.3 introduced temporary tablespaces that improve the performance of sorts and hash joins (COMPATIBLE=7.3.1)
DEFAULT: NULL
VALUES: Any Oracle RDBMS (e.g., Oracle7.2.3)

CONTROL_FILES - Specifies one or more names of control files, separated by commas. Oracle Corporation recommends using multiple files on different devices or mirroring the file at the OS level.
DEFAULT: OS Dependent
VALUES: 1 - 8 filenames

CURSOR_SPACE_FOR_TIME - Turns waiting for application cursors on (TRUE) or off (FALSE). If it is set to TRUE, the database uses more space for cursors to save time. It affects both the shared SQL area and the client's private SQL area. Shared SQL areas are kept pinned in the shared pool when this parameter's value is TRUE. As a result, shared SQL areas are not aged out of the pool as long as there is an open cursor that references them. Because each active cursor's SQL area is present in memory, execution is faster. Because the shared SQL areas never leave memory while they are in use, however, you should set this parameter to TRUE only when the shared pool is large enough to hold all open cursors simultaneously. Setting this parameter to TRUE also retains the private SQL area allocated for each cursor between executes instead of discarding it after cursor execution. This saves cursor allocation and initialization time.
DEFAULT: FALSE
VALUES: TRUE/FALSE

DB_BLOCK_BUFFERS - Sets the size, in database blocks, of the data block buffer cache in the SGA. The larger the data block buffer cache is, the more memory will be available for sharing data already in memory amoung users - reducing the need for physical reads. You can determine the effectiveness of the data block buffer cache by measuring the hit ratio of the database.
DEFAULT: 32 Buffers
VALUES: 4 to unlimited

DB_BLOCK_SIZE - Defined when the database is created; it determines the size of each block within the database. You CANNOT change the block size of an existing database; the only method available for increasing the block size is to perform a full database Export, recreate the database with a different DB_BLOCK_SIZE value, and Import the database. In most environments, the default value for DB_BLOCK_SIZE is 2048 bytes (2KB). If you operating environment permits, you should increase the value to 4KB, 8KB, or higher. The performance gains obtained by using a larger block size are significant for both OLTP and batch applications. In general, each doubling of the database block size will reduce the time required for I/O-intensive batch operations be around 40 percent. As the database block size increases, your overall memory requirements may increase.
DEFAULT: OS Dependent
VALUES: 1024-32768

DB_FILE_MULTIBLOCK_READ_COUNT parameter helps determine how many blocks are read at a time by the database during full table scans. You should set the DB_BLOCK_MULTIBLOCK_READ_COUNT parameter to a value that takes the greatest advantage of the operating system's buffer during reads. For example, suppose the OS buffer available for reads is 64KB in size. If your database block size is 2KB, you should set DB_FILE_MULTIBLOCK_READ_COUNT to 32; if the block size is 4KB, set DB_FILE_MULTIBLOCK_READ_COUNT to 16. In some operating systems, the available buffer size is configurable.
DEFAULT: OS Dependent but usually a function of DB_BLOCK_BUFFERS and PROCESSES
VALUES: OS Dependent (normally 1 to either the DB_BLOCK_BUFFERS / 4 or the OS maximum I/O size / DB_BLOCK_SIZE)

DB_FILE_SIMULTANEOUS_WRITES - Number of write batches written by the database writers. If you are experiencing problems getting buffers written to your disks during checkpoints, try increasing the value. It is applicable only to the operation systems that support more than one write to your disk devices.
DEFAULT: 4
VALUES: 1 to 24

DB_WRITERS - Once the database block size and memory areas are properly established, tune the way in which Oracle writes data from memory. If your OS permits using multiple DBWR processes, set a value greater than '1' for the DB_WRITERS parameter. If you start more than on DBWR process, you may be able to reduce contention for blocks within the database block buffer cache. If there is only one DBWR process available, it becomes a possible bottleneck during I/O operations, even if the data is distributed among multiple devices.

ENQUEUE_RESOURCES - An enqueue is a sophisticated locking mechanism which permits several concurrent processes to share known resources to varying degrees. Any object which can be used concurrently can be protected with enqueues. For example, Oracle allows varying levels of sharing on tables: two processes can lock a table in share mode or in share update mode.
DEFAULT: Derived
VALUES: 10 - 65535

HASH_AREA_SIZE - This parameter specifies the maximum amount of memory, in bytes, to be used for the hash join. If this parameter is not set, its value defaults to twice the value of the SORT_AREA_SIZE parameter. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: 2 times the value of SORT_AREA_SIZE
VALUES: Any integer

HASH_JOIN_ENABLED - This parameter enables or disables the hash join feature. Set this parameter to TRUE to use hash joins. Set this parameter to FALSE to disable hash joins. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: TRUE
VALUES: TRUE/FALSE

HASH_MULTIBLOCK_IO_COUNT - This parameter specifies how many blocks a hash join reads and writes at once. When operating in multi-threaded server mode, however, this parameter is ignored (that is, the default value of 1 is used even if you set the parameter to another value). The value of DB_BLOCK_SIZE multiplied by the value of HASH_MULTIBLOCK_IO_COUNT should be less than 64 KB. This parameter strongly affects performance because it controls the number of partitions into which the input is divided.
DEFAULT: 1
VALUES: 1 - (65,536/DB_BLOCK_SIZE)

LOG_ARCHIVE_BUFFER_SIZE - The size of each archival buffer, in redo log blocks (operating system blocks). The default should be adequate for most applications. This parameter, with LOG_ARCHIVE_BUFFERS, can tune archiving so that it runs as fast as necessary, but not so fast that it reduces system performance.
DEFAULT: OS Dependent
VALUES: 1 - OS Dependent (in O/S blocks)

LOG_ARCHIVE_DEST - Applicable only if using the redo log in ARCHIVELOG mode. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported on all operating systems.) The value cannot be a raw partition. To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the Server Manager command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination.
DEFAULT: OS Dependent
VALUES: Any valid path or device name, except raw partitions

LOG_ARCHIVE_START - Applicable only when you use the redo log in ARCHIVELOG mode, LOG_ARCHIVE_START indicates whether archiving should be automatic or manual when the instance starts up. TRUE indicates that archiving is automatic. FALSE indicates that the DBA will archive filled redo log files manually. (The Server Manager command ARCHIVE LOG START or STOP overrides this parameter.) In ARCHIVELOG mode, if all online redo log files fill without being archived, an error message is issued, and instance operations are suspended until the necessary archiving is performed. This delay is more likely if you use manual archiving. You can reduce its likelihood by increasing the number of online redo log files.
DEFAULT: FALSE
VALUES: TRUE/FALSE

LOG_BUFFER Sets the size, in bytes, of the redo log buffer area in the SGA. The default is set to four times the maximum database block size for the OS. For an OLTP application in which many users perform transactions, the LOG_BUFFER parameter needs to be increased beyond its default value. If the 'redo log space requests' statistic in V$SYSSTAT is non-zero, you should increase LOG BUFFER to support the transaction load without forcing transactions to wait for accesses to the redo log buffer.
DEFAULT: OS Dependent
VALUES: OS Dependent

LOG_CHECKPOINT_INTERVAL - The number of newly filled redo log file blocks needed to trigger a checkpoint. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. If the value exceeds the actual redo log file size, checkpoints occur only when switching logs. The number of times DBWR has been notified to do a checkpoint for a given instance is shown in the cache statistic dbwr checkpoints, which is displayed in the System Statistics Monitor of the Server Manager. Additional cache statistics include background checkpoints started and background checkpoints completed.
DEFAULT: OS Dependent
VALUES: Unlimited (OS blocks, not database blocks)

LOG_SIMULTANEOUS_COPIES - The maximum number of redo buffer copy latches available to write log entries simultaneously. For good performance, you can have up to twice as many redo copy latches as CPUs. For a single-processor system, set to zero so that all log entries are copied on the redo allocation latch. If this parameter is set to 0, redo copy latches are turned off, and the parameters LOG_ENTRY_PREBUILD_THRESHOLD and LOG_SMALL_ENTRY_MAX_SIZE are ignored. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: CPU_COUNT
VALUES: 0 - unlimited

OPTIMIZER_MODE - When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.) You can set the goal for cost-based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans that minimize response time. ALL_ROWS causes the optimizer to choose execution plans that minimize total execution time. The goal of cost-based optimization can also be set within a session by using ALTER SESSION SET OPTIMIZER_MODE.
DEFAULT: CHOOSE
VALUES: RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

PARALLEL_MAX_SERVERS - Each instance must either have a value of zero or the same value as the other instances. Maximum number of query servers or parallel recovery processes for an instance.
DEFAULT: OS Dependent
VALUES: 0 - 256

PARALLEL_MIN_SERVERS - Minimum number of query server processes for an instance. This is also the number of query server processes Oracle creates when the instance is started.
DEFAULT: 0
VALUES: 0 - PARALLEL_MAX_SERVERS

PROCESSES - For a multiple-process operation, this parameter specifies the maximum number of operating system user processes that can simultaneously connect to an Oracle7 Server. Should include up to 6 for the background processes (or more if GC_LCK_PROCS is non-zero or if you use the dispatcher configuration) plus one for login; so a value of 20 would permit 13 or 14 concurrent users. The default values of DB_FILE_MULTIBLOCK_READ_COUNT and SESSIONS are derived from PROCESSES. If you alter the value of PROCESSES, you may want to adjust the values of these derived parameters.
DEFAULT: 25
VALUES: 6 - OS Dependent

ROLLBACK_SEGMENTS - One or more rollback segments to allocate by name to this instance. If ROLLBACK_SEGMENTS is not overridden, an instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated from the ratio TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT).
DEFAULT: NULL (the instance uses public rollback segments by default if you do not specify this parameter
VALUES: Any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM

ROW_CACHE_CURSORS - The number of cached recursive cursors used by the row cache manager for selecting rows from the data dictionary. The default value is sufficient for most systems.
DEFAULT: 10
VALUES: 10 - 3300

SHARED_POOL_RESERVED_MIN_ALLOC - This parameter controls allocation of reserved memory. Memory allocations larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle Server will allow fewer allocations from the reserved list and will request more memory from the shared pool list.
DEFAULT: 5000
VALUES: 5000 - SHARED_POOL_RESERVED_SIZE (in bytes)

SHARED_POOL_RESERVED_SIZE - This parameter controls the amount of SHARED_POOL_SIZE reserved for large allocations. SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC to create a reserved list. The default value of 0 represents no reserved shared pool area. Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. The amount of operating system memory, however, may constrain the size of the shared pool. In general, you should set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. For most systems, this value will be sufficient if you have already tuned the shared pool.
DEFAULT: 0
VALUES: From SHARED_POOL_RESERVED_MIN_ALLOC to one half of SHARED_POOL_SIZE (in bytes)

SHARED_POOL_SIZE - Sets the size, in bytes, of the shared pool in the SGA. If your application is OLTP-oriented, and you use packages and other procedural objects, you'll need a large shared SQL area. In environments using a large number of procedural objects, the size of your shared pool may exceed your data block buffer cache. If you have many users, you should increase the SHARED_POOL_SIZE parameter everytime you increase the DB_BLOCK_BUFFERS parameter.
DEFAULT: 3,500,000
VALUES: 300 Kbytes - OS Dependent

SORT_AREA_RETAINED_SIZE - This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory retained after a sort. This memory is released back to the PGA, not to the operating system, after the last row is fetched from the sort space. If a sort requires more memory, a temporary segment is allocated and the sort becomes an external (disk) sort. The maximum amount of memory to use for the sort is then specified by SORT_AREA_SIZE instead of by this parameter. Larger values permit more sorts to be performed in memory. However, multiple sort spaces of this size may be allocated. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, though, additional concurrent sorts are required. Each sort occurs in its own memory area, as specified by SORT_AREA_RETAINED_SIZE.
DEFAULT: The value of SORT_AREA_SIZE
VALUES: From the value equivalent to one database block to the value of SORT_AREA_SIZE

SORT_AREA_SIZE - This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. After the sort is complete and all that remains to do is to fetch the rows out, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched out, all memory is freed. The memory is released back to the PGA, not to the operating system. Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time. The default is usually adequate for most database operations. Only if very large indexes are created might you want to adjust this parameter. For example, if one process is doing all database access, as in a full database import, then an increased value for this parameter may speed the import, particularly the CREATE INDEX statements.
DEFAULT: OS Dependent
VALUES: The value equivalent to two database blocks (minimum)

SORT_DIRECT_WRITES - SORT_DIRECT_WRITES can improve sort performance if memory and temporary space are abundant on your system. When set to the default value of AUTO, and if the value of SORT_AREA_SIZE is greater than ten times the buffer size, SORT_DIRECT_WRITES automatically configures the SORT_WRITE_BUFFER_SIZE and SORT_WRITE_BUFFERS parameters. When SORT_DIRECT_WRITES is in AUTO mode, SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE have no effect. When SORT_DIRECT_WRITES is set to TRUE, each sort allocates additional buffers in memory to write directly to disk. When SORT_DIRECT_WRITES is set to FALSE, the sorts that write to disk write through the buffer cache.
DEFAULT: AUTO
VALUES: AUTO/TRUE/FALSE

SORT_READ_FAC - SORT_READ_FAC is a unitless ratio that describes the amount of time to read a single database block divided by the block transfer rate.
DEFAULT: OS Dependent

SORT_WRITE_BUFFER_SIZE - This parameter sets the size of the sort buffer when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
DEFAULT: 32768
VALUES: Any integer

SORT_WRITE_BUFFERS - This parameter sets the number of sort buffers when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
DEFAULT: 2
VALUES: Any integer

SQL_TRACE - Disables or enables the SQL trace facility. Setting this parameter to TRUE provides information on tuning that you can use to improve performance. Because the SQL trace facility causes system overhead, you should run the database with the value TRUE only for the purpose of collecting statistics. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: FALSE
VALUES: TRUE/FALSE

TIMED_STATISTICS - By default (when set to FALSE), the Server Manager statistics related to time (from the buffer manager) always are zero and the Server can avoid the overhead of requesting the time from the operating system. To turn on statistics, set the value to TRUE. Should normally be set to FALSE.
DEFAULT: FALSE
VALUES: TRUE/FALSE

USER_DUMP_DEST - The pathname for a directory where the server will write debugging trace files on behalf of a user process.
DEFAULT: OS Dependent
VALUES: Valid local pathname, directory, or disk

select NAME,
 VALUE,
 ISDEFAULT,
 ISSES_MODIFIABLE,
 ISMODIFIED
from  v$parameter
order   by NAME