Re: Flushing data buffer

To flush the SGA of data blocks:

Create a table that is as large as the SGA Create an index on the table
Issue:
  select /*+ rule */
  count(*) from table
   where index_column > chr(0)
   and last_column_in_table = '{impossible value}'

The answer comes back as 0, but in the process any previously buffered data has been kicked out to make way for the index and table blocks.

This won't necessarily help you in your timing problem, however, unless you are running on raw disk, and don't have battery-backed buffering on your disk devices, as the original data you are testing against could either be in the file-system buffer, or in the device hard-buffer.

---
Jonathan Lewis
ora_mail_at_jlcomp.demon.co.uk