Thursday, 31 March 2016

Oracle Database Automatic Big Table Cache

Serial direct path reads were first introduced in Oracle 11g to prevent wiping out a large number of buffers from the buffer cache by a serial large table scan. As a result, big tables accessed via serial Full Table Scans bypass the Buffer Cache and read data from the Data Files into the users’ Program Global Area (PGA). This prevents flooding of the Buffer Cache, but subsequent SELECTs on big table always access the disk thereby degrading the performance.

If a big table is accessed repeatedly using serial FTS, and it is desirable to avoid repeated disk reads during subsequent serial full table scans, there are a few options according the version of your Oracle instance. Prior to, the data in big tables can be cached in buffer cache using two different methods: