How to bypass buffer cache for full table scans.
How to reduce impact of full table scans:Full table scans (FTS) have their place, even on OLTP databases, and they are not evil when used properly. FTS are unavoidable on most DW databases. So apart from popular belief of possible increased response time and increased I/O, is there any impact on the database?
FTS on a large table can effectively pollute the buffer cache by aging out blocks in the buffer cache otherwise needed by other sessions, even though the FTS blocks are placed at LRU end. It will also have increased latch activity (cache buffer chains, cache buffer lru, etc.). It can also create many CR cloned buffers, if there are any blocks for that table already in the cache.
All these problems can be solved by setting one hidden parameter "_serial_direct_read = true" for that session or at the system level. The beauty of this parameter is that once execution plan is generated for any sql with this setting on, same behavior will be exhibited even when it is not set as long as the cursor is not re-parsed.
"_serial_direct_read = true" basically use "direct path reads" for single threaded multi block read operations like FTS. Oracle will first issue fast checkpoint at object level to write all dirty buffers of that object to the disk and perform direct path reads in to the PGA of shadow process.
This will be especially useful for batch jobs.
Oracle introduced event 10379 to do the same for rowid range scans but never really implemented it fully. Of course you can always use parallel query option to achieve the same thing with multiple threads, but you will be consuming additional resources on the system.
00:50:19 SQL> alter session set "_serial_direct_read" = true;
Session altered.
00:50:19 SQL> select avg(id) from test;
00:50:32 SQL> select event, total_waits from v$session_event
00:50:40 2 where sid = (select sid from v$mystat where rownum = 1)
00:50:40 3 and event like '%read%';
EVENT TOTAL_WAITS
------------------------------ -----------
direct path read 124
Labels: buffer cache _serial_direct_read direct path read full table scan parallel
posted by Saibabu Devabhaktuni @ 12:15 AM
8 comments
