Re: anyway to flush the db_buffer_cache?

Ryan wrote:
>
> "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> news:k718bv8lpaj0d8b0i6hp272pp8t1jtosfa_at_4ax.com...
> > On Sat, 03 May 2003 16:56:21 GMT, "Ryan" <rgaffuri_at_cox.net> wrote:
> >
> > >Im doing some tuning now and its hard to establish benchmarks if all the
> > >blocks are in memory. So for tuning purposes only Id like to flush them
> out
> > >to test different methods.
> > >
> > >Im not a good enough tuner to simply analyze a plan and no for certain
> what
> > >to do.
> > >
> >
> > It doesn't really matter. The amount of logical I/O will remain the
> > same whether the blocks are in memory or not. The goal of your tuning
> > should be to reduce the logical I/O, not to get the hit ratio up. If
> > that's your goal there more than enough tuning books around
> > recommending what to do. Try it! just issue set autotrace on explain
> > stats and run your query 2 times and see what happens.
> > If you really want to flush the buffer cache there are 2 methods
> > - run a full table scan on any table bigger than the buffer cache
> > - bounce the database
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
> Im not trying to get the hit-ratio up. I run a query. Blocks go to memory.
> Run it a second time and its faster. So I thought it might be a good idea,
> to try to repeat the situation where the blocks are not in memory. Easier to
> gage benchmarks.

A good way of benchmark (say) two possible alternatives is then to:

1. run alternative 1 to warm the cache
2. run it again and check results
3. run alternative 2 to warm the cache
4. run it again and check results

(b) and (d) are the ones you would then compare as these are probably the closest to real-world execution, ie some blocks in the cache, some not

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"