Re: anyway to flush the db_buffer_cache?

From: Ryan <rgaffuri_at_cox.net>
Date: Sun, 04 May 2003 01:31:13 GMT
Message-ID: <B3_sa.16110$g41.1207421@news1.east.cox.net>

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:sKYsa.8283$MJ5.7633_at_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?
They are only 2 million records. Im not at work now, so cant calculate the bytes.
>
> How big is your DB_BLOCK_BUFFERS (in MB)?
cant answer this now.

>

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

2-3 minutes.
>
> How long does the batch process typically take?
2 hours, but alot of that is due to really bad SQL. Much of it is being corrected. Its still going to take a good 35-40 minutes. Id like to get it lower. We have other batches to run.

>

> What makes you think that getting the data into memory
> is where the majority of the time delay occurs?
We have alot of queries of the form

select col1
from tab1
where tab1 = 'X'

lots of joins too between those tables. Nearly all of the queries involve 2 tables, but none of the queries do full table scans. So I wondered if I did a quick full table scan on them and loaded the blocks to memory, they queries may speed up.

>

> What will be your next guess when "pre-loading tables" does not
> measurably reduce run times?

Too much work to do. Ill have to look for more drastic measures. Someone mentioned fragmentation of the SGA. Ill look into that. Possibly look into stored outlines. Not sure.

btw, I have read the Milsap article on the Cache Hit ratios. Dont need advice on that. So I know that PIOs are not always horrible. > Received on Sat May 03 2003 - 20:31:13 CDT