Oracle Database Performance Optimization-IO reduction

Oracle Database Performance Optimization-factors that affect oracle performance can be divided into hardware and software. This article analyzes the oracle database architecture, in terms of hardware, An OLTP system is proposed to optimize the performance of oracle databases. If you have any shortcomings, please click here. Oracle Database physical storage Architecture:

Oracle Database physical file growth:

Oracle archived REDO log files are growing fast, indicating that redo logs are frequently written. From the actual performance tests of large data volumes and high-performance OLTP application systems, it is found that magnetic array IO is a bottleneck that limits the system performance,
Therefore, reducing I/O is the key to optimizing oracle databases. www.2cto.com combines the physical storage Architecture diagram of oracle databases, the growth of physical database files, and actual test findings,
The redo log file, data file, and index file read/write bring a great deal of load to the magnetic array IO, in addition, when the amount of data, performance, and continuous pressure reach a certain level, the IO load generated by these three is equivalent,
Therefore, the ultimate goal of reducing disk I/O is to reduce the I/O of these three types of files. The test results show that the oracle redo log files, data files, and index files are all created on the memory disk,
Using this almost zero IO mode greatly improves the performance of oracle,
Based on the above analysis, the oracle database IO can be optimized as follows: 1. SSD stores redo logs to improve the write speed of redo logs. From the perspective of actual use, due to the volatility of memory disks, creating a redo log file on a memory disk is insecure,
The redo log file is stable in size, generally between 2 and 3 GB. Therefore, the redo log file is created
SSD is a good choice for www.2cto.com 2 because the memory is large enough, so the temporary tablespace is built on the memory disk, for statistics, large data volume queries, large data volume
SQL operations (such as group by and sum) require a large number of temporary tablespaces to store temporary data. These temporary data is directly stored in the memory, eliminating oracle
Large read and big computation results in temporary IO3 because the index can be rebuilt based on the data file, not afraid of loss, the index file is built on the temporary file system, the oracle re-index at each startup, in this way, normal read/write operations will not
Index IO is generated. Index Update and reading are performed in the memory. When the system restarts for various reasons, this article is based on the optimization made when the system memory is very large (usually dozens or more GB), based on the above optimization, remove the three major factors that affect oracle I/O, so the performance is expected to multiply. Author's mind