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

Id like to improve the performance of my PIOs. Unfortunately Im locked into 8k block sizes. We are on Oracle 8.1.7.3 with 64 bit option on Solaris v5. We have a DB_MULTIBLOCK_READ_COUNT of 16. I believe that is the default. Would increasing this improve PIO performance? I dont know enough to analyze what to increase it to. Any suggestions?

Anything else I can check to improve PIO performance? I know the 8k block size is a real bottleneck, but we have multiple instances on remote servers and we perform nightly tablespace transportation, so its just not practical to rebuild the database with a larger block size. Received on Sun May 04 2003 - 08:26:16 CDT