Oracle Tuning instance parameters reduce I/O requests

In this method, there are two main ways to achieve the I/O optimization.

Oracle uses memory cache to reduce I/O

Through some memory cache, such as buffer cache, Log buffer, Sort area, you can reduce the database on I/O requests. When the buffer cache is increased to a certain size, most of the results can be obtained directly from the cache without having to read from the disk. In a sort operation, if the sort area is large enough, the temporary data generated during the sort process can be placed directly in memory without taking up temporary tablespace space.

Oracle adjusts the size of Multiblock I/O (mostly block I/O)

The parameter that controls mult iblock I/O is called Db_file_multiblock_read_co UNT, which controls the number of data blocks read at a time when most blocks are read. Increasing the size of this parameter appropriately can improve I/O efficiency of multiple block operations, such as full table scans.

For example, reading 100M of data is more efficient than reading 100 times a total of 1M reads 1000 times per read 100K. But when this number reaches a certain size, the increase will not do much: each 10M read 100 times to read the efficiency of 1G data and the efficiency of a single read 1G data is not very different. This is because I/O efficiency is affected by 2 factors: I/O build time and I/O transfer time.

I/O build time is the same for different I/O sizes, which determines the total I/O time for small I/O, and increases the mult iblock I/O size to reduce I/O build time;

I/O transfer time is proportional to I/o size, I/O transfer time is generally less than I/O build time in small I/O, but for large I/O operations, I/O transfer time determines total I/O time. Therefore, when the multiblock I/O size increases to a certain size, it has little impact on the total I/O time.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/