Re: anyway to flush the db_buffer_cache?

"Ryan" <rgaffuri_at_cox.net> wrote in message news:<B3_sa.16110$g41.1207421_at_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.

Don't you think if a lot of queries are htting the tables serially, that you _want_ the data in the buffers? Perhaps you would be better off tuning queries with explain plan and such, and ordering the batch queries so that queries likely to use the same data will be contiguous.

Flushing the buffers to see how long a query takes might hide the sql tuning problems behind physical I/O's. You want a repeatable test that reflects the conditions the batches will run in. You want the data to load into buffers without having to run an extra scan. Also, you should check out the LRU algorithm and decide whether you should simply let the queries flush out stuff that won't be used in the rest of the batch run.

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

Top Sessions under OEM Diagnostics (not to mention many other tools) can show you the explain plan with little work. Then just look in the docs where it explains the explain. Sometimes adding a temporary index for the batch can do wonders. Other times all you can do is spend lots of time rewriting the query. In extreme cases, I manipulate the data outside of the db - not something I recommend.

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

jg

--
@home.com is bogus.
Happy Drinko de Mayo!