Re: Why Statistics doesn't match reponse time?

<poddar007_at_gmail.com> wrote in message
news:1126551618.890389.246790_at_g44g2000cwa.googlegroups.com...
> 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.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005