Dear Expers,
I would like to ask the most anticipated question for all times!!!
What exactly happens when a buffer get pinned? Here is a article that I found explain the buffer pinning very good by Dion Cho (Buffer Pinning | Dion Cho - Oracle Performance Storyteller).
"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."
He explains the steps of buffer pinning like below in the comment part.
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.
Tanel Poder also replied to Dion Cho as follows,
Hi,
A little correction. In step 3 above (in your reply to LSCheng) the CBC latch is not released and retaken in exclusive mode, but upgraded to exclusive mode from shared. This is what the “shared hash latch upgrades” statistics show in v$sesstat.
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
So, please someone tell me what happens when Oracle pin the buffer? What does Tanel mean when he write "db buffer handle"? Why Oracle have to get latch when unpin the buffer? I mean, logically can't it unpin without latch? I am seriouslly wondering very much. So, please someone throw some light on it. I try to find on the internet but there is not much information about it.
I also want to find out when concurrent READ block each other? in latch or pin? Because, the following comment make me confused.
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.
Thanks a million.
Regards
Charlie