Oracle pfile/spfile Parameter file detailed

Basic rules

A. In the SPFile file, all parameters are optional, that is, you only need to list those parameters that need to be modified in the initialization parameter file, others to keep the default values.

The B.spfile file can contain only parameter assignment statements and comment statements. The comment statement starts with "#" and is a single-line comment.

The parameters listed in the C.spfile file are in no order.

The name and value of the D.spfile parameter are case-insensitive, and only in a UNIX system is it necessary to be aware of the case when setting parameters, because the operating system itself distinguishes between file names and path capitalization.

E. To set multiple parameters on the same line in the SPFile file, you need to use a space separator. For example: processes=100 cpu_count=1 open_cursors=10

F. For initialization parameters that can have multiple values, you need to enclose all values in parentheses and separate them with commas, such as rollback_segments= (SEG1,SEG2,SEG3,SEG4,SEG5)

G. In the SPFile file, you can use the IFile parameter to refer to other spfile files.

H. For a parameter with a string type value, if the string contains spaces or tabs, you must enclose the string in either single or double quotes, such as: Nls_territory= "CZECH Republic"

Common parameters:

Db_name and Db_domain

The global database tutorial name typically consists of a local database identity (db_name) and a network domain name (db_domain) that uniquely identifies an oracle on the network and, in the case of only one instance, the same db_name as the instance identity SID.

The global database name can be changed after the database is created, using the Alter database RENAME global_name statement, and after the statement has been successfully executed, Oracle must be restarted and the control file rebuilt (rebuilding the control file is a more dangerous task, so It is recommended that you avoid modifying the global database name.

Db_name is also stored in data files, control files, and redo log files, and Oracle will not start if the database name specified by the Db_name parameter is not consistent with the control file.

Control_file

The control file holds basic information about the physical structure of the database, and each Oracle must have at least one control file. Oracle recommends creating two control files and should place them on a different point hard drive.

Db_block_buffer

The main function of this memory is to cache the chunks of data that are read from the database in memory, and the larger the database buffer, the greater the memory provided by the user's shared data in memory, which can reduce the amount of physical disk read and write times required.

In the Oracle 9i database, the buffer is determined by db_block_buffers*db_block_size, and the Db_block_size parameter is set when the database is created, which determines the size of each block in the database. 9i cannot change the block size of an existing database before, so be sure to set the appropriate database block size when you initially create the database. The product that is set to 8k,db_block_buffers*db_block_size when the database is created is the 20%~30% of physical memory, within 1G within the physical, can account for 25% between 20%,1g~2g, the physical presence 2G above, can account for 30%, Even higher.

Db_block_lru_latches

Multiple LRU latches are allowed on multiple CPU machines via initialization parameters db_block_lru_latches. When the DBWR and server processes scan the data block buffer cache, they need to obtain the LRU latch. This latch is necessary to avoid buffer fouling and to avoid being altered by other processes, which also avoids inconsistent results when the scan is returned. If a latch is not used, a process that scans the free buffer may find a free buffer, but it may be used by other processes at once. Each LRU latch protects at least 50 data block buffers. All buffers are assigned to a specific LRU latch based on the hash algorithm for Oracle. If the value is too small, it can potentially cause competition when the amount of database activity is large. This competition is higher on a multiple-CPU machine because multiple server processes may queue to obtain a LRU Cache. The default value for this parameter is half the number of CPUs, and in a multiple-CPU system, it is recommended to be equal to twice times ~3 times the number of CPUs or CPUs (in Oracle 9i, this parameter has become an implied parameter).

Db_block_size

A database block is the smallest unit that Oracle uses to manage storage space, also known as an Oracle block, which can be composed of one or more operating systems quickly.

The size of the Oracle block is specified by Db_block_size when Oracle is created, and cannot be changed after Oracle is established. Db_block_size The specified value is the size of the standard Oracle block, and the system table space and other tablespaces use the standard Oracle block by default. The standard Oracle block must be an integer multiple of the operating system, usually 4KB or 8KB, and as the size of the database block increases, so does the need for the memory (SGA area) of the database.

Note: In Oracle9i, a maximum of four different sizes of non-standard Oracle blocks are supported, which can be any of the four in 2KB, 4KB, 8KB, 16KB, or 32KB.

Db_cache_size

The SGA area is an important part of an Oracle instance, which is a shared memory area specifically allocated by the operating system to Oracle.

This parameter directly specifies the size (in kilobytes or MB) of the database in the SGA area, and dynamic parameters that can be dynamically changed during the instance's operation.

Db_file_multiblock_read_count

This parameter is mainly related to full table scanning. This parameter controls the read rate of the block when Oracle is requesting a large number of contiguous blocks of data. The Db_file_multiblock_read_count parameter can have a great influence on the system performance, and it has an important relationship with db_block_size parameters. Because Oracle always reads in the smallest 64K database block on the UNIX physical layer, the product of these 2 parameters should be 64K. That is, if the db_block_size is 8192, the Db_file_multiblock_read_count should be set to 8.

Db_nk_cache_size

If you want to use non-standard blocks in your database, you must specify at least one db_nk_cache_size parameter in addition to using the Db_cache_size parameter.

There are altogether 5 db_nk_cache_size: Db_2k_cache_size, Db_4k_cache_size, Db_8k_cache_size, Db_16k_cache_size, DB_32K_CACHE_SIZE

Db_queue_process

This parameter specifies the number of SNP job queue processes for each routine. When using a job or copy, be sure to set the parameter, which is recommended to be 3 or 4. Parameters that need to be set in a parallel query

Parallel queries can have a beneficial effect on the performance of some queries (with full table scan operations) only if the multiple CPUs are idle and the data is distributed across different disks, and even in this case the maximum number of parallel query servers is best not to exceed the number of CPUs. The parameters that should be modified are as follows.

　　

Dml_locks

This parameter represents the maximum number of table locks that are obtained by all users. A DML lock is required to perform a DML operation on each table. For example, if 3 users modify 2 tables, the value is required to be 6. A too small value may cause a deadlock problem. This parameter is related to the transaction parameter, and the default is 4 times times the transaction size. It is recommended that this parameter should not be less than 600, you can check the current value of the system, if smaller than 600, then change the transaction parameter to 150 or more, the Dml_locks parameter will automatically change to Transaction 4 times times.

Java_pool_size

Oracle databases have built-in support for Java in later versions, and the short message system does not use these features, so the java_pool_size value can be reduced to 1M. However, if the Jserver component is selected when the database is installed, this value can be set to between 20m~30m. (You can query the v$option dynamic view, and if the Java value is True, Jserver is installed, false to indicate that it is not installed).

Large_pool_size

Oracle's Large_pool is used for MTS, parallel queries, and Rman. If MTS or rman,large_pool is particularly useful, it can reduce user contention for Shared_pool, which is not recommended for settings. (or set to 1M).

Log_buffer

Log_buffer is the redo log buffer, and any modifications to the database are recorded sequentially in the buffer and then written to disk by the LGWR process. The LGWR write condition is that the user commits, has 1/3 redo log buffers not been written to disk, has a greater than 1M redo log buffer not written to disk, timed out, DBWR the data required to write the SCN number greater than the LGWR record's SCN number, DBWR triggers the LGWR write. It can be seen that the value of log buffer greater than 1M is not significant, it is recommended that no matter how much physical memory