Direct Path Read and Full Table Scan

 Before reading this article, you might want to read this article about poor buffer cache hit ratio because of full table scans. In this article I will explain a feature of Oracle introduced after 10 to avoid flooding of database buffer cache because of full table scans. Database block is the unit of IO in Oracle, which means that whenever some data is read from the disks, one copy of data block is put in the buffer cache so that this data can be reused later to avoid reading same block from the disk in future. After that, the copy of data is used for user’s SQL. 

Starting 11g, Oracle introduced a new wait event called Direct Path Read. This wait event occurs when a full table scan happens on big table. What a “big table” is, is decided by oracle itself based on different factors including size of buffer pool where data blocks are stored in the memory. Oracle would try not to flood the buffer pool and would read table from the disk and send directly to the PGA instead of first putting a copy of data blocks in buffer cache, thus not interfering the cache hit ratio. Without direct path read, oracle would always put a copy of physically read blocks in the buffer cache for future use. Direct path reads are for better performance of the database, but in some cases you might see performance degradation for the tables that are frequently full table scanned. Since data was not stored in buffer cache during previous access, every time a physical read is performed. Following image shows the directy path read wait event at the top indicating most of the queries doing full table scans, yet buffer cache hit ratio is above 90 percent.

Data fetching may be slow in case of direct path reads as data is not taken from buffer cache, and every time physical reads are performed. If you want to disable direct path read and utilize buffere cache for the tables that are frequently scanned fully and oracle is using direct path read is in use, you can set following event to disable this.

alter system set events '10949 trace name context forever';

To again enable this, use following command.

alter system set events '10949 trace name context off';

 Ofcourse the best approach is to tune the SQLs and create indexes to avoid FULL TABLE SCANs