How to Improve Oracle Performance

Guidance:In fact, this truth lies inOracle DatabaseIt also works. That is, key data files can be stored on multiple hard disks to improvePerformance. This article uses Oracle11G as an example to describe how to improve performance by distributing key data files between hard disks. I hope this will help you.

1. Basic principles for distributing key data files between hard disks.

Deploying an Oracle database on a traditional File System (not on bare metal, the database performance can be improved by distributing key data files to multiple available file systems or different hard disks. Specifically, the following principles must be followed.

First, a table usually contains two parts: basic table and index table. As long as an index is created for the fields in the basic table, there is an index table corresponding to it. When you access data in a table, the application system needs to access both the index table and data table. Now we can compare the two tables to two vehicles. If there is only one driveway (that is, they will be stored in one hard disk or file system at the same time), then the two cars must be both active and standby. If there are now two lanes (that is, the basic table and its corresponding index table are stored in different hard disks or file systems), then the two cars can be run side by side. Obviously, the latter is more efficient. For this reason, I suggest that you store frequently accessed tables separately from their corresponding index tables.

Second, log files can be stored separately. This is not just the case with data tables and index tables. This is also true in log file management. As long as conditions permit, it is best to store online redo logs and archive logs in Different Hard disks or file systems than other data files. Because when you write data to the database, you need to write data to both the data file and the redo log file. In this case, if they are stored separately, it is equivalent to having multiple lanes to write data to different files respectively. This will undoubtedly improve the efficiency of Data Writing and thus improve the performance of the database.

2. What files should be stored separately?

When talking about the basic principles for distributing key data files between hard disks, I have provided several cases for separate storage. However, in actual work, it is not limited to the files mentioned above. I believe that, if conditions permit, you can consider placing the following files on different hard disks.

1. tablespace, such as temporary tablespace, system tablespace, and UNDO tablespace. These three tablespaces may be accessed by the system at the same time. To do this, you need to store them separately. Second, data files and index files. As mentioned above, you need to store frequently accessed data files and their corresponding index files on different hard disks. These two types of files may also be accessed when accessing data. Third, the operating system disk and database files are stored separately. Obviously, the Oracle system must run at the same time as the operating system. To avoid I/Q conflicts between them, you need to deploy Oracle on a disk other than the operating system disk. 4. Online redo log files. This file is complex, not only to store it separately from other files. It is also worth noting that it is best to store it on the hard disk with the best performance.

The last thing to note is that increasing the disk will also increase the cost. This is not just the cost of buying a disk, but also the cost of management. Therefore, there will also be a balance between cost and performance. If the enterprise does not have a lot of data or involves query operations, this design may not be reasonable. Because the input must be greater than the return value.

3. How do I determine whether to separate and store files?

In actual work, enterprise data is a process from few to many. That is to say, when the database is used at the beginning, the data volume may be relatively small. At this time, the related files are not stored on different disks for cost consideration. However, with the deepening of work, users will find that the database performance is gradually decreasing. In this case, the administrator needs to consider how to improve the database performance by taking such multi-lane building measures. Of course, before taking this measure, the administrator needs to evaluate the advanced nature. In this case, one metric required for evaluation is the I/O contention of the disk.

Disk contention usually occurs when multiple processes attempt to access a physical disk at the same time. If you need to access the data in a data table, the system needs to access the index file and data table file. If they are placed on the same disk, an I/O conflict occurs during access. Therefore, evaluating the severity of I/O conflicts can help us determine whether key files need to be stored on different disks.

Evenly distributes I/O to multiple available disks, which can effectively reduce contention between disks and improve data storage and read performance. This improves the efficiency of Oracle and other applications. In practice, there are two parameters in the Database Control file to help us evaluate this indicator. The two parameters are average file read time and average file write time. However, when using these two parameters, only the files associated with the database are evaluated. If necessary, the administrator can use the following query statement to check whether there is an I/O problem in the data file. Shows the query syntax and result:

From the above query results, we can see whether a data file is busy and whether there are/I/O conflict files between data files. Note that this result is a dynamic result. Different conclusions are drawn at different times and when users perform different operations. For this reason, I suggest that you use this data for several more times. Then, analyze the running results. Only in this way can we get reasonable judgments.

Generally, the administrator can draw three conclusions based on the above results.

The first conclusion is that these data files are not very busy. That is, the average read time and write time of the files are relatively short, indicating that the two files are relatively idle. Under normal circumstances, the database performance should be good. That is to say, if the database performance is not satisfactory at this time, it is not caused by disk I/O. The Administrator should improve the database performance from other perspectives.

The second conclusion is that every database file is very busy. In this case, the read time or write time may be relatively long, or both are relatively long. When multiple data files are busy at the same time and they are on the same disk, the administrator needs to consider buying a new disk and then reorganize the key files mentioned above, deploy them on different disks.

The third conclusion is that a specific data file is busy, while other data files can. In this case, the Administrator does not need to purchase a new hard disk if the cost is limited. If there is a big difference in the number of physical writes and reads on the disk, it indicates that the load on a disk is too large, that is, there is a very serious I/O conflict. At this time, it is best to adjust the files in this disk, such as moving some files to another disk with a relatively low I/O severity. However, pay attention to this operation. For online redo log files, even if the disk I/O conflicts are low or the file access time is short, however, it is not recommended to transfer other data files to the disk where they are located. Generally, to ensure database performance, we recommend that you store online redo log files separately and place them on hard disks with high performance.

In short, key Oracle database files are placed separately. This effectively prevents disk contention from becoming the performance bottleneck of the Oracle database system. The method described above can improve the performance of Oracle.

How to adjust Oracle Performance

Misunderstanding of Oracle performance Adjustment

Description of Oracle performance adjustment Solution

Oracle performance adjustment and recovery of tablespace fragments