Re: anyway to flush the db_buffer_cache?

On Sun, 04 May 2003 13:26:16 GMT, "Ryan" <rgaffuri_at_cox.net> wrote:

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

The behavior of your (desupported!!!!!!!) version of Solaris is determined by the parameter maxphys, which can be set in /etc/system. You only need to reboot to have it altered. On ufs file systems the default maxphys is 128, so this matches with your db_file_multiblock_read_count of 16. If you have a maxphys of 128k, increasing db_file_multiblock_read_count doesn't make sense. The maximum maxphys on ufs is 1 M.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun May 04 2003 - 14:51:19 CDT