Release 8.0
A54645_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Server Reference Release 8.0 A54645_01 |
|
This chapter contains detailed descriptions of the database initialization parameters.
The following topics are included in this chapter:
The initialization parameter file is a text file that contains a list of parameters and a value for each parameter. The file should be written in the client's default character set. Specify values in the parameter file which reflect your installation.
The following are sample entries in a parameter file:
PROCESSES = 100
OPEN_LINKS = 12
GLOBAL_NAMES = TRUE
The name of the parameter file varies depending on the operating system. For example, it can be in mixed case or lowercase, or it can have a logical name or a variation on the name INIT.ORA. As the database administrator, you can choose a different filename for your parameter file. There is also an INITDW.ORA file which contains suggested parameter settings for data-warehouses and data-marts.
See your Oracle operating system-specific documentation for the default locations and filenames for these parameter files. The INIT.ORA file is what the Oracle Server reads for its parameter information upon startup.
Sample parameter files are provided on the Oracle Server distribution medium for each operating system. A distributed sample file is sufficient for initial use, but you will want to make changes in the file to tune the database system for best performance. Any changes will take effect the next time you completely shut down the instance and then restart it.
Database administrators can use initialization parameters to do the following:
Many initialization parameters can be fine-tuned to improve database performance. Other parameters should never be altered or only be altered under the supervision of Oracle Corporation Worldwide Support staff.
Note: If you are using Trusted Oracle, see the Trusted Oracle Server Administrator's Guide for information about additional initialization parameters.
This section describes several aspects of setting parameter values in the parameter file. The following topics are included:
The following rules govern the specification of parameters in the parameter file:
PROCESSES = 100 SAVEPOINTS = 5 OPEN_CURSORS = 10
ROLLBACK_SEGMENTS = (SEG1, SEG2, SEG3, SEG4, SEG5)
Or, you can enter multiple values without parentheses and commas. For example:
ROLLBACK_SEGMENTS = SEG1 SEG2 SEG3 SEG4 SEG5
Either syntax is valid.
ROLLBACK_SEGMENTS = (SEG1, SEG2, \
SEG3, SEG4, SEG5)
NLS_TERRITORY = "CZECH REPUBLIC"
Suggestion: It is advisable to list parameters in alphabetical order in the parameter file. That makes it easier to find them and helps ensure that each parameter is specified only once. If a parameter is specified consecutively "n" number of times, it is associated with a list of "n" values. If a parameter is specified "n" number of times, but not all consecutively, then the last consecutive list of values is used.
See your operating system-specific Oracle documentation for more information on parameter files.
If a parameter value contains a special character, then either the special character must be preceded by an escape character or the entire parameter value must be contained in double quotes. For example:
DB_DOMAIN = "JAPAN.ACME#.COM"
or
DB_DOMAIN = JAPAN.ACME\#.COM
Table 1-1 lists the special characters.
Wherever a special character must be treated literally in the initialization parameter file, it must be either prefaced by the escape character or the entire string that contains the special character must be surrounded by single or double quotes.
As described in "Rules" on page 1-3, the escape character (\) can also signify a line continuation. If the escape character is followed by an alphanumeric character, then the escape character is treated as a normal character in the input. If it is not followed by an alphanumeric, then the escape character is treated either as an escape character or as a continuation character.
Quotes can be nested in any of three ways. One method is to double the quotes in the nested string. For example:
NLS_DATE_FORMAT = """Today is"" MM/DD/YYYY"
Another method is to alternate single and double quotes. For example:
NLS_DATE_FORMAT = '"Today is" MM/DD/YYYY'
The third method is to escape the inner quotes. For example:
NLS_DATE_FORMAT = "\"Today is\" MM/DD/YYYY"
To change a parameter's value, edit the parameter file. The next time the instance starts, it uses the new parameter values in the updated parameter file. Note that the change does not take effect until the instance is shut down and restarted.
Some initialization parameters are dynamic, that is, they can be modified using the ALTER SESSION, ALTER SYSTEM, or ALTER SYSTEM DEFERRED commands while an instance is running.
Use this syntax for dynamically altering the initialization parameters:
ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value
ALTER SYSTEM SET parameter_name = value DEFERRED
Whenever a dynamic parameter is modified using the ALTER SYSTEM, or ALTER SYSTEM DEFERRED command, then the command that modifies the parameter is also recorded in the alert log.
The ALTER SESSION command changes the value of the parameter specific to the session that invokes this command. The value of this parameter does not change for other sessions in the instance. The value of the initialization parameters listed in Table 1-2 can be changed with ALTER SESSION.
The ALTER SYSTEM command modifies the global value of the parameter until the database is shut down. The ALTER SYSTEM command does not always change the parameter value for the current session. Use the ALTER SESSION command to change the parameter value for the current session. The value of the initialization parameters listed in Table 1-3 can be changed with ALTER SYSTEM.
The ALTER SYSTEM DEFERRED command does not modify the global value of the parameter for existing sessions, but the value will be modified for future sessions that connect to the database. The value of the initialization parameters listed in Table 1-4 can be changed with ALTER SYSTEM DEFERRED.
To see the current settings for initialization parameters, use the following server manager command:
SVRMGR> SHOW PARAMETERS
This displays all parameters in alphabetical order, with their current values.
Enter the following text string to see a display for all parameters having BLOCK in their name.:
SVRMGR> SHOW PARAMETERS BLOCK
If you display all the parameters, you might want to use the SPOOL command to write the output to a file.
Initialization parameters can be grouped by function in several different ways. For example, there are parameters that perform the following functions:
The set of variable parameters are of particular interest to database administrators because these parameters are used primarily for improving database performance.
The Oracle Server has the following types of initialization parameters:
Some initialization parameters are noted as derived. This means that their values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, the value you specify overrides the calculated value.
Initialization parameters with the prefix GC, such as GC_DB_LOCKS, apply to systems using the Oracle Parallel Server. The prefix GC stands for Global Cache. The settings of these parameters determine how the Oracle Parallel Server coordinates multiple instances. The settings you choose have an effect on the use of certain operating system resources.
Additional Information: For more information about the Parallel Server, see Oracle8 Parallel Server Concepts & Administration.
See your system release bulletins or other operating system-specific Oracle documentation for platform-specific information on Parallel Server parameters.
For some initialization parameters, the valid values or ranges depend upon the host operating system. This is denoted in the default, or range column as operating system-dependent. For example, the parameter DB_BLOCK_BUFFERS indicates the number of data buffers in main memory, and its maximum value depends on the operating system. The size of those buffers, set by DB_BLOCK_SIZE, has a system-dependent default value.
See your operating system-specific Oracle documentation for more information on operating system dependent Oracle parameters and operating system parameters.
The variable initialization parameters offer the most potential for improving system performance. Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its 11th cursor receives an error. Other variable parameters affect performance but do not impose absolute limits. For example, reducing the value of DB_BLOCK_BUFFERS does not prevent work even though it may slow down performance.
Increasing the values of variable parameters may improve your system's performance, but increasing most parameters also increases the System Global Area (SGA) size. A larger SGA can improve database performance up to a point. In virtual memory operating systems, an SGA that is too large can degrade performance if it is swapped in and out of memory. Operating system parameters that control virtual memory working areas should be set with the SGA size in mind. The operating system configuration can also limit the maximum size of the SGA.
There are a number of initialization parameters specific to Heterogeneous Services which must be set using a package called DBMS_HS.
For information about specifying these parameters, see Oracle8 Server Distributed Database Systems.
The following types of parameters might never have to be specified in the parameter file:
Some parameters have a minimum setting below which an Oracle instance will not start. For other parameters, setting the value too low or too high may cause Oracle to perform badly, but it still runs.
You may see messages indicating that a parameter value is too low or too high, or that you have reached the maximum for some resource. Frequently, you can wait a short while and retry the operation when the system is not as busy. If a message occurs repeatedly, you should shut down the instance, adjust the relevant parameter, and restart the instance.
The parameter descriptions in this chapter follow the format shown below.
The remaining paragraphs provide a textual description of the parameter and the effects of different settings.
For more information, see references to chapters or books that contain more detailed information on this subject.
Descriptions of the individual initialization parameters follow in alphabetical order.
Most initialization parameter values are global (on a database-wide basis), not per user, unless otherwise specified.
For more information, see your system release bulletins or other operating system-specific Oracle documentation.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
TRUE |
| Range of values: |
TRUE, FALSE |
O7_DICTIONARY_ACCESSIBILITY is intended to be used for migration from Oracle7 to Oracle8. O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to TRUE, access to objects in SYS schema is allowed (Oracle7 behavior). If this parameter is set to FALSE, SYSTEM privileges that allow access to objects in other schema do not allow access to objects in dictionary schema.
For example, if O7_DICTIONARY_ACCESSIBILITY=FALSE, then the SELECT ANY TABLE statement will allow access to views or tables in any schema except SYS schema (for example, dictionaries could not be accessed). The system privilege, EXECUTE ANY PROCEDURE will ALLOW ACCESS on the procedures in any other schema except in SYS schema.
If you need to access objects in the SYS schema, then you must be granted explicit object privilege. Also, the following roles, which can be granted to the database administrator, also allow access to dictionary objects: SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and DELETE_CATALOG_ROLE.
For more information on this parameter and the roles mentioned above, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SESSION, |
| Default value: |
FALSE |
| Range of values: |
TRUE, FALSE |
| Multiple instances: |
should have the same value |
ALLOW_PARTIAL_SN_RESULTS is a Parallel Server parameter. This parameter allows for partial results to be returned on queries to global performance tables (GV$) even if a slave could not be allocated on the instance.
If the value of MAX_PARALLEL_SERVERS equals 0, then a query on the global dynamic performance table (GV$) will revert to a sequential query on the local instance. If the value of MAX_PARALLEL_SERVERS is greater than 0 and a slave cannot be allocated on an instance in a GV$ query, then the value of ALLOW_PARTIAL_SN_RESULTS determines whether the query returns partial results or returns a failure.
If ALLOW_PARTIAL_SN_RESULTS is TRUE, then the query succeeds and returns results from all of the instances which were able to allocate a slave for the query. If ALLOW_PARTIAL_SN_RESULTS is FALSE, then the query fails and returns an error message.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NESTED_LOOPS |
| Range of values: |
NESTED_LOOPS/MERGE/HASH |
ALWAYS_ANTI_JOIN sets the type of antijoin that the Oracle Server uses. The system checks to verify that it is legal to perform an antijoin, and if it is, processes the subquery depending on the value of this parameter. When set to the value NESTED_LOOPS, the Oracle Server uses a nested loop antijoin algorithm. When set to the value MERGE, the Oracle Server uses the sort merge antijoin algorithm. When set to the value HASH, the Oracle Server uses the hash antijoin algorithm to evaluate the subquery.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope=ALTER SYSTEM |
| Default value: |
0 |
| Range of values: |
either 0 or 1 |
AQ_TM_PROCESSES specifies whether a time manager is created. If AQ_TM_PROCESSES is set to 1, then one time manager process is created to monitor the messages. If AQ_TM_PROCESSES is not specified or is set to 0, then the time manager is not created. Setting the parameter to a value greater than 1 results in an error, and no time manager is created.
For more information about this parameter and Advanced Queuing, see the Oracle8 Server Application Developer's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
0 |
| Range of values: |
0 - 15 |
ARCH_IO_SLAVES specifies the number of I/O slaves used by the ARCH process to archive redo logfiles. The ARCH process and its slaves always write to disk. By default the value is 0 and I/O slaves are not used.
This parameter is normally adjusted when an I/O bottleneck has been detected in the ARCH process. Typically, I/O bottlenecks in this process will occur on platforms that do not support asynchronous I/O or implement it inefficiently.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
$ORACLE_HOME/RDBMS/AUDIT |
AUDIT_FILE_DEST specifies the directory where auditing files are stored.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NONE |
| Range of values: |
NONE (FALSE), DB (TRUE), OS |
AUDIT_TRAIL enables or disables the writing of rows to the audit trail. Audited records are not written if the value is NONE or if the parameter is not present. The OS option enables system-wide auditing and causes audited records to be written to the operating system's audit trail. The DB option enables system-wide auditing and causes audited records to be written to the database audit trail (the SYS.AUD$ table).
The values TRUE and FALSE are also supported for backward compatibility. TRUE is equivalent to DB, and FALSE is equivalent to NONE.
The SQL AUDIT statements can set auditing options regardless of the setting of this parameter.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
B_TREE_BITMAP_PLANS makes the optimizer consider a bitmap access path even when a table only has regular B-tree indexes. Do not change the value of this parameter unless instructed by Oracle Technical Support.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
FULL |
| Range of values: |
FULL/PARTIAL |
BACKGROUND_CORE_DUMP specifies whether the SGA is dumped as part of the generated core file. When BACKGROUND_CORE_DUMP=FULL, the SGA is dumped as part of the generated core file. If BACKGROUND_CORE_DUMP=PARTIAL, then the SGA is not dumped as part of the generated core file.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
| Range of values: |
valid local pathname, directory, or disk |
BACKGROUND_DUMP_DEST specifies the pathname for a directory where debugging trace files for the background processes (LGWR, DBWR, and so on) are written during Oracle operations.
An ALERT file in the directory specified by BACKGROUND_DUMP_DEST logs significant database events and messages. Anything that affects the database instance-wide or globally is recorded here. This file records all instance start ups and shut downs, messages to the operator console, and errors that cause trace files to be written. It also records every CREATE, ALTER, or DROP operation on a database, tablespace, or rollback segment.
The ALERT file is a normal text file. Its filename is operating system-dependent. For platforms that support multiple instances, it takes the form ALERT_sid.LOG. This file grows slowly, but without limit, so the database administrator might want to delete it periodically. The file can be deleted even when the database is running.
For more information, see the Oracle8 Server Administrator's Guide. See your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM DEFERRED |
| Default value: |
0 |
| Range of values: |
0 - 15 although a value under 7 is recommended |
BACKUP_DISK_IO_SLAVES specifies the number of I/O slaves used by the Recovery Manager to backup, copy, or restore. Note that every Recovery Manager channel can get the specified number of I/O slave processes. By default, the value is 0 and I/O slaves are not used.
Typically I/O slaves are used to "simulate" asynchronous I/O on platforms that either do not support asynchronous I/O or implement it inefficiently. However, I/O slaves can be used even when asynchronous I/O is being used. In that case the I/O slaves will use asynchronous I/O.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SYSTEM DEFERRED |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
BACKUP_TAPE_IO_SLAVES specifies whether I/O slaves are used by the Recovery Manager to backup, copy, or restore data to tape. When BACKUP_TAPE_IO_SLAVES = TRUE, an I/O slave process is used to write to or read from a tape device. If this parameter is FALSE (the default), then I/O slaves are not used for backups; instead, the shadow process engaged in the backup will access the tape device.
Note, as a tape device can only be accessed by one process at any given time, this parameter is a boolean, that allows or disallows deployment of an I/O slave process to access a tape device.
Typically I/O slaves are used to "simulate" asynchronous I/O on platforms that either do not support asynchronous I/O or implement it inefficiently. However, I/O slaves can be used even when asynchronous I/O is being used. In that case the I/O slaves will use asynchronous I/O.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
1 Mb |
| Range of values: |
system-dependent value |
BITMAP_MERGE_AREA_SIZE parameter specifies the amount of memory used to merge bitmaps retrieved from a range scan of the index. The default value is 1 Mb. A larger value should improve performance because the bitmap segments must be sorted before being merged into a single bitmap. This parameter is not dynamically alterable at the session level.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
BLANK_TRIMMING specifies the data assignment semantics of character datatypes. A value of TRUE allows the data assignment of a source character string/variable to a destination character column/variable even though the source length is longer than the destination length. In this case, however, the additional length over the destination length is all blanks. This is in compliance with SQL92 Transitional Level and above semantics. A value of FALSE disallows the data assignment if the source length is longer than the destination length and reverts to SQL92 Entry Level semantics.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
0.1*DB_BLOCK_BUFFERS |
| OK to change: |
yes |
| Multiple instances: |
should have the same value |
CACHE_SIZE_THRESHOLD specifies the maximum size of a cached partition of a table split among the caches of multiple instances. If the partition is larger than the value of this parameter, the table is not split among the instances' caches. The default value of this parameter is 1/10 the number of database blocks in the buffer cache. This parameter can also specify the maximum cached partition size for a single instance.
As of Release 8.0.3, the CACHE_SIZE_THRESHOLD parameter is being denigrated.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
20 |
CLEANUP_ROLLBACK_ENTRIES specifies the number of undo records processed at one time when rolling back a transaction. Prevents long transactions from freezing out shorter transactions that also need to be rolled back. Normally this parameter will not need modification.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
CLOSE_CACHED_OPEN_CURSORS specifies whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT. A value of FALSE signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. If PL/SQL cursors are reused frequently, setting the parameter to FALSE can cause subsequent executions to be faster.
A value of TRUE causes open cursors to be closed at each COMMIT or ROLLBACK. The cursor can then be reopened as needed. If cursors are rarely reused, setting the parameter to TRUE frees memory used by the cursor when the cursor is no longer in use.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
| Range of values: |
0 - 255 |
COMMIT_POINT_STRENGTH specifies a value that determines the commit point site in a distributed transaction. The node in the transaction with the highest value for COMMIT_POINT_STRENGTH will be the commit point site. A database's commit point strength should be set relative to the amount of critical shared data in the database. For example, a database on a mainframe computer typically shares more data among users than one on a personal computer. Therefore, COMMIT_POINT_STRENGTH should be set to a higher value for the mainframe computer.
The commit point site stores information about the status of transactions. Other computers in a distributed transaction require this information, so it is desirable to have machines that are always available as commit point sites. Therefore, set COMMIT_POINT_STRENGTH to a higher value on your more available machines.
For more information about two-phase commit, see Oracle8 Server Concepts and Oracle8 Server Distributed Database Systems. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
8.0.0 |
| Range of values: |
default release to current release |
| Multiple instances: |
must have the same value |
COMPATIBLE allows you to use a new release, 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. This parameter specifies the release with which the Oracle Server must maintain compatibility. Some features of the current release may be restricted.
When using the standby database and feature, this parameter must have the same value on the primary and standby databases, and the value must be 7.3.0.0.0 or higher.
This parameter allows you to immediately take advantage of the maintenance improvements of a new release in your production systems without testing the new functionality in your environment.
The default value is the earliest release with which compatibility can be guaranteed.
For more information, see Oracle8 Server Migration. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
release dependent |
| Range of values: |
default version to current version |
| Multiple instances: |
must have the same value |
COMPATIBLE_NO_RECOVERY functions like the COMPATIBLE parameter, except that the earlier version may not be usable on the current database if recovery is needed.
The default value is the earliest version with which compatibility can be guaranteed. In some cases, this version may be earlier than the version which can be specified with the COMPATIBLE parameter.
For more information, see Oracle8 Server Migration. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
7 (days) |
| Range of values: |
0 - 365 (days) |
Records in some sections in the control file are circularly reusable while records in other sections are never reused. CONTROL_FILE_RECORD_KEEP_TIME applies to reusable sections. It specifies the minimum age in days that a record must have before it can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If CONTROL_FILE_RECORD_KEEP_TIME is set to 0, then reusable sections never expand and records are reused as needed.
Table 1-5 lists the names of reusable sections.
|
ARCHIVED LOG |
BACKUP CORRUPTION |
|
BACKUP DATAFILE |
BACKUP PIECE |
|
BACKUP REDO LOG |
BACKUP SET |
|
COPY CORRUPTION |
DATAFILE COPY |
|
DELETED OBJECT |
LOGHISTORY |
|
OFFLINE RANGE |
------------------ |
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
| Range of values: |
1 - 8 filenames |
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.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
$ORACLE_HOME/DBS/ |
CORE_DUMP_DEST specifies the directory where core files are dumped.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
automatically set by Oracle |
| Range of values: |
0 - unlimited |
| OK to change: |
no |
CPU_COUNT specifies the number of CPUs available to Oracle. Oracle uses it to set the default value of the LOG_SIMULTANEOUS_COPIES parameter. On single-CPU computers, the value of CPU_COUNT is 0.
Warning: On most platforms Oracle automatically sets the value of CPU_COUNT to the number of CPUs available to your Oracle instance. Do not change the value of CPU_COUNT.
If there is heavy contention for latches, change the value of LOG_SIMULTANEOUS_COPIES to twice the number of CPUs you have. Do not change the value of CPU_COUNT.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for information about this parameter.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
8 Mb |
| Range of values: |
operating system-dependent |
CREATE_BITMAP_AREA_SIZE specifies the amount of memory allocated for bitmap creation. The default value is 8 Mb. A larger value might lead to faster index creation. If cardinality is very small, you can set a small value for this parameter. For example, if cardinality is only 2 then the value can be on the order of kilobytes rather than megabytes. As a general rule, the higher the cardinality, the more memory is needed for optimal performance. This parameter is not dynamically alterable at the session level.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
If CURSOR_SPACE_FOR_TIME 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.
For more information, see Oracle8 Server Concepts.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
50 buffers |
| Range of values: |
4 - operating system-specific |
| OK to change: |
yes |
| Multiple instances: |
can have different values |
DB_BLOCK_BUFFERS specifies the number of database buffers available in the buffer cache. It is one of the primary parameters which contribute to the total memory requirements of the SGA on the instance. The DB_BLOCK_BUFFERS parameter, together with the DB_BLOCK_SIZE parameter, determines the total size of the buffer cache. Effective use of the buffer cache can greatly reduce the I/O load on the database. Since DB_BLOCK_SIZE can be specified only when the database is first created, use DB_BLOCK_BUFFERS to control the size of the buffer cache.
This parameter affects the probability that a data block will be pinged when Parallel Server is enabled: the more buffers, the more chance of pings.
For more information, see Oracle8 Server Concepts. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM IMMEDIATE |
| Default value: |
8 |
| Range of values: |
0 - derived |
DB_BLOCK_CHECKPOINT_BATCH specifies the number of buffers that will be added to each batch of buffers that DBWR writes in order to advance checkpoint processing.
Reducing DB_BLOCK_CHECKPOINT_BATCH prevents the I/O system from being flooded with checkpoint writes and allows other modified blocks to be written to disk. Setting it to a higher value allows checkpoints to complete more quickly.
In general, DB_BLOCK_CHECKPOINT_BATCH should be set to a value that allows the checkpoint to complete before the next log switch takes place. If a log switch takes place every 20 minutes, then this parameter should be set to a value that allows check pointing to complete within 20 minutes.
Setting DB_BLOCK_CHECKPOINT_BATCH to zero causes the default value to be used. If an overly large value is specified for this parameter, Oracle (silently) limits it to the number of blocks that can be written in a database writer write batch.
For more information, see Oracle8 Server Concepts.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
If DB_BLOCK_CHECKSUM is set to TRUE, DBWR and the direct loader will calculate a checksum and store it in the cache header of every data block when writing it to disk.
Warning: Setting DB_BLOCK_CHECKSUM to TRUE can cause performance overhead. Set this parameter to TRUE only under the advice of Oracle Support personnel to diagnose data corruption problems.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
0 |
| Range of values: |
0 - dependent on system memory capacity |
DB_BLOCK_LRU_EXTENDED_STATISTICS disables or enables compilation of statistics which measures the effects of increasing the number of buffers in the buffer cache in the SGA. When this facility is enabled, it keeps track of the number of disk accesses that would be saved if additional buffers were allocated. A value greater than zero specifies the additional number of buffers (over DB_BLOCK_BUFFERS) for which statistics are kept. This tuning tool should be turned off during normal operation.
When compiling statistics, set this parameter to the maximum size you want to use to evaluate the buffer cache. It should be set to zero otherwise. (Although you can set this value very high, it is not practical to set it to a size beyond your system's memory capacity.)
Setting this parameter can cause a large performance loss, so it should only be set when the system is lightly loaded.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
CPU_COUNT/2 |
| Range of values: |
1 - the number of CPUs |
DB_BLOCK_LRU_LATCHES specifies the upper bound of the number of LRU latch sets. Set this parameter to a value equal to the desired number of LRU latch sets. Oracle decides whether to use this value or reduce it based on a number of internal checks. If the parameter is not set, Oracle calculates a value for the number of sets. The value calculated by Oracle is usually adequate. Increase this only if misses are higher than 3% in V$LATCH.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
DB_BLOCK_LRU_STATISTICS disables or enables compilation of statistics in the X$KCBCBH table, which measures the effect of fewer buffers in the SGA buffer cache.
Set this parameter to TRUE when you want to compile statistics for the X$KCBCBH table; otherwise, leave it set to FALSE. This parameter is a tuning tool and should be set to FALSE during normal operation.
Setting this parameter can cause a large performance loss, so it should only be set when the system is lightly loaded.
For more information, see Oracle8 Server Administrator's Guide and Oracle8 Server Tuning.
DB_BLOCK_MAX_DIRTY_TARGET specifies the number of buffers that can be dirty (modified and different from what is on disk). If the number of dirty buffers in a buffer cache exceeds this value, DBWR will write out buffers in order to try and keep the number of dirty buffers below the specified value.
Note that this parameter does not impose a hard limit on the number of dirty buffers; in other words, DBWR attempts to keep the number of dirty buffers below this value, but will NOT stop (or slow) database activity if the number of dirty buffers exceeds this value occasionally.
This parameter can be used to influence the amount of time it takes to perform instance recovery since recovery is related to the number of buffers that were dirty at the time of the crash. The smaller the value of this parameter, the faster the instance recovery. Note that this improvement in recovery time is achieved at the expense of writing more buffers during normal processing. Hence, setting this parameter to a very small value might adversely affect performance if the workload modifies large numbers of buffers.
Setting this value to 0 disables writing of buffers for incremental checkpointing purposes; all other write activity continues as before (that is, it is unaffected by setting this parameter to 0).
DB_BLOCK_SIZE specifies the size in bytes of Oracle database blocks. Typical values are 2048 and 4096. The value for DB_BLOCK_SIZE in effect at CREATE DATABASE time determines the size of the blocks; at all other times the value must be set to the original value.
This parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. DSS (data warehouse) database environments tend to benefit from larger block size values.
For more information about block size, see Oracle8 Server Concepts. See also your operating system-specific Oracle documentation for the default value.
DB_DOMAIN specifies the extension components of a global database name, consisting of valid identifiers, separated by periods. Specifying DB_DOMAIN as a unique string for every database is highly recommended.
For example, this parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales department's DB_DOMAIN = "JAPAN.ACME.COM", then their "SALES" database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB_NAME = "SALES" but with DB_DOMAIN = "US.ACME.COM".
The following characters are valid in a database domain name:
For more information, see the Oracle8 Server Administrator's Guide.
DB_FILES specifies the maximum number of database files that can be opened for this database. The maximum valid value for DB_FILES is the maximum number of files, subject to operating system constraint, that will ever be specified for the database, including files to be added by ADD DATAFILE statement.
If you increase the value of DB_FILES, you must shut down and restart all instances accessing the database before the new value can take effect.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope= ALTER SYSTEM |
| Default value: |
64 |
| Range of values: |
operating system-dependent |
DB_FILE_DIRECT_IO_COUNT is used to specify the number of blocks to be used for IO operations done by backup, restore or direct path read and write functions. The IO buffer size is a product of DB_FILE_DIRECT_IO_COUNT and DB_BLOCK_SIZE. The IO buffer size cannot exceed max_IO_size for your platform.
Assigning a high value to this parameter results in greater use of PGA or SGA memory.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM, ALTER SESSION |
| Default value: |
8 |
| Range of values: |
operating system-dependent |
DB_FILE_MULTIBLOCK_READ_COUNT is used for multi-block I/O and 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 factors such as these:
The default is 8. OLTP and batch environments typically have values for this parameter in the range of 4 to 16. DSS (data warehouse) database environments tend to get the most benefit from maximizing the value for this parameter.
The actual maximums vary by operating system; they are always less than the operating system's maximum I/O size expressed as Oracle blocks (max_IO_size/DB_BLOCK_SIZE). Attempts to set this parameter to a value greater than the maximum will cause the maximum to be used.
For information on the optimizer, see Oracle8 Server Tuning. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
none |
| Range of values: |
character string |
Use DB_FILE_NAME_CONVERT to convert the filename of a new data file on the primary database to a filename on the standby database. Adding a datafile to the primary database necessitates adding a corresponding file to the standby database. When the standby database is updated, this parameter is used to convert the datafile name on the primary database to the a datafile name on the standby database. The file must exist and be writable on the standby database or the recovery process will halt with an error.
Set the value of this parameter to two strings: the first string is the pattern found in the datafile names on the primary database; the second string is the pattern found in the datafile names on the standby database.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
4 |
| Range of values: |
maximum: |
DB_FILE_SIMULTANEOUS_WRITES specifies the maximum number of simultaneous writes that can be made to a given database file. Oracle also uses the value of this parameter in computing various internal parameters that affect read and write operations to database files.
If you specify an excessively large value for this parameter, significant delays in performing read and write operations to a given database file might occur. This is because I/O requests get queued in the disk. If you set a value which is too small, the number of I/Os that can be issued to a given database file will be limited.
In environments where the database files reside on RAM devices or which use disk striping at the operating system level, it is beneficial to increase the value of this parameter. If striped files are used, Oracle recommends that you set the value of this parameter to 4 times the maximum number of disks in the file that is striped the most.
This parameter is also used to determine the number of reads-per-file in the redo read-ahead when reading redo during recovery.
For more information, see Oracle8 Server Tuning. See also your operating system-specific Oracle documentation for the default value.
DB_NAME can specify a database identifier of up to eight characters. If specified, it must correspond to the name specified in the CREATE DATABASE statement. Although the use of DB_NAME is optional, it should generally be set before invoking CREATE DATABASE and then referenced in that statement.
If not specified, a database name must appear on either the STARTUP or the ALTER DATABASE MOUNT command line for each instance of the parallel server.
The following are valid characters in a database name:
No other characters are valid. Double quotation marks are removed before processing the database name. They cannot be used to embed other characters in the name.
Lowercase characters are not treated with special significance. They are considered the same as their uppercase counterparts.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
DBLINK_ENCRYPT_LOGIN specifies whether attempts to connect to other Oracle Servers through database links should use encrypted passwords. When you attempt to connect to a database using a password, Oracle encrypts the password before sending it to the database. If the DBLINK_ENCRYPT_LOGIN parameter is TRUE, and the connection fails, Oracle does not re-attempt the connection. If this parameter is FALSE, Oracle re-attempts the connections using an unencrypted version of the password.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
0 |
| Range of values: |
0 to system-dependent value |
DBWR_IO_SLAVES specifies the number of I/O slaves used by the DBWR process. The DBWR process and its slaves always write to disk. By default, the value is 0 and I/O slaves are not used.
Typically I/O slaves are used to "simulate" asynchronous I/O on platforms that do not support asynchronous I/O or implement it inefficiently. However, I/O slaves can be used even when asynchronous I/O is being used. In that case the I/O slaves will use asynchronous I/O.
I/O slaves are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
TRUE |
| Range of values: |
TRUE/FALSE |
| OK to change: |
yes |
| Multiple instances: |
need not be identical |
DELAYED_LOGGING_BLOCK_CLEANOUTS turns the delayed block cleanout feature on or off. This reduces pinging in an Oracle Parallel Server. Keeping this feature set to TRUE sets a fast path, no logging block cleanout at commit time. Logging the block cleanout occurs at the time of a subsequent change to the block. This generally improves Oracle Parallel Server performance, particularly if block pings are a problem.
When Oracle commits a transaction, each block that the transaction changed is not immediately marked with the commit time. This is done later, on demand, when the block is read or updated. This is called block cleanout.
When block cleanout is performed during an update to a current block, the cleanout changes and the redo records are appended with those of the update. In previous releases, when block cleanout was needed during a read to a current block, extra cleanout redo records were generated and the block was dirtied. This has been changed.
When a transaction commits, all blocks changed by the transaction are cleaned out immediately. This cleanout performed at commit time is a "fast version" which does not generate redo log records (delayed logging) and does not reping the block. Most blocks will be cleaned out in this way, with the exception of blocks changed by long running transactions.
During queries, therefore, the data block's transaction information is normally up-to-date and the frequency of needing block cleanout is greatly reduced. Regular block cleanouts are still needed when querying a block where the transactions are still truly active, or when querying a block which was not cleaned out during commit.
Note: In long-running transactions, block cleanouts will not be performed during the transaction. If the transaction is not long running, block cleanout will be performed and the block cleanout is logged at the change of block.
During changes (INSERT, DELETE, UPDATE), the cleanout redo log records are generated and appended with the redo of the changes.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
Set DISCRETE_TRANSACTIONS_ENABLED to TRUE to implement a simpler, faster rollback mechanism that improves performance for certain kinds of transactions. There are strict limits on the kinds of transactions that can occur in discrete mode, but greater efficiency can be obtained for these transactions.
For more information about supplied packages, see the Oracle8 Server Tuning.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
TRUE |
| Range of values: |
TRUE, FALSE |
DISK_ASYNCH_IO can be used to control whether I/O to datafiles, controlfiles and logfiles are asynchronous. If a platform supports asynchronous I/O to disk, it is recommended that this parameter is left to its default. However, if the asynchronous I/O implementation is not stable, this parameter can be set to FALSE to disable asynchronous I/O. If a platform does not support asynchronous I/O to disk, this parameter has no effect.
If DISK_ASYNCH_IO is set to FALSE, then DBWR_IO_SLAVES should also be set.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
60 seconds |
| Range of values: |
1 - unlimited |
DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time in seconds for distributed transactions to wait for locked resources.
For more information on data concurrency, see Oracle8 Server Concepts and Oracle8 Server Distributed Database Systems.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
200 seconds |
| Range of values: |
0 - 1800 seconds |
DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME specifies the length of time to hold a remote connection open after a distributed transaction fails, in hope that communication will be restored without having to reestablish the connection. Larger values minimize reconnection time, but they also consume local resources for a longer time period. Values larger than 1800 seconds can be specified. Because the reconnection and recovery background process runs every 30 minutes (1800 seconds) (whether or not a failure occurs), a value of 1800 or larger means that the connection never closes.
For more information, see the Oracle8 Server Administrator's Guide and Oracle8 Server Distributed Database Systems.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
| Range of values: |
0 - TRANSACTIONS |
DISTRIBUTED_TRANSACTIONS specifies the maximum number of distributed transactions in which this database can concurrently participate. The value of this parameter cannot exceed the value of the parameter TRANSACTIONS.
If network failures are occurring at an abnormally high rate, causing many in-doubt transactions, you may want to decrease this parameter's value temporarily. This limits the number of concurrent distributed transactions, which then reduces the number of in-doubt transactions. Thus, the amount of blocked data and possible heuristic decision making (because of in-doubt transactions) is reduced.
If DISTRIBUTED_TRANSACTIONS is set to 0, no distributed transactions are allowed for the database. The recovery (RECO) process also does not start when the instance starts up.
For more information, see the Oracle8 Server Administrator's Guide and Oracle8 Server Distributed Database Systems. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
derived (4 * TRANSACTIONS) |
| Range of values: |
20 - unlimited, 0 |
| Multiple instances: |
must all have positive values or must all be 0 |
DML_LOCKS specifies the maximum number of DML locks-one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if 3 users are modifying data in one table, then 3 entries would be required. If 3 users are modifying data in 2 tables, then 6 entries would be required.
The default value assumes an average of 4 tables referenced per transaction. For some systems, this value may not be enough.
If the value is set to 0, enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE. If the value is set to 0 on one instance, it must be set to 0 on all instances of an Oracle Parallel Server.
For more information on data concurrency, see Oracle8 Parallel Server Concepts & Administration, Oracle8 Server Concepts, and Oracle8 Server Distributed Database Systems.
DML_LOCKS has the following PDML restrictions regarding locks acquired by a parallel UPDATE/DELETE/INSERT statement.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
derived |
| Range of values: |
10 - 65535 |
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.
One difference between enqueues and latches is that in latches there is no ordered queue of waiting processes as there are in enqueues. Processes waiting for latches can either use timers to wake up and retry or spin (only in multiprocessors).
ENQUEUE_RESOURCES sets the number of resources that can be concurrently locked by the lock manager. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and should be adequate, as long as DML_LOCKS + 20 is less than ENQUEUE_RESOURCES. For three or fewer sessions, the default value is 20. For 4 to 10 sessions, the default value is ((SESSIONS - 3) * 5) + 20; and for more than 10 sessions, it is ((SESSIONS - 10) * 2) + 55.
If you explicitly set ENQUEUE_RESOURCES to a value higher than DML_LOCKS + 20, then the value you provide is used.
If there are many tables, the value may be increased. Allow one per resource (regardless of the number of sessions or cursors using that resource), not one per lock Only increase this parameter if Oracle returns an error specifying that enqueues are exhausted.
For more information on data concurrency, see Oracle8 Parallel Server Concepts & Administration, Oracle8 Server Concepts and Oracle8 Server Distributed Database Systems.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NULL |
EVENT is used to debug the system. This parameter should not usually be altered except at the direction of Oracle technical support personnel.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
NULL |
FIXED_DATE lets you set a constant date that SYSDATE will always return instead of the current date. The format of the date is:
YYYY-MM-DD-HH24:MI:SS.
It also accepts the default Oracle date format, without a time. Specify the value with double quotes (but not single quotes) or without quotes. For example,
FIXED_DATE = "30-nov-95"
or
FIXED_DATE = 30-nov-95
This parameter is useful primarily for testing.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
see below |
| Range of values: |
TRUE, FALSE |
| OK to change: |
yes |
| Multiple instances: |
must have identical values |
FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY is a Parallel Server parameter. The value of this parameter lets the database administrator control whether Oracle freezes the entire database during instance recovery. When this parameter is set to TRUE, Oracle freezes the entire database during instance recovery. The advantage of freezing the whole database is that it stops all other disk activities except those for instance recovery. This lets instance recovery complete faster. The drawback of freezing the whole database is that the entire database becomes unavailable during instance recovery.
When this parameter is set to FALSE, Oracle does not freeze the entire database, unless Oracle is responsible for resilvering some of the mirrored data files. Resilvering means ensuring data consistency of mirrored data files after a node crash. When Oracle does not freeze the entire database, part of the unaffected database will be accessible during instance recovery.
If all online datafiles use hash locks, the default value of this parameter is FALSE. If any data files use fine-grain locks, the default is TRUE.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
0 |
| Range of values: |
any positive integer |
| Multiple instances: |
can have different values |
| OK to change: |
no |
GC_DEFER_TIME specifies the time (in 100ths of a second) that the server waits, or defers, before responding to forced-write requests for hot blocks from other instances. Specifying the GC_DEFER_TIME parameter makes it more likely that buffers will be properly cleaned out before being written, thus making them more useful when they are read by other instances. It also improves the chance of hot blocks being used multiple times within an instance between forced writes.
The default value, 0, means that the feature is disabled: no deferring occurs.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NULL |
| Multiple instances: |
must have identical values |
| OK to change: |
yes |
GC_FILES_TO_LOCKS is a Parallel Server parameter. This parameter controls the mapping of PCM locks to datafiles. The value of the parameter should be set to cover as many files as possible. Thus, to avoid performance problems, you should always change GC_FILES_TO_LOCKS when the size of datafiles change or when new datafiles are added. This requires you to shutdown and restart your parallel server.
GC_FILES_TO_LOCKS has the following syntax:
GC_FILES_TO_LOCKS = "{file_list=lock_count[!blocks][EACH]}[:]..."
where file_list is one or more datafiles listed by their file numbers, or ranges of file numbers, with comma separators:
filenumber[-filenumber][,filenumber[-filenumber]]...
and lock_count is the number of PCM locks assigned to file_list. If lock_count is set to 0, then fine-grain locking is used for these files.
A colon (:) separates each clause that assigns a number of PCM locks to file_list. The optional parameter blocks, specified with the "!" separator, indicates the number of contiguous blocks covered by one lock. The default is non-contiguous blocks. EACH specifies that each datafile in file_list is assigned a separate set of lock_count PCM locks. Spaces are not allowed within the quotation marks.
If the number of PCM locks allocated to a datafile is less than or equal to the number of blocks in a datafile, each of these locks will cover a number of contiguous blocks within the datafile equal to !blocks. If the number of PCM locks assigned to the datafile is larger than its number of blocks, resources will be wasted since there will be locks which are not covering any blocks.
The datafiles not specified in GC_FILES_TO_LOCKS are covered, by default, by releasable locks. Releasable locks are controlled by a different parameter, GC_RELEASABLE_LOCKS. See "GC_RELEASABLE_LOCKS" on page 1-46.
To find the correspondence between filenames and file numbers, query the data dictionary view DBA_DATA_FILES. See "DBA_DATA_FILES" on page 2-55.
GC_FILES_TO_LOCKS has no effect on an instance running in exclusive mode.
For more information on GC_FILES_TO_LOCKS, see Oracle8 Parallel Server Concepts & Administration.
GC_LCK_PROCS is a Parallel Server parameter. This parameter sets the number of background lock processes (LCK0 through LCK9) for an instance in a parallel server. The default of 1 is normally sufficient, but you can increase the value if the distributed lock request rate saturates the lock process. The lock process is saturated if it becomes CPU bound.
Increase the value of the PROCESSES parameter by one for each LCKn process, and increase the values of other parameters whose default values are derived from PROCESSES if you do not use their defaults.
For more information, see Oracle8 Parallel Server Concepts & Administration.
Lock elements can be fixed or non-fixed. Fixed lock elements are used by hashed PCM locks, in which the lock element name is preassigned. Non-fixed lock elements are used with fine-grain locking.
If the GC_RELEASABLE_LOCKS parameter is set, its value is used to allocate space for fine-grain locking. There is no maximum value, except as imposed by memory restrictions.
This parameter is specific to the Oracle Parallel Server in shared mode.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
20 |
| Multiple instances: |
must have identical values |
| OK to change: |
yes |
GC_ROLLBACK_LOCKS is a Parallel Server parameter. This parameter specifies, for each rollback segment, the number of distributed locks available for simultaneously modified rollback segment blocks. The default is adequate for most applications.
These instance locks are acquired in exclusive mode by the instance that acquires the rollback segment. They are used to force the instance to write rollback segment blocks to disk when another instance needs a read-consistent version of a block.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SESSION, ALTER SYSTEM |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, then no check is performed. Oracle recommends setting this parameter to TRUE to ensure the use of consistent naming conventions for databases and links.
If you use distributed processing, set GLOBAL_NAMES to TRUE to ensure a unique identifying name for your database in a networked environment.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope= ALTER SESSION |
| Default value: |
2 * SORT_AREA_SIZE |
| Range of values: |
0 - system-dependent value |
HASH_AREA_SIZE specifies the maximum amount of memory, in bytes, to be used for hash joins. If this parameter is not set, its value defaults to twice the value of the SORT_AREA_SIZE parameter.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope= ALTER SESSION, ALTER SYSTEM |
| Default value: |
1 |
| Range of values: |
operating system dependent |
HASH_MULTIBLOCK_IO_COUNT specifies how many sequential blocks a hash join reads and writes in one IO. When operating in multi-threaded server mode, however, this parameter is ignored (a value of 1 is used even if you set the parameter to another value).
The maximum value for HASH_MULTIBLOCK_IO_COUNT varies by operating system. It is always less than the operating system's maximum I/O size expressed as Oracle blocks (max_IO_size/DB_BLOCK_SIZE).
This parameter strongly affects performance because it controls the number of partitions into which the input is divided. If you change the parameter value, try to make sure that the following formula remains true:
R / M <= Po2(M/C)
where:
R = sizeof(left input to the join)
M = HASH_AREA_SIZE * 0.9
Po2(n) = largest power of 2 that is smaller than n
C = HASH_MULTIBLOCK_IO_COUNT * DB_BLOCK_SIZE
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NULL |
| Range of values: |
valid parameter filenames |
| Multiple instances: |
can have different values |
Use IFILE to embed another parameter file within the current parameter file. For example:
IFILE = COMMON.ORA
You can have up to three levels of nesting. In this example, the file COMMON.ORA could contain a second IFILE parameter for the file COMMON2.ORA, which could contain a third IFILE parameter for the file GCPARMS.ORA. You can also include multiple parameter files in one parameter file by listing IFILE several times with different values:
IFILE = DBPARMS.ORA
IFILE = GCPARMS.ORA
IFILE = LOGPARMS.ORA
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
string LIST |
| Parameter class: |
static |
| Allowable values: |
a string of group names, separated by commas. |
INSTANCE_GROUPS is a Parallel Server parameter. It can be specified in parallel mode only. This parameter assigns the current instance to the specified groups. The value of INSTANCE_GROUPS must be a comma-separated list of instance groups. Instance groups are used when allocating query slaves for a parallel operation.
See also "PARALLEL_INSTANCE_GROUP" on page 1-89.
For more information, see Oracle8 Parallel Server Concepts & Administration.
INSTANCE_NUMBER is a Parallel Server parameter. This parameter can be specified in parallel mode or exclusive mode. It specifies a unique number that maps the instance to one group of free space lists for each table created with storage option FREELIST GROUPS.
The INSTANCE option of the ALTER TABLE ALLOCATE EXTENT statement assigns an extent to a particular group of free lists. If you set INSTANCE_NUMBER to the value specified for the INSTANCE option, the instance uses that extent for inserts, and updates that expand rows.
The practical maximum value of this parameter is the maximum number of instances specified in the CREATE DATABASE statement; the absolute maximum is operating system dependent.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
60 (seconds) |
| Range of values: |
1 - 3600 (seconds) |
| Multiple instances: |
can have different values |
JOB_QUEUE_INTERVAL specifies the interval between wake-ups for the SNPn background processes of the instance.
For more information on managing table snapshots, see Oracle8 Server Replication.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
0 |
| Range of values: |
0 - 36 |
| Multiple instances: |
can have different values |
JOB_QUEUE_PROCESSES specifies the number of SNPn background processes per instance, where n is 0 to 9 followed by A to Z. If you wish to have your snapshots updated automatically, you must set this parameter to a value of one or higher. One snapshot refresh process will usually be sufficient unless you have many snapshots that refresh simultaneously.
Job Queue processes are also used to process requests created by DBMS_JOB_QUEUE.
For more information on managing table snapshots, see Oracle8 Server Replication.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
16K |
| Range of values: |
maximum: ~64M |
LARGE_POOL_MIN_ALLOC specifies the minimum allocation size from the large pool. The value of the parameter can be specified in megabytes or kilobytes.
LARGE_POOL_MIN_ALLOC can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".
The parameter LARGE_POOL_SIZE lets you specify the size of the large pool allocation heap. The default size is 0, and the minimum size is 300K or LARGE_POOL_MIN_ALLOC, whichever is larger. The value of the parameter can be specified in megabytes or kilobytes. If specified, the large pool is used for session memory if running with the multithreaded server. It is also used for IO buffers during backup operations.
LARGE_POOL_SIZE can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
0 |
| Range of values: |
0 - system-dependent value |
LGWR_IO_SLAVES specifies the number of I/O slaves used by the LGWR process. The LGWR process and its slaves always write to disk. By default the value is 0 and I/O slaves are not used.
Typically I/O slaves are used to "simulate" asynchronous I/O on platforms that do not support asynchronous I/O or implement it inefficiently. However, I/O slaves can be used even when asynchronous I/O is being used. In that case the I/O slaves will use asynchronous I/O.
The default value is almost always adequate.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
0 |
| Range of values: |
0 - number of session licenses |
| Multiple instances: |
can have different values |
LICENSE_MAX_SESSIONS specifies the maximum number of concurrent user sessions allowed simultaneously. When this limit is reached, only users with the RESTRICTED SESSION privilege can connect to the server. Users who are not able to connect receive a warning message indicating that the system has reached maximum capacity.
A zero value indicates that concurrent usage (session) licensing is not enforced. If you set this parameter to a non-zero number, you might also want to set LICENSE_SESSIONS_WARNING.
Concurrent usage licensing and user licensing should not both be enabled. Either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS should always be zero.
Multiple instances can have different values, but the total for all instances mounting a database should be less than or equal to the total number of sessions licensed for that database.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
0 |
| Range of values: |
0 - number of user licenses |
| Multiple instances: |
should have the same values |
LICENSE_MAX_USERS specifies the maximum number of users you can create in the database. When you reach this limit, you cannot create more users. You can, however, increase the limit.
Concurrent usage (session) licensing and user licensing should not both be enabled. Either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS, or both, should be zero.
If different instances specify different values for this parameter, the value of the first instance to mount the database takes precedence.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
0 |
| Range of values: |
0 - LICENSE_MAX_SESSIONS |
| Multiple instances: |
can have different values |
LICENSE_SESSIONS_WARNING specifies a warning limit on the number of concurrent user sessions. When this limit is reached, additional users can connect, but Oracle writes a message in the ALERT file for each new connection. Users with RESTRICTED SESSION privilege who connect after the limit is reached receive a warning message stating that the system is nearing its maximum capacity.
If this parameter is set to zero, no warning is given when approaching the concurrent usage (session) limit. If you set this parameter to a nonzero number, you should also set LICENSE_MAX_SESSIONS.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
12000 |
| Range of values: |
maximum: limited by |
| Multiple instances: |
must have the same value |
LM_LOCKS is a Parallel Server parameter. This parameter specifies the number of locks which will be configured for the lock manager. The number of locks can be represented by the following equation, where R is the number of resources, N is the total number of nodes, and L is the total number of locks.
L = R + (R*(N - 1))/N
Note that lock configurations are per lock manager instance. Thus the value of LM_LOCKS must be the same for all lock manager instances.
LM_PROCS is a Parallel Server parameter. The value of this parameter represents the value of the PROCESSES parameter plus the maximum number of instances. Note that the processes configurations are per lock manager instance. Thus the value for LM_PROCS must be the same for all lock manager instances.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
6000 |
| Range of values: |
maximum: limited by |
| Multiple instances: |
must have the same value |
LM_RESS is a Parallel Server parameter. This parameter controls the number of resources that can be locked by each lock manager instance. It is recommended that each instance be assigned the same parameter value.
The value specified for LM_RESS should be much less than 2 * DML_LOCKS plus an overhead of about 20 locks.
LM_RESS covers the number of lock resources allocated for DML, DDL (data dictionary locks), data dictionary and library cache locks plus the file and log management locks.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
"(ADDRESS_LIST = (Address = (Protocol = TCP) (Host=localhost) (Port=1521)) (Address=(Protocol = IPC) |
The LOCAL_LISTENER parameter is optional and identifies "local" Net8 listeners so that they can complete client connections to dedicated servers. LOCAL_LISTENER specifies the network name of either a single address or an address list of Net8 listeners. These Net8 listeners need to be running on the same machine as the instance.
The instance and dispatchers register certain information with the listener. This information enables the listener to connect clients to the appropriate dispatchers and dedicated servers. In order to connect clients to dedicated servers, the listener and the instance must be running on the same machine.
When it is present, the LOCAL_LISTENER parameter overrides the MTS_LISTENER_ADDRESS and MTS_MULTIPLE_LISTENERS parameters. For more information on these parameters, see "MTS_LISTENER_ADDRESS" on page 1-71 and "MTS_MULTIPLE_LISTENERS" on page 1-72.
For more information about instances, listener processes, and dispatcher processes, see the Oracle8 Server Administrator's Guide. See your operating system-specific Oracle documentation and Net8 documentation for a description of how to specify addresses for the protocols on your system.
| Parameter type: |
string |
| Parameter class: |
static |
| Range of values: |
eight characters maximum, no special characters allowed |
LOCK_NAME_SPACE specifies the name space that the distributed lock manager (DLM) uses to generate lock names. This might need to be set if there is a standby or clone database with the same database name on the same cluster.
LOG_ARCHIVE_BUFFER_SIZE specifies 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 be used to tune archiving.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
| Range of values: |
operating system-dependent |
| Multiple instances: |
can have different values |
LOG_ARCHIVE_BUFFERS specifies the number of buffers to allocate for archiving. The default should be adequate for most applications.
This parameter, with LOG_ARCHIVE_BUFFER_SIZE, can tune archiving so that it runs as fast as necessary, but not so fast that it reduces system performance.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value.
LOG_ARCHIVE_DEST is applicable only if you are 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.
For more information, see the Oracle8 Server Administrator's Guide. See also, "LOG_ARCHIVE_DUPLEX_DEST" on page 1-58, "LOG_ARCHIVE_MIN_SUCCEED_DEST" on page 1-60 and "V$ARCHIVE_DEST" on page 3-5
See your Oracle operating system-specific documentation for the default value and for an example of how to specify the destination path or filename using LOG_ARCHIVE_DEST.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
a NULL string |
| Range of values: |
Either a NULL string or any valid path or device name, except raw partitions |
LOG_ARCHIVE_DUPLEX_DEST is similar to the initialization parameter LOG_ARCHIVE_DEST. This parameter specifies a second archive destination: the duplex archive destination. This duplex archive destination can be either a must-succeed or a best-effort archive destination, depending on how many archive destinations must succeed.
If LOG_ARCHIVE_DUPLEX_DEST is set to be a NULL string ("") or (`'), it means there is no duplex archive destination. The default of this parameter is a NULL string.
For more information, see "LOG_ARCHIVE_DEST" on page 1-58, "LOG_ARCHIVE_MIN_SUCCEED_DEST" on page 1-60, and "V$ARCHIVE_DEST" on page 3-5.
LOG_ARCHIVE_FORMAT is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter. The following variables can be used in the format:
%s log sequence number
%t thread number
Using uppercase letters (for example, %S) for the variables causes the value to be a fixed length padded to the left with zeros.
The following is an example of specifying the archive redo log filename format:
LOG_ARCHIVE_FORMAT = "LOG%s_%t.ARC"
For more information, see Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values for LOG_ARCHIVE_FORMAT.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
1 |
| Range of values: |
1 - 2 |
LOG_ARCHIVE_MIN_SUCCEED_DEST specifies the minimum number of archive log destinations that must succeed. When automatic archiving is enabled, the allowable values are 1 and 2. If this parameter is 1, LOG_ARCHIVE_DEST is a must-succeed destination and LOG_ARCHIVE_DUPLEX_DEST is a best-effort destination. If this parameter is 2, both LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST are must-succeed destinations.
For more information, see "LOG_ARCHIVE_DEST" on page 1-58, "LOG_ARCHIVE_DUPLEX_DEST" on page 1-58, and "V$ARCHIVE_DEST" on page 3-5
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
| Multiple instances: |
can have different values |
LOG_ARCHIVE_START is 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 database administrator 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.
To use ARCHIVELOG mode while creating a database, set this parameter to TRUE. Normally, a database is created in NOARCHIVELOG mode and then altered to ARCHIVELOG mode after creation.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
If LOG_BLOCK_CHECKSUM is TRUE, then every log block will be given a checksum before it is written to the current log.
Warning: Setting LOG_BLOCK_CHECKSUM to TRUE can cause performance overhead. Set this parameter to TRUE only under the advice of Oracle Support personnel to diagnose data corruption problems.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
| Range of values: |
operating system-dependent |
LOG_BUFFER specifies the amount of memory, in bytes, that is used when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.
In general, larger values for LOG_BUFFER reduce redo log file I/O, particularly if transactions are long or numerous. In a busy system, the value 65536 or higher would not be unreasonable.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.
LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of redo log file blocks that are written between consecutive checkpoints.
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 checkpoint frequency is one of the factors which impacts the time required for the database to recover from an unexpected failure.
Extremely frequent checkpointing can cause excessive writes to disk, possibly impacting transaction performance. In addition, if the intervals are so close together that the interval checkpoint requests are arriving at a rate faster than the rate at which Oracle can satisfy these requests, Oracle can choose to ignore some of these requests in order to avoid excessive interval checkpointing activity.
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 Enterprise Manager. For more information about this statistic, see "DBWR checkpoints" on page C-3
Note that specifying a value of 0 (zero) for the interval might cause interval checkpoints to be initiated very frequently since a new request will be started even if a single redo log buffer is written since the last request was initiated. Hence, setting the value to 0 is not recommended.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
0 seconds |
| Range of values: |
0 - unlimited |
| Multiple instances: |
can have different values |
LOG_CHECKPOINT_TIMEOUT specifies the maximum amount of time before another checkpoint occurs. The value is specified in seconds. The time begins at the start of the previous checkpoint, then a checkpoint occurs after the amount of time specified by this parameter.
Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended.
Note: A checkpoint scheduled to occur because of this parameter is delayed until the completion of the previous checkpoint if the previous checkpoint has not yet completed.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
LOG_CHECKPOINTS_TO_ALERT allows you to log your checkpoints to the alert file. This parameter is useful to determine if checkpoints are occurring at the desired frequency.
For more information, see Oracle8 Server Concepts.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
none |
| Range of values: |
character strings |
The value of LOG_FILE_NAME_CONVERT converts the filename of a new log file on the primary database to the filename of a log file on the standby database. Adding a log file to the primary database necessitates adding a corresponding file to the standby database. When the standby database is updated, this parameter is used to convert the log file name on the primary database to the log file name on the standby database. The file must exist and be writable on the standby database or the recovery process will halt with an error.
Set the value of this parameter to two strings: the first string is the pattern found in the log file names on the primary database; the second string is the pattern found in the log file names on the standby database.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
255 |
| Range of values: |
2 - 255 (must be a minimum of MAXLOGFILES*MAXLOGMEMBERS) |
| Multiple instances: |
must have the same value |
LOG_FILES specifies the maximum log group number. This value specifies the maximum number of redo log files that can be opened at runtime for the database. It also gives the upper limit on the group numbers that can be specified when issuing log-related commands. Reduce the value only if you need SGA space and have fewer redo log files.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
CPU_COUNT |
| Range of values: |
0 - unlimited |
LOG_SIMULTANEOUS_COPIES specifies 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.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
operating system-dependent |
| Range of values: |
operating system-dependent |
LOG_SMALL_ENTRY_MAX_SIZE specifies the size in bytes of the largest copy to the log buffers that can occur under the redo allocation latch without obtaining the redo buffer copy latch. If the value for LOG_SIMULTANEOUS_COPIES is 0, this parameter is ignored (all writes are "small" and are made without the copy latch).
If the redo entry is copied on the redo allocation latch, the user process releases the latch after the copy. If the redo entry is larger than this parameter, the user process releases the latch after allocating space in the buffer and getting a redo copy latch.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
90000 |
| Range of values: |
0 - 90000 |
| Multiple instances: |
must have identical values |
| OK to change: |
no |
MAX_COMMIT_PROPAGATION_DELAY is a Parallel Server parameter. This initialization parameter should not be changed except under a limited set of circumstances specific to the Parallel Server. This parameter specifies the maximum amount of time allowed before the System Change Number (SCN) held in the SGA of an instance is refreshed by LGWR. It determines if the local SCN should be refreshed from the lock value when getting the snapshot SCN for a query. Units are in hundredths of seconds. Under very unusual circumstances involving rapid updates and queries of the same data from different instances, the SCN might not be refreshed in a timely manner. Setting the parameter to zero causes the SCN to be refreshed immediately after a commit. The default value of 90,000 hundredths of a second, or fifteen minutes, is an upper bound that allows the preferred existing high performance mechanism to remain in place.
Change this parameter only when it is absolutely necessary to see the most current version of the database when doing a query.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SYSTEM, ALTER SYSTEM DEFERRED, ALTER SESSION |
| Default value: |
10000 blocks |
| Range of values: |
0 - UNLIMITED |
MAX_DUMP_FILE_SIZE specifies the maximum size of trace files to be written. Change this limit if you are concerned that trace files may take up too much space.
MAX_DUMP_FILE_SIZE can accept a numerical value or a number followed by the suffix "K", or "M", where "K" means multiply by 1000 and "M" means multiply by 1000000. A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks, whereas a number followed by a "K" or "M" suffix specifies the file size in number of bytes. MAX_DUMP_FILE_SIZE can also assume the special value string UNLIMITED. UNLIMITED means that there is no upper limit on trace file size, thus dump files can be as large as the operating system permits.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
20 |
| Range of values: |
0 - 148 |
MAX_ENABLED_ROLES specifies the maximum number of database roles that a user can enable, including sub-roles.
The actual number of roles a user can enable is 2 plus the value of MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC, and the user's own role. For example, if MAX_ENABLED_ROLES is set to 5, user SCOTT can have 7 roles enabled, the five enabled by MAX_ENABLED_ROLES plus PUBLIC and SCOTT.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
30 |
| Range of values: |
2 - 65535 |
MAX_ROLLBACK_SEGMENTS specifies the maximum size of the rollback segment cache in the SGA. The number specified signifies the maximum number of rollback segments that can be kept online (that is, status of INUSE) simultaneously by one instance. For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
8 |
| Range of values: |
1 - 32 |
MAX_TRANSACTION_BRANCHES controls the number of branches in a distributed transaction. For example, a certain TP monitor uses one branch per server involved in a distributed transaction. Another TP monitor uses one branch per server group involved in a distributed transaction.
The previously fixed maximum number of branches limited the number of servers or server groups involved in a distributed transaction to 8 per Oracle instance. With the MAX_TRANSACTION_BRANCHES parameter, the maximum number of branches can be increased to 32, allowing for 32 servers or server groups per Oracle instance to work on one distributed transaction.
Setting MAX_TRANSACTION_BRANCHES to a lower value reduces shared pool memory usage slightly according to the following equation:
MAX_TRANSACTION_BRANCHES * DISTRIBUTED_TRANSACTIONS * 72 bytes
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
NULL |
MTS_DISPATCHERS lets the database administrator enable various attributes for each dispatcher. In Oracle 7.3, the database administrator could specify a protocol and an initial number of dispatchers. These attributes are specified in a position-dependent, comma-separated string assigned to MTS_DISPATCHERS. For example:
MTS_DISPATCHERS = "TCP, 3"
While remaining backwardly compatible with this format, the parsing software in Oracle8 supports a name-value syntax (similar to the syntax used by Net8) to enable the specification of the existing and additional attributes in a position-independent case-insensitive manner. For example:
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)"
One and only one of the following attributes is required: ADDRESS, DESCRIPTION, or PROTOCOL.
The ADDRESS and DESCRIPTION attributes provides support for the specification of additional network attributes. (This enables support of multi-homed hosts.)
The attributes CONNECTION, DISPATCHERS, LISTENER, MULTIPLEX, POOL, SERVICE, and TICKS are optional:
| Attribute | Description |
|---|---|
|
CONNECTION |
The maximum number of network connections to allow for each dispatcher. Default is set by Net8 and is platform specific. |
|
DISPATCHERS |
The initial number of dispatchers to start. Default is 1. |
|
LISTENER |
The network name of an address or address list of the Net8 listeners with which the dispatchers will register. The LISTENER attribute makes it easier to administer multi-homed hosts. This attribute specifies the appropriate listeners with which the dispatchers will register. The LISTENER attribute overrides the LOCAL_LISTENER parameter and the denigrated MTS_LISTENER_ADDRESS and MTS_MULTPLE_LISTENERS parameters. For more information, see "LOCAL_LISTENER" on page 1-56, "MTS_LISTENER_ADDRESS" on page 1-71, and "MTS_MULTIPLE_LISTENERS" on page 1-72. |
|
MULTIPLEX |
Used to enable the Net8 "Network Session Multiplex" feature. If "1", "ON", "YES", "TRUE", or "BOTH" is specified, then "Network Session Multiplex" is enabled for both incoming and outgoing network connections. If "IN" is specified, then "Network Session Multiplex" is enabled for incoming network connections. If "OUT" is specified, then "Network Session Multiplexing" is enabled for outgoing network connections. If "0", "NO", "OFF", or "FALSE" is specified, then "Network Session Multiplexing" is disabled for both incoming and outgoing network connections. The default "Network Session Multiplex" is disabled on both incoming and outgoing network connections. |
|
POOL (POO) |
Used to enable the Net8 "Connection Pooling" feature. If a number is specified, then "Connection Pooling" is enabled for both incoming and outgoing network connections and the number specified is the timeout in ticks for both incoming and outgoing network connections. If "ON", "YES", "TRUE", or "BOTH" is specified, then "Connection Pooling" is enabled for both incoming and outgoing network connections and the default timeout (set by Net8) will be used for both incoming and outgoing network connections. If "IN" is specified, then "Connection Pooling" is enabled for incoming network connections and the default timeout (set by Net8) will be used for incoming network connections. If "OUT" is specified, then "Connection Pooling" is enabled for outgoing network connections and the default timeout (set by Net8) will be used for outgoing network connections. If "NO", "OFF", or "FALSE" is specified, then "Connection Pooling" is disabled for both incoming and outgoing network connections. POOL can also be assigned a name-value string such as: "(IN=10)", "(OUT=20)", or "((IN=10)(OUT=20))", in which case, if an "IN" numeric value is specified, then "Connection Pooling" is enabled for incoming connections and the number specified is the timeout in ticks for incoming network connections. If an "OUT" numeric value is specified, then "Connection Pooling" is enabled for outgoing network connections and the number specified is the timeout in ticks for outgoing network connections. If the numeric value of a specified timeout is 0, then the default value (set by Net8) will be used. The default "Connection Pooling" is disabled on both incoming and outgoing network connections. |
|
SERVICE (SER, SERV) |
The service name which the dispatchers register with the Net8 listeners. The SERVICE attribute overrides the MTS_SERVICE parameter. This attribute specifies a service name that the dispatchers will use to register. For more information, see "MTS_SERVICE" on page 1-75. |
|
SESSIONS (SES or SESS) |
The maximum number of network sessions to allow for each dispatcher. Default is set by Net8 and is platform specific. |
|
TICKS |
The size of a network tick in seconds. See the Net8 Administrator's Guide for more details about what this means. |
For more information, see the Oracle8 Server Administrator's Guide. See also the Net8 Administrator's Guide.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NULL |
MTS_LISTENER_ADDRESS specifies the configuration of the Listener process. The Listener process requires an address to listen for connection requests for each network protocol that is used on your system. Addresses are specified as the Net8 description of the connection address.
Warning: Each address must be specified with its own parameter. (This differs from the Net8 syntax.) For example, if you use TCP/IP as well as DECNet, you would provide specifications similar to the following in your initialization file:
MTS_LISTENER_ADDRESS = \
"(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=7002))"
MTS_LISTENER_ADDRESS = \
"(ADDRESS=(PROTOCOL=decnet)(NODE=name)(OBJECT=mts))"
Note: If you have multiple MTS_LISTENER_ADDRESS parameters, they must be adjacent to each other in your initialization file.
Address specifications for the Listener process are operating system-specific and network protocol-specific.
MTS_LISTENER_ADDRESS is obsolete but is supported for backward compatibility. The functionality of MTS_LISTENER_ADDRESS has been replaced with the LOCAL_LISTENER parameter and LISTENER attribute of the MTS_DISPATCHERS parameter. For more information on these parameters, see "LOCAL_LISTENER" on page 1-56 and "MTS_DISPATCHERS" on page 1-68.
For more information, see the Oracle8 Server Administrator's Guide. See your operating system-specific Oracle documentation and Net8 documentation for a description of how to specify addresses for the protocols on your system.
MTS_MAX_DISPATCHERS specifies the maximum number of dispatcher processes allowed to be running simultaneously.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
defaults to whichever is greater: 20 or 2 times the value of MAX_SERVERS |
| Range of values: |
operating system-dependent |
MTS_MAX_SERVERS specifies the maximum number of shared server processes allowed to be running simultaneously.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
If MTS_MULTIPLE_LISTENERS is set to TRUE, the syntax of the MTS_LISTENER_ADDRESS parameter changes to the following:
MTS_MULTIPLE_LISTENERS = TRUE
MTS_LISTENER_ADDRESS =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(PORT=5000)(HOST=zeus))\
(ADDRESS=(PROTOCOL=decnet)(OBJECT=outa)(NODE=zeus))
MTS_MULTIPLE _LISTENERS is obsolete but is supported for backward compatibility. The functionality of MTS_MULTIPLE _LISTENERS has been replaced by the LOCAL_LISTENER parameter and LISTENER attribute of the MTS_DISPATCHERS parameter. For more information, see "LOCAL_LISTENER" on page 1-56 and "MTS_DISPATCHERS" on page 1-68.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
each name-value listed in Table 1-6 defaults to 10 |
| Range of values: |
DEFAULTS/EVENT_LOOPS/MESSAGES/SERVER_BUFFERS/CLIENT_BUFFERS/TOTAL_BUFFERS/IN_CONNECTS/OUT_CONNECTS/RECONNECTS |
MTS_RATE_LOG_SIZE specifies the sample size used to calculate dispatcher rate statistics. The sample size determines how much memory will be used and the frequency with which maximum rates will be determined. The memory used by each dispatcher is about 8 bytes per statistic multiplied by the sample size specified.
Dispatcher rate statistics themselves are calculated by first logging a sample of events (the size of the sample is specified by MTS_RATE_LOG_SIZE) and the times at which they occur. The rates are then calculated based on this sample.
MTS_RATE_LOG_SIZE accepts a name-value string. Each value defaults to 10. These values are shared among all dispatchers.
The following declaration for MTS_RATE_LOG_SIZE directs each dispatcher to log this many events: 4 inbound connections, 32 buffers to go either to the client or the server, and 16 events for unspecified statistics.
MTS_RATE_LOG_SIZE="(IN_CONNECTS=4)(TOTAL_BUFFERS=32)(DEFAULTS=16)"
Valid name values for MTS_RATE_LOG_SIZE are listed below.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
default values for the name-value strings are listed in Table 1-7 |
| Range of values: |
DEFAULTS/EVENT_LOOPS/MESSAGES/SERVER_BUFFERS/CLIENT_BUFFERS/TOTAL_BUFFERS/IN_CONNECTS/OUT_CONNECTS/RECONNECTS |
MTS_RATE_SCALE specifies the scale at which dispatcher rate statistics are reported. The values are specified in 100ths of a second. Thus,
MTS_RATE_SCALE = "(EVENT_LOOPS=6000)"
means that the event loops statistic will be reported on a once per-minute interval.
MTS_RATE_SCALE accepts a name-value string. Valid names are listed in Table 1-7.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
0 |
| Range of values: |
operating system-dependent |
MTS_SERVERS specifies the number of server processes that you want to create when an instance is started up.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NULL |
MTS_SERVICE specifies the name of the service you want to be associated with the dispatcher. Using this name in the CONNECT string allows users to connect to an instance through a dispatcher. Oracle always checks for such a service before establishing a normal database connection.
The name you specify must be unique. It should not be enclosed in quotation marks. It is a good idea for this name to be the same as the instance name. That way, if the dispatcher is unavailable for any reason, the CONNECT string will still connect the user to the database.
If not specified, MTS_SERVICE defaults to the value specified by DB_NAME. If DB_NAME also is not specified, the Oracle Server returns an error at startup indicating that the value for this parameter is missing.
For more information, see the Oracle8 Server Administrator's Guide. See also the Net8 Administrator's Guide.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
derived |
| Range of values: |
any valid character string, with a maximum of 10 bytes (not including null) |
NLS_CURRENCY specifies the string to use as the local currency symbol for the L number format element. The default value of this parameter is determined by NLS_TERRITORY.
For more information, see "NLS_CURRENCY" on page 4-20. See also the Oracle8 Server Administrator's Guide.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
derived |
| Range of values: |
any valid date format mask but not exceeding a fixed length |
NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY. The value of this parameter can be any valid date format mask, and the value must be surrounded by double quotation marks. For example:
NLS_DATE_FORMAT = "MM/DD/YYYY"
For more information, see "NLS_DATE_FORMAT" on page 4-21. See also the Oracle8 Server Administrator's Guide.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
value for NLS_LANGUAGE |
| Range of values: |
any valid NLS_LANGUAGE value |
NLS_DATE_LANGUAGE specifies the language to use for the spelling of day and month names and date abbreviations (AM, PM, AD, BC). The default value of this parameter is the language specified by NLS_LANGUAGE.
For more information, see "NLS_DATE_ LANGUAGE" on page 4-22. See also the Oracle8 Server Administrator's Guide.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
derived |
| Range of values: |
any valid NLS_TERRITORY value |
NLS_ISO_CURRENCY specifies the string to use as the international currency symbol for the C number format element. The default value of this parameter is determined by NLS_TERRITORY.
For more information, see "NLS_ISO_CURRENCY" on page 4-23. See also the Oracle8 Server Administrator's Guide.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
operating system-dependent |
| Range of values: |
any valid language name |
NLS_LANGUAGE specifies the default language of the database. This language is used for messages, the day and month names, the symbols for AD, BC, AM, and PM, and the default sorting mechanism. This parameter has the format:
NLS_LANGUAGE = FRENCH
Examples of supported languages are American, French, and Japanese.
This parameter determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT. For a complete list of languages, see "Supported Languages" on page 4-41.
For more information, see "NLS_LANGUAGE" on page 4-15. See also the Oracle8 Server Administrator's Guide, your country release notes, and operating system-specific Oracle documentation.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
derived |
NLS_NUMERIC_CHARACTERS specifies the characters to use as the group separator and decimal and overrides those defined implicitly by NLS_TERRITORY. The group separator is the character that separates integer groups (that is, the thousands, millions, billions, and so on). The decimal separates the integer portion of a number from the decimal portion.
Any character can be the decimal or group separator. The two characters specified must be single-byte, and both characters must be different from each other each other. The characters cannot be any numeric character or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>).
The characters are specified in the following format:
NLS_NUMERIC_CHARACTERS = "<decimal_character><group_separator>"
For example, if you wish to specify a comma as the decimal character and a space as the group separator, you would set this parameter as follows:
NLS_NUMERIC_CHARACTERS = ", "
The default value of this parameter is determined by NLS_TERRITORY.
For more information, see "NLS_NUMERIC_CHARACTERS" on page 4-24. See also the Oracle8 Server Administrator's Guide.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
derived |
| Range of values: |
BINARY or valid linguistic definition name |
NLS_SORT specifies the collating sequence for ORDER BY queries. If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.
Note: Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort into the execution plan.
You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.
The default value of this parameter depends on the value of the NLS_LANGUAGE parameter.
For more information on this parameter, see "NLS_SORT" on page 4-25 and the Oracle8 Server Administrator's Guide. For a list of supported linguistic definitions and extended definitions, see "Linguistic Definitions" on page 4-51. See also your operating system-specific Oracle documentation for the sorting rules used by the linguistic sorting mechanisms.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
operating system-dependent |
| Range of values: |
any valid territory name |
NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering. Also specifies the default date format, the default decimal character and group separator, and the default ISO and local currency symbols. Supported territories include America, France, Japan, and so on. For a complete list of territories, see "Supported Territories" on page 4-42.
This parameter determines the default values for the following parameters: NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, and NLS_NUMERIC_CHARACTERS.
For more information, see "NLS_TERRITORY" on page 4-17. See also the Oracle8 Server Administrator's Guide. See your operating system-specific Oracle documentation for the territory-dependent default values for these parameters.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SESSION, ALTER SYSTEM DEFERRED |
| Default value: |
10% |
| Range of values: |
0% to operating system-dependent maximum |
OBJECT_CACHE_MAX_SIZE_PERCENT specifies the percentage of the optimal cache size that the session object cache can grow past the optimal size; the maximum size is equal to the optimal size plus the product of this percentage and the optimal size. When the cache size exceeds this maximum size, the system will attempt to shrink the cache to the optimal size.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SESSION, ALTER SYSTEM DEFERRED |
| Default value: |
100 Kbytes |
| Range of values: |
10 Kbytes to operating system-dependent maximum |
OBJECT_CACHE_OPTIMAL_SIZE specifies the size to which the session object cache is reduced when the size of the cache exceeds the maximum size.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
50 |
| Range of values: |
1 - operating system limit |
OPEN_CURSORS specifies the maximum number of open cursors (context areas) a session can have at once. This constrains a session from opening an excessive number of cursors. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead by setting this value too high.
It is important to have the value of OPEN_CURSORS set high enough to prevent your application from running out of open cursors. The number will vary from one application to another.
This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the range of values.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
4 |
| Range of values: |
0 - 255 |
OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one session. The value should equal or exceed the number of databases referred to in a single SQL statement that references multiple databases so that all the databases can be open to execute the statement. Value should be increased if many different databases are accessed over time. Thus, if queries alternately access databases A, B, and C and OPEN_LINKS is set to 2, time would be spent waiting while one connection was broken and another made.
This parameter refers only to connections used for distributed transactions. Direct connections to a remote database specified as an application connects are not counted. For information on migratable open connections for XA transactions, see "OPEN_LINKS_PER_INSTANCE" on page 1-82.
If OPEN_LINKS is set to 0, then no distributed transactions are allowed.
For more information, see the Oracle8 Server Administrator's Guide and Oracle8 Server Distributed Database Systems.
OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open connections. XA transactions use migratable open connections so that the connections are cached after a transaction is committed. Another transaction can use the connection provided the user that created the connection is the same as the user that owns the transaction.
OPEN_LINKS_PER_INSTANCE is different from the OPEN_LINKS parameter in that OPEN_LINKS indicates the number of connections from a session. The OPEN_LINKS parameter is not applicable to XA applications. For more information, see "OPEN_LINKS" on page 1-82.
| Parameter type: |
string LIST |
| Parameter class: |
dynamic, scope = ALTER SYSTEM, ALTER SESSION |
| Default value: |
all active instances |
| Allowable values: |
a string representing a group name. |
OPS_ADMIN_GROUP is a Parallel Server parameter. OPS_ADMIN_GROUP allows instances to be partitioned in a parallel server environment for monitoring or administration purposes. The database must be mounted in parallel server mode (that is, PARALLEL_SERVER=TRUE).
The value of OPS_ADMIN_GROUP determines which instances return information in a GV$ fixed-view query. For example, assume instances {1,3,4} are active at the time of a GV$ query and the instance group group1 contains the instances 1 and 4. If OPS_ADMIN_GROUP = group1, then a query over GV$viewname retrieves information only from instances 1 and 4.
If none of the instances specified for OPS_ADMIN_GROUP are active and a GV$ view is queried, then an error is returned.
If the database is mounted in non-parallel sever mode (that is, PARALLEL_SERVER=FALSE) then the OPS_ADMIN_GROUP parameter has no effect. Every query involving GV$ views will be run on the local instance.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope=ALTER SESSION |
| Default value: |
CHOOSE |
| Range of values: |
RULE/CHOOSE/FIRST_ROWS/ALL_ROWS |
OPTIMIZER_MODE specifies the behavior of the optimizer. 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.
For more information about tuning SQL statements, see Oracle8 Server Tuning. For more information about the optimizer, see Oracle8 Server Concepts and Oracle8 Server Tuning.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
0 |
| Range of values: |
0 - 100 |
OPTIMIZER_PERCENT_PARALLEL specifies the amount of parallelism that the optimizer uses in its cost functions. The default of 0 means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object's degree of parallelism in computing the cost of a full table scan operation. Low values favor indexes, and high values favor table scans.
Cost-based optimization will always be used for any query that references an object with a nonzero degree of parallelism. For such queries a RULE hint or optimizer mode or goal will be ignored. Use of a FIRST_ROWS hint or optimizer mode will override a nonzero setting of OPTIMIZER_PERCENT_PARALLEL.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
5 |
OPTIMIZER_SEARCH_LIMIT specifies the search limit for the optimizer.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NULL |
| Range of values: |
valid collection name up to 16 characters long |
ORACLE_TRACE_COLLECTION_NAME specifies the Oracle Trace collection name. This parameter is also used in the output file names (collection definition file .CDF and data file .DAT).
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
operating system-specific |
| Range of values: |
full directory pathname |
ORACLE_TRACE_COLLECTION_PATH specifies the directory pathname where Oracle Trace collection definition and data files are located. If you accept the default, the complete file specification is generally (may be different for non-UNIX systems) $ORACLE_HOME/rdbms/log/collection name.cdf and collection name.dat.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
5242880 |
| Range of values: |
0 - 4294967295 |
ORACLE_TRACE_COLLECTION_SIZE specifies the maximum size, in bytes, of the Oracle Trace collection file. Once the collection file reaches this maximum, the collection is disabled.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE, FALSE |
In order to enable Oracle Trace collections for the server, ORACLE_TRACE_ENABLE should be set and left at TRUE. When set to TRUE, this does not start an Oracle Trace collection, it allows Oracle Trace to be used for that server. When set to TRUE, Oracle Trace can then be started by using the Oracle Trace Manager application (supplied with the Oracle Enterprise Manager Performance Pack), or including a name in the oracle_trace_collection_name parameter (default = null).
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
operating system-specific |
| Range of values: |
valid facility name up to 16 characters long |
ORACLE_TRACE_FACILITY_NAME specifies the Oracle Trace product definition file (.FDF file). The file must be located in the directory pointed to by the ORACLE_TRACE_FACILITY_PATH parameter. The product definition file contains definition information for all the events and data items that can be collected for a product that uses the Oracle Trace data collection API. Products can have multiple product definition files (multiple event sets and data items). The Oracle Server has multiple event sets and therefore multiple product definition files. Oracle recommends that you use the "default" event set for Server collections ORACLED.FDF. See the Oracle Trace documentation for more information on the Server event sets.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
operating system-specific |
| Range of values: |
full directory pathname |
ORACLE_TRACE_FACILITY_PATH specifies the directory pathname where Oracle TRACE facility definition files are located.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
operating system-specific (typically "OPS$") |
OS_AUTHENT_PREFIX authenticates users attempting to connect to the server with the users' operating system account name and password. The value of this parameter is concatenated to the beginning of every user's operating system account. The prefixed username is compared with the Oracle usernames in the database when a connection request is attempted. The default value of this parameter is OPS$ for backward compatibility with previous versions. However, you might prefer to set the prefix value to "" (a null string), thereby eliminating the addition of any prefix to operating system account names.
Note: The text of the OS_AUTHENT_PREFIX parameter is case sensitive with some operating systems.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
If OS_ROLES is set to TRUE, the database allows the operating system to identify each username's roles. When a user attempts to create a session, the username's security domain is initialized using the roles identified by the operating system. A user can subsequently enable as many roles identified by the operating system as specified by the parameter MAX_OS_ROLES.
If OS_ROLES is set to TRUE, the operating system completely manages the role grants for all database usernames. Any revokes of roles granted by the operating system are ignored, and any previously granted roles are ignored.
The default value, FALSE, causes roles to be identified and managed by the database.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
| Range of values: |
0 - number of instances |
| Multiple instances: |
should have the same value |
PARALLEL_DEFAULT_MAX_INSTANCES specifies the default number of instances to split a table across for parallel query processing. The value of this parameter is used if the INSTANCES DEFAULT is specified in the PARALLEL clause of a table's definition. This parameter might be desupported in future releases.
For more information, see Oracle8 Parallel Server Concepts & Administration.
PARALLEL_INSTANCE_GROUP is a Parallel Server parameter and can be used in parallel mode only. This parameter identifies the parallel instance group to be used for spawning parallel query slaves. Parallel operations will spawn parallel query slaves only on instances that specify a matching group in their INSTANCE_GROUPS parameter.
If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a group that does not exist, then the operation runs serially. No parallelism is used.
For more information see Oracle8 Parallel Server Concepts & Administration.
PARALLEL_MAX_SERVERS specifies the maximum number of parallel query servers or parallel recovery processes for an instance. Oracle will increase the number of query servers as demand requires from the number created at instance startup up to this value. The same value should be used for all instances in a parallel server environment.
Proper setting of the PARALLEL_MAX_SERVERS parameter ensures that the number of query servers in use will not cause a memory resource shortage during periods of peak database use.
If PARALLEL_MAX_SERVERS is set too low, some queries may not have a query server available to them during query processing.
Setting PARALLEL_MAX_SERVERS too high leads to memory resource shortages during peak periods, which can degrade performance. For each instance to which you do not want to apply the parallel query option, set this initialization parameter to zero.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
cpus*parallel_max_servers*1.5*(OS message buffer size) or cpus*5*1.5*(OS message size) |
| Range of values: |
0 -(SHARED_POOLSIZE*.90) |
PARALLEL_MIN_MESSAGE_POOL specifies the minimum permanent amount of memory which will be allocated from the SHARED POOL (see SHARED_POOL_SIZE), to be used for messages in parallel execution.
This memory is allocated at startup time if PARALLEL_MIN_SERVERS is set to a non-zero value, or when the server is first allocated. Setting this parameter is most effective when PARALLEL_MIN_SERVERS is set to a non-zero value, because the memory will be allocated in a contiguous section.
This parameter should only be set if the default formula is known to be significantly inaccurate. setting this parameter too high will lead to a shortage of memory for the shared pool; setting it too low will lead to costlier memory allocation when doing parallel execution. This parameter cannot be set to a number higher than 90% of the shared pool.
PARALLEL_MIN_PERCENT specifies the minimum percent of threads required for parallel query. Setting this parameter ensures that a parallel query will not be executed sequentially if adequate resources are not available. The default value of 0 means that this parameter is not used.
If too few query slaves are available, an error message is displayed and the query is not executed. Consider the following settings:
PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
In a system with 20 instances up and running, the system would have a maximum of 200 query slaves available. If 190 slaves are already in use and a new user wants to run a query with 40 slaves (for example, degree 2 instances 20), an error message would be returned because 20 instances (that is, 50% of 40) are not available.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
0 |
| Range of values: |
0 - PARALLEL_MAX_SERVERS |
| Multiple instances: |
can have different values |
PARALLEL_MIN_SERVERS specifies the 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.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
| Multiple instances: |
must have the same value |
Set PARALLEL_SERVER to TRUE to enable the Parallel Server option.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
operating system-specific |
| Range of values: |
0 to the OS-dependent maximum |
| Multiple instances: |
can have different values |
PARALLEL_SERVER_IDLE_TIME specifies the amount of idle time after which Oracle terminates a query server process. This value is expressed in minutes.
PARALLEL_TRANSACTION_RESOURCE_TIMEOUT is a Parallel Server parameter. This parameter specifies the maximum amount of time which can pass before a session, executing a parallel operation (either parallel DDL or parallel DML), times out while waiting for a resource held by another session in an incompatible lock mode. Such timeouts are an indication of potential deadlock involving the parallel transaction and other transactions currently running in the parallel server system.
The value of the parameter is specified in seconds. The time begins when the session starts to wait for a busy resource. Setting the parameter to 0 sets the maximum timeout to an effectively infinite value. Note that this is only the maximum timeout allowed for parallel transactions. For certain resources, a lower timeout value is used by Oracle.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
| OK to change: |
yes |
If PARTITION_VIEW_ENABLED is set to TRUE, the optimizer prunes (or skips) unnecessary table accesses in a partition view. This parameter also changes the way the cost-based optimizer computes statistics on a partition view from statistics on underlying tables.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SESSION, ALTER SYSTEM |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
The PLSQL_V2_COMPATIBILITY initialization parameter is optional and sets the compatibility level for PL/SQL. The default value is FALSE: PL/SQL V3 behavior is enforced and V2 behavior is not allowed.
If PLSQL_V2_COMPATIBILITY=TRUE,then the following PL/SQL V2 behaviors are accepted when you are running PL/SQL V3:
function foo (x IN table_t) is
begin
x.delete(2);
end;
In contrast, PL/SQL V3 correctly enforces the read-only semantics of IN parameters and does not let index table methods modify index tables passed in as IN parameters.
In contrast, PL/SQL V3 does not permit OUT parameters to be used in expression contexts.
return expression
which should be
return type
In contrast, PL/SQL V3 returns an error.
In contrast, PL/SQL V3 does not allow the passing of an IN argument into another procedure as an OUT.
In contrast, PL/SQL V3 requires a type definition to precede its use.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
FALSE/TRUE |
| OK to change: |
no |
If PRE_PAGE_SGA is set to TRUE, this parameter touches all the SGA pages, causing them to be brought into memory. As a result, it increases instance start up time and user login time, but it can reduce the number of page faults that occur shortly thereafter. The reduction in page faults allows the instance to reach its maximum performance capability quickly rather than through an incremental build up. It is most useful on systems that have sufficient memory to hold all the SGA pages without degrading performance in other areas.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
30 |
| Range of values: |
6 to operating system-dependent |
| Multiple instances: |
can have different values |
For a multiple-process operation, PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to an Oracle Server. This value should allow for all background processes such as LCK processes, Job Queue processes, and Parallel Query processes.
The default values of SESSIONS is derived from PROCESSES. If you alter the value of PROCESSES, you may want to adjust the values of this derived parameters.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the range of values.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
| Range of values: |
operating system-dependent, but cannot exceed PARALLEL_MAX_SERVERS |
RECOVERY_PARALLELISM specifies the number of processes to participate in instance or media recovery. A value of zero or one indicates that recovery is to be performed serially by one process.
For more information, see Oracle8 Parallel Server Concepts & Administration.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
REDUCE_ALARM is an initialization parameter.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SESSION, ALTER SYSTEM |
| Default value: |
TIMESTAMP |
| Range of values: |
TIMESTAMP/SIGNATURE |
REMOTE_DEPENDENCIES_MODE is used with PL/SQL stored procedures. It specifies how dependencies upon remote stored procedures are to be handled by the database.
If this parameter is set to TIMESTAMP, which is the default setting, the client running the procedure compares the timestamp recorded on the server side procedure with the current timestamp of the local procedure and executes the procedure only if the timestamps match.
If the parameter is set to SIGNATURE, the procedure is allowed to execute as long as the signatures are considered safe. This allows client PL/SQL applications to be run without recompilation.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
NONE |
| Range of values: |
NONE/SHARED/EXCLUSIVE |
| Multiple instances: |
should have the same value |
REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file and how many databases can use the password file. Setting the parameter to NONE signifies that Oracle should ignore any password file (and therefore privileged users must be authenticated by the operating system). Setting the parameter to EXCLUSIVE signifies that the password file can be used by only one database and the password file can contain names other than SYS and INTERNAL. Setting the parameter to SHARED allows more than one database to use a password file. However, the only users recognized by the password file are SYS and INTERNAL.
For more information about secure connections for privileged users, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
Setting REMOTE_OS_AUTHENT to TRUE allows authentication of remote clients with the value of OS_AUTHENT_PREFIX.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
Setting REMOTE_OS_ROLES to TRUE allows operating system roles for remote clients. The default value, FALSE, causes roles to be identified and managed by the database for remote clients.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
TRUE |
| Range of values: |
TRUE/FALSE |
Setting REPLICATION_DEPENDENCY_TRACKING to TRUE turns on dependency tracking for read/write operations to the database. Dependency tracking is essential for the Replication Server to propagate changes in parallel. This is the default value. FALSE allows read/write operations to the database to run faster, but does not produce dependency information for the Replication Server to perform parallel propagations. Users should not specify this value unless they are sure that their application will perform absolutely no read/write operations to replicated tables.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
The value of RESOURCE_LIMIT changes the enforcement status of resource limits set in database profiles. A value of FALSE disables the enforcement of resource limits. A value of TRUE enables the enforcement of resource limits.
For more information, see the Oracle8 Server Administrator's Guide.
ROLLBACK_SEGMENTS specifies one or more rollback segments to allocate by name to this instance. If ROLLBACK_SEGMENTS is set, 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).
Note: Never name the SYSTEM rollback segment as a value for the ROLLBACK_SEGMENTS parameter.
This parameter has the following syntax:
ROLLBACK_SEGMENTS = (rbseg_name [, rbseg_name] ... )
Although this parameter usually specifies private rollback segments, it can also specify public rollback segments if they are not already in use.
Different instances in an Oracle Parallel Server cannot name the same rollback segment for any of the ROLLBACK_SEGMENTS. Query the data dictionary view DBA_ROLLBACK_SEGS to find the name, segment ID number, and status of each rollback segment in the database.
For more information, see the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
10 |
| Range of values: |
10 - 3300 |
ROW_CACHE_CURSORS specifies the maximum number of cached recursive cursors used by the dictionary cache manager for selecting rows from the data dictionary. The default value is sufficient for most systems.
For more information about memory structure and processes, see Oracle8 Server Concepts.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
ALWAYS |
| Range of values: |
ALWAYS/DEFAULT/INTENT |
| Multiple instances: |
must have the same value |
ROW_LOCKING specifies whether row locks are acquired when a table is updated or on update. The default of ALWAYS means that only row locks are acquired when a table is updated. DEFAULT is the same as ALWAYS. INTENT means that only row locks are used on a SELECT FOR UPDATE, but at update time table locks are acquired.
For information about tuning SQL statements, see Oracle8 Server Tuning.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
10 |
| Range of values: |
10 - 32000 |
| Multiple instances: |
can have different values |
SEQUENCE_CACHE_ENTRIES specifies the number of sequences that can be cached in the SGA for immediate access. This cache is managed on a least recently used (LRU) basis, so if a request is made for a sequence that is not in the cache and there are no free entries, the oldest one on the LRU list is deleted and replaced with the newly requested one. Highest concurrency is achieved when this value is set to the highest possible number of sequences that will be used on an instance at one time.
Each entry requires approximately 110 bytes in the SGA for an Oracle Parallel Server. Sequences created with the NOCACHE option do not reside in this cache. They must be written through to the data dictionary on every use.
For more information about managing schema objects, see Oracle8 Server Administrator's Guide and Oracle8 Server Application Developer's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
7 |
| Multiple instances: |
can have different values |
SEQUENCE_CACHE_HASH_BUCKET specifies the number of sequences that can be cached in the SGA for immediate access. This cache is managed on a least recently used (LRU) basis, so if a request is made for a sequence that is not in the cache and there are no free entries, the oldest one on the LRU list is deleted and replaced with the newly requested one. Highest concurrency is achieved when this value is set to the highest possible number of sequences that will be used on an instance at one time.
Each entry requires approximately 110 bytes in the SGA for an Oracle Parallel Server. Sequences created with the NOCACHE option do not reside in this cache. They must be written through to the data dictionary on every use.
For more information about managing schema objects, see Oracle8 Server Administrator's Guide and Oracle8 Server Application Developer's Guide.
| Parameter type: |
string LIST |
| Parameter class: |
static |
| Default Value: |
NULL |
| Range of values: |
DISABLE/SELECT/DML/PLSQL/ALL/NULL |
This parameter indicates which types of SQL cursors should make use of the serial-reusable memory feature. This feature moves well-structured private cursor memory into the SGA (shared pool) so that it can be reused by sessions executing the same cursor. The default NULL value is equivalent to setting the value to DISABLE. Values include:
| Parameter type: |
integer |
| Parameter class: |
static |
| Default: |
0 |
| Range of values: |
0 to operating system dependent |
| Multiple instances: |
can have different values: |
SESSION_CACHED_CURSORS lets you specify the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and need not reopen the cursor. The value of this parameter is the maximum number of session cursors to keep in the session cursor cache.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
10 |
| Range of values: |
1 - the least of (50, MAX_OPEN_FILES defined at the OS level) |
SESSION_MAX_OPEN_FILES specifies the maximum number of BFILEs that can be opened in any given session. Once this number is reached, subsequent attempts to open more files in the session using DBMS_LOB.FILEOPEN() or OCILobFileOpen() will fail. This parameter is also dependent on the equivalent parameter defined for the underlying operating system.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
derived (1.1 * PROCESSES + 5) |
SESSIONS specifies the total number of user and system sessions. The default number is greater than PROCESSES to allow for recursive sessions.
The default values of ENQUEUE_RESOURCES and TRANSACTIONS are derived from SESSIONS. If you alter the value of SESSIONS, you might want to adjust the values of ENQUEUE_RESOURCES and TRANSACTIONS.
With the multi-threaded server, you should adjust the value of SESSIONS to approximately 1.1 * (total number of connections).
For more information on memory structures and processes, see Oracle8 Server Concepts.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
FULL |
| Range of values: |
FULL/PARTIAL |
The value of SHADOW_CORE_DUMP determines whether the SGA will be included in core dumps. By default (FULL), the SGA is included in the core dump. If SHADOW_CORE_DUMP=PARTIAL, the SGA is not dumped.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
5000 |
| Range of values: |
5000 - SHARED_POOL_RESERVED_SIZE (in bytes) |
The value of SHARED_POOL_RESERVED_MIN_ALLOC 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.
SHARED_POOL_RESERVED_MIN_ALLOC can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".
For more information on this parameter see Oracle8 Server Tuning.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
5% of the value of SHARED_POOL_SIZE |
| Range of values: |
from SHARED_POOL_RESERVED_MIN_ALLOC to one half of SHARED_POOL_SIZE (in bytes) |
SHARED_POOL_RESERVED_SIZE specifies the shared pool space which is reserved for large contiguous requests for shared pool memory. This parameter, along with the SHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid performance degradation in the shared pool from situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.
The shared pool contains the library cache of shared SQL requests, the dictionary cache, stored procedures, and other cache structures that are specific to a particular instance configuration. For example, in an MTS configuration, the session and private SQL area for each client process is included in the shared pool. When the instance is configured for parallel query, the shared pool includes the parallel query message buffers.
Proper sizing of the shared pool can reduce resource consumption in at least three ways:
Default value for SHARED_POOL_RESERVED_SIZE is 5% of the SHARED_POOL_SIZE. This means that, by default, the reserved list will always be configured.
If SHARED_POOL_RESERVED_SIZE > 1/2 SHARED_POOL_SIZE, Oracle signals an error.
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.
SHARED_POOL_RESERVED_SIZE can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".
For more information on this parameter see Oracle8 Server Tuning.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
3,500,000 bytes |
| Range of values: |
300 Kbytes - operating system-dependent |
SHARED_POOL_SIZE specifies the size of the shared pool in bytes. The shared pool contains shared cursors and stored procedures. Larger values improve performance in multi-user systems. Smaller values use less memory.
SHARED_POOL_SIZE can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".
For more information, see the Oracle8 Server Administrator's Guide.
SORT_AREA_RETAINED_SIZE specifies the maximum amount, in bytes, of User Global Area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer which is used to maintain a portion of the sort in memory. This memory is released back to the UGA, 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.
For more information, see Oracle8 Server Concepts.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED |
| Default value: |
operating system-dependent |
| Range of values: |
0 - system-dependent value |
SORT_AREA_SIZE specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. If MTS is enabled, the sort area is allocated from the SGA. After the sort is complete and all that remains to do is to fetch the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched, 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.
If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk hold the intermediate sort runs.
The default is usually adequate for most OLTP operations. You might want to adjust this parameter for decision support systems, batch jobs, or large CREATE INDEX operations.
For more information, see Oracle8 Server Concepts. See also your operating system-specific Oracle documentation for the default value on your system.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED |
| Default value: |
AUTO |
| Range of values: |
AUTO/TRUE/FALSE |
SORT_DIRECT_WRITES can improve sort performance if memory and temporary space are abundant on your system. This parameter controls whether sort data will bypass the buffer cache to write intermediate sort results to disk. When set to the default of AUTO, and the value of the sort area size is greater than ten times the block size, memory is allocated from the sort area to do this. When SORT_DIRECT_WRITES is TRUE, additional buffers are allocated from memory during each sort.
Additional temporary segment space can be required when SORT_DIRECT_WRITES is enabled. The sort allocation mechanism allocates temporary space using fixed-size chunks which are based on the SORT_WRITE_BUFFER_SIZE parameter. Since the values for this parameter are typically an order of magnitude larger than the DB_BLOCK_SIZE chunks used when SORT_DIRECT_WRITES is disabled, unused temporary space in the final sort segment increases the overall space requirements.
When SORT_DIRECT_WRITES is set to FALSE, the sorts that write to disk write through the buffer cache.
For more information, see Oracle8 Server Tuning.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED |
| Default value: |
operating system-dependent |
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. The value is operating system-specific. You can set the value for your specific disk subsystem using the following equation:
See your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
operating system-dependent |
SORT_SPACEMAP_SIZE specifies the size in bytes of the sort space map. Only if you have very large indexes should you adjust this parameter. A sort automatically increases its space map if necessary, but it does not necessarily do so when it will make best use of disk storage. The sort makes optimal use of disk storage if SORT_SPACEMAP_SIZE is set to
[(total_sort_bytes) / (sort_area_size)] + 64
where total_sort_bytes is
(number_of_records) * [sum_of_average_column_sizes + (2 * number_of_col)]
Here, columns include the SELECT list for the ORDER BY, the SELECT list for the GROUP BY, and the key list for CREATE INDEX. It also includes 10 bytes for ROWID for CREATE INDEX and GROUP BY or ORDER BY columns not mentioned in the SELECT list for these cases.
For more information on memory structures and processes, see Oracle8 Server Concepts. See also your operating system-specific Oracle documentation for the default value.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED |
| Default value: |
32768 |
| Range of values: |
32Kb, 64Kb |
SORT_WRITE_BUFFER_SIZE sets the size of the sort IO buffer when the SORT_DIRECT_WRITES parameter is set to TRUE. SORT_WRITE_BUFFER_SIZE is recommended for use with symmetric replication.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED |
| Default value: |
1 |
| Range of values: |
2-8 |
SORT_WRITE_BUFFERS specifies the number of sort buffers when the SORT_DIRECT_WRITES parameter is set to TRUE. SORT_WRITE_BUFFERS is recommended for use with symmetric replication.
| Parameter type: |
integer |
| Parameter class: |
dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED |
| Default value: |
1 |
| Range of values: |
1-1,000,000 |
In multi-processor environments, you can improve performance by tuning the SPIN_COUNT initialization parameter.
A process continues to request a latch until it obtains one. If the number of requests reaches SPIN_COUNT, the process fails to acquire the latch, sleeps, then tries to acquire the latch again. Because a latch is a low-level lock, a process does not hold it long. It is less expensive to use CPU time by spinning a process than it is to make a process sleep.
You can check the contention level of the latch by monitoring the miss rate and sleep rate from the UTLBSTAT and UTLESTAT scripts. Try reducing the sleep rate by tuning the spin count. If the contention level is high, increase the spin count to allow processes to spin more before acquiring latches. However, since increasing the spin count increases CPU usage, system throughput may decline at some point.
The default value is adequate for almost all systems.
See your operating system-specific documentation for more information.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
The value of 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. The value can also be changed using the DBMS_SYSTEM package.
For more information about performance diagnostic tools, see Oracle8 Server Tuning. See also Oracle8 Server SQL Reference.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
SQL92_SECURITY specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SESSION |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
The value of STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries. If set to TRUE, the optimizer will consider performing a cost-based query transformation on the star query. If set to FALSE, the transformation will not be applied.
For more information, see Oracle8 Server Concepts.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
TRUE |
| Range of values: |
TRUE, FALSE |
TAPE_ASYNCH_IO can be used to control whether I/O to sequential devices (for example, BACKUP/RESTORE of Oracle data TO/FROM tape) is asynchronous. If a platform supports asynchronous I/O to sequential devices, it is recommended that this parameter is left to its default. However, if the asynchronous I/O implementation is not stable, TAPE_ASYNCH_IO can be used to disable its use. If a platform does not support asynchronous I/O to sequential devices, this parameter has no effect.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
derived (SESSIONS) |
| Range of values: |
0 - operating system-dependent |
TEMPORARY_TABLE_LOCKS specifies the number of temporary tables that can be created in the temporary segment space. A temporary table lock is needed any time a sort occurs that is too large too hold in memory, either as the result of a select on a large table with ORDER BY or as a result of sorting a large index. Installations with many users of applications that simultaneously perform several ordered queries on large tables might need to increase this number. Most installations should do well with the default.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the range of values.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
0 |
| Range of values: |
0 - maximum number of enabled threads |
| Multiple instances: |
if specified, must have different values |
THREAD is applicable only to instances that intend to run in parallel (shared) mode.
THREAD specifies the number of the redo thread that is to be used by the instance. Any available redo thread number can be used, but an instance cannot use the same thread number as another instance. Also, an instance cannot start when its redo thread is disabled. A value of zero causes an available, enabled public thread to be chosen. An instance cannot mount a database if the thread is used by another instance or if the thread is disabled.
Redo threads are specified with the THREAD option of the ALTER DATABASE ADD LOGFILE command. Redo threads are enabled with the ALTER DATABASE ENABLE [PUBLIC] THREAD command. The PUBLIC keyword signifies that the redo thread may be used by any instance. This is useful when running systems that have faster access to disks from certain nodes.
Thread 1 is the default thread in exclusive mode. An instance running in exclusive mode can specify THREAD to use the redo log files in a thread other than thread 1.
For more information, see Oracle8 Parallel Server Concepts & Administration and Oracle8 Server SQL Reference.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
OFF |
| Range of values: |
OFF, CALL, LOGOFF |
TIMED_OS_STATISTICS can be used by the system administrator to gather operating system statistics when calls are pushed or popped, or when the user logs off. The operating system statistics are gathered only if the TIMED_STATISTICS parameter is set to TRUE. For more information on this parameter, see "TIMED_STATISTICS" on page 1-113.
TIMED_OS_STATISTICS can be assigned one of the following strings:
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SYSTEM, ALTER SESSION |
| Default value: |
FALSE |
| Range of values: |
TRUE/FALSE |
If TIMED_STATISTICS is FALSE, the statistics related to time are always 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. Normally, TIMED_STATISTICS should be FALSE. On some systems with very fast timer access, timing might be enabled even when the parameter is set to FALSE. On these systems, setting the parameter to TRUE might produce more accurate statistics for long-running operations.
For more information about performance diagnostic tools, see Oracle8 Server Tuning.
| Parameter type: |
boolean |
| Parameter class: |
dynamic, scope = ALTER SYSTEM DEFERRED |
| Default value: |
TRUE |
| Range of values: |
TRUE/FALSE |
If TRANSACTION_AUDITING is TRUE, the transaction layer generates a special redo record which contains session and user information. This information includes the user logon name, user name, the session ID, some operating system information, and client information. On each successive commit, the transaction layer generates a record that contains only the session ID (which links back to the first record since it also contains the session ID). These records might be useful if using a redo log analysis tool.
If TRANSACTION_AUDITING is FALSE, no redo record is generated.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
derived (1.1 * SESSIONS) |
| Multiple instances: |
can have different values |
TRANSACTIONS specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.
For more information about memory structures and processes, see Oracle8 Server Concepts and the Oracle8 Server Administrator's Guide.
| Parameter type: |
integer |
| Parameter class: |
static |
| Default value: |
21 |
| Range of values: |
1 - operating system-dependent |
| Multiple instances: |
can have different values |
TRANSACTIONS_PER_ROLLBACK_SEGMENT specifies the number of concurrent transactions allowed per rollback segment. The minimum number of rollback segments acquired at startup is TRANSACTIONS divided by the value for this parameter. For example, if TRANSACTIONS is 101 and this parameter is 10, then the minimum number of rollback segments acquired would be the ratio 101/10, rounded up to 11.
More rollback segments can be acquired if they are named in the parameter ROLLBACK_SEGMENTS.
For more information, see the Oracle8 Server Administrator's Guide. See also your operating system-specific Oracle documentation for the range of values.
| Parameter type: |
boolean |
| Parameter class: |
static |
| Default value: |
TRUE |
| Range of values: |
TRUE/FALSE |
If USE_ISM is FALSE, the shared page table is not enabled. By default, the page table is enabled.
| Parameter type: |
string |
| Parameter class: |
dynamic, scope = ALTER SYSTEM |
| Default value: |
operating system-dependent |
| Range of values: |
valid local pathname, directory, or disk |
USER_DUMP_DEST specifies the pathname for a directory where the server will write debugging trace files on behalf of a user process.
For example, this directory might be set to C:\ORACLE\UTRC on MS-DOS; to /oracle/utrc on UNIX; or to DISK$UR3:[ORACLE.UTRC] on VMS.
For more information about performance diagnostic tools, see Oracle8 Server Tuning. See also your operating system-specific Oracle documentation for the range of values.
| Parameter type: |
string |
| Parameter class: |
static |
| Default value: |
none |
| Range of values: |
any valid directory path |
UTL_FILE_DIR allows database administrators to specify directories that are permitted for PL/SQL file I/O. Each directory must be specified with a separate UTL_FILE_DIR parameter in the INIT.ORA file.
Note that all users can read or write all files specified in the UTL_FILE_DIR parameter(s). This means that all PL/SQL users must be trusted with the information in the directories specified by the UTL_FILE_DIR parameters.
