The Oracle (tm) Users' Co-Operative FAQ
Is is possible to flush the db_block_buffer for testing purposes
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 28th March 2002 Oracle version(s): 8.1.7 |
| It is well-known that you can use alter system flush shared_pool; to clear the library cache of recently execute SQL etc. But is there an equivalent option for clearing the db_block_buffer in order to test the effects of changing execution paths without getting accidental benefits from buffered data ?. |
There is no matching command, however there is a little trick which may be adequate.
alter tablespace XXX offline; alter tablespace XXX online;
When you alter the tablespace offline, any blocks which are in the buffer are invalidated, and therefore subject to rapid elimination as the buffer is re-used. In fact, even when you bring the tablespace back online, even if some blocks are still apparently buffered, they cannot be reused.
Bear in mind, though, that if you are running Oracle on a file-system, and not using direct I/O as the O/S level, then the blocks may still be buffered in the file-system buffer; so your tests may still suffer from some spurious buffering benefit; especially if your code is accessing some smallish tables through tablescans. Remember that a small table is one that is no more that 2% of the size of the db_block_buffer, it is NOT, as is commonly believed, one that is only four blocks or less..
Further reading: N/A
