Oracle Physical I/O – Introduction

When you want to retrieve data from the database, the fastest way to do this is if the data the query returns is already located in memory, or cache. If it is not located in cache, then the server process needs to perform physical I/O to retrieve the data from disk. Physical I/O is much slower than a memory read so, we need to reduce the amount of physical I/O the database does. Luckily, Oracle is a very flexible database and there are many things we can do to reduce the amount of physical I/O performed by the database.

However, before looking at reducing the amount of physical I/O the database does, we need to clarify what exactly a physical I/O is. In Oracle terms, the distinction between a physical I/O (one that results in a miss on the database buffer cache and therefore a read from disk) and a logical I/O (where data is already located in the database buffer cache) is fairly straight forward. However, a physical I/O in Oracle terms does not necessarily result in a physical read from the underlying disk.

At the operating system level, a database that is built on top of file systems (rather than raw devices) will often find that its disk I/O needs can be met by information that is already stored in file system caches. On the other hand, a single database I/O request may require more than one disk I/O requests. This could be the case, for example, when there is a mismatch in the block sizes between the operating system and the database. In addition (depending on the operating system), there may be support for asynchronous I/O. Some operating system kernels provide specific support for asynchronous I/O (this is known as kernelized asynchronous I/O) for raw devices and Quick I/O files, while others implement a threaded form of asynchronous I/O using the kernel's ability to perform multiple synchronous I/O requests in separate threads.

At the storage layer, modern technology has also introduced a number of mechanisms to optimize disk I/O. These can include satisfying a request for I/O without performing actual physical disk I/O by using a hardware cache, or by using read ahead technology which allows a process to perform additional I/O in the expectation that later requests will need information that is stored in adjacent blocks on disk.

As a result of all these I/O optimization mechanisms, it is just about impossible to determine whether the physical I/O statistics you can retrieve from the Oracle database in views such as V$FILESTAT actually match real physical disk I/O counts. However, whether the physical I/O statistics in the database are completely accurate or not, it is still important to understand the different mechanisms that are available to database administrators to minimize physical I/O.