Oracle Real Application Clusters

ACTIVE_INSTANCE_COUNT

This parameter is used to configure a RAC environment in an active passive con- figuration. This parameter when set to 1 determines that the first instance that starts up becomes the primary instance. This parameter has no meaning in a three or more node RAC configuration.

 Oracle 9i   CLUSTER_DATABASE (Introduced in Oracle 9i Release 1)

Specifies if the clustered database option has been enabled.

 Oracle 9i   CLUSTER_DATABASE_INSTANCES (Introduced in Oracle 9i Release 1)

This specifies the number of instances par- ticipating in the clustered configuration. This parameter should be set identical on all participating instances. Oracle com- putes the default LARGE_POOL_SIZE based on the value of this parameter.

 Oracle 9i   CLUSTER_INTERCONNECTS (Introduced in Oracle 9i Release 1)

Specifies information regarding any additional cluster interconnects available to use.

CURSOR_SHARING

This parameter can have three possible values: FORCE: Forces statements to share a cursor in spite of minor differences SIMILAR: Statements that differ in some literals, however are otherwise identical to share a cursor EXACT: Allows only statements with identical text to share the same cursor

CURSOR_SPACE_FOR_TIME

Allows additional space allocation for cur- sors. Allowed values are: TRUE: Shared SQL areas are kept pinned in the shared pool, which enhances execu- tion of cursor FALSE: Shared SQL areas are deallocated from the library cache to make room for new SQL statements

DB_nK_CACHE_SIZE (Introduced in Oracle 9i Release 1)

Specifies the size of the cache for the nK buffers, where n = 2, 4, 8, 16, 32 and can be specified in kilobytes, megabytes, and gigabytes. This parameter is set based on the DB_BLOCK_SIZE parameter, which in turn is dependent on the O/S block size.

 Oracle 9i   DB_BLOCK_SIZE

Specifies the size of the Oracle database block. Typically, the block size values are 2048 and 4096. In a RAC implementation this parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. However, the FREELISTS and FREELIST GROUP parameter do not apply if the locally man- aged storage option is used.

 Oracle 9i   DB_CACHE_ADVICE (Introduced in Oracle 9i Release 1)

Enables or disables statistics gathering used for predicting behavior with different cache sizes through GV$DB_CACHE_ADVICE view. Valid values are: OFF: Advisory is turned off READY: Advisory is turned off, but the memory remains allocated ON: Advisory is turned on Turning on Advisory incurs CPU and memory overheads.

 Oracle 9i   DB_CACHE_SIZE (Introduced in Oracle 9i Release 1)

Specifies the size of the default buffer pool for buffers with the primary block size. Note: The DB_CACHE_SIZE parameter replaces the DB_BLOCK_BUFFERS para- meter used in previous versions of Oracle.

DB_MULTIBLOCK_READ_COUNT

Specifies the maximum number of blocks read in one I/O operation during a sequen- tial scan. In a RAC environment, setting this value to an optimal value is critical, else this affects the GCS resource traffic across the cluster interconnect. Idle values for an OLTP system are in the range of 4 to 16.

 Oracle 9i   DB_KEEP_CACHE_SIZE (Introduced in Oracle 9i Release 1)

Specifies the size of the keep buffer pool. The size of the buffers in the keep buffer pool is of the primary block size.

DB_NAME

Specifies a database identifier of up to 8 characters in length. It should contain the same value that was specified during the database creation. In a RAC environment this is the shared physical database name and not the instance name.

DB_RECYCLE_CACHE_SIZE (Introduced in Oracle 9i Release 1)

Specifies the size of the recycle buffer pool.

DB_WRITER_PROCESSES

Specifies the initial number of database writer processes for an instance. This is useful for systems that modify data heavily. In Oracle 9i this value can be of range 1 to 20 and is displayed as DBWn processes (DBW0 to DBW9) and (DBWa to DBWj).

DBWR_IO_SLAVES

This parameter is helpful on systems that have only one DBW0 process and specifies the number of I/O server processes used by the DBW0 process. The DBWn process should be preferred over the I/O slave processes.

ENQUEUE_RESOURCES

Sets the number of resources that can be concurrently locked by the lock manager. If no value is specified, Oracle allocates a value based on the SESSIONS parameter. Oracle will automatically allocate additional enqueues from the shared pool as needed if the number speci- fied by ENQUEUE_RESOURCES is exceeded. In a RAC environment the resource usage could be verified by querying the GV$RESOURCE_LIMIT view.

 Oracle 9i   FAST_START_MTTR_TARGET (Introduced in Oracle 9i Release 1)

Specifies the number of seconds the database takes to perform crash recovery of a single instance. This parameter replaces FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL. However, setting these parameters in Oracle 9i will override the FAST_START_MTTR_TARGET parameter.

GC_FILES_TO_LOCKS

This parameter has been provided for backward compatibility, when set it dis- ables the cache fusion processing in a RAC environment.

