Re: Buffer cache is not LRU?!

"Bob Jones" <email_at_me.not> wrote in message news:<_HAdc.2559$i74.49230_at_bgtnsc04-news.ops.worldnet.att.net>...
> "Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message
> news:4076B222.5772E8BF_at_remove_spam.peasland.com...
> > How the buffer cache is managed with respect to full table scans has
> > changed, depending on which version of Oracle you are dealing with.
> >
> > At one time, the buffer cache was a strict LRU algorithm. Unfortunately,
> > when a large table was scanned (FTS), then every block of that table was
> > placed on the MRU end of the chain. This could have detrimental affects
> > to the other blocks that were needed more frequently than one large
> > table scan.
> >
> > So the algorithm was changed. IIRC, there as an alogorithm where a FTS
> > would place the buffers at the end of the LRU list. Not at the MRU end
> > of the list. This way, more frequently used blocks wouldn't get aged out
> > as fast just because a FTS was performed. I can't recall which version
> > this was for....
> >
> > In Oracle 8i, the LRU algorithm for FTS was modified. This algorithm
> > uses touch counts and blocks for a FTS are put on the LRU list below
> > "hot" buffers, or those where the touch count is two or more. This way,
> > a FTS one time on a table won't interfere with other blocks that are
> > more frequently used. For more information on this algorithm, refer to
> > the following:
> >
> > http://www.ixora.com.au/newsletter/2000_10.htm#cache
> >
> > By the way, the above web site is a great place to go to learn more
> > about Oracle internals.
> >
> > HTH,
> > Brian
> >
>
> It just make you wonder if Oracle is really making DBAs life easier after
> every release. Are we going to see self-tuning in Oracle 20?
>
> 512M of RAM -> $200
> Training -> $3000
> Oracle consultant -> priceless!
>
> To me, the price of RAM sounds a lot better than tuning.

That would be true if simply adding RAM would help, but since Oracle has to make the RAM match the disk, at some point you spend more time matching and making sure what is in RAM is consistent than doing stuff. Tuning lets you minimize that time, and so minimize the RAM.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=223724.1 , http://otn.oracle.com/products/manageability/database/pdf/ServerManageability92TWP.PDF , http://otn.oracle.com/pub/articles/10gdba/index.html among others.

jg

--
@home.com is bogus.
Self-tuning traffic lights: 
http://sfgate.com/cgi-bin/article.cgi?f=/chronicle/archive/2004/04/07/MNG8N61MGG1.DTL