Re: data block cache algorithm and cache hit or not

"mako" <makoml_at_jcom.hone.ne.jp> wrote in message news:d7Hs7.1817$kn.217964_at_news1.rdc1.ky.home.ne.jp...
> Thank you Lawrence,
> Your reply is very helpful, and I understand at last.
>
> The Conclusion I understand is ...
> When DML was given, foreground processe will cache result sets into
buffer,
> and second time query will have a higher hit ratio if these result was not
> aged.
> If result sets are large, these results will placed in LRU and will be
aged
> soon.

The LRU is not some substitute for the Buffer Cache, merely a tool to determine which bits of the Buffer Cache are capable of being aged out to disk.

*ALL* SQL statements, whether select queries or DML, cause data blocks to be read into the Buffer Cache. If you update one person's salary, the *ENTIRE* block in which that record is located is placed into the Buffer Cache. That will help speed up someone's subsequent *select* for a completely different person's telephone number (provided that their row is also within that block).

The LRU list, as its name implies, simply tells us which of the thousands of buffers in use are the Least Recently Used. If they haven't been used (by anyone) for a long time, and we need the space for something more useful, then such buffers are (by definition) at the Least Recently Used end of the LRU list, and are candidates for being flushed. A popular block that is continually being touched (by either Selects or DML) will be at the Most Recently Used end of the LRU list, and will tend not to be flushed out.

Regards
HJR
>
>
> > No need to apologise, your English is very good... my Japanese is
> > non-existent save for a few Karate terms.
>
> Great!
>
> Mako
>
>
Received on Fri Sep 28 2001 - 20:10:55 CDT