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.
select col, dbms_rowid.rowid_relative_fno(rowid) rfile#, dbms_rowid.rowid_block_number(rowid) block# from cbc;
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:-
declare aa varchar2(1000); begin for i in 1..1000000 loop select count(1) into aa from cbc ; end loop; end; /
From another session ,run following:-
declare begin for i in 1..1000000 loop update CBC set c1='MAITY'; end loop; commit; end; /
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 from x$bh 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(*) from v$active_session_history 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.
select segment_name from dba_extents where file_id = 48 and 94182 between block_id and block_id + blocks - 1 and rownum = 1