§ Parallel Query Option
§ Analysis Tools
§ General
§ Recovery Manager
§ Multithreaded Server
§ Distributed Option
§ Parallel Server Parameters
§ Security
§ Trusted Oracle7
§ National Language Support
This lists the Oracle tuning parameters, grouping them into general areas of use and then sorting them alphabetically within the group. The section headings are the syntax for the parameters. The syntax contains information in the following format:
PARAMETER [option1, option2, option3, etc..]
A value that is italicized indicates that the value should be replaced with one of your own. A value that is italicized and in CAPS indicates a choice of this keyword. The / character indicates an OR condition. A value enclosed in brackets (<>) indicates the default value for that parameter.
These parameters are divided into sections based on whether the parameter affects performance, enables system analysis, is a general parameter, and so on. There might be some overlap, so if a parameter is not in the section you expected, keep looking.
Performance
These parameters change the performance characteristics of the system.
ALWAYS_ANTI_JOIN [NESTED_LOOPS/MERGE/ HASH]
B_TREE_BITMAP_PLANS [TRUE/FALSE]
When set to TRUE, the optimizer considers a bitmap access path even though a table might have only a regular B*-tree index.
BITMAP_MERGE_AREA_SIZE [System Dependent] <1MB>
This parameter specifies the amount of memory used to merge bitmaps retrieved from a range scan of the index. Larger values typically improve performance.
CLOSE_CACHED_OPEN_CURSORS [TRUE/FALSE]
This parameter specifies whether cursors opened and cached in memory are automatically closed at each commit. If you frequently use cursors, this should be set to FALSE.
CPU_COUNT [0-unlimited]
This parameter specifies the number of CPUs used by Oracle. This parameter is set automatically and should not be changed.
CREATE_BITMAP_AREA_SIZE [OS Dependent] <8MB>
This parameter specifies the amount of memory to be used for bitmap creation. A larger value might provide greater bitmap-creation performance. If the cardinality is small, this number can be small.
CURSOR_SPACE_FOR_TIME [TRUE/FALSE]
CURSOR_SPACE_FOR_TIME causes the system to use more space for cursors, thus increasing performance. This parameter affects both the shared SQL areas and the user's private SQL area. This parameter speeds performance but uses more memory.
If CURSOR_SPACE_FOR_TIME is TRUE, the shared SQL areas remain pinned in the shared pool as long as an open cursor references them. This parameter should be used only if you have a sufficiently large shared pool to simultaneously hold all the processes' cursors.
The user's private SQL area is also retained during cursor execution, thus saving time and I/Os at the expense of memory. DB_BLOCK_BUFFERS [4..65535] <32 buffers> This parameter controls the number of database block buffers in the SGA. DB_BLOCK_BUFFERS is probably the most significant instance tuning parameter because the majority of I/Os in the system are generated by database blocks. Increasing DB_BLOCK_BUFFERS increases performance at the expense of memory. You can calculate the amount of memory that will be consumed with the following formula:
Buffer size = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE
A larger number of database block buffers in the system creates a higher cache-hit rate, thus reducing the amount of utilized I/O and CPU and improving performance.
DB_BLOCK_CHECKPOINT_BATCH [0..derived] <8>
This parameter specifies the number of blocks that the DBWR writes in one batch when performing a checkpoint. Setting this value too high causes the system to flood the I/O devices during the checkpoint, severely degrades performance, and increases response times--maybe to unacceptable levels.
You should set DB_BLOCK_CHECKPOINT_BATCH to a level that allows a checkpoint to finish before the next checkpoint occurs. Setting DB_BLOCK_CHECKPOINT_BATCH to 0 causes the default value of 8 to be used.
DB_BLOCK_SIZE [1024..8192 (OS dependent)]
This parameter specifies in bytes the size of the Oracle database blocks. The typical values are 2048 and 4096. If you set the block size relative to the size of the rows in a database, you can reduce I/O. In some types of applications in which large amounts of sequential accesses are performed, a larger database block size can be beneficial. This value is useful only at database-creation time.
DB_FILE_MULTIBLOCK_READ_COUNT [number (OS dependent)]
DB_FILE_MULTIBLOCK_READ_COUNT specifies the maximum number of blocks read in one I/O during a sequential scan. The default is a function of DB_BLOCK_BUFFERS and PROCESSES. Reasonable values are 4, 16, or 32. The maximum allowed values are OS dependent.
This parameter can be especially useful if you perform a large number of table scans, such as in a DSS system.
DB_FILE_SIMULTANEOUS_WRITES [1..24] <4>
This parameter specifies the number of simultaneous writes for each database file when written by the DBWR. For disk arrays that handle large numbers of requests in the hardware simultaneously, it is advantageous to set DB_FILE_SIMULTANEOUS_WRITES to its maximum.
DISCRETE_TRANSACTIONS_ENABLED [TRUE/FALSE]
This parameter implements a simpler, faster rollback mechanism that, under certain conditions, can improve performance. You can obtain greater efficiency in this mode, but the qualification criteria for what kind of transactions can take advantage of discrete transactions are quite strict.
DISK_ASYNCH_IO [TRUE/FALSE]
This parameter specifies that I/O to datafiles, control files, and log files are asynchronous. This should be left enabled and not altered.
DML_LOCKS [20..unlimited,0] <4 * TRANSACTIONS>
This parameter specifies the maximum number of DML locks. A DML lock is used for each table-modification transaction. DML locks are used in the DROP TABLE, CREATE INDEX, and LOCK TABLE IN EXCLUSIVE MODE statements. If the value is set to 0, enqueues (Oracle locking mechanisms) are disabled, which improves performance slightly.
DBWR_IO_SLAVES [0..OS Dependent] <0>
This parameter specifies the number of I/O slaves used by the DBWR process.
HASH_AREA_SIZE [0..OS Dependent] <2*SORT_AREA_SIZE>
This parameter specifies the maximum amount of memory to be used for hash joins.
HASH_MULTIBLOCK_IO_COUNT [OS Dependent] <1>
This parameter specifies how many sequential blocks a hash join reads and writes in one I/O.
LARGE_POOL_MIN_ALLOC [16K-64KB] <16KB>
This parameter specifies the minimum allocation size from the large pool. LARGE_POOL_SIZE
[300K or LARGE_POOL_MIN_ALLOC, whichever is larger] <0>
This parameter specifies the size of the large pool allocation heap.
LGWR_IO_SLAVES [0..OS Dependent] <0>
This parameter specifies the number of I/O slaves used by the LGWR process.
LOG_ARCHIVE_BUFFER_SIZE [1..OS Dependent]
When running in ARCHIVELOG mode, this parameter specifies the size of each archival buffer in redo log blocks. This parameter can be used in conjunction with the LOG_ARCHIVE_BUFFERS parameter to make the archiving speed faster or slower to affect overall system performance.
LOG_ARCHIVE_BUFFERS [1..OS Dependent]
When running in ARCHIVELOG mode, this parameter specifies the number of buffers to allocate to archiving. This parameter is used with the LOG_ARCHIVE_BUFFER_SIZE parameter to control the speed of archiving.
LOG_BUFFER [OS Dependent]
LOG_BUFFER specifies the number of bytes allocated to the redo log buffer. Larger values reduce I/Os to the redo log by writing fewer blocks of a larger size. This might help performance, particularly in a heavily used system.
LOG_CHECKPOINT_INTERVAL [2..unlimited]
This parameter specifies the number of redo log file blocks to be filled to cause a checkpoint to occur. Remember that a checkpoint always happens when a log switch occurs. This parameter can be used to cause checkpoints to occur more frequently. Sometimes, frequent checkpoints have less effect on the system than one large checkpoint when the log switch occurs.
LOG_CHECKPOINT_TIMEOUT [0..unlimited]
This parameter specifies the maximum amount of time that can pass before another checkpoint must occur. This parameter can also be used to increase the frequency of the checkpoint process, thus changing the overall system effect.
LOG_SIMULTANEOUS_COPIES [0..unlimited]
LOG_SIMULTANEOUS_COPIES specifies the number of redo buffer copy latches simultaneously available to write log entries. You can have up to two redo copy latches per CPU. This helps the LGWR process keep up with the extra load generated by multiple CPUs.
If this parameter is 0, redo copy latches are turned off and all log entries are copied on the redo allocation latch.
LOG_SMALL_ENTRY_MAX_SIZE [number (OS dependent)]
This parameter specifies the size in bytes of the largest copy to the log buffers that can occur under the redo allocation latch without obtaining the redo buffer copy latch. If LOG_SIMULTANEOUS_COPIES is zero, this parameter is ignored.
OPTIMIZER_MODE [RULE/COST/FIRST_ROWS/ALL_ROWS] COST
When set to RULE, this parameter causes rule-based optimization to be used, unless hints are supplied in the query. When set to COST, this parameter causes a cost-based approach for the SQL statement, providing that there are any statistics in the data dictionary. When set to FIRST_ROWS, the optimizer chooses execution plans that minimize response time. When set to ALL_ROWS, the optimizer chooses execution plans that minimize total execution time.
OPTIMIZER_PERCENT_PARALLEL [0..100] <0>
This parameter specifies the amount of parallelism the optimizer uses in its cost functions.
OPTIMIZER_SEARCH_LIMIT <5>
This parameter specifies the search limit for the optimizer.
PRE_PAGE_SGA [TRUE/FALSE]
When set to TRUE, this parameter specifies that at instance startup all pages of the SGA are touched, causing them to be allocated in memory. This increases startup time but reduces page faults during runtime. This is useful if you have a large number of processes starting at once. This parameter can increase the system performance in that case by avoiding memory-allocation overhead.
ROLLBACK_SEGMENTS [Any rollback segment names]
ROLLBACK_SEGMENTS specifies one or more rollback-segment names to be allocated to this instance. If ROLLBACK_SEGMENTS is not specified, the public rollback segments are used. If you want to move your rollback segments to a different disk device, you must specify it here. The parameter is specified as follows:
ROLLBACK_SEGMENTS = (roll1, roll2, roll3)
If you use the Oracle Parallel Server option, you must name different rollback segments for each instance.
ROW_CACHE_CURSORS [10..3300] <10>
This parameter specifies the number of cached recursive cursors used by the row cache manager for selecting rows from the data dictionary. The default is usually sufficient unless you have particularly high access to the data dictionary.
ROW_LOCKING [ALWAYS/INTENT]
The value ALWAYS specifies that only row locks are acquired when a table is updated. If you set this value to INTENT, row locks are acquired on a SELECT FOR UPDATE, but when the update occurs, a table lock is acquired. SEQUENCE_CACHE_ENTRIES [10..32000] <10> This parameter specifies the number of sequences that can be cached in the SGA. By caching the sequences, an immediate response is achieved for sequences. Set a large value for SEQUENCE_CACHE_ENTRIES if you have a high concurrency of processes requesting sequences.
SEQUENCE_CACHE_HASH_BUCKETS [1..32000 (prime number)] <7>
This parameter specifies the number of buckets to speed up access to sequences in the cache. The cache is arranged as a hash table.
SERIAL_REUSE [DISABLE/SELECT/DML/PLSQL/ALL/NULL]
This parameter specifies which type of SQL cursors should make use of serial-reusable memory.
SERIALIZABLE [TRUE/FALSE]
If this value is set to TRUE, queries obtain table-level read locks, which prohibits other transactions from modifying that table until the transaction has committed or rolled back the transaction. This mode provides repeatable reads and ensures that within the transactions multiple queries to the same data achieve the same result.
With SERIALIZABLE set to TRUE, degree-three consistency is provided. You pay a performance penalty when you run in this mode. Running in this mode is usually not necessary.
SESSION_CACHED_CURSORS [0..OS dependent] <0>
This parameter specifies the number of session cursors to cache. If parse calls of the same SQL statement are repeated, this can cause the session cursor for that statement to be moved into the session cursor cache. Subsequent calls need not reopen the cursor.
SESSION_MAX_OPEN_FILES [1..MAX_OPEN_FILES] <10>
This parameter specifies the maximum number of BFILEs that can be opened by any given session. The BFILE stores unstructured binary data in OS files outside the database.
SHARED_POOL_RESERVED_MIN_ALLOC [5000..SHARED_POOL_RESERVE_SIZE] <5000>
Memory allocations larger than this value cannot allocate space from the reserved list.
SHARED_POOL_RESERVE_SIZE [SHARED_POOL_RESERVE_MIN_ALLOC.. (SHARED_POOL_SIZE/2)] <5% of SHARED_POOL_SIZE>
This parameter specifies the shared pool space that is reserved for large contiguous requests for shared-pool memory.
SHARED_POOL_SIZE [300KB..OS dependent] <3.5MB>
This parameter specifies the size of the shared pool in bytes. The shared pool contains the data dictionary cache (row cache) and the library cache as well as session information. Increasing the size of the shared pool should help performance, but at the cost of memory.
SMALL_TABLE_THRESHOLD [0..OS dependent] <4>
This parameter specifies the number of buffers available in the SGA for table scans. A small table might be read entirely into cache if it fits in SMALL_TABLE_THRESHOLD number of buffers. When scanning a table larger than this, these buffers are reused immediately. This provides a mechanism to prohibit a single-table scan from taking over the buffer cache.
SORT_AREA_RETAINED_SIZE [0..SORT_AREA_SIZE]
SORT_AREA_RETAINED_SIZE defines the maximum amount of session memory in bytes that can be used for an in-memory sort. The memory is released when the last row is fetched from the sort area.
If the sort does not fit in SORT_AREA_RETAINED_SIZE bytes, a temporary segment is allocated and the sort is performed in this temporary table. This is called an external (disk) sort. This value is important if sort performance is critical. SORT_AREA_SIZE [number of bytes]
Remember that each user process has its own PGA. You can calculate the potential memory usage if all the users are doing a large sort with the following formula:
Potential memory usage = SORT_AREA_SIZE * (number of users doing a large sort)
If very large indexes are being created, you might want to increase the value of this parameter. SORT_SPACEMAP_SIZE [bytes]
SORT_SPACEMAP_SIZE = (total-sort-bytes / sort-area-size) + 64
In this formula, total-sort-bytes has the following value:
total-sort-bytes = record-count * ( sum-of-average-column-sizes + ( 2 * number-of-columns ) )
number-of-columns includes the SELECT list for ORDER BY, GROUP BY, and the key list for the CREATE INDEX. You should also add 10 or 20 extra bytes for overhead.
SORT_WRITE_BUFFER_SIZE [32KB/64KB] <32768>
This parameter specifies the size of the sort I/O buffer when SORT_DIRECT_WRITES is set to TRUE.
SORT_WRITE_BUFFERS [2..8] <1>
This parameter specifies the number of sort buffers when SORT_DIRECT_WRITES is set to TRUE.
SPIN_COUNT [1..1,000,000] <1>
This parameter specifies the number of times to spin on a latch before sleeping.
STAR_TRANSFORMATION_ENABLED [TRUE/FALSE]
This parameter specifies whether a cost-based query transformation will be applied to star queries.
USE_ISM [TRUE/FALSE]
This parameter specifies that the shared page table is enabled.
Parallel Query Option
The following parameters affect the operation of the Parallel Query option, which has been available in Oracle since version 7.1. The Parallel Query option can dramatically affect the performance of certain operations.
PARALLEL_DEFAULT_MAX_SCANS [0..unlimited]
This value specifies the maximum number of query servers to be used by default for a query. This valued is used only if there are no values specified in a PARALLEL hint or in the PARALLEL definition clause. This limits the number of query servers used by default when the value of PARALLEL_DEFAULT_SCANSIZE is used by the query coordinator.
PARALLEL_DEFAULT_SCANSIZE [0..OS Dependent ]
This parameter is used to determine the number of query servers to be used for a particular table. The size of the table divided by PARALLEL_DEFAULT_SCANSIZE determines the number of query servers, up to PARALLEL_DEFAULT_MAX_SCANS.
PARALLEL_MAX_SERVERS [0..100]
This parameter specifies the maximum number of query servers or parallel recovery processes available for this instance.
PARALLEL_MIN_MESSAGE_POOL [0..(SHARED_POOLSIZE*.9)]
This parameter specifies the minimum permanent amount of memory that will be allocated from the shared pool for messages in parallel execution.
PARALLEL_MIN_PERCENT [0..100] <0>
This parameter specifies the minimum percent of threads required for parallel query.
PARALLEL_MIN_SERVERS [0..PARALLEL_MAX_SERVERS] <0>
This parameter determines the minimum number of query servers for an instance. It is also the number of query servers started at instance startup.
PARALLEL_SERVER_IDLE_TIME [0..unlimited]
This parameter specifies the number of minutes before Oracle terminates an idle query server process.
RECOVERY_PARALLELISM [0..PARALLEL_MAX_SERVERS]
This parameter specifies the number of processes to be used for instance or media recovery. A large value can greatly reduce instance recovery time. A value of 0 or 1 indicates that parallel recovery will not be performed and that recovery will be serial.
Analysis Tools
These parameters turn on special features in Oracle for detailed analysis and debugging.
DB_BLOCK_CHECKSUM [TRUE/FALSE]
Setting this parameter to TRUE causes the DBWR and direct loader to calculate a checksum for every block they write to disk. This checksum is written into the header of each block.
DB_LOG_CHECKSUM [TRUE/FALSE]
Setting this parameter to TRUE causes the LGWR to calculate a checksum for every block it writes to disk. The checksum is written into the header of the redo block.
DB_BLOCK_LRU_EXTENDED_STATISTICS [0..unlimited] <0>
This parameter enables statistics in the X$KCBRBH table to be gathered. These statistics estimate the increased number of database block buffer cache hits for each additional buffer. Any value over zero specifies the number of buffers to estimate the cache hits for. If you are interested in estimating the cache hits for an additional 100 buffers, set this parameter to 100.
This parameter affects performance and should be turned off during normal operation.
DB_BLOCK_LRU_LATCHES [1.. number of CPUs]
This parameter specifies the upper bound of the number of LRU latch sets. This is the number of LRU latch sets that you want. Oracle decides whether to use this number or a smaller one.
DB_BLOCK_LRU_STATISTICS [TRUE/FALSE]
This parameter specifies whether statistics are gathered for database block buffer cache hit estimates as specified in DB_BLOCK_LRU_EXTENDED_STATISTICS. Set this parameter to TRUE when you want to gather these statistics.
DB_BLOCK_MAX_DIRTY_TARGET [100..all buffers or 0]
This parameter specifies the number of buffers that can be dirty. If the number of dirty buffers exceeds this, the DBWR writes out buffers to reduce the number of dirty buffers.
EVENT
The EVENT parameter modifies the scope of ALTER SESSION SET EVENTS commands so that they pertain to the entire instance rather than just the session. This is an Oracle internal parameter and should be changed only at the direction of Oracle support.
FIXED_DATE [date string]
FIXED_DATE allows you to set as a constant the Oracle function SYSDATE in the format YYYY-MM-DD-HH24:MI:SS. Use this parameter for debug only. This parameter allows you to test your application's functionality with certain dates, such as the turn of the century.
ORACLE_TRACE_COLLECTION_NAME [valid name]
This parameter specifies the Oracle Trace collection name.
ORACLE_TRACE_COLLECTION_PATH [valid path]
This parameter specifies the directory where Oracle Trace collection definition and datafiles are located.
ORACLE_TRACE_COLLECTION_SIZE [0..4294967295] <5242880>
The maximum size in bytes of the Oracle Trace collection file.
ORACLE_TRACE_ENABLE [TRUE/FALSE]
Enables Oracle Trace collections for the server.
ORACLE_TRACE_FACILITY_NAME [valid name]
This parameter specifies the name of the Oracle Trace product definition file.
ORACLE_TRACE_FACILITY_PATH [valid directory name]
This parameter specifies the directory where the Oracle Trace facility definition files are located.
SQL_TRACE [TRUE/FALSE]
This parameter specifies whether the SQL*Trace facility is enabled. The SQL*Trace facility can provide valuable information but at the price of some overhead. Use SQL*Trace only when you are tracking down a specific problem.
SORT_READ_FAC [integer]
SORT_READ_FAC defines a unitless ratio that describes the amount of time to read a single database block divided by the block transfer rate.
TIMED_OS_STATISTICS [OFF/CALL/LOGOFF]
This parameter allows the system administrator to gather OS statistics when calls are pushed or popped or when a user logs off.
TIMED_STATISTICS [TRUE/FALSE]
When TIMED_STATISTICS is set to TRUE, the time-related statistics in the dynamic performance tables are enabled. This information can be quite useful, but there is considerable overhead involved. Only enable TIMED_STATISTICS when you are analyzing the system.
General
These parameters are of a general nature; they typically set limits and do not significantly affect performance--except that they might take up space in the SGA. AQ_TM_PROCESS [0/1] <0> This parameter specifies whether a time manager is created. If AQ_TM_PROCESS is set to 1, a time-manager process is created to monitor the messages.
ARCH_IO_SLAVES [0-15] <0>
The number of I/O slaves to be used by the ARCH process. This should be adjusted if archiving is running into an I/O bottleneck.
BACKGROUND_CORE_DUMP [FULL/PARTIAL]
This parameter specifies whether the SGA is dumped as part of the generated core file.
BACKGROUND_DUMP_DEST [pathname]
This parameter specifies the destination directory where the debugging trace files for the background processes are written. The background processes log all startup and shutdown messages and errors to these files, as well as any other error logs. A log of all CREATE, ALTER, or DROP statements is also stored here.
BLANK_TRIMMING [TRUE/FALSE]
If the value of BLANK_TRIMMING is TRUE, this allows a data assignment of a string variable to a column value that is smaller (assuming that the truncated characters are blank).
CHECKPOINT_PROCESS [TRUE/FALSE]
This parameter determines whether the CKPT background process is enabled. During a checkpoint, the headers of all the datafiles must be updated. This task is usually performed by the LGWR process. Writing the blocks to disk is the job of the DBWR process. If you notice that the LGWR is slowing down during checkpoints, it might be necessary to enable CKPT to eliminate the extra work that LGWR is doing.
CLEANUP_ROLLBACK_ENTRIES [number] <20>
This parameter specifies the number of undo records processed at a time when a rollback occurs. This breaks up the rollback and limits a large rollback from locking out smaller rollbacks.
CLOSE_CACHED_OPEN_CURSORS [TRUE/FALSE]
This parameter specifies whether cursors that have been opened and cached by PL/SQL are automatically closed at COMMIT. A value of FALSE allows these cursors to remain open for further use. If cursors are rarely reused, you can save space in the SGA by setting this value to TRUE. If cursors are reused, you can improve performance by leaving this parameter at the default value of FALSE.
COMPATIBLE [variable]
Setting this variable guarantees that the DBMS will remain compatible with the specified release. Some features might have to be limited for the compatibility to be maintained.
COMPATIBLE_NO_RECOVERY [variable]
This parameter works like the COMPATIBLE parameter except that the earlier version (specified as the parameter) might not work on the current database if recovery is necessary.
CONTROL_FILE_RECORD_KEEP_TIME [0-365] <7>
This parameter specifies the minimum age (in days) that a record in the control file must be kept before it can be reused.
CONTROL_FILES [1..8 filenames]
This parameter specifies the path names of one to eight control files. It is recommended that there always be more than one control file and that they exist on different physical devices.
CORE_DUMP_DEST [directory name]
This parameter specifies the directory where core files are dumped.
DB_DOMAIN [extension components of a global db name]
This parameter specifies the extension components of the global database name consisting of valid identifiers separated by periods (for example, texas.us.widgets.com). This allows multiple divisions to each have an ACCOUNTING database that is uniquely identified by the addition of the domain.
DBLINK_ENCRYPT_LOGIN [TRUE/FALSE]
When you connect to another server, Oracle encrypts the password. If the value of DBLINK_ENCRYPT_LOGIN is FALSE and the connection fails, Oracle tries to connect again with a nonencrypted password. If DBLINK_ENCRYPT_LOGIN is TRUE and the connection fails, Oracle does not attempt to reconnect.
DB_FILES [min: MAXDATAFILES, max OS dependent]
This parameter specifies the maximum number of database files that can be open. This value can be reduced if you want to reclaim space in the SGA. No performance degradation is incurred by leaving this value high, just additional memory usage in the SGA.
DB_FILE_DIRECT_IO_COUNT [OS Dependent] <64>
This parameter specifies the number of blocks to be used for I/O operations done by backup, restore, or direct path read/write functions.
DB_NAME [valid name]
This parameter provides a string of up to eight characters in length that specifies the name of the database. The following characters are valid:
· Alphabetic characters
· Numbers
· Underscore (_)
· Pound sign (#)
· Dollar sign ($)
No other characters can be used. Double quotation marks are removed and cannot be part of the name. The characters used in the DB_NAME parameter are case insensitive, so SALES, Sales, and sales are equal.
ENQUEUE_RESOURCES [10..65535]
This parameter specifies the number of resources that can be locked by the lock manager. The default value is derived from PROCESSES and is usually sufficient. The value is derived from this formula:
PROCESSES <= 3; default values = 20 PROCESSES 4-10; default value = ((PROCESSES - 3) * 5) + 20 PROCESSES > 10; default value = ((PROCESSES - 10) * 2) + 55
If you use a large number of tables, you might have to increase this value. This value should never exceed DML_LOCKS + DDL_LOCKS + 20 (overhead).
GLOBAL_NAMES [TRUE/FALSE]
This parameter determines whether a database link is required to have the same name as the database to which it connects. Oracle recommends setting this parameter to TRUE to ensure the use of consistent naming conventions for databases and links.
IFILE [parameter filename]
This parameter embeds another parameter file into the current parameter file. This can be very useful to separate specific changes from the general changes that you often make. The parameter also allows you to separate different types of parameters such as parallel options.
INIT_SQL_FILES [SQL filename]
This parameter lists the names of SQL files that should be run immediately after database creation. This parameter can be used to automatically create the data dictionary.
JOB_QUEUE_INTERVAL [1..3600] <60>
This parameter specifies, in seconds, the interval between wake-ups of the SNP background process. The processes run jobs that have been queued.
JOB_QUEUE_KEEP_CONNECTIONS [1..10] <0>
This parameter specifies the number of SNP background processes per instance.
JOB_QUEUE_PROCESSES [TRUE/FALSE]
This parameter specifies whether remote connections should be shut down after remote jobs have finished executing.
LICENSE_MAX_SESSIONS [0..number of session licenses] <0>
LICENSE_MAX_USERS sets the maximum number of concurrent user sessions allowed. When this limit is reached, only users with RESTRICTED SESSION privilege can connect to the server. A zero value indicates that this constraint is not enforced. Either LICENSE_MAX_USERS or LICENSE_MAX_SESSIONS should be set, not both.
LICENSE_MAX_USERS [0..number of user licenses] <0>
LICENSE_MAX_USERS sets the maximum number of concurrent users that can simultaneously access the database. When this limit is reached, no more user sessions can be created. A zero value indicates that this constraint is not enforced. Either LICENSE_MAX_USERS or LICENSE_MAX_SESSIONS should be set, not both.
LICENSE_SESSIONS_WARNING [0..LICENSE_MAX_SESSIONS] <0>
Sets a warning limit so that the administrator can be aware that the LICENSE_MAX_SESSIONS limit might soon be reached. After LICENSE_SESSIONS_WARNING number of users have connected, a message is written to the alert log for each additional user connecting.
LOCAL_LISTENER [string]
This parameter identifies local Net8 listeners.
LOG_ARCHIVE_DEST [valid path or device name]
When running in ARCHIVELOG mode, this text value specifies the default location and root of the file or tape device to use when archiving redo log files. Archiving to tape is not supported under all operating systems.
LOG_ARCHIVE_DUPLEX_DEST [valid path]
This parameter specifies a second archive destination for duplexed archiving.
LOG_ARCHIVE_FORMAT [valid filename]
This parameter uses a text string and variables to specify the default filename format of the archive log files. This string is appended to the LOG_ARCHIVE_DEST parameter name. The following variables can be used in the string:
· %s--Log sequence number.
· %t--Thread number. Using uppercase letters (%S, %T) causes the value to be fixed length, padded to the left with zeros. A good value is similar to the following:
LOG_ARCHIVE_FORMAT = `log%S_%T.arc'
LOG_ARCHIVE_MIN_SUCCEED_DEST [1..2] <1>
This parameter specifies the minimum number of archive log destinations that must succeed.
LOG_ARCHIVE_START [TRUE/FALSE]
When running in ARCHIVELOG mode, LOG_ARCHIVE_START specifies whether archiving should be started up automatically at instance startup. A setting of TRUE indicates that archiving is automatic; FALSEindicates that archiving is manual.
LOG_BLOCK_CHECKSUM [TRUE/FALSE]
Setting this parameter to TRUE causes each log block to be given a checksum. This checksum is written into the header of each block.
LOG_CHECKPOINTS_TO_ALERT [TRUE/FALSE]
This parameter specifies whether you want to log the checkpoints to the alert log. This can be useful in verifying the frequency of checkpoints.
LOG_FILES [2..255] <255>
This parameter specifies the maximum number of redo log files that can be opened at instance startup. Reducing this value can save some space in the SGA. If this value is set higher than the value ofMAXLOGFILES used at database creation, it does not override MAXLOGFILES.
MAX_DUMP_FILE_SIZE [0..unlimited] <500 blocks>
This parameter specifies the maximum size in OS blocks of any trace file written. Set this if you are worried that trace files might consume too much space. MAX_ENABLED_ROLES [0..48] <20> This parameter specifies the maximum number of database roles (including subroles) that a user can enable.
MAX_ROLLBACK_SEGMENTS [1..65536] <30>
This parameter specifies the maximum number of rollback segments that can be online for one instance.
OBJECT_CACHE_MAX_SIZE_PERCENT [0%..OS Dependent] <10%>
This parameter specifies the percentage of the optimal cache size beyond which the Session object cache size can grow.
OBJECT_CACHE_OPTIMAL_PERCENT [10KB..OS Dependent] <100KB>
This parameter specifies the optimal size of the Session object cache.
OPEN_CURSORS [1..OS limit] <50>
This parameter specifies the maximum number of open cursors that a single user process can have open at once.
OPEN_LINKS [0..255] <4>
This parameter specifies the maximum number of concurrent open connections to remote database processes per user process. This value should exceed the maximum number of remote systems accessed within any single SQL statement.
PARTITION_VIEW_ENABLED [TRUE/FALSE]
If set to TRUE, the optimizer skips unnecessary table accesses in a partition view.
PLSQL_V2_COMPATIBILITY [TRUE/FALSE]
This parameter sets the compatibility level for PL/SQL.
PROCESSES [6 to OS dependent] <50>
This parameter specifies the maximum number of OS user processes that connect to the Oracle instance. This number must take into account the background processes and the login process that started the instance. Be sure to add an extra six processes for the background processes.
REMOTE_DEPENDENCIES_MODE [TIMESTAMP/SIGNATURE]
This parameter specifies how dependencies on remote stored procedures are to be handled by the database.
REMOTE_LOGIN_PASSWORDFILE [NONE/SHARED/EXCLUSIVE]
This parameter specifies whether Oracle checks for a password file. A value of NONE indicates that users are authenticated through the operating system. A value of EXCLUSIVE indicates that the password file can be used only by one database and can contain names other than SYS and INTERNAL. Setting this parameter to SHARED allows more than one database to use this password file, but only SYS andINTERNAL are recognized by this password file.
REPLICATION_DEPENDENCY_TRACKING [TRUE/FALSE]
This parameter specifies that dependency tracking for read/write operations to the database is turned on.
RESOURCE_LIMIT [TRUE/FALSE]
A value of FALSE disables the enforcement of resource limits such as sessions, CPU time, and so on. This disables the enforcement of those limits regardless of how they are set.
SESSIONS [number] <1.1 * PROCESSES>
This parameter specifies the total number of user and system sessions. Because recursive sessions might occur, this number should be set slightly higher than PROCESSES. DDL_LOCKS is derived from this parameter.
SHADOW_CORE_DUMP [FULL/PARTIAL]
This parameter specifies whether the SGA is included in core dumps.
SNAPSHOT_REFRESH_INTERVAL [1..3600] <60>
This parameter specifies the number of seconds between wake-ups for the instance's snapshot refresh process.
SNAPSHOT_REFRESH_KEEP_CONNECTION [TRUE/FALSE]
This parameter specifies whether the snapshot refresh process should keep remote connections after the refresh. If set to FALSE, the remote database connections are closed after the refreshes occur.
SNAPSHOT_REFRESH_PROCESS [0..10] <0>
This parameter specifies the number of snapshot refresh processes per instance. You must set this value to 1 or higher for automatic refreshes. One snapshot refresh process is usually sufficient.
SINGLE_PROCESS [TRUE/FALSE] FALSE
If SINGLE_PROCESS is set to TRUE, the database instance is brought up in a single-user mode. A value of FALSE indicates that the database is brought up in a multiprocess mode.
TEMPORARY_TABLE_LOCKS [0..OS dependent]
TEMPORARY_TABLE_LOCKS specifies the number of temporary tables that can be created in the temporary segment space. A temporary table lock is required whenever a sort occurs that cannot be held in memory (that is, the sort exceeds SORT_AREA_RETAINED_SIZE). If your application contains a large number of ORDER BY clauses or if you perform a large number of index sorts, you might want to increase this number.
TRANSACTIONS [number] <1.1 * PROCESSES>
This parameter specifies the maximum number of concurrent transactions in the instance. The default value is greater than PROCESSES to provide for recursive transactions. A larger value increases the size of the SGA. If you increase the number of transactions allowed in the system, you might also want to increase the number of rollback segments available.
TRANSACTIONS_PER_ROLLBACK_SEGMENT [1..OS dependent] <30>
This value specifies the maximum number of concurrent transactions allowed per rollback segment. You can calculate the minimum number of rollback segments enabled at startup with this formula:
Rollback Segments = TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT
Performance can be improved if there is less contention on rollback segments. In a heavily used system, you might want to reduce TRANSACTIONS_PER_ROLLBACK_SEGMENT to decrease this contention.
USER_DUMP_DEST [valid path name]
USER_DUMP_DEST specifies the path to where the debugging trace files are written.
UTL_FILE_DIR [valid directory]
This parameter specifies directories that are permitted for PL/SQL file I/O.
Recovery Manager
These parameters are used in conjunction with the Recovery Manager.
BACKUP_DISK_IO_SLAVES [0..15] <0>
This parameter defines the number of I/O slaves used by the Recovery Manager to back up, copy, or restore.
BACKUP_TAPE_IO_SLAVES [TRUE/FALSE]
This parameter specifies whether I/O slaves are used by the Recovery Manager for tape operations.
DB_FILE_NAME_CONVERT [string]
This parameter converts the filename of a new datafile on the primary database to a filename on the standby database.
LOG_FILE_NAME_CONVERT [string]
This parameter converts the filename of a new log file on the primary database to a filename on the standby database.
TAPE_ASYNCH_IO [TRUE/FALSE]
This parameter specifies that I/O to sequential devices are asynchronous. This should be left enabled and not altered.
Multithreaded Server
These parameters are used if you are using the multithreaded server process.
MTS_DISPATCHERS ["protocol, number"]
This parameter specifies the configuration of the dispatcher process(es) created at startup time. The value of this parameter is a quoted string of two values separated by a comma. The values are the network protocol and the number of dispatchers. Each protocol requires a separate specification. This parameter can be specified multiple times. Here is an example of two dispatcher definitions:
MTS_DISPATCHERS = "tcp, 2"
MTS_DISPATCHERS = "ipx, 1"
MTS_LISTENER_ADDRESS [configuration]
This parameter specifies the configuration of the listener process addresses. There must be a listener process address for each protocol used in the system. Addresses are specified as the SQL*Net description of the connection address.
Because each connection is required to have its own address, this parameter might be specified several times. Here is an example:
MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=7002))"
MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=ipx)()())"
MTS_MAX_DISPATCHERS [OS dependent] <5>
This parameter specifies the maximum number of dispatcher processes allowed to run simultaneously.
MTS_MAX_SERVERS [OS dependent] <20>
This parameter specifies the maximum number of shared server processes allowed to run simultaneously.
MTS_MULTIPLE_LISTENERS [TRUE/FALSE]
This parameter is obsolete.
MTS_RATE_LOG_SIZE [DEFAULTS/EVENT_LOOPS/MESSAGES/SERVER_BUFFERS/CLIENT_BUFFERS/TOTAL_BUFFERS/IN_CONNECTS/OUT_CONNECTS/RECONNECTS] <10>
This parameter specifies the sample size used to calculate dispatcher-rate statistics.
MTS_RATE_SCALE [DEFAULTS/EVENT_LOOPS/MESSAGES/SERVER_BUFFERS/CLIENT_BUFFERS/TOTAL_BUFFERS/IN_CONNECTS/OUT_CONNECTS/RECONNECTS]
This parameter specifies the scale at which dispatcher-rate statistics are reported.
MTS_SERVERS [OS dependent] <0>
This parameter specifies the number of server processes created at instance startup.
MTS_SERVICE [name]
This parameter specifies the name of the service to be associated with the dispatcher. Using this name in the CONNECT string allows users to connect using the dispatcher. The name should be unique. Do not specify this name in quotes. It is usually a good idea to make this name the same as the instance name. Because the dispatcher is tried first, if it is not available, the CONNECT string can still connect the user into the database through a normal database connection.
Distributed Option
These parameters are meaningful only when you use the distributed option.
COMMIT_POINT_STRENGTH [0..255]
This value is used to determine the commit point site when executing a distributed transaction. The site with the highest value for COMMIT_POINT_STRENGTH is the commit point site. The site with the largest amount of critical data should be the commit point site.
DISTRIBUTED_LOCK_TIMEOUT [1..unlimited] <60 seconds>
DISTRIBUTED_LOCK_TIMEOUT specifies, in seconds, how long distributed transactions should wait for locked resources.
DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME [1..1800] <200 seconds>
DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME specifies, in seconds, how long to hold a remote connection open after a distributed transaction fails. A larger value holds the connection longer but also continues to use local resources even though the connection might have been severed. Any value larger than 1,800 seconds interferes with the reconnection and recovery background processes and will never drop a failed connection.
DISTRIBUTED_TRANSACTIONS [0..TRANSACTIONS]
DISTRIBUTED_TRANSACTIONS specifies the maximum number of distributed transactions that the database can process concurrently. This value cannot exceed the value of TRANSACTIONS. If you are having problems with distributed transactions because network failures are causing many in-doubt transactions, you might want to limit the number of distributed transactions.
If DISTRIBUTED_TRANSACTIONS is set to 0, no distributed transactions are allowed and the RECO process does not start at instance startup.
MAX_TRANSACTION_BRANCHES [1..32] <8>
This parameter controls the number of branches in a distributed transaction.
REMOTE_OS_AUTHENT [TRUE/FALSE]
If this parameter is set to TRUE, it allows authentication to remote systems with the value of OS_AUTHENT_PREFIX.
REMOTE_OS_ROLES [TRUE/FALSE]
If this parameter is set to TRUE, it allows remote clients to have their roles managed by the OS. If REMOTE_OS_ROLES is FALSE, roles are managed and identified by the database for the remote system.
Parallel Server Parameters
These parameters are used only in conjunction with the Oracle Parallel Server option.
ALLOW_PARTIAL_SN_RESULTS [TRUE/FALSE]
This parameter allows partial results to be returned on queries to global performance tables even if a slave could not be allocated.
CACHE_SIZE_THRESHOLD [number] <0.1 * DB_BLOCK_BUFFERS>
This parameter specifies the maximum size of a cached partition table split among the caches of multiple instances. If the partition is larger than this value, the table is not split among the caches.
DELAYED_LOGGING_BLOCK_CLEANOUTS [TRUE/FALSE]
This parameter enables the delayed block cleanout feature. This can reduce OPS pinging.
FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY [TRUE/FALSE]
This parameter specifies that the entire database freeze in order to speed recovery.
GC_DEFER_TIME [integer] <0>
This parameter specifies the time the server waits (in hundredths of a second) before responding to a forced-write request for hot blocks.
GC_DB_LOCKS [0..unlimited] <0>
This parameter specifies the number of PCM locks allocated. The value of GC_DB_LOCKS should be at least one greater than the sum of the locks specified with the parameter GC_FILES_TO_LOCKS.
GC_FILES_TO_LOCKS [file_number=locks:filename=locks]
This parameter supplies a list of filenames, each specifying how many locks should be allocated for that file. Optionally, the number of blocks and the value EACH can be added to further specify the allocation of the locks.
GC_LCK_PROCS [0..10] <1>
This parameter specifies the number of lock processes (LCK0 to LCK9) to create for the instance. The default value of 1 is usually sufficient unless an unusually high number of locks are occurring.
GC_RELEASABLE_LOCKS [0..DB_BLOCK_BUFFERS]
This parameter allocates space for fine-grain locking.
GC_ROLLBACK_LOCKS [number] <20>
This parameter specifies the number of distributed locks available for each rollback segment. The default value is usually sufficient.
GC_ROLLBACK_SEGMENTS [number] <20>
GC_ROLLBACK_SEGMENTS specifies the maximum number of rollback segments systemwide. This includes all instances in the parallel server system, including the SYSTEM rollback segment.
GC_SAVE_ROLLBACK_LOCKS [number] <20>
This parameter specifies the number of distributed locks reserved for deferred rollback segments. These deferred rollback segments contain rollback entries for segments taken offline.
GC_SEGMENTS [number] <10>
This parameter specifies the maximum number of segments that might have space-management activities simultaneously performed by different instances.
GC_TABLESPACES [number] <5>
This parameter specifies the maximum number of tablespaces that can be simultaneously brought online or offline.
INSTANCE_GROUPS [string]
This parameter assigns the current instance to this instance group.
INSTANCE_NUMBER [1..OS dependent]
This parameter specifies a unique number that maps the instance to a group of free space lists.
LM_LOCKS [512..Limited by Instance Size] <12000>
This parameter specifies the number of locks that are configured for the lock manager.
LM_PROCS [36..PROCESSES+instances+safety factor] <64+instances>
This parameter represents the number of the PROCESSES parameter plus the number of instances.
LM_RESS [256..Limited by Instance Size] <6000>
This parameter controls the number of resources that can be locked by each lock-manager process.
LOCK_NAME_SPACE [string]
This parameter specifies the name space that the distributed lock manager (DLM) uses to generate lock names.
MAX_COMMIT_PROPAGATION_DELAY [0..90000] <90000>
This parameter specifies the maximum amount of time that can pass before the SCN (System Change Number) is changed by the DBWR. This value helps in certain conditions where the SCN might not be refreshed often enough because of a high load from multiple instances.
OPEN_LINKS_PER_INSTANCE [0..UB4MAXVAL] <4>
This parameter specifies the maximum number of migratable open connections.
OPS_ADMIN_GROUP [group name]
This parameter allows instances to be grouped for monitoring and administration.
PARALLEL_DEFAULT_MAX_INSTANCES [0..instances]
This parameter specifies the default number of instances to spit a table among for parallel query processing. This value is used if the INSTANCES DEFAULT is specified in the table/cluster definition.
PARALLEL_INSTANCE_GROUP [string]
This parameter specifies the parallel instance group to be used for spawning parallel query slaves.
PARALLEL_SERVER [TRUE/FALSE]
Setting this to TRUE enables the Parallel Server option.
PARALLEL_TRANSACTION_RESOURCE_TIMEOUT [0..OS Dependent] <300>
This parameter specifies the maximum amount of time (seconds) that can pass before a session executing a parallel operation will time-out while waiting on a resource held by another session. THREAD [0..max threads] <0> This parameter specifies the number of the redo thread to be used by this instance. Any number can be used, but the value must be unique within the cluster.
Security
These parameters help set up system security; manipulate them to obtain the best mix of efficiency and security.
AUDIT_FILE_DEST [dir_name] <$ORACLE_HOME/RDBMS/AUDIT>
This parameter specifies the directory where audit files are stored.
AUDIT_TRAIL [NONE,DB,OS]
The AUDIT_TRAIL parameter enables auditing to the table SYS$AUD$. Auditing causes a record of database and user activity to be logged. Because auditing causes overhead, it limits performance. The amount of overhead and the effect on performance is determined by what and how much is audited. Once AUDIT_TRAIL is enabled, auditing is turned on by the Oracle command AUDIT.
O7_DICTIONARY_ACCESSIBILITY [TRUE/FALSE]
If set to TRUE (default), access to the SYS schema is allowed. This is Oracle7 behavior.
OS_AUTHENT_PREFIX []
This is the value concatenated to the beginning of the user's OS login account to give a default Oracle account name. The default value of OPS$ is OS dependent and is provided for backward compatibility with previous Oracle versions. Typically, you use the default or set the value to "" (NULL) to eliminate prefixes altogether.
OS_ROLES [TRUE/FALSE]
Setting this parameter to TRUE allows the OS to have control over the username's roles. If set to FALSE, the username's roles are controlled by the database.
SQL92_SECURITY [TRUE/FALSE]
This parameter specifies whether the table-level SELECT privileges are needed to execute an update or delete that reference's table-column values.
TRANSACTION_AUDITING [TRUE/FALSE]
This parameter specifies that additional transaction information is included in a special redo record.
Trusted Oracle7
The following parameters apply to the Trusted Oracle7 option.
AUTO_MOUNTING [TRUE/FALSE]
When set to TRUE, this parameter specifies that a secondary database is mounted by the primary database whenever a user connected to the primary database requests data from the secondary database.
DB_MOUNT_MODE [NORMAL/READ_COMPATIBLE]
This parameter specifies the access mode to which the database is mounted at instance startup. A value of NORMAL starts the database in normal read-write mode; READ_COMPATIBLE starts the database in read-write mode with the added feature of supporting concurrent mounting by one or more read-secure instances.
LABEL_CACHE_SIZE [number> 50] <50>
This parameter specifies the cache size for dynamic comparison of labels. This number should be greater than the label-category combinations in the OS and should never be less than 50.
MLS_LABEL_FORMAT [valid label format]
This parameter specifies the format used to display labels. The default value sen specifies sensitive.
OPEN_MOUNTS [0..255] <5>
This parameter specifies the maximum number of databases that an instance can simultaneously mount in OS MAC mode. This value should be large enough to handle all the primary and secondary databases you might mount.
National Language Support
The following parameters are used in the configuration of National Language Support features.
NLS_CURRENCY [character string]
This parameter specifies the string to use as the local currency symbol for the L number format element.
NLS_DATE_FORMAT [format mask]
This parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. The value of this parameter is any valid date format mask. Here is an example:
NLS_DATE_FORMAT = `DD/MM/YYYY'
NLS_DATE_LANGUAGE [NLS_LANGUAGE value]
This parameter determines the language to use for the day and month names and date abbreviations (AM, PM, AD, BC).
NLS_ISO_CURRENCY [valid NLS_TERRITORY value]
This parameter defines the string to use as the international currency symbol for the C number format element.
NLS_LANGUAGE [NLS_LANGUAGE value]
This parameter defines the default language of the database. This specifies the language to use for messages, the language of day and month names, symbols to be used for AD, BC, A.M. and P.M., and the default sorting mechanisms.
NLS_NUMERIC_CHARACTERS [two characters]
This parameter defines the characters to be used as the group separator and decimal. The group separator is used to separate the integer groups (that is, hundreds, thousands, millions, and so on). The decimal separator is used to distinguish between the integer and decimal portion of the number. Any two characters can be used but they must be different. The parameter is specified by two characters within single quotes. To set the group separator to , (comma) and the decimal separator to . (period), use the following statement:
NLS_NUMERIC_CHARACTERS = `,.'
NLS_SORT [BINARY or named linguistic sort]
If this parameter is set to BINARY, the collating sequence for ORDER_BY is based on the numeric values of the characters. A linguistic sort decides the order based on the defined linguistic sort. A binary sort is much more efficient and uses much less overhead.
NLS_TERRITORY [territory name]
This parameter specifies the name of the territory whose conventions are used for day and week numbering. The parameter also provides defaults for other NLS parameters.
Oracle Corporation has invested millions of dollars in making the cost-based SQL optimizer (CBO) one of the most sophisticated tools ever created. The job of the CBO is to always choose the most optimal execution plan for any SQL statement.
However, there are some things that the CBO cannot detect, which is where the DBA comes in. The types of SQL statements, the speed of the disks and the load on the CPUs, all affect the "best" execution plan for a SQL statement. For example, the best execution plan at 4:00 A.M. when 16 CPUs are idle may be quite different from the same query at 3:00 P.M. when the system is 90 percent utilized.
Despite the name "Oracle", the CBO is not psychic, and Oracle can never know, a priori, the exact load on the Oracle system. Hence the Oracle professional must adjust the CBO behavior periodically. Most Oracle professionals make these behavior adjustments using the instance-wide CBO behavior parameters such asoptimizer_index_cost_adj and optimizer_index_caching.
However, Oracle does not recommend changing the default values for many of these CBO settings because the changes can affect the execution plans for thousands of SQL statements.
Here are some of the major adjustable parameters that influence the behavior of the CBO:
· optimizer_index_cost_adj: This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the cheaper the cost of index access.
· optimizer_index_caching: This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting foroptimizer_index_caching affects the CBO's decision to use an index for a table join (nested loops), or to favor a full-table scan.
· optimizer_max_permutations: This controls the maximum number of table join permutations allowed before the CBO is forced to pick a table join order. For a six-way table join, Oracle must evaluate 6-factorial, or 720, possible join orders for the tables.
· db_file_multiblock_read_count: When set to a high value, the CBO recognizes that scattered (multi-block) reads may be less expensive than sequential reads. This makes the CBO friendlier to full-table scans.
· parallel_automatic_tuning: When set "on", full-table scans are parallelized. Because parallel full-table scans are very fast, the CBO will give a higher cost to index access, and be friendlier to full-table scans.
· hash_area_size (if not using pga_aggregate_target): The setting forhash_area_size parameter governs the propensity of the CBO to favor hash joins over nested loop and sort merge table joins.
· sort_area_size (if not using pga_aggregate_target): The sort_area_sizeinfluences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over pre-sorted index retrieval.
The parameter optimizer_index_cost_adj controls the CBO's propensity to favor index scans over full-table scans. As we will see, in a dynamic system, the "ideal" value foroptimizer_index_cost_adj may change radically in just a few minutes, as the type of SQL and load on the database changes.
Using optimizer_index_cost_adj
The optimizer_index_cost_adj is the most important parameter of all, and the default setting of 100 is incorrect for most Oracle systems. However, for OLTP systems, resetting this parameter to a smaller value (between 10 and 30) may result in huge performance gains.
Is it possible to query the Oracle environment and intelligently determine the optimal setting for optimizer_index_cost_adj? Let's examine the issue.
The optimizer_index_cost_adj parameters default to a value of 100, and can range in value from 1 to 10,000. A value of 100 means that equal weight is given to index vs. multiblock reads. In other words, optimizer_index_cost_adj can be thought of as a "how much do I like full-table scans?" parameter.
With a value of 100, the CBO likes full-table scans and index scans equally, and a number lower than 100 tells the CBO that index scans are faster than full-table scans. However, even with a super-low setting (optimizer_index_cost_adj=1), the CBO will still choose full-table scans for no-brainers, like tiny tables that reside on two blocks.
If you are having slow performance because the CBO first_rows optimizer mode is favoring too many full-table scans, you can reset the optimizer_index_cost_adjparameter to immediately tune all of the SQL in your database to favor index scans over full-table scans. This is a "silver bullet" that can improve the performance of an entire database in cases where the database is OTLP and you have verified that the full-table scan costing is too low.
The parameter is an initialization parameter that can be enabled at the session level by using the alter session set optimizer_index_cost_adj = nn syntax. This parameter lets you tune the optimizer behavior for access path selection to be more or less index-friendly, and it is very useful when you feel that the default behavior for the CBO favors full-table scans over index scans.
When the CBO has minimal or too small a statistics sample, the CBO sometimes falsely determines that the cost of full-table scan is less than the cost of an index access. The optimizer_index_cost_adj parameter is a great approach to whole-system SQL tuning, but you will need to evaluate the overall effect by slowly resetting the value down from 100 and observing the percentage of full-table scans.
You can also slowly bump down the value of optimizer_index_cost_adj when you bounce the database and then either use the access.sql scripts or reexamine SQL from the STATSPACK stats$sql_summary table to see the net effect of index scans on the whole database.
As you can see, the suggested starting value for optimizer_index_cost_adj may be too high because 98 percent of the data waits are on index (sequential) block access. How we can "weight" this starting value for optimizer_index_cost_adj to reflect the reality that this system has only two percent waits on full-table scan reads (a typical OLTP system with few full-table scans). As a practical matter, we never want an automated value for optimizer_index_cost_adj to be less than one or more than 100.
No comments:
Post a Comment