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.

What is the most likely scenario in a production database that has been up for days to months? That the blocks are in memory or not? If they are ... flushing them out gives you bogus results. If they are not then consider tuning memory so that they are.

The only point-in-time where you should assume no blocks are in memory is when doing a cold start-up.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)