Oracle Parameter Optimization

Many parameters are required to optimize the Oracle database. Some of these parameters have a great impact on the system performance. These parameters are variable parameters. Variable parameters can be divided into two categories based on their functions. One category is restricted, such as OPEN_CURSORS. The other category is affected by system performance, such as DB_BLOCK_BUFFERS.

When optimizing the performance of the database system, you must be familiar with and understand some variable parameters. This article discusses some parameters that have a great impact on the system.
Variable parameters of the restriction class
(1) DML_LOCKS
This parameter indicates the number of users and the number of tables that can be modified at the same time. For example, if three users modify two tables at the same time, the total number of tables must be 6. If it is set to 0, the organizational queue does not work, and its performance will be slightly improved. You cannot use drop table, create index, or explicit blocking when using this parameter.
(2) LICENSE_MAX_SESSION
This parameter specifies the maximum number of concurrent user sessions allowed. If this parameter is set to 0, concurrency cannot be implemented. If the number of concurrent user sessions reaches this limit, only users with restricted session permissions can connect to the server.
(3) LICENSE_MAX_USERS
This parameter specifies the maximum number of users that can be created in a database. When the maximum value is reached, you cannot create a new user. You can change this value to relax the limit. When the value of LICENSE_MAX_SESSION or LICENSE_MAX_USER is 0, the concurrent session or any user cannot use it. If this parameter is different for different instances, the parameter of the first database instance to be logged on prevails.
(4) MAX_DUMP_FILE_SIZE
This parameter specifies the maximum number of write trace file blocks in the operating system. This value can be used to limit the space of the trace file.
(5) OPEN_CURSORS
This parameter specifies the maximum number of user processes that can open the cursor at the same time. It can limit the amount of memory space occupied by each user process.
(6) OPEN_LINKS
This parameter specifies the maximum number of user processes concurrently connected to the remote database. If multiple databases are referenced at the same time, this value should be increased. For example, if OPEN_LINKS is set to 2 when you access A, B, and C databases at the same time, you need to wait for the connection time. This parameter is only used for distributed transactions. If this parameter is set to 0, distributed transactions are not allowed.
(7) PROCESS
This parameter specifies the maximum number of user processes simultaneously connected to the Oracle server. This parameter includes six background processes and one logon. Therefore, if this parameter is set to 20, only 13 or 14 concurrent users can connect to the server.
(8) ROW_LOCKING
This parameter specifies the row blocking method. If it is set to "ALWAYS", only row blocking is implemented when the table is modified. If it is set to "INTENT", the row blocking only applies to select for update, while the table blocking is implemented during modification.
 
