Direct Path Reads Solution Explained:
So, how do you solve the “ direct path reads ” problem (where the same data is being constantly re-read from disk)? There are a couple of suggestions for dealing with direct path reads in Oracle Database 11gR2.
- Put the table in question in the KEEP buffer cache. This is a two-step process. First allocate memory for the KEEP cache by setting the initialization parameter DB_KEEP_CACHE_SIZE. The second step is to assign the table to the KEEP cache. ALTER TABLE
STORAGE (BUFFER_POOL KEEP);
- The second suggestion is to set the parameter _small_table_threshold. This parameter sets the threshold to where the table is cached or not cached. If a table exceeds _small_table_threshold a direct path read is done. By default the _small_table_threshold is set to 2% of the buffer cache.
I recommend that the first solution to this problem is to make sure that your system has sufficient memory for buffer cache. If AMM (Automatic Memory Management) or ASMM (Automatic Shared Memory Management) is used, make sure to set the floor for the buffer cache by setting db_cache_size.
You can experiment by setting event 10949 at the session level. If this provides the solution you are looking for, you can see if it helps or hurts at the system level. Never make changes to your system without testing thoroughly.