HASH_JOIN_ENABLED

Specifies whether the optimizer should consider using a hash join as a join method. If set to TRUE, the optimizer compares the cost of a hash join with other types of joins and chooses hashing if it gives the lowest cost. The parameter should be set to TRUE in a data-warehousing environment.

HASH_AREA_SIZE

Specifies the maximum amount of memory in bytes to be used for hash joins. This parameter is more relevant to parallel execution operations. The default value for this parameter is derived from twice the SORT_AREA_SIZE.

INSTANCE_GROUPS

This parameter is specific to a RAC imple- mentation when the parallel mode of operation is used. It is used in conjunction with the PARALLEL_INSTANCE_GROUP parameter. It specifies one or more instance groups and assigns the current instance to those groups.

INSTANCE_NAME

When two or more instances can be con- figured in a RAC environment against common shared physical database, this parameter specifies the unique name of this instance. It should be noted that only in a RAC configuration is there a distinction of unique names between the database and the instance. In a single-instance config- uration, the instance name is often the same as the database name.

INSTANCE_NUMBER

Specifies a unique number that maps the instance to one free list group for each database object created with storage parameter FREELIST GROUPS.

LOG_BUFFER

Specifies the amount of memory that Oracle uses when buffering redo entries to a redo log file. A larger value for LOG_BUFFER will reduce redo log file I/O, especially when there are long or a large number of trans- actions. On busy systems a value of 65,536 or higher is reasonable.

LOG_CHECKPOINT_INTERVAL

Specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. The number refers to physical operating system blocks and not database blocks.

LOG_CHECKPOINT_TIMEOUT

Specifies the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log occurred.

LOCK_CHECKPOINTS_TO_ALERT

Setting the value of this parameter to TRUE helps monitor the checkpoint activ- ity. Every time a checkpoint occurs it writes entries to the alert log file.

 Oracle 9i   LOG_PARALLELISM (Introduced in Oracle 9i Release 2)

Specifies the level of concurrency for redo allocation within Oracle. On systems that have 16 or more processors and there is a very high contention on the redo alloca- tion latch, setting the value of this parameter between 2 and 8 would help increase the throughput of certain update-intensive workloads.

MAX_COMMIT_PROPAGATION_DELAY

This parameter is specific to RAC and is used to specify the maximum amount of time allowed before the SCN held in the SGA of an instance is refreshed by the LGWR process.

OPTIMIZER_DYNAMIC_SAMPLING

Controls the level of dynamic sampling performed by the optmizer.

OPTIMIZER_INDEX_CACHING

Setting this parameter to an appropriate value helps adjust the behavior of cost- based optimizer to favor nested loop joins and IN-list iterators.

OPTIMIZER_INDEX_COST_ADJ

Setting this parameter to an appropriate value helps tune the optimizer behavior for access path selection to be more or less index friendly, i.e., make the optimizer to use the index access path over a full table scan.

OPTIMIZER_MAX_PERMUTATIONS

Restricts the number of permutations of the tables the optimizer will consider in queries with joins. The default value for this parameter has been reduced in Oracle 9i from 80,000 to 2000.

OPTIMIZER_MODE

This parameter establishes the default behavior for choosing an optimization approach for the instance. RULE: Chooses the rule-based approach for all SQL statements regardless of the presence of statistics. Note: This is a desupported option in Oracle 10g. CHOOSE: The optimizer chooses between cost-based approach and a rule-based approach based on whether statistics are available. Note: This is a desupported option in Oracle 10g. FIRST_ROWS_n: The optimizer uses a cost-based approach regardless of the pre- sence of statistics and optimizes with a goal of best response time to return the first n rows. FIRST_ROWS: The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. ALL_ROWS: The optimizer uses a cost- based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput. Note: ALL-ROWS is the default OPTIMIZER-MODE option starting with Oracle 10g.

ORACLE_TRACE_ENABLE

Setting this parameter to a value of TRUE helps perform trace collection of server event data by using: Oracle trace manager Oracle command line interface, or By specifying a collection name in the ORACLE_TRACE_COLLECTION_NAME parameter

 Oracle 9i   PGA_AGGREGATE_TARGET (Introduced in Oracle 9i Release 1)

Specifies the target aggregate PGA memory available to all server processes attached to the instance. This parameter enables the automatic sizing of SQL working areas used by memory-intensive SQL operators such as sort, group by, hash-join, bitmap merge, and bitmap create. Note: Oracle recommends using this para- meter over the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE by enabling automatic sizing of SQL working areas. SORT_AREA_RETAINED_SIZE is retained for backward compatibility.

SERVICE_NAMES

Specifies one or more names for the data- base service to which the instance is con- nected. In a RAC implementation all instances should have this parameter set to at least one common value. This parameter plays an important role for implementation of the TAF option.

SESSION_CACHED_CURSORS

Specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursors for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and would not be required to reopen the cursor.

 Oracle 9i   SGA_MAX_SIZE (Introduced in Oracle 9i Release 1) STAR_TRANSFORMATION_ENABLED

Specifies the maximum size of the SGA for the lifetime of the instance. Setting the value of this parameter to TRUE determines whether a cost-based query transformation will be applied to start queries.

 Oracle 9iR2   STATISTICS_LEVEL (Introduced in Oracle 9i Release 2)

This parameter sets the statistics collection level of the database. The possible values for this parameter are: ALL TYPICAL BASIC

THREAD

This parameter is specifically used in a RAC implementation and specifies the number of the redo threads to be used by an instance.

 Oracle 9i   TIMED_OS_STATISTICS (Introduced in Oracle 9i Release 1)

Specifies the interval at which Oracle col- lects operating system statistics when a request is made from the client to the server or when a request completes. Note: Collecting O/S level statistics is very expensive and should be done with cau- tion under controlled testing.

TIMED_STATISTICS

Specifies whether or not statistics related to time are collected. When the value of this parameter is set to TRUE, the statis- tics are collected and stored in trace files or displayed in the GV$SESSTATS or GV$SYSSTATS views.

 Oracle 9i   TRACE_ENABLED (Introduced in Oracle 9i Release 1)

This parameter controls tracing of the exe- cution history or code path of Oracle. This parameter by default is enabled and helps retain diagnostics for the entire cluster.

TRANSACTION_AUDITING

This parameter by default is enabled and generates a special redo record that contains the user login name, username, the session ID, some O/S information, and client information. If set to FALSE, no redo record will be generated.

TRANSACTIONS

Specifies the maximum number of concur- rent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated.

 Oracle 9i   UNDO_MANAGEMENT (Introduced in Oracle 9i Release 1)

Specifies which undo space management mode the system should use. The values are: AUTO: Instance starts automatic undo management MANUAL: Undo management is managed by the DBAs and the space is allocated externally as rollback segments. MANUAL is the default value

 Oracle 9i   UNDO_RETENTION (Introduced in Oracle 9i Release 1)

Specifies the amount of committed undo information to retain in the database. Oracle allocates the required space to retain the data specified by this parameter. However, if additional undo space is requi- red by an active transaction the space allo- cated for retention purposes will be used.

 Oracle 9i   UNDO_TABLESPACE (Introduced in Oracle 9i Release 1)

Specifies the undo tablespace to be used when an instance starts up. This parameter is valid only when automatic undo man- agement has been enabled.

 Oracle 9i   WORKAREA_SIZE_POLICY (Introduced in Oracle 9i Release 1)

Specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned. Possible values are: AUTO: Work areas used by memory- intensive operators are sized automati- cally, based on the PGA memory used by the system, the PGA memory is set using PGA_AGGREGATE_TARGET param- eter. This value can only be used when the PGA_AGGREGATE_TARGET is defined MANUAL: Work area sizing is done manually

 Oracle 10g   CREATE_STORED_OUTLINES (Introduced in Oracle 10g Release 1)

Determines whether Oracle automatically creates and stores an outline for each query submitted during the session. Possible values are: TRUE: Enables automatic outline creation for subsequent queries in the same session. FALSE: Disables automatic outline crea- tion during the session. <category_name> Enables the beha- vior of TRUE except that any outline created during the session is stored in the category_name category.

 Oracle 10g  DB_FLASHBACK_RETENTION_TARGET (Introduced in Oracle 10g Release 1)

Specifies the upper limit (in minutes) on how far back in time the database may be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the recovery area.

 Oracle 10g   DB_RECOVERY_FILE_DEST (Introduced in Oracle 10g Release 1)

Specifies the default location for the recovery area. The recovery area contains multiplexed copies of the current control files and online redo logs, as well as archived redo logs, flashback logs and RMAN backups.

 Oracle 10g    DB_RECOVERY_FILE_DEST_SIZE (Introduced in Oracle 10g Release 1)

Specifies the hard limit on the total space to be used by target database recovery files.

 Oracle 10g   DDL_WAIT_FOR_LOCKS (Introduced in Oracle 10g Release 1)

Specifies whether DDL statements should wait and complete instead of timing out if the statement is not able to acquire all required locks. Possible values are:

TRUE: DDL statements wait until the statement acquires all required locks. FALSE: DDL statements time out if the statement cannot obtain all required locks.

 Oracle 10g   INSTANCE_TYPE (Introduced in Oracle 10g Release 1)

Specifies whether the instance is a data- base instance or an automated storage management instance. Possible values are: RDBMS: The instance is a database instance.

OSM: The instance is an Automated Storage Management instance.

 Oracle 10g   STREAMS_POOL_SIZE (Introduced in Oracle 10g Release 1)

Specifies the size of the streams pool, from which memory is allocated for Streams. If this parameter is not defined or is set to ZERO then 10% of the shared pool is allocated for streams.