| Oracle7 Server Reference Manual |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Reference Manual | Library |
Product |
Contents |
Index |
The following topics are included in this chapter:
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 your operating system. For example, it may be in mixed case or lowercase, or it may have a logical name or a variation on the name INIT.ORA. As the DBA, you can choose a different filename for your parameter file.
See your Oracle operating system specific documentation for the default location and filename for the parameter file. This is the file that the Oracle7 Server reads for its parameter information upon startup.
A sample parameter file is provided on the Oracle7 Server distribution medium for each operating system. The distributed sample file is sufficient for initial use, but you will want to make changes in the file to tune your 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:
See your operating system specific Oracle documentation for more information on parameter files.
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 may wish to use the SPOOL command to write the output to a file.
The syntax for dynamically altering the initialization parameters is as follows:
ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value
The ALTER SESSION command changes the value of the parameter until the database is shut down.
The ALTER SYSTEM command modifies the global value of the parameter and survives database shutdown and startup. 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.
Additional Information: For more information about the Parallel Server, see the Oracle7 Parallel Server Concepts & Administration manual.
See your system release bulletins or other operating system specific Oracle documentation for platform specific information on Parallel Server parameters.
See your operating system specific Oracle documentation for more information on operating system dependent Oracle parameters and operating system parameters.
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.
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 value this parameter assumes if not explicitly specified
Range of values:
the valid range of values that this parameter can assume, shown as a minimum and maximum value. Not applicable to all parameters.
Multiple instances:
how the values for this parameter must be specified for multiple instances in an Oracle7 Parallel Server. Not applicable to all parameters.
Ok to change?
notes on changing the parameter value; not specified for all releases
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.
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.
none
Range of values:
NESTED_LOOPS/MERGE/HASH
This parameter sets the type of antijoin that the Oracle7 Server uses. The system checks to verify that it is legal to perform an anijoin, and if it is, processes the subquery depending on the value of this parameter. When set to the value NESTED_LOOPS, the Oracle7 Server evaluates the subqueries in the same way as in release 7.2. When set to the value MERGE, the Oracle7 Server uses the sort merge antijoin algorithm. When set to the value HASH, the Oracle7 Server uses the hash antijoin algorithm to evaluate the subquery.
NONE
Range of values:
NONE (FALSE), DB (TRUE), OS
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 Oracle7 Server Administrator's Guide.
For more information, see the Trusted Oracle7 Server Administrator's Guide.
operating system-dependent
Range of values:
valid local pathname, directory, or disk
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 startups and shutdowns, 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 specific. For platforms that support multiple instances, it takes the form ALERT_sid.LOG. This file grows slowly, but without limit, so the DBA may wish to delete it periodically. The file may be deleted even when the database is running.
For more information, see the Oracle7 Server Administrator's Guide.
See your operating system-specific Oracle documentation for the default value.
FALSE
Range of values:
TRUE/FALSE
The initialization parameter BLANK_TRIMMING changes 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.
Default value:
0.1*DB_BLOCK_BUFFERS
Multiple instances:
should have the same value
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.
For more information, see Oracle7 Parallel Server Concepts & Administration.
FALSE
Range of values:
TRUE/FALSE
Multiple instances:
can have different values
Setting this parameter to TRUE enables the CKPT background process. You should enable the CKPT process only if the performance of the LGWR process decreases significantly during a checkpoint.
Note: Adjust all calculations that depend on the number of background processes to allow for the CKPT process. For example, increase the value of the PROCESSES parameter by one, and increase the values of other parameters whose default values are derived from PROCESSES if you do not use their default values.
Additional Information: For more information, see SESSIONS ![[*]](jump.gif)
.
20
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 Oracle7 Server Administrator's Guide.
FALSE
Range of values:
TRUE/FALSE
This parameter controls 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.
operating system-dependent
Range of values:
0 - 255
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 the Oracle7 Server Concepts and Oracle7 Server Distributed Systems, Volume I.
See also your operating system-specific Oracle documentation for the default value.
release dependent
Range of values:
default release to current release
Multiple instances:
must have the same value
This parameter 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 Oracle7 Server must maintain compatibility. Some features of the current release may be restricted. For example, if you run release 7.2.2.0 with compatibility set to 7.1.0.0 in order to guarantee compatibility, you will not be able to use 7.2 features.
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 Oracle7 Server Migration.
See also your operating system specific Oracle documentation for the default value.
release dependent
Range of values:
default version to current version
Multiple instances:
must have the same value
This parameter 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 specifiable with the COMPATIBLE parameter.
For more information, see the Oracle7 Server Migration manual.
See also your operating system-specific Oracle documentation for the default value.
operating system-dependent
Range of values:
1 - 8 filenames
One or more names of control files, separated by commas. Oracle Corporation recommends using multiple files on different devices.
For more information, see the Oracle7 Server Administrator's Guide.
automatically set by Oracle
Range of values:
0 - unlimited
OK to change:
No
This parameter lists 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 Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for information about this parameter.
FALSE
Range of values:
TRUE/FALSE
Setting this parameter to TRUE causes the database to use 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 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 the Oracle7 Server Concepts manual.
32 buffers
Range of values:
4 - O/S specific
The number of database blocks cached in memory in the SGA (one block equals one buffer). This parameter is the most significant determinant of the SGA size and database performance. The advantage of a higher value is that when a user needs a database block, that block is more likely to be in memory, thus reducing I/O. The disadvantage of high values is that more memory is consumed. The size of each buffer is equal to the size of the parameter DB_BLOCK_SIZE.
For more information, see Oracle7 Server Concepts.
See also your operating system-specific Oracle documentation for the default value.
8
Range of values:
0 - derived
The maximum number of blocks that the database writer process will write in one batch that are devoted to checkpoints. 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 checkpoint log switch takes place. If a checkpoint 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. A value larger than the maximum can be specified, but its effect is the same as specifying the maximum.
For more information, see Oracle7 Server Concepts.
FALSE
Range of values:
TRUE/FALSE
If this parameter 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. This happens for temporary data blocks that need to be written.
Warning: Setting DB_BLOCK_CHECKSUM to TRUE can cause a 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 Oracle7 Server Administrator's Guide.
0
Range of values:
0 - dependent on system memory capacity
Disables or enables compilation of statistics in the X$KCBRBH table, 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 for the X$KCBRBH table, 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.)
For more information, see the Oracle7 Server Administrator's Guide.
CPU_COUNT/2
Range of values:
1 - the number of CPUs
Set this parameter to a value equal to the desired number of LRU latch sets. The value of this parameter represents the upper bound of the 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.
FALSE
Range of values:
TRUE/FALSE
This parameter 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.
For more information, see Oracle7 Server Administrator's Guide and Oracle7 Server Tuning.
operating system-dependent
Range of values:
operating system-dependent (1024 - 8192)
Multiple instances:
must have the same value
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.
For more information block size, see Oracle7 Server Concepts.
See also your operating system-specific Oracle documentation for the default value.
WORLD
Range of values:
any legal string of name components, separated by periods and up to 128 characters long, including periods (see valid characters below) --this value cannot be null
Multiple instances:
must have the same value
This parameter 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:
operating system-dependent
Range of values:
minimum: MAXDATAFILES for the database to be mounted maximum: operating system-dependent
Multiple instances:
must have the same value
The maximum number of database files that can be opened at runtime for this database. If you increase the value, you must shut down and restart all instances accessing the database before the new value can take effect.
Reduce the value only if you need SGA space and do not anticipate having more database files.
DB_FILES is similar to the MAXDATAFILES argument for the CREATE DATABASE statement, which sets the absolute maximum number of datafiles at database creation. An instance cannot mount a database unless DB_FILES is equal to or greater than MAXDATAFILES for that database. Greater values are only useful for instances that mount different databases at different times.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value.
operating system-dependent
Range of values:
operating system-dependent
Used for multi-block I/O, this is the maximum number of blocks read in one I/O operation during a sequential scan. The default is a function of DB_BLOCK_BUFFERS and PROCESSES. Values in the range of 4 to 16 or even 32 are reasonable.
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), and can never be larger than DB_BLOCK_BUFFERS/4.
For information on the optimizer, see Oracle7 Server Tuning.
See also your operating system-specific Oracle documentation for the default value.
4
Range of values:
1 - 24
The number of simultaneous writes ("batches") for each database file when written by DBWR.
If the operating system supports only one write per device and cannot combine writes to adjacent blocks, then the value should be 1. Though the value has no maximum because DBWR writes blocks in groups, it is not useful to use a value larger than 24.
For more information, see Oracle7 Server Concepts.
See also your operating system-specific Oracle documentation for the default value.
none
Range of values:
primary pattern, standby pattern
This parameter is used to convert the filename of a new data file on the primary database to a filename on the standby database. Adding a data 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 data file name on the primary database to the a data 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 data file names on the primary database; the second string is the pattern found in the data file names on the standby database.
For more information, see the Trusted Oracle7 Server Administrator's Guide.
NULL
Range of values:
any valid database name
Multiple instances:
must have the same value, or else the same value must be specified in STARTUP OPEN db_name or ALTER DATABASE db_name MOUNT
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.
The following are valid characters in a database name:
Lowercase characters are not treated with special significance. They are considered the same as their uppercase counterparts.
For more information, see the Oracle7 Server Administrator's Guide.
FALSE
Range of values:
TRUE/FALSE
Release:
7.1
Signifies whether attempts to connect to other Oracle7 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 reattempt the connection. If this parameter is FALSE, Oracle reattempts the connections using an unencrypted version of the password.
For more information, see the Oracle7 Server Administrator's Guide.
TRUE
Range of values:
TRUE/FALSE
OK to change?
Yes
Multiple instances:
Need not be identical
This parameter turns on or off the delayed block cleanout feature, which reduces pinging in an Oracle Parallel Server. Keeping this feature set to TRUE sets a fast path, not 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, upon demand--when the block is read or updated. This is called block cleanout.
When block cleanout is done during an update to a current block, the cleanout changes and the redo records are piggybacked 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.
As of release 7.3, 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 repin 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 much 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: As of Oracle Server release 7.3, performing a SELECT COUNT (*) no longer does a block cleanout.
During changes (INSERT, DELETE, UPDATE), the cleanout redo log records are generated and piggyback with the redo of the changes.
FALSE
Range of values:
TRUE/FALSE
Implements 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 Oracle7 Server Application Developer's Guide.
60 seconds
Range of values:
1 - unlimited
The amount of time in seconds for distributed transactions to wait for locked resources.
For more information on data concurrency, see Oracle7 Server Concepts and Oracle7 Server Distributed Systems, Volume I.
200 seconds
Range of values:
0 - 1800 seconds
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 Oracle7 Server Administrator's Guide and Oracle7 Server Distributed Systems, Volume I.
operating system-dependent
Range of values:
0 - TRANSACTIONS
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 DISTRIBUTED_TRANSACTIONS is set to 0, no distributed transactions are allowed for the database. The recoverer (RECO) process also does not start when the instance starts up.
For more information, see the Oracle7 Server Administrator's Guide and Oracle7 Server Distributed Systems, Volume I.
See also your operating system-specific Oracle documentation for the default value.
Range of values:
20 - unlimited, 0
Multiple instances:
must all have positive values or must all be 0
The maximum number of DML locks--one for each table modified in a transaction. Value should equal the grand total of locks on tables 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 Oracle7 Server Concepts and Oracle7 Server Distributed Systems, Volume I.
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.
Enqueues are platform-specific locking mechanisms. An enqueue allows the user to store a value in the lock, that is, the mode in which the lock is requested. The operating system lock manager keeps track of the resources locked. If a process cannot be granted the lock because it is incompatible with the mode requested and the lock is requested with wait, the operating system puts the requesting process on a wait queue which is serviced FIFO (first-in, first-out).
One difference between enqueues and latches is that in latches there is no ordered queue of waiters as there is in enqueues. Latch waiters may either use timers to wake up and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get the latch.
ENQUEUE_RESOURCES sets the number of resources that can be 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.
For more information on data concurrency, see Oracle7 Server Concepts and Oracle7 Server Distributed Systems, Volume I.
NULL
Modifies the scope of ALTER SESSION SET EVENTS commands so that they pertain to an entire instance, instead of to a single session.
EVENT is used to debug the system. This parameter should not usually be altered except at the direction of Oracle technical support personnel.
NULL
For more information on datatypes, see Oracle7 Server Concepts.
Default value:
0
Range of values:
1 - unlimited (depending on available memory and operating system)
OK to change?
yes
Multiple instances:
must have identical values
The total number of PCM locks covering data blocks cached in the multiple SGAs of a parallel server.
The value of GC_DB_LOCKS must be greater (by at least 1) than the sum of the locks specified with the GC_FILES_TO_LOCKS initialization parameter.
GC_DB_LOCKS is always rounded up to the next prime number to ensure that PCM locks are available for datafiles not specified in GC_FILES_TO_LOCKS. For example, if GC_DB_LOCKS has a value of 1000, then 1009 PCM locks are available. New datafiles added while the parallel server is running are covered by the extra nine PCM locks.
This parameter has no effect on an instance running in exclusive mode.
For more information, see Oracle7 Parallel Server Concepts & Administration.
0
Range of values:
any integer
This is a Parallel Server parameter. It specifies the translation to use for files that are not mentioned in GC_FILES_TO_LOCKS. This parameter is used if releasable locks are enabled with the GC_RELEASABLE_LOCKS parameter.
For more information, see Oracle7 Parallel Server Concepts & Administration.
Default value:
NULL
OK to change?
yes
Multiple instances:
must have identical values
This parameter controls the mapping of PCM locks to datafiles. To avoid performance problems, you should always change GC_FILES_TO_LOCKS when the size of datafiles change or new datafiles are added. This requires that you 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. A colon (:) separates each clause that assigns a number of PCM locks to file_list.
The optional parameter blocks, with the "!" separator, specifies the number of contiguous blocks covered by one lock, if it covers multiple blocks; the default value is 1. 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 specified for file_list is less than the actual number of data blocks in the datafiles, then some PCM locks will cover more datablocks than specified by lock_count!blocksEACH.
The value of the GC_DB_LOCKS parameter must be greater (by at least 1) than the sum of lock_count for all datafiles specified. The excess PCM locks are assigned to any datafiles not specified in GC_FILES_TO_LOCKS.
To find the correspondence between filenames and file numbers, query the data dictionary view DBA_DATA_FILES.
This parameter has no effect on an instance running in exclusive mode.
For more information, see Oracle7 Parallel Server Concepts & Administration.
Default value:
1 (ignored when the database is mounted in exclusive mode)
Range of values:
1 - 10, or 0 for a single instance running in exclusive mode
OK to change?
yes (1 is usually sufficient)
Multiple instances:
must have identical values
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 processes. (Lock requests are asynchronous, but a request is blocked until it knows if the lock can be granted.)
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.
This parameter has no effect on an instance running in exclusive mode.
For more information, see Oracle7 Parallel Server Concepts & Administration.
value of DB_BLOCK_BUFFERS
Range of values:
any integer
This is a Parallel Server parameter. It sets the default mode for locking to fine grained and sets the maximum number of locks allocated for fine grained locking.
For more information, see Oracle7 Parallel Server Concepts & Administration.
Default value:
OK to change?
yes
Multiple instances:
must have identical values
For each rollback segment, the number of distributed locks available for simultaneously modified rollback segment blocks. The default is adequate for most applications.
These distributed 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.
This parameter has no effect on an instance running in exclusive mode.
For more information, see Oracle7 Parallel Server Concepts & Administration.
Default value:
20
OK to change?
yes
Multiple instances:
must have identical values
The maximum number of rollback segments system wide. Set this parameter to the total number of rollback segments acquired by all instances in a parallel server, including the SYSTEM rollback segment. To allow for additional instances in the future, or additional rollback segments for the current instances, you can set it to a higher value.
Each rollback segment requires one distributed lock, specified by this parameter, in addition to the number specified by the GC_ROLLBACK_LOCKS parameter. The total number of distributed locks for rollback segments is:
(GC_ROLLBACK_SEGMENTS * (GC_ROLLBACK_LOCKS + 1 ))
This parameter has no effect on an instance running in exclusive mode.
For more information, see Oracle7 Parallel Server Concepts & Administration.
Default value:
20
OK to change?
yes
Multiple instances:
must have identical values
This initialization parameter reserves distributed locks for deferred rollback segments, which contain rollback entries for transactions in tablespaces that were taken offline.
The default is adequate for one or two instances but should be increased to 10 per instance for more instances if you need to take tablespaces offline while Oracle is running in parallel mode.
This parameter has no effect on an instance running in exclusive mode.
For more information, see Oracle7 Parallel Server Concepts & Administration.
Default value:
10
OK to change?
yes
Multiple instances:
must have identical values
The maximum number of segments system wide that may have space management activities performed simultaneously by different instances. The default is adequate for most applications. If tables acquire new extents frequently, you can increase the value to two or three times the number of tables that different instances extend simultaneously.
Each segment that undergoes simultaneous space management in a parallel server requires approximately nine distributed locks dedicated to coordinating space management activities. The total number of distributed locks reserved by this parameter is therefore approximately (9 * GC_SEGMENTS).
This parameter has no effect on an instance running in exclusive mode.
For more information, see Oracle7 Parallel Server Concepts & Administration.
Default value:
5
OK to change?
yes
Multiple instances:
must have identical values
The maximum number of tablespaces in a parallel server that can be brought from offline to online (or vice versa) concurrently.
For more information, see Oracle7 Parallel Server Concepts & Administration.
FALSE
Range of values:
TRUE/FALSE
This parameter determines whether or not 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.
For more information, see the Oracle7 Server Administrator's Guide.
2 times the value of SORT_AREA_SIZE
Range of values:
any integer
This parameter specifies the maximum amount of memory, in bytes, to be used for the hash join. If this parameter is not set, its value defaults to twice the value of the SORT_AREA_SIZE parameter.
You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
TRUE
Range of values:
TRUE/FALSE
This parameter enables or disables the hash join feature. Set this parameter to TRUE to use hash joins. Set this parameter to FALSE to disable hash joins.
You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
1
Range of values:
1 - (65,536/DB_BLOCK_SIZE)
This parameter specifies how many blocks a hash join reads and writes at once. When operating in multi-threaded server mode, however, this parameter is ignored (that is, the default value of 1 is used even if you set the parameter to another value).
The value of DB_BLOCK_SIZE multiplied by the value of HASH_MULTIBLOCK_IO_COUNT should be less than 64 KB.
This parameter strongly affects performance because it controls the number of partitions into which the input is divided. If you change the parameter value, make sure that the following formula remains true:
NULL
Range of values:
valid parameter filenames
Multiple instances:
can have different values
Embeds 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 Oracle7 Server Administrator's Guide.
Default value:
lowest available number (depends on instance startup order and on the INSTANCE_NUMBER values assigned to other instances)
Range of values:
1 - O/S dependent
OK to change?
yes (can be specified in both parallel and exclusive modes)
Multiple instances:
if specified, instances must have different values
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.
For more information, see Oracle7 Parallel Server Concepts & Administration.
60 (seconds)
Range of values:
1 - 3600 (seconds)
Multiple instances:
can have different values
Sets the interval between wake-ups for the SNPn background processes of the instance.
This parameter replaces the SNAPSHOT_REFRESH_INTERVAL parameter.
0
Range of values:
0 .. 36
Multiple instances:
can have different values
Sets the number of SNPn background processes per instance, where n is 0 to 9 followed by A to Z.
This parameter replaces the SNAPSHOT_REFRESH_PROCESS parameter.
For more information, see the Trusted Oracle7 Server Administrator's Guide.
0
Range of values:
0 - number of session licenses
Multiple instances:
can have different values
Sets 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 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 may 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 Oracle7 Server Administrator's Guide.
0
Range of values:
0 - number of user licenses
Multiple instances:
should have the same values
Sets 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 Oracle7 Server Administrator's Guide.
0
Range of values:
0 - LICENSE_MAX_SESSIONS
Multiple instances:
can have different values
Sets 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 Oracle7 Server Administrator's Guide.
operating system-dependent
Range of values:
1 - operating system-dependent (in O/S blocks)
Multiple instances:
can have different values
The size of each archival buffer, in redo log blocks (operating system blocks). The default should be adequate for most applications.
This parameter, with LOG_ARCHIVE_BUFFERS, can tune archiving so that it runs as fast as necessary, but not so fast that it reduces system performance.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system specific Oracle documentation for the default value.
operating system-dependent
Range of values:
operating system-dependent
Multiple instances:
can have different values
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 Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value.
operating system-dependent
Range of values:
any valid path or device name, except raw partitions
Multiple instances:
can have different values
Applicable only if using the redo log in ARCHIVELOG mode. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported on all operating systems.) The value cannot be a raw partition.
To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the Server Manager command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination.
For more information, see the Oracle7 Server Administrator's Guide.
See your Oracler 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 and LOG_ARCHIVE_FORMAT.
operating system-dependent (length for uppercase variables is also operating system-dependent)
Range of values:
any valid filename
Multiple instances:
can have different values, but identical values are recommended
Applicable only if 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 the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value and range of values.
FALSE
Range of values:
TRUE/FALSE
Multiple instances:
can have different values
Applicable only when you use the redo log in ARCHIVELOG mode, LOG_ARCHIVE_START indicates whether archiving should be automatic or manual when the instance starts up. TRUE indicates that archiving is automatic. FALSE indicates that the DBA will archive filled redo log files manually. (The Server Manager command ARCHIVE LOG START or STOP overrides this parameter.)
In ARCHIVELOG mode, if all online redo log files fill without being archived, an error message is issued, and instance operations are suspended until the necessary archiving is performed. This delay is more likely if you use manual archiving. You can reduce its likelihood by increasing the number of online redo log files.
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 Oracle7 Server Administrator's Guide.
FALSE
Range of values:
TRUE/FALSE
If this parameter is TRUE, then every log block will be given a checksum before it is written to the current log. The COMPATIBILITY parameter must be set to 7.2.0 or higher if the parameter is TRUE; otherwise, the instance will not start.
Any logs written with the COMPATIBILITY parameter set to 7.2.0 or higher will not be readable by earlier releases. This compatibility restriction exists even if checksumming is not enabled.
Warning: Setting LOG_BLOCK_CHECKSUM to TRUE can cause a performance overhead. Set this parameter to TRUE only under the advice of Oracle Support personnel to diagnose data corruption problems.
operating system-dependent
Range of values:
operating system-dependent
The number of bytes allocated to the redo log buffer in the SGA. In general, larger values 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. The default is set to 4 times the maximum database block size for the host operating system.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value and range of values.
operating system-dependent
Range of values:
unlimited (operating-system blocks, not database blocks)
Multiple instances:
can have different values
The number of newly filled redo log file blocks needed to trigger a checkpoint. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. If the value exceeds the actual redo log file size, checkpoints occur only when switching logs.
The number of times DBWR has been notified to do a checkpoint for a given instance is shown in the cache statistic dbwr checkpoints, which is displayed in the System Statistics Monitor of the Server Manager. Additional cache statistics include background checkpoints started and background checkpoints completed.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value.
0 seconds
Range of values:
0 - unlimited
Multiple instances:
can have different values
The amount of time to pass before another checkpoint occurs. The value is specified in seconds. A value of zero disables time-based checkpoints. The time begins at the start of the previous checkpoint, then a checkpoint occurs after the amount of time specified by this parameter.
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 Oracle7 Server Administrator's Guide.
NO
Range of values:
YES/NO
This parameter 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 the Oracle7 Server Concepts manual.
0 bytes
Range of values:
0 - unlimited
Multiple instances:
can have different values
Ok to change?
yes
The maximum number of bytes of redo data to gather together before copying to the log buffer. A non-zero value forces user processes to prebuild redo information before requesting the redo copy latch. If the value for LOG_SIMULTANEOUS_COPIES is 0, this parameter is ignored.
For multiple-processor systems, it is sometimes beneficial to increase this parameter. Single-processor systems should keep the value at 0.
For systems experiencing latch contention that have fast processors and efficient memory-to-memory copy algorithms, increasing this value will prebuild log entries and reduce the time that the copy latch is held.
Do not increase this value for systems experiencing memory contention.
255
Range of values:
2 - 255 (must be a minimum of MAXLOGFILES*MAXLOGMEMBERS)
Multiple instances:
must have the same value
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 Oracle7 Server Administrator's Guide.
none
Range of values:
primary pattern, standby pattern
This parameter 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.
CPU_COUNT
Range of values:
0 - unlimited
The maximum number of redo buffer copy latches available to write log entries simultaneously. For good performance, you can have up to twice as many redo copy latches as CPUs. For a single-processor system, set to zero so that all log entries are copied on the redo allocation latch.
If this parameter is set to 0, redo copy latches are turned off, and the parameters LOG_ENTRY_PREBUILD_THRESHOLD and LOG_SMALL_ENTRY_MAX_SIZE are ignored.
You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
For more information, see the Oracle7 Server Administrator's Guide.
operating system-dependent
Range of values:
operating system-dependent
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 Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value and range of values.
Default:
90000
Range of values:
0 - 90000
OK to change?
no
Multiple instances:
must have identical values
This initialization parameter should not be changed except under a limited set of circumstances specific to the Parallel Server. This parameter indicates 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 may not be refreshed in a timely manner. 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 Oracle7 Parallel Server Concepts & Administration.
500 blocks
Range of values:
0 - 4,000,000
Maximum size in operating system blocks of trace files to be written. Change this limit if you are concerned that trace files may take up too much space.
Oracle silently restricts the maximum value for this parameter to approximately 4,000,000 physical blocks. If the user exceeds this value then MAX_DUMP_FILE_SIZE is set to 4,000,000 physical blocks, which is the maximum Oracle can provide.
For example, if your logical file system block size is 512 bytes and you do not want to exceed 5 MB for the trace file size, you would set the MAX_DUMP_FILE_SIZE to 10,000.
For more information, see the Oracle7 Server Administrator's Guide.
20
Range of values:
0 - 148
Sets 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 + 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 Oracle7 Server Administrator's Guide.
30
Range of values:
1 - 65536
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 Oracle7 Server Administrator's Guide.
8
Range of values:
1 - 32
This parameter 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
For more information, see the Oracle7 Server Administrator's Guide.
NULL
The configuration of the dispatcher processeses created when the instance starts up. The value of this parameter is specified as one or more configuration strings. Each configuration string is a quoted string of two values separated by a comma that specifies the configuration of a group of one or more dispatchers.
The configuration string for each group of dispatcher processes includes the network protocol for that group and the number of dispatcher processes in the group (one or more). Each network protocol that you use on your system requires a separate specification.
You can specify multiple network protocols in a single parameter or in multiple parameters. For example, if you are using TCP/IP and DECNet to connect to the server, you could either specify both in one parameter, as follows:
MTS_DISPATCHERS = ("tcp, 1", "decnet, 4")or specify two parameters, as follows:
MTS_DISPATCHERS = "tcp, 1"
MTS_DISPATCHERS = "decnet, 4"
In these examples the first configuration string specifies one dispatcher process for the TCP/IP protocol and the second configuration string specifies four dispatcher processes for the DECNet protocol.
Note: If you have multiple MTS_DISPATCHERS parameters, they must be adjacent to each other in your initialization file.
For more information, see the Oracle7 Server Administrator's Guide. See also the Oracle Network Manager Administrator's Guide.
NULL
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 SQL*Net description of the connection address.
Warning: Each address must be specified with its own parameter. (This differs from the SQL*Net 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.
For more information, see the Oracle7 Server Administrator's Guide.
See your operating system-specific Oracle documentation and SQL*Net documentation for a description of how to specify addresses for the protocols on your system.
5
Range of values:
operating system-dependent
The maximum number of dispatcher processes allowed to be running simultaneously.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value and range of values.
20
Range of values:
operating system-dependent
The maximum number of shared server processes allowed to be running simultaneously.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value and range of values.
FALSE
Range of values:
TRUE/FALSE
When this parameter 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))
0
Range of values:
operating system-dependent
The number of server processes that you wish to create when an instance is started up.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the default value and range of values.
NULL
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 Oracle7 Server returns an error at startup indicating that the value for this parameter is missing.
For more information, see the Oracle7 Server Administrator's Guide. See also the Oracle Network Manager Administrator's Guide.
Gregorian
Range of values:
any valid calendar format name
Many calendar systems are in use throughout the world. NLS_CALENDAR specifies which calendar system Oracle uses.
NLS_CALENDAR can have one of the following values:
SELECT SYSDATE FROM DUAL;
SYSDATE
--------
07-02-17
derived
Range of values:
any valid character string, with a maximum of 10 bytes (not including null)
Defines 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 example, the following query uses the L format element to return the default local currency symbol for the territory FRANCE:
SELECT TO_CHAR(TOTAL, 'L099') "TOTAL"
FROM ORDERS WHERE CUSTNO = 586;
TOTAL
-------
F635
For more information, see the Oracle7 Server Administrator's Guide.
derived
Range of values:
any valid date format mask but not exceeding a fixed length
Defines 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"
The value of this parameter is stored in the tokenized internal date format. Each format element occupies two bytes, and each string occupies the number of bytes in the string plus a terminator byte. Also, the entire format mask has a two-byte terminator. For example, "MM/DD/YY" occupies 12 bytes internally because there are three format elements, two one-byte strings (the two slashes), and the two-byte terminator for the format mask. The tokenized format for the value of this parameter cannot exceed 24 bytes.
For more information, see the Oracle7 Server Administrator's Guide.
value for NLS_LANGUAGE
Range of values:
any valid NLS_LANGUAGE value
Determines the language to use for 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 the Oracle7 Server Administrator's Guide.
derived
Range of values:
any valid NLS_TERRITORY value
Defines 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 example, the following query uses the C format element to return the default international currency symbol for the territory FRANCE:
SELECT TO_CHAR(TOTAL, 'C099') "TOTAL"
FROM ORDERS WHERE CUSTNO = 586;
TOTAL
-------
FRF635
The value of this parameter can be any valid territory specified in NLS_TERRITORY.
For more information, see the Oracle7 Server Administrator's Guide.
operating system-dependent
Range of values:
any valid language name
Defines the default language of the database. Specifies the language to use for messages, the language of day and month names, symbols to use 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" .
For more information, see the Oracle7 Server Administrator's Guide.
See also your country release notes and operating system-specific Oracle documentation.
derived
Defines the characters to use as the group separator and decimal. 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.
Note: When the decimal character is not a period ( . ) or when the group separator is used, numeric literals must appear in quotation marks. For example, with the value of NLS_NUMERIC_CHARACTERS above, the following SQL statement requires quotation marks around the numeric literals:
INSERT INTO SIZES ( ITEMID, PRICE, WIDTH )
VALUES ( 618, '45,50', TO_NUMBER('1 234,11', '9G999D99'));For more information, see the Oracle7 Server Administrator's Guide.
derived
Range of values:
BINARY or valid linguistic definition name
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 optimzer.
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 a list of supported linguistic definitions and extended definitions, see page 4 - 38.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the sorting rules used by the linguistic sorting mechanisms.
operating system-dependent
Range of values:
any valid territory name
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" .
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 the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the territory-dependent default values for these parameters.
50
Range of values:
1 - operating system limit
The maximum number of open cursors (context areas) a session can have at once. This parameter does not control a system-wide feature, but rather, the maximum address/memory space used. The control of context areas is specific to the application.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the range of values.
4
Range of values:
0 - 255
The maximum number of concurrent open connections to remote databases per user process in one session. 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.
If set to 0, then no distributed transactions are allowed.
For more information, see the Oracle7 Server Administrator's Guide and Oracle7 Server Distributed Systems, Volume I.
For more information, see the Trusted Oracle7 Server Administrator's Guide.
CHOOSE
Range of values:
RULE/CHOOSE/FIRST_ROWS/ALL_ROWS
When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.)
You can set the goal for cost-based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans that minimize response time. ALL_ROWS causes the optimizer to choose execution plans that minimize total execution time. The goal of cost-based optimization can also be set within a session by using ALTER SESSION SET OPTIMIZER_MODE. See Oracle7 Server SQL Reference for more information about the ALTER SESSION command.
For more information about tuning SQL statements, see Oracle7 Server Tuning.
For more information about the optimizer, see Oracle7 Server Concepts and Oracle7 Server Tuning.
0
Range of values:
0 - 100
This parameter defines 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.
You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command. 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.
operating system specific
Range of values:
valid collection name up to 16 characters long
This parameter specifies the Oracle TRACE collection name.
operating system specific
Range of values:
full directory pathname
This parameter specifies the directory pathname where Oracle TRACE collection definition and data log files are located.
5242880
Range of values:
0 - 4294967295
This parameter specifies the maximum size, in bytes, of the Oracle TRACE collection data file. Once the collection data file reaches this maximum, the collection is disabled.
FALSE
Range of values:
TRUE/FALSE
This parameter disables or enables the Oracle TRACE facility.
operating system specific
Range of values:
valid facility name up to 16 characters long
This parameter specifies the Oracle TRACE facility name.
operating system specific
Range of values:
full directory pathname
This parameter specifies the directory pathname where Oracle TRACE facility definition files are located.
operating system-dependent (typically "OPS$")
This parameter is 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 Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation.
FALSE
Range of values:
TRUE/FALSE
Setting this parameter to TRUE causes the database to allow 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 equal 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 Oracle7 Server Administrator's Guide.
operating system-dependent
Range of values:
0 - number of instances
Multiple instances:
should have the same value
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.
For more information, see Oracle7 Parallel Server Concepts & Administration.
operating system-dependent
0 - 256
Multiple instances:
each instance must either have a value of zero or the same value as the other instances
Maximum number of query servers or parallel recovery processes for an instance.
For more information, see Oracle7 Parallel Server Concepts & Administration.
0
Range of values:
0 - 100
This parameter sets the minimum percent of query slaves required for parallel queries. If the number of query slaves specified by the value of PARALLEL_MIN_PERCENT is not available, the query will terminate with an error. If the number of slaves aquired is less than the number of slaves requested times the value of PARALLEL_MIN_PERCENT divided by 100, then the query will terminate with an error. If this parameter is not set, the query executes with as many slaves as are available.
0
Multiple instances:
0 - PARALLEL_MAX_SERVERS
can have different values
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 Oracle7 Parallel Server Concepts & Administration.
operating system-dependent
Range of values:
0 - unlimited
Multiple instances:
can have different values
The amount of idle time after which Oracle terminates a query server process. This value is expressed in minutes.
FALSE
Range of values:
TRUE/FALSE
OK to change?
Yes
When 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.
You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
NO
Range of values:
NO/YES
OK to change?
No
When set to YES, this parameter touches all the SGA pages, causing them to be brought into memory. As a result, it increases instance startup 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.
25
Range of values:
6 to operating system-dependent
Multiple instances:
can have different values
For a multiple-process operation, this parameter specifies the maximum number of operating system user processes that can simultaneously connect to an Oracle7 Server. Should include up to 6 for the background processes (or more if GC_LCK_PROCS is non-zero or if you use the dispatcher configuration) plus one for login; so a value of 20 would permit 13 or 14 concurrent users.
The default values of DB_FILE_MULTIBLOCK_READ_COUNT and SESSIONS are derived from PROCESSES. If you alter the value of PROCESSES, you may want to adjust the values of these derived parameters.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the range of values.
operating system-dependent
Range of values:
operating system-dependent, but cannot exceed PARALLEL_MAX_SERVERS
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 Oracle7 Parallel Server Concepts & Administration.
TIMESTAMP
Range of values:
TIMESTAMP/SIGNATURE
This parameter 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.
NONE
Range of values:
NONE/SHARED/EXCLUSIVE
Multiple instances:
should have the same value
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 Oracle7 Server Administrator's Guide.
FALSE
Range of values:
TRUE/FALSE
Setting this parameter to TRUE allows authentication of remote clients with the value of OS_AUTHENT_PREFIX.
For more information, see the Oracle7 Server Administrator's Guide.
FALSE
Range of values:
TRUE/FALSE
Setting this parameter 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 Oracle7 Server Administrator's Guide.
FALSE
Range of values:
TRUE/FALSE
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 Oracle7 Server Administrator's Guide.
NULL (the instance uses public rollback segments by default if you do not specify this parameter
Range of Values
any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM
Multiple instances:
must have different values (different instances cannot specify the same rollback segment)
One or more rollback segments to allocate by name to this instance. If ROLLBACK_SEGMENTS is not overridden, an instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated from the ratio TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT).
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 Oracle7 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 Oracle7 Server Administrator's Guide.
10
Range of values:
10 - 3300
The number of cached recursive cursors used by the row cache manager for selecting rows from the data dictionary. The default value is sufficient for most systems.
For more information about memory structure and processes, see Oracle7 Server Concepts.
ALWAYS
Range of values:
ALWAYS/DEFAULT/INTENT
Multiple instances:
must have the same value
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 the Oracle7 Server Tuning manual.
10
Range of values:
10 - 32000
Multiple instances:
can have different values
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 Oracle7 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 Oracle7 Server Administrator's Guide and Oracle7 Server Application Developer's Guide.
FALSE
Range of values:
TRUE/FALSE
Multiple instances:
must have the same value
If TRUE, then queries acquire table-level read locks, preventing any update of objects read until the transaction containing the query is committed. This mode of operation provides repeatable reads and ensures that two queries for the same data within the same transaction see the same values.
Setting SERIALIZABLE to TRUE provides ANSI degree three consistency at a considerable cost in concurrency.
For more information about data concurrency, see the Oracle7 Server Tuning manual.
derived (1.1 * PROCESSES + 5)
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 may 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 memory structures and processes, see the Oracle7 Server Concepts manual.
3,500,000 bytes
Range of values:
300 Kbytes - operating system-dependent
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.
For more information, see the Oracle7 Server Administrator's Guide.
5000
Range of values:
5000 - SHARED_POOL_RESERVED_SIZE (in bytes)
This parameter controls allocation of reserved memory. Memory allocations larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists.
The default value is adequate for most systems. If you increase the value, then the Oracle Server will allow fewer allocations from the reserved list and will request more memory from the shared pool list.
0
Range of values:
from SHARED_POOL_RESERVED_MIN_ALLOC to one half of SHARED_POOL_SIZE (in bytes)
This parameter controls the amount of SHARED_POOL_SIZE reserved for large allocations. SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC to create a reserved list.
The default value of 0 represents no reserved shared pool area.
Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. The amount of operating system memory, however, may constrain the size of the shared pool. In general, you should set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. For most systems, this value will be sufficient if you have already tuned the shared pool.
0
Range of values:
0 - 10
Multiple instances:
can have different values
This parameter sets the number of snapshot refresh processes per instance. 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.
For more information on managing table snapshots, see Oracle7 Server Distributed Systems, Volume II.
60 (one minute)
Range of values:
1 - 3600 seconds (one second to 60 minutes)
Multiple instances:
can have different values
This parameter sets the interval between wake-ups for the snapshot refresh process(es) on the instance.
For more information on managing table snapshots, see Oracle7 Server Distributed Systems, Volume II.
the value of SORT_AREA_SIZE
Range of values:
from the value equivalent to one database block to the value of SORT_AREA_SIZE
This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory retained after a sort. This memory is released back to the PGA, not to the operating system, after the last row is fetched from the sort space.
If a sort requires more memory, a temporary segment is allocated and the sort becomes an external (disk) sort. The maximum amount of memory to use for the sort is then specified by SORT_AREA_SIZE instead of by this parameter.
Larger values permit more sorts to be performed in memory. However, multiple sort spaces of this size may be allocated. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, though, additional concurrent sorts are required. Each sort occurs in its own memory area, as specified by SORT_AREA_RETAINED_SIZE.
For more information, see Oracle7 Server Concepts.
operating system-dependent
Minimum value:
the value equivalent to two database blocks
This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. After the sort is complete and all that remains to do is to fetch the rows out, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched out, all memory is freed. The memory is released back to the PGA, not to the operating system.
Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time.
The default is usually adequate for most database operations. Only if very large indexes are created might you want to adjust this parameter. For example, if one process is doing all database access, as in a full database import, then an increased value for this parameter may speed the import, particularly the CREATE INDEX statements.
For more information, see Oracle7 Server Concepts.
See also your operating system specific Oracle documentation for the default value on your system.
AUTO
Range of values:
AUTO/TRUE/FALSE
SORT_DIRECT_WRITES can improve sort performance if memory and temporary space are abundant on your system.
When set to the default value of AUTO, and if the value of SORT_AREA_SIZE is greater than ten times the buffer size, SORT_DIRECT_WRITES automatically configures the SORT_WRITE_BUFFER_SIZE and SORT_WRITE_BUFFERS parameters. When SORT_DIRECT_WRITES is in AUTO mode, SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE have no effect.
When SORT_DIRECT_WRITES is set to TRUE, each sort allocates additional buffers in memory to write directly to disk.
When SORT_DIRECT_WRITES is set to FALSE, the sorts that write to disk write through the buffer cache.
For more information, see Oracle7 Server Tuning.
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:
(avg_seek_time + avg_latency + blk_transfer_time)
sort_read_fac = --------------------------------------------
blk_transfer_timeSee also your operating system-specific Oracle documentation for the default value.
operating system-dependent
The size in bytes of the sort space map in the context area. 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. Also include 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 the Oracle7 Server Concepts.
See also your operating system-specific Oracle documentation for the default value.
32768
Range of values:
any integer
This parameter sets the size of the sort buffer when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
2
Range of values:
any integer
This parameter sets the number of sort buffers when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
FALSE
Range of values:
TRUE/FALSE
Disables or enables the SQL trace facility. Setting this parameter to TRUE provides information on tuning that you can use to improve performance. Because the SQL trace facility causes system overhead, you should run the database with the value TRUE only for the purpose of collecting statistics.
You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
For more information about performance diagnostic tools, see Oracle7 Server Tuning.
See also the Oracle7 Server SQL Reference manual.
FALSE
Range of values:
TRUE/FALSE
Release:
7.1
Specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values.
derived (= SESSIONS)
Range of values:
0 - operating system-dependent
Determines 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 may need to increase this number. Most installations should do well with the default.
For more information, see the Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the range of values.
0
Range of values:
0 - maximum number of declared threads
Multiple instances:
if specified, must have different values
This parameter is applicable only to instances that intend to run in parallel (shared) mode.
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.
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 Oracle7 Parallel Server Concepts & Administrationand Oracle7 Server SQL Reference.
FALSE
Range of values:
TRUE/FALSE
By default (when set to FALSE), the Server Manager statistics related to time (from the buffer manager) always are zero and the Server can avoid the overhead of requesting the time from the operating system. To turn on statistics, set the value to TRUE. Should normally be set to FALSE.
For more information about performance diagnostic tools, see Oracle7 Server Tuning.
derived (1.1 * SESSIONS)
Multiple instances:
can have different values
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 PROCESSES to allow for recursive transactions.
For more information about memory structures and processes, see Oracle7 Server Concepts and the Oracle7 Server Administrator's Guide.
30
Range of values:
1 - operating system-dependent
Multiple instances:
can have different values
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 Oracle7 Server Administrator's Guide.
See also your operating system-specific Oracle documentation for the range of values.
operating system-dependent
Range of values:
valid local pathname, directory, or disk
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 Oracle7 Server Tuning.
See also your operating system-specific Oracle documentation for the range of values.
none
Range of values:
any valid directory path
This parameter allows DBAs 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 may 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 in the UTL_FILE_DIR parameters.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |