Re: anyway to flush the db_buffer_cache?

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message news:60d8bv4pl5kmaebso0911q9a78rhpicdrh_at_4ax.com...
> On Sat, 03 May 2003 18:35:24 GMT, "Ryan" <rgaffuri_at_cox.net> 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.
> >
>
> You have a very weird definition of benchmark and I doubt whether you
> actually read and understood what I said. The number of logical I/O
> requests will be identical, whether the data is in cache or not. If
> the data is not cache a physical I/O request will result from the
> logical I/O request. You aren't tuning statements, you are tuning the
> buffer cache, so YES, you ARE indeed tuning HIT RATIOS
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

I did read your post. However, I wasnt clear in my response. By benchmark I simply meant response time. Wasnt looking at PIOs or LIOs or anything else.

This is a nightly batch process, so the blocks will not be in memory when I run them. I want them to run as fast as possible one time. I think pre-loading the tables into memory with a cursor is the best way to go. Since I have a variety of queries to make on these tables.

thanks. Received on Sat May 03 2003 - 18:30:01 CDT