Redo Oracle log files

After learning to redo Oracle log files for a long time, I would like to share with you that you have certainly gained a lot after reading this article. I hope this article will teach you more. In Windows 2000 Server, the "performance" tool can be used to monitor disk activity. It is excessive to perform more than 40 I/O operations on most VMS and Unix disks per second and should be adjusted.

Distribute I/O to solve disk contention

(1) store redo Oracle log files
Redo Oracle log files are written to the disk sequentially, so try not to store the redo Oracle log files and data files on the same disk. If you have to store the DATA file and the redo Oracle log file on the same disk, the disk space should not belong to the SYSTEM tablespace, RBS tablespace, and other table spaces such as DATA and INDEX tablespace. Both of them directly lead to contention with Oracle log files Redo, and increase the read/write speed of the log database.

(2) storing archive log files
When a large number of data transactions are processed, selecting the archive function for the database will cause contention between the database and the redo log disk. In addition, when there is insufficient space for redo Oracle log files, the database will be frozen. Therefore, archive log files cannot be stored on the same device as SYSTEM tablespace, RBS tablespace, or INDEX tablespace, nor on the same device as any online redo Oracle log files. It can only be stored with small and static files.

(3) store data code files
To reduce contention between database files and database code, you should avoid placing database files on the same disk as code files. If the data file has to be placed on the same disk device, the least commonly used data file should be put there.

(4) reduce non-Oracle file I/O

(5) Optimize DBWR process contention

Database write process (DBWR), log write process (LGWR), and archive process (ARCH) background processing processes can freely access the database files on the disk. I/O contention is often caused by simultaneous read/write contention among the three processes, while the DBWR process also has the possibility of self-contention. Assigning values to DBWR can effectively solve the self-contention problem of DBWR.

Generally, multiple DBWR processes are created for each instance in the operating system, and the number of DBWR is determined by the initial parameter DB_WRITERS. We recommend that you assign a value between n and 2n, where n refers to the number of disks. If the assignment cannot effectively solve the internal contention of DBWR, you can use asynchronous I/O to reduce the internal contention of DBWR. Because I/O processes are executed in parallel, asynchronous I/O only needs one DBWR to solve the problem.

(6) Table Data Storage Policy
Divide the data of a large table into several small parts and store these parts in different data files on different disks. Use create tablespace to create a tablespace and specify the data file in the datafile clause. Each file should be on a different disk. To store data files on three disks, use the following code:

 
 

  
  
create tablespace trip  
  
  
datafile 'file_on_disk_1' size 500k,  
  
  
'file_on_disk_2' size 500k,  
  
  
'file_on_disk_3' size 500k; 
 
 

(7) Specify the tablespace and table size
Use "create table" to create a table, specify the tablespace in the tablespace clause, and specify the table size in the storage clause, as shown in the following SQL statement:

 
 

  
  
create table striptab (  
  
  
……  
  
  
Tablespace strip 1500k  
  
  
Storage (initial 495k next 495k minextent 5 pctincreate 0); 
 
 

(8) Clear other disk I/O
The allocation of data block space because the data in the database is stored in the data block, how to allocate the space in the data block will directly affect its access performance.

When a row of data is updated using the update statement, the data of the row may be increased, so that it cannot be accommodated in a data block. In this case, Oracle will find the data block that can hold the next row. If such a block can be found, it will store all the rows in the new block (this is called row moving ). If you cannot find a data block that can hold the entire row, split the row into several slices and store each shard in one data block, therefore, such rows are stored in multiple data blocks. Such rows are called linked rows. Querying a link row requires multiple I/O operations, resulting in an I/O bottleneck, which reduces data access performance. The preceding section describes how to redo Oracle log files.

1. Spare time importing Oracle Data
2. Refined summary Oracle Performance Testing
3. Introduction to Oracle INDEX prompt
4. Five-minute master of Oracle Hints
5. Brief Introduction to Oracle driver table