Re: anyway to flush the db_buffer_cache?

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:Cu0ta.28220$1s1.416085_at_newsfeeds.bigpond.com...

> 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.
>

Hi Howard,

I disagree with you here.

When tuning SQL, you should focus on the logical I/Os and concentrate on reducing the buggers as much as possible. By doing so, you implicitly reduce the associated physical I/Os and so they shouldn't be a burning consideration.

Then (as mentioned by Connor) you eliminate anomalies due to physical I/Os in benchmark times by running comparative statements several times. (As mentioned by Sybrand) you want to test your code as you would expect the thing to run in the production environment, not as it would run after a database has just been bounced (at my current site, once in a blue moon)

I'm not too sure what environments you've looked after but if developers kept coming to me with requests to bounce dev/test/QA instances each time they want to retest a piece of code, guess what my reply would be:

1. Hello again, of course, here's cuppa while you wait
2. Hello again, sure no worries, listen to this great Bowie song while you wait
3. Hello again, yeah fine, but please join the queue as there are currently 18 other developers waiting for the some thing
4. #@*? OFF !! (with a subtle cough cough in the background)

Cheers

Richard Received on Mon May 05 2003 - 08:33:10 CDT