Re: Database Buffer Cache Hit Ratio

"pcambraf" <pedro.cambra_at_gmail.com> wrote:
> My question was, exactly if its possible that dirty buffers stand in
> databuffer cache waiting to be written by the lonely and sad DBWR
> proccess and this could cause that really needed blocks cannot be
> pulled to memory causing a low ratio.

Most likely not. One cache miss is one cache miss, no matter how long it takes that miss to be resolved. If dirty blocks have the buffer tied in knots, then the cache miss will also cause a (IIRC) "free buffer wait", which is timed and thus does carry information about how long it takes to be resolved.

One of the bizarre[1] situations I alluded to is if a bunch of other connections also demand that same block.

If the first connection is in the process of reading that block into memory, the other processes will get "Buffer Busy Waits". I don't know if those other processes also log a cache miss or not, but I doubt it. If they do, that could lead to lower hit ratio.

If the first connection is trying to read the block into memory but it is in a "Free buffer wait", then I don't know what the other connections do. Is there a nexus where servers can declare their frustrated desire to read a block? If not, then all the other processes will probably also try to read the block, perhaps causing not only more cache misses, but also more "free buffer waits"

> I didnt ask you for a new
> profession o a reorientation of my professional career...

You seem to have confused this discussion group with a paid help desk. You post questions, we discuss them. If that discussions answers your question, so much the better. If not, tough nuggies.

Xho

[1] Bizarre because ordinarly if there is a "hot block" wanted by all connections, it would already be in the cache. However, I have seen some unfortunate code that created multiple independent connections that went through blocks in a fixed pattern across the connections, so 10 processes all want block 1239875 at ~ the same time, and then they all want 9834524 at ~ the same time. That caused substantial (but not problematic) "buffer busy waits", which of course the senior DBA, obviously from the Burleson rather than Lewis school, blamed on freelists.

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB