Oracle pfile/spfile parameter file

Basic Rules

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

B. The SPFile file can only contain parameter value 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.

D. the name and value of the SPFile parameter are case-insensitive. Only in Unix systems are case sensitive because the operating system itself is case sensitive to file names and paths.

E. To set multiple parameters in the same row of the SPFile file, use spaces to separate them. For example: PROCESSES = 100 CPU_COUNT = 1 OPEN_CURSORS = 10

F. for initialization parameters that can have multiple values, you need to put all values in one bracket 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 reference other SPFile files.

H. For parameters with string type values, if the string contains spaces or tabs, you must enclose the string with single or double quotation marks, for example, NLS_TERRITORY = "czech republic"

Common parameters:

DB_NAME and DB_DOMAIN

The global database tutorial name is usually composed of a local database ID (DB_NAME) and a network domain name (DB_DOMAIN). It uniquely identifies an Oracle database on the network. When there is only one instance, DB_NAME is the same as the instance id SID.

The global DataBase name can be changed after the DataBase is created. After the statement is successfully executed, use the Alter DataBase RENAME GLOBAL_NAME statement, you must restart Oracle and recreate the control file (rebuilding the control file is a dangerous task, so we recommend that you avoid modifying the global database name ).

DB_NAME is stored in both data files, control files, and redo log files. If the database name specified by the DB_NAME parameter is inconsistent with the control file, Oracle cannot start the database.

CONTROL_FILE

The Control File Stores basic information about the physical structure of the database. Each Oracle must have at least one control file. Oracle recommends that you create two control files and place them on different hard disks.

DB_Block_Buffer

This part of the memory is mainly used to cache data blocks read from the database in the memory. The larger the database buffer, the larger the memory provided for the user's shared data in the memory, this reduces the number of physical reads and writes to the required disk.

In database versions earlier than Oracle 9i, the buffer is determined by db_block_buffers * db_block_size. The db_block_size parameter is set during database creation and determines the size of each block in the database, before 9i, you cannot change the block size of an existing database. Therefore, make sure to set the appropriate block size when creating the database. When the database is created, it is set to 8 k. The product of db_block_buffers * db_block_size accounts for 20% of the physical memory ~ 30%. The physical memory is less than 1 GB, which may account for 20%, 1 GB ~ 2G can account for 25% of physical resources. 2g or more physical resources can account for 30% or even higher.

DB_Block_Lru_Latches

Multiple LRU latches are allowed on multiple CPU instances by initializing the DB_BLOCK_LRU_LATCHES parameter. When DBWR and server processes scan the data block buffer Cache, they need to obtain the LRU lock. This kind of latch is necessary to avoid dirty buffer and other process changes, which also avoids returning inconsistent results during scanning. If no latches are used, a process that scans the free buffer may find a free buffer, but it may be immediately used by other processes. Each LRU latch protects at least 50 data block buffers. All the buffers are allocated to specific LRU latches by Oracle Based on the Hash algorithm. If this value is too small, it may lead to potential competition when the database activity volume is large. The competition on multiple CPU machines is higher, because multiple server processes may queue up to obtain an LRU Cache. The default value of this parameter is half of the number of cpus. In multi-CPU systems, it is recommended to set it to equal to the number of CPUs or 2 times of the number of CPUs ~ 3X (in Oracle 9i, this parameter has been changed to an implicit parameter ).

DB_BLOCK_SIZE

 

A database BLOCK is the smallest unit used by Oracle to manage buckets. It is also known as an Oracle BLOCK. An Oracle BLOCK can be quickly composed of one or more operating systems.

The size of Oracle blocks is specified by DB_BLOCK_SIZE when Oracle is created, and cannot be changed after Oracle is created. The value specified by DB_BLOCK_SIZE is the size of the standard Oracle block. The standard Oracle block is used by default for the SYSTEM tablespace and other tablespaces. The standard Oracle block must be an integer multiple of the operating system, usually 4 kb or 8 KB. As the size of the database block increases, the database Requirements for memory (SGA) will also increase.

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

DB_Cache_Size

The SGA zone is an important part of Oracle instances. It is a shared memory zone allocated by the operating system to Oracle.

This parameter specifies the size (in KB or MB) of the buffer area that the database in the SGA Region informs. The dynamic parameter can be dynamically changed during the instance running.

DB_File_MultibLock_Read_Count

This parameter is mainly related to full table scan. When Oracle requests a large number of continuous data blocks, this parameter controls the reading rate of the blocks. The DB_FILE_MULTIBLOCK_READ_COUNT parameter has a significant impact on system performance and has an important relationship with the DB_BLOCK_SIZE parameter. Because on the Unix physical layer, Oracle always reads data with a minimum of 64 K database blocks, so the product of these two parameters should be 64 K. That is, if DB_BLOCK_SIZE is 8192, DB_FILE_MULTIBLOCK_READ_COUNT should be set to 8.

DB_nK_CACHE_SIZE

If you want to use a non-standard block in the database, you must specify at least one DB_nK_CACHE_SIZE parameter in addition to the DB_CACHE_SIZE parameter.

DB_nK_CACHE_SIZE: DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_16K_CACHE_SIZE, and DB_32K_CACHE_SIZE

DB_Queue_Process

This parameter specifies the number of SNP Job Queue processes for each routine. This parameter must be set when job or replication is used. It is recommended to set it to 3 or 4. Parameters to be set in parallel query

Parallel query only when multiple CPUs are idle and data is distributed on different disks can negatively affect the performance of some queries (with full table scan operations, in this case, the maximum number of parallel query servers should not exceed the number of CPUs. The parameters to be modified are as follows. DML_Locks

This parameter indicates the maximum number of table locks obtained by all users. A dml lock is required for performing DML operations on each table. For example, if three users modify two tables, the value is 6. A small value may cause a deadlock. This parameter is related to the transaction parameter. The default value is 4 times the transaction size. It is recommended that this parameter not be lower than 600. You can check the current value of the system. If it is smaller than 600, the transaction parameter is changed to 150 or above, and the dml_locks parameter is automatically changed to 4 times of transaction.

Java_Pool_Size

In Versions later than Oracle 8i, Oracle database has built-in support for Java. The short message system does not use these features. Therefore, the value of java_pool_size can be reduced to 1 MB. However, if the JServer component is selected during database installation, the value can be set to 20 mb ~ Between 30 mb. (You can query the v $ option dynamic view. If the value of java is true, JServer is installed. If the value is false, JServer is not installed ).

Large_Pool_Size

Oracle's large_pool is used for MTS, parallel queries, and RMAN. If MTS or RMAN is used, large_pool is particularly useful. It can reduce user contention for shared_pool. this parameter is not recommended. (Or set to 1 M ).

Log_Buffer

Log_buffer is the redo log buffer. Any changes to the database are recorded in the buffer in order, and then written to the disk by the LGWR process. The write conditions for LGWR are: the SCN numbers of data submitted by the user, 1/3 redo log buffers not written to the disk, 1 Mbit/s redo log buffers not written to the disk, timeout, and DBWR data to be written are greater than the SCN numbers recorded by LGWR, DBWR triggers LGWR writing. It can be seen that the value of log buffer greater than 1 MB is of little significance. We recommend that you