Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Why Statistics doesn't match reponse time?

Re: Why Statistics doesn't match reponse time?

From: Jonathan Lewis <>
Date: Mon, 12 Sep 2005 19:18:56 +0000 (UTC)
Message-ID: <dg4kb0$lvt$>

<> wrote in message
> Jonathan Lewis wrote:
> So oracle is pinning the index block for the entire duration
> of the query. My question is if this block is pinned for
> entire duration i.e. about 3 minutes then will the other queries
> trying to access the same block would wait on buffer busy waits
> through out the entire duration ?

A buffer can be pinned in share mode or exclusive mode. For the duration of this query, this buffer would be pinned in share mode, and another session could perfectly reasonably attach itself to the "pin list" in share mode.

If another session wanted to update this buffer, it would clone it, pin the clone exclusive, mark the clone as the current buffer, and (if necessary) change the original from CURrent to ConsistentRead - it's just a flag on the buffer header. This would (probably) appear as statistic: "switch current to new buffer". There are lots of little details I have missed, and some variations in activity, but that probably gives you an idea of how pinning can be non-contentious.

One case where pinning blocks activity is when the buffer reaches the end of the LRU chain and is a candidate for clearing - pinned buffers cannot be kicked out of the buffer pool, even if their touch count is only 1. Similarly, if a dirty CUR buffer is pinned when DBWR wants to write it, there are some silly games to deal with that situation.

Buffer busy waits (of the change/change type) appear when one session pins exclusive and another wants to pin exclusive - the second session attaches to the 'waiters' pin-list and waits for the buffer to become available. But buffers are usually only pinned exclusive for very short periods.


Jonathan Lewis
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated 4th Sept 2005
Received on Mon Sep 12 2005 - 14:18:56 CDT

Original text of this message