Re: How do u make Oracle forget data buffered from previous queries?

In article <36cogc$t18_at_potogold.rmii.com>, bromberg_at_hobbes (Rich Bromberg) wrote:
>
> I have gone with the "ugly" method of running queries which will retrieve
> enough blocks of non-benchmark data to completely flush the cache prior to
> running each benchmark. To my knowledge, (which is limited), there is no
> other way to clear the data buffer cache other than toggling the instance.
>
> I would be interesed in a more elegant solution if one exists.
>
>
> Hans Lindberg (d7hansl_at_dtek.chalmers.se) wrote:
> : Is there an easy way to flush everything buffered from previous queries?
> : I wan't to time a number of queries, and as the time varies depending on
> : what's been queried about earlier, I need to flush everything chached.
 

> : Hans Lindberg Tel: 031-183151 EMail d7hansl_at_dtek.chalmers.se
> : ---------------------------------------------------------------------------
> : / / / "-Fashion is a form of ugliness so intolerable
> : / / / that we have to alter it every six months."
> : /------/ /
> : / / / --Oscar Wilde
> : / / /.....
> : ---------------------------------------------------------------------------

No real clean solution for cleaning out the data blocks other then refilling the cache with other data blocks. Remember, prior to 7.1 a full table scan larger then the init.ora parameter SMALL_TABLE_THRESHOLD will reuse blocks on the cold end of the LRU instead of emptying the cache. Therefore you will have to use a large index access. (the functionality is the same with 7.1 but there are now hints CACHE and NOCACHE available along with additions to the CREATE TABLE command that allow the equivalent to SMALL_TABLE_THRESHOLD to be set per table)

The ALTER SYSTEM FLUSH SHARED_POOL command is used for the shared_pool (dd cache and shared sql) not the buffer cache.

Steve Hoffman - Contracting at Apple Computer Received on Thu Oct 06 1994 - 22:37:11 CET