buffer is pinned count

[source]  조동욱님 영문 블로그 

                http://dioncho.wordpress.com/2009/04/08/buffer-pinning/

In case of consecutive access on the same buffer within the same fetch call, Oracle pins the buffer so as to visit the buffer without acquiring the cache buffers chains latch and searching the cache buffers chains. The duration of pin is fetch-call scope, so we have a decreased overhead without the pin contention.

Also, pinning a buffer additionally means estabilishing pointers between session’s db buffer handles and pinned buffer headers. Oracle caches the db buffer handles in SGA (separate handles for each process) and doesn’t close these (nor unpins buffers) immediately after block visit. So, Oracle caches db handles for a call and closes them when a call finishes or the number of already cached handles exceeds a threshold (controlled by _db_handles_cached parameter, 5 by default)

So, the “buffer is already pinned count” statistic

@Tanel

I was about to add comment on my comments about latch acquisition. As test case cleary shows, the latch acquisition count is almost 2 times of logical reads. This means that Oracle does not release and reacquire the CBCL, but upgrades it. Your explanation clearly proves my assumption.

Really thanks for the awesome explanation!

Session diff makes me realize that this number comes from the diffference of buffer is pinned count

-- index T1_N1(good clustering factor)
NAME                                             DIFF
---------------------------------------- ------------
buffer is pinned count                   19,835
table fetch by rowid                           10,000
session logical reads                             219
consistent gets from cache                        199
consistent gets                                   199
buffer is not pinned count                        165

@Roberto: it’s easier to understand if you read this as “buffer is already pinned”:
- you pin the buffer on first access
- second access (for “good” case) is to same block so no need to pin again (and this counter gets incremented).
- When your index scan finally requires you to fetch a row from another block (and/or to move on to the next index block?), the data block is finally unpinned;
- on reading the next data block, the logical read count is incremented.

@Lscheng

Read operation acquires buffer pin in shared mode. So concurrent reads do not block each other.

Read operation(shared mode) and update operation(exclusive mode) can block each other.

But the blocking is normally not a problem. The duration of buffer pinning in read operation is

“fetch on the same block”, so the blocking time is very short and it would not make a noticeable contention.

Cocurrent read on the same block can be blocked by “cache buffers chains latch(CBCL)”. The corresponding wait event is “latch: cache buffers chains”.

CBCL is sharedable for read operation, but at the very moment of acquiring buffer pin in shared mode to read the buffer Oracle needs to acquire CBCL in exclusive mode. That’s why we are blocked even with the cocurrent read.

Yes, it’s always confusing at first.

CBCL *was* exclusive for read operation at the ancient versions(8 maybe). But in recent versions, as for the read operation, CBCL is acquired in shared mode. This was a big improvement.

This means that (as you exactly pointed out) multiple sessions can walk through the cache buffers chains in the same time with CBCL held in the shared mode.

But this does not mean that we are never blocked in the read operation. It is just walking through the chains which is shared.

Just after the session finds the buffer what it is supposed to read, it needs to acquire buffer pin. The exact process is as following.

1. Acquire CBCL in the shared mode.
2. Walk through the chain and find the buffer to read.
3. Release CBCL.
4. Acquire CBCL in the exclusive mode.
5. Acquire buffer pin for the buffer – shared mode for SELECT, exclusive mode for DML.
6. Release CBCL.
7. Read the buffer.
8. Acquire CBCL in the exclusive mode.
9. Release buffer pin.
10. Release CBCL.
11. Logical Reads done!

Buffer pin itself is protected by CBCL. This is why we still suffer from the CBCL contention even for the concurrent reads.

The contention got alleviated by the improvement(CBCL in shared mode), but we still see heavy CBCL contention in the highly concurrent systems.

This is why Oracle 11g introduced the concept of result cache.

PS) With buffer pinning activated, we don’t need to acquire latch and walk through the chain. As the buffer is pinned(buffer pin is not released) and the buffer is guaranteed not to be aged out, we are safe to read the buffer directly. This is the merit of buffer pinning.

@Lscheng

The step got modified as following.

1. Acquire CBCL in the shared mode.
2. Walk through the chain and find the buffer to read.
3. Release CBCL.
4. Acquire CBCL in the exclusive mode.
5. Acquire buffer pin for the buffer - shared mode for SELECT, exclusive mode for DML.
6. Release CBCL.
7. Read the buffer.
8. Acquire CBCL in the exclusive mode.
9. Release buffer pin.
10. Release CBCL.
11. Logical Reads done!

The point here is that buffer pin itself is a shared memory object which should be protected by latch. CBCL is used to protect buffer pin.

So to acquire buffer pin, 1) acquire CBCL in exclusive mode 2) acquire buffer pin 3) release CBCL.

And to release buffer pin, 1) acquire CBCL in exclusive mode 2) release buffer pin 3) release CBCL.

And this is why concurrent reads are still blocked by CBCL contention. At the short period during buffer read, we still need to acquire CBCL in exclusive mode.