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

I have to say, Sybrand, that this is not the normal definition of "logical I/O"< which is rather 'I go the buffer cache, and if my data is there, I perform logical I/O to read it, and if it isn't, I perform physical I/O to obtain the data". And in that respect, our Original Poster is perfectly correct in wanting to flush the buffer cache between test runs ... otherwise, his SQL statements will be giving run-times of .01 of a microsecond the second time they are run, but 10+ seconds the first time they are run, because the second time they are run, the data is already in the cache, and hence no physical I/O is required to produce the required results.

To the original poster: I bounce my instance between test runs to achieve the same result.

And, apropos nothing, testing code like this is absolutely fundamental to good performance tuning. And it doesn't mean you are after achieving a spurious buffer cache hit ratio, merely that you don't want the effects of caching to obscure the results of SQL statement tuning. A bad SQL statement is always bad, however much cache you throw at it. A good SQL statement is always good, even if it takes 0.1 second the first time and 0.001 second the second time it's run: the OP is quite right to want to eliminate the effects of cacheing from his testing.

Regards
HJR Received on Sat May 03 2003 - 23:28:05 CDT