Re: anyway to flush the db_buffer_cache?

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 03 May 2003 17:00:24 -0700
Message-ID: <sKYsa.8283$MJ5.7633@fed1read03>

Ryan wrote:
> "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.
>
>

How big (in MB) are each of the tables?

How big is your DB_BLOCK_BUFFERS (in MB)?

How long does it take to do a single Full Table Sacn for both tables?

How long does the batch process typically take?

What makes you think that getting the data into memory is where the majority of the time delay occurs?

What will be your next guess when "pre-loading tables" does not measurably reduce run times? Received on Sat May 03 2003 - 19:00:24 CDT