Cache buffer chain CBC latch simple query to investigate
For deep understanding on CBC,please refer to below links:-
Video tutorial from ORAPUB.
I am just going to mention some useful queries to investigate or identify CBC latch
Step 1:-You as user fire a sql query from client.
select * from cbctest;
Step 2:-Now oracle will search metadata and determine what is your file_id and block_id to locate the block to be fetched from buffer cache or physical disk.
Step 3:-Let me for example choose file_id = 6 and block_id=2288143.Now oracle will determine DBA value from it.
SQL> select dbms_utility.make_data_block_address(6,2288143) from dual;
Step 4:-If there are 3 chains,oracle will use module function that returns the reminder from an input after dividing it by 3:
SQL> select mod(27453967,3) from dual;
So this block will be put into chain #1.
Simulation of CBC test:-
From one session,run following:-
for i in 1..1000000 loop
select count(1) into aa from cbc ;
From another session ,run following:-
for i in 1..1000000 loop
update CBC set c1='MAITY';
From oratop,I can see “Latch: cache buffers chains” now.
1.Oracle once determine file_id and block_id,create a hash value first.
2.After determining hash value,oracle will acquire latch to hash into hash bucket and move across to chain to ask block header whether the block is already in memory or it needs to be fetched from disk.
When the block comes to the buffer cache, Oracle applies a hash function to determine the buffer chain number and places the block in a buffer in that chain alone. Similarly, while looking up a specific buffer, Oracle applies the same hash function to the DBA, instantly knows the chain the buffer will be found and walks that specific buffer only.This makes accessing a buffer much easier compared to searching the entire cache.
Why CBC cache buffer chain occurs
a)When different session try to acquire latch (Your buffer is very popular) chain #1.It needs to spin and sleep several times.
b)When different session try to acquire latch chain #1 and chain #2.It needs to spin and sleep several times.
Remember,latch is exclusive and if one session is trying to acquire latch,other session will be in queue.
When different session try to acquire latch chain #1 and chain #4.There will be no contention as they are in different latch .
Now how we can identify CBC problem
Step 5:-Let us find the latch number of “cache buffers chains”
SQL> select latch# from v$latch where name = 'cache buffers chains';
If you check the values of the two hidden parameters explained earlier, you will see:
Step 7:-Now check longest sleep and gets by executing following query 1 minutes gap and check whether sleep and gets are increasing.
select * from v$latch_children where latch#=228 order by sleeps desc;
Step 8:-You can also identify the buffer causing cache buffer chain wait for session you are experiencing CBC problem.Let us say session# 208 is experiencing CBC contention.
select p1, p1raw, p1text
from v$session where sid = 208;
Here please take a note of p1raw which is hash address.
Step 8:-Please verify gets,misses and sleeps from v$latch_children
select gets, misses, sleeps, name
from v$latch where addr = '000000014AFC7A70';
Step 9:-Also determine touch count of the corresponding block .
select dbarfil, dbablk, tch
where hladdr = '000000014AFC7A70';
Identify overall problem on CBC
Step 10:-You can also check from v$session_wait or v$active_session_history
select p1raw,count(*) from v$session_wait where event like '%cache%buffers%' group by event, p1raw order by 2 desc
select p1, count(*)
where sample_time < sysdate – 1/24
and event = 'latch: cache buffers chain'
group by event, p1
order by 3 desc
Step 11:-The easiest way is to dump the block and get the object ID from the dump file. Here is how you dump the above mentioned block.
alter system dump datafile 6 block min 220 block max 220;
or we can get block_id of corresponding address.
select hladdr, file#, dbablk, decode(state,1,'cur ',3,'CR',state) ST, tch
from x$bh where hladdr in
(select addr from (select addr from v$latch_children where addr='000000B9CA3336A0'
order by sleeps, misses,immediate_misses desc )where rownum <2)
Step 12:-Now we can identify the segment_name which may be table or index using below query.
where file_id = 48
and 94182 between block_id and block_id + blocks - 1
and rownum = 1
a)CBC latch waits are caused by popularity of the blocks by different processes. If you reduce the popularity, you reduce the chances that two processes will wait for the same
buffer. Note: you can’t completely eliminate the waits; you can only reduce it. To reduce is, reduce logical I/O. For instance,Nested Loops revisit the same object several times causing the buffers to be accessed multiple times. If you rewrite the query to avoid NLs, you will significantly reduce the chance that one process will wait for the CBC latch.
b)You can minimize less no of rows per block.The less the number of rows in a block, the less popular the block will be. You can reduce the number of rows in a block by increasing PCTFREE or using ALTER TABLE … MINIMIZE RECORDS_PER_BLOCK.
c) If that does not help, you can partition a table. That forces the data block address to be recomputed for each partition, making it more likely that the buffers will end up in different buffer chains and hence the competition for the same chain will be less.