Oracle Kernel Technology Disclosure_Lv Haibo's Learning Notes

When Cache Buffers Chain(CBC) Latch is competing, there are two main situations:
1. Multiple incompatible processes apply for the same CBC Latch to access different Buffer Headers (BH) of different chain lists protected by this CBC Latch, which is called hot chain competition.
2. Multiple incompatible processes apply for the same CBC Latch, accessing the same BH of the same list protected by this CBC Latch, which is called hot block competition.

The hidden parameters _db_block_hash_buckets and _db_block_hash_latches control the number of HASH Bucket s and the number of CBC Latch es, respectively.
When hot chain competition occurs, the hidden parameters can be modified, and the corresponding relationship between BH and HASH Bucket will be recalculated. BH that was originally in the same list may not be in the same list.

1. Look at the data block of the row id=1 in table t71 and get its Latch position in the x$bh table by file number and block number; if there is no Latch value in the first query, it is because there is no catch in the cache for the data block, then select the data.

SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from t71 where id=1;

   FILE_ID   BLOCK_ID
---------- ----------
         4     327499


SQL> select HLADDR from x$bh where file#=4 and  dbablk=327499;

HLADDR
----------------
00000000D17C9B68

2, querying this Latch protects a total of 10 Buffer s, the following will simulate hot chain competition using table SYS.WRI$_ADV_SQLT_PLANS as an example.

SQL> select file#,dbablk,owner,object_name,object_type,object_id from x$bh a,dba_objects b where hladdr='00000000D17C9B68' and a.obj=b.data_object_id;

     FILE#     DBABLK OWNER        OBJECT_NAME          OBJECT_TYP  OBJECT_ID
---------- ---------- ------------ -------------------- ---------- ----------
         1      23644 SYS          IDL_UB2$             TABLE             303
         1      12698 SYS          C_TOID_VERSION#      CLUSTER           627
         1      12698 SYS          TYPE$                TABLE             629
         1      12698 SYS          COLLECTION$          TABLE             638
         1      12698 SYS          ATTRIBUTE$           TABLE             642
         1      12698 SYS          METHOD$              TABLE             645
         1      12698 SYS          PARAMETER$           TABLE             647
         1      12698 SYS          RESULT$              TABLE             651
         2     110484 SYS          WRI$_ADV_SQLT_PLANS  TABLE            7776
         4     327499 SYSTEM       T71                  TABLE           94361

3. Check the ROWID of the first row of data in block 110484 for table SYS.WRI$_ADV_SQLT_PLANS and verify that the ROWID corresponds to the location of the data block.

SQL> select dbms_rowid.rowid_create(1,7776,2,110484,1) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAB5gAACAAAa+UAAB

SQL> select  count(*) from SYS.WRI$_ADV_SQLT_PLANS where rowid='AAAB5gAACAAAa+UAAB';

  COUNT(*)
----------
         1
		 
SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from SYS.WRI$_ADV_SQLT_PLANS where rowid='AAAB5gAACAAAa+UAAB';

   FILE_ID   BLOCK_ID
---------- ----------
         2     110484

4. Execute the program in two sessions to implement two programs accessing different Buffer s with the same Latch protection infrequently in their respective loops.

declare
r number;
begin
  for i in 1..10000000 loop
    select count(*) into r  from  SYS.WRI$_ADV_SQLT_PLANS where ROWID='AAAB5gAACAAAa+UAAB';
  end loop;
end;
/


declare
n varchar2(20);
begin
  for i in 1..10000000 loop
    select name into n  from  t71 where id=1;
  end loop;
end;
/

5. Open a new session to see the wait events for the 37 and 41 sessions above

SQL> select sid,event,p1raw,p2raw  from v$session where wait_class <> 'Idle' order by event;

       SID EVENT                        P1RAW            P2RAW
---------- ---------------------------- ---------------- ----------------
        31 SQL*Net message to client    0000000062657100 0000000000000001
        41 latch: shared pool           000000006010ED50 00000000000001C5
        37 latch: shared pool           000000006010ED50 00000000000001C5