Variable parameters that affect system performance
(1) CHECKPOINT_PROCESS
This parameter is set to TRUE or FALSE based on whether the checkpoint is required. When all the buffer information is written to the disk, the checkpoint process (CHPT) establishes a static point. Make a mark in the archived log file to indicate that a checkpoint has occurred. The checkpoint occurs when the archive log is converted or when the number of log_checkpoint_interval blocks is reached. When this parameter is set to TRUE, the background process CHPT can work. During the checkpoint period, if the performance of the log writing process (LGWR) is reduced, the CHPT process can be used to improve the performance.
(2) DB_BLOCK_CHECKPOINT_BATCH
When this parameter is set to a large value, the checkpoint can be accelerated. When the specified value is greater than DB_BLOCK_CHECKPOINT_BATCH, the effect is the same as the specified maximum value.
(3) DB_BLOCK_BUFFERS
This parameter is the number of database blocks that can be buffered in SGA. This parameter determines the size of the SGA and determines the database performance. If a large value is used, the I/O count can be reduced, but the memory space is required to be large. The size of each buffer is determined by the db_block_size parameter.
(4) db_block_size
This parameter indicates the size of the Oracle database block, in bytes. The typical value is 2048 or 4096. Once set, this value cannot be changed. It affects the maximum value of the freelists parameter for tables and indexes.
(5) db_files
This parameter is the maximum number of data files that can be opened when the database is running.
(6) db_file_multiblock_read_count
This parameter indicates the maximum number of readable parts for one I/O operation during sequential scanning. the maximum number of parts depends on the operating system, and the value ranges from 4 to 16 or 32.
(7) d1screte_transaction_enabled
This parameter implements a simpler and faster rollback mechanism to improve the performance of some transaction types. When set to true, the transaction performance of some types can be improved.
(8) log_archive_buffer_size
The value of this parameter depends on the operating system. It is used together with the log_archive_buffer parameter to adjust the running of archived logs so that the running speed is as fast as possible, but the performance cannot be reduced quickly. These parameter values must be added only when files are directly archived to tape devices. Redo the log buffer to wait until the archived log buffer becomes available.
(9) log_archive_buffer
This parameter specifies the number of buffers used for archiving logs.
(10) log_buffer
This parameter specifies the number of bytes allocated to the log buffer in SGA. When this parameter is set to a large value, the number of log I/O operations can be reduced. We recommend that you use a value greater than or equal to 64 kb for a busy system. Default Value: 4 times the database block.
(11) log_checkpoint_timeout
This parameter specifies the time interval between two checkpoints. If it is set to 0, time-based checkpoints are not allowed.
(12) log_checkpoint_interval
This parameter is used to determine the execution frequency of the checkpoint process. This value is set to the number of redo buffer blocks processed before the checkpoint.
(13) log_files
This parameter specifies the number of log files that can be opened by the database during running. This value can be reduced if a large SGA space is required without multiple log files.
(14) log_simultaneous_copies
This parameter is the maximum number of replica latches in the log buffer. It is used to write log entries at the same time. To improve performance, you can set this parameter to double the number of cpus. For a single-process system, this value is mostly set to 0. In this case, the locks are closed.
(15) log_small_entry_max_size
This parameter is used with the log_simultaneous_copies parameter. If the log entry is greater than this option, the user process releases the log replication lock after allocating space to the buffer zone and obtaining the log replication lock.
(16) optimizrer_mode
If the value of this parameter is rule, the Oracle optimizer selects rule-based optimization. If it is set to cost and there is statistical information in the data dictionary, the optimizer selects a cost-based Optimization Method for optimization.
(17) sequence_cache_entries
This parameter specifies the number of sequences that can be cached in SGA for direct access. The cache is managed based on the least recently used (LRU) algorithm. If this value is set to a higher value, high concurrency can be achieved.
(18) sequence_cache_hash_buckets
This parameter is used to accelerate the number of Bucket addresses in the latest sequence of recent requests to view the high-speed buffer. Each bucket address occupies 8 bytes. The high-speed buffer is arranged in a hash. This parameter must be a prime number.
(19) SERIALIZEABLE
This parameter is used to ensure the consistency of repeated reads. When this parameter is set to TRUE, the query ensures table-level read consistency to prevent modifications made before the query is submitted.
(20) SHARED_POOL_SIZE
This parameter specifies the size of the Shared Pool, including the shared cursor and stored procedure. In a multi-user system, a large value of SHARED_POOL_SIZE can improve SQL statement execution performance, but a small value can save memory.
(21) SMALL_TABLE_THRESHOLD
This parameter determines the number of buffers used for scanning in SGA. If the number of tables is smaller than this value, the table can be fully read into the cache zone. If the table is greater than this value, the buffer zone is reused immediately. The default value is generally used to optimize performance.
(22) SORT_AREA_TETAINED_SIZE
This is the maximum number of session memory used for memory sorting. When the last row is raised from the sorting space, the memory is released. If the sorting requires a large memory size, a temporary segment is allocated and the sorting can be performed on the disk. The maximum amount used for sorting can be specified by SORT_AREA_SIZE without this parameter. Multiple sorting spaces of the same size can be allocated, but they are generally required for complex queries.
(23) SORT_AREA_SIZE
This parameter is used to specify the maximum amount of PGA memory required for external sorting (Disk), in bytes. When the sort row is written to the disk, the memory is released. Increasing the value of this parameter can improve the sorting efficiency. This parameter is not adjusted unless the sorting volume is large.
(24) SORT_SPACEMP_SIZE
This parameter is adjusted only when the sorting volume is large. You can set this parameter in the following way to make the best use of disk space for sorting:
[(Total_sort_bytes)/(SORT_AREA_SIZE)] Ten 64
Where total_sort_bytes is:
(Number_of_records) * [sum_of_aver_average_column_sizes + (12 * number of (al)]
(25) SQLTRACE
When this parameter is set to TRUE, you can trace it to obtain information about performance improvement. Because tracing increases overhead, it is generally set to TRUE only when collecting information. In actual use, you can use the alter session command to overwrite it.
(26) TRANSACTION
This parameter sets the maximum number of concurrent transactions. If this value is large, you need to increase the SGA space and the number of allocated rollback segments. When the default value is greater than PROCESS, recursive transactions are allowed.

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/nilxin/archive/2009/07/13/4344828.aspx