Cache buffer chain CBC latch simple query to investigate

Cache buffer chain CBC latch simple query to investigate

For deep understanding on CBC,please refer to below links:-

http://www.proligence.com/pres/ioug14/2014_431_Nanda_ppr.pdf

and

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;

MOD(27340036,3)
—————
1

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.

Mechanism:-

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

Problem:-

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.

No problem:-

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';

LATCH#
———-
228

If you check the values of the two hidden parameters explained earlier, you will see:
_db_block_hash_buckets 524288
_db_block_hash_latches 16384

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

or

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

Solving problem:-

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.