In this episode of Ask Tom TV, we will explore the concept of Direct Path Read in Oracle databases. Direct Path Read is a feature that allows the database to read data directly from disk and pass it directly to the process for processing, bypassing the buffer cache. This is particularly useful when dealing with large amounts of data where placing it in the buffer cache may not be efficient.
Typically, when performing queries in Oracle, data is read from disk and stored in the buffer cache, which is then accessed by the program or PGA for any further operations. This allows other sessions to benefit from not having to read the same information from disk repeatedly. However, when dealing with massive tables and reading numerous blocks of data, it is not practical to put all that information into the buffer cache. The chances of another session needing all those blocks for reuse are low, and populating the buffer cache with such large amounts of data can consume significant CPU resources.
This is where Direct Path Read comes into play. With Direct Path Read, the database bypasses the buffer cache entirely and reads the data straight from disk, passing it directly to the process for processing. This avoids the need to populate the buffer cache with large amounts of data that may not be reused by other sessions. However, a common question arises – what happens if there are already changes made to the data in the buffer cache? How does Direct Path Read ensure consistency?
Oracle addresses this concern through a mechanism called “object checkpointing.” When a Direct Path Read is initiated, the database performs an object checkpoint, flushing the buffers for that object back to disk, ensuring that any committed changes are saved before the direct read is performed. This guarantees that consistent results are obtained even when there are modifications in the buffer cache.
But what if there are uncommitted changes present in the buffer cache? Let’s say a row from a huge table has been deleted and the information is stored in a modified block in the buffer cache. In this case, Oracle still flushes that modified block to disk using an object checkpoint before performing the direct read. Additionally, the direct path read process will apply undo operations in the normal fashion to ensure a consistent view of the data.
To demonstrate this concept, let’s take a look at a simple example. We have two database sessions running on Oracle version 12.2.2. Session 1 updates 1000 rows in a table called “tea” by setting the owner to the lowercase of the current owner. These changes are then committed and stored in the buffer cache. Session 2 is set up to perform a direct read test using an underscore parameter, which forces every read to be a direct read.
We perform a query on the “tea” table in session 2 to obtain the maximum “created” value. To ensure that an object checkpoint has been performed, we check the session events and confirm that a fast object checkpoint has occurred. Since the changes were committed, we expect to see no undo operations applied.
Next, in session 1, we update another 5000 rows in the “tea” table by setting the owner to the uppercase of the current owner. However, this time, we do not commit these changes. Now we have uncommitted changes in the buffer cache. We rerun the select query in session 2 and check the session events again. This time, the object checkpoint count increases from 1 to 2, indicating that another object checkpoint has been performed to ensure consistency. Additionally, we see that block undo records have been applied to handle the uncommitted changes.
In conclusion, Direct Path Read in Oracle guarantees consistent results by utilizing object checkpointing and undo operations when necessary. It allows for efficient processing of large amounts of data without populating the buffer cache unnecessarily. By bypassing the buffer cache, the database can optimize resource utilization and improve overall performance. With the understanding of Direct Path Read, Oracle users can make informed decisions on when and how to leverage this feature for their specific database needs.
Thank you for watching this episode of Ask Tom TV. We hope you found this information helpful. Stay tuned for more informative content in the future.