begin
 for i in 1 .. 100000 loop
   insert into test values('bys'||i,i,'sta'||i,');
  end loop;
  commit;
 end;
 /
 
 select * from test where rownum=1;
  select test.*,rowid from test where rownum=1;  
select test.*,rowid from test where rowid='AAAFSJAAEAAAACrAAA';

x$bh

CBC LCTCH


#######################################################
实验重现CBC Latch
1.准备实验的表，查出OBJECT_id为UNDO$的行ROWID等信息
col object_name for a12
col colname for a10
select a.rowid,a.object_id,a.file_id,a.block_id,a.row_num,b.object_name,a.colname from
(select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) row_num,
&colname as colname from &tablename t) a,
dba_objects b
where a.object_id=b.object_id;

ROWID               OBJECT_ID    FILE_ID   BLOCK_ID    ROW_NUM OBJECT_NAME  COLNAME
------------------ ---------- ---------- ---------- ---------- ------------ ----------
AAAFSJAAEAAAACrAAD      21641          4        171          3 TEST         I_USER1
AAAFSJAAEAAAACrAAC      21641          4        171          2 TEST         CON$
AAAFSJAAEAAAACrAAB      21641          4        171          1 TEST         ICOL$
AAAFSJAAEAAAACrAAA      21641          4        171          0 TEST         UNDO$

BYS@ bys3>select * from test where rownum=1;
OBJECT_NAME   OBJECT_ID STATUS
------------ ---------- -------
UNDO$                 1 VALID
查出在内存中的地址
SYS@ bys3>SELECT  HLADDR  FROM X$BH WHERE FILE#=4 AND DBABLK=171;
HLADDR
--------
27798B1C


2.查出内存块中的相应信息
SYS@ bys3>select a.file#,a.dbablk,b.owner,a.obj,b.object_name from x$bh a,dba_objects b where hladdr='27798B1C' and a.obj=b.data_object_id;
     FILE#     DBABLK OWNER                                 OBJ OBJECT_NAME
---------- ---------- ------------------------------ ---------- ------------
         1      43407 SYS                                   444 C_OBJ#_INTCO
                                                                L#
         1      43407 SYS                                   444 HISTGRM$
         1      34498 SYS                                   518 C_TOID_VERSI
                                                                ON#
         1      33799 SYS                                   518 C_TOID_VERSI
                                                                ON#
         1      34498 SYS                                   518 TYPE$
         1      33799 SYS                                   518 TYPE$
         1      34498 SYS                                   518 COLLECTION$
         1      33799 SYS                                   518 COLLECTION$
         1      34498 SYS                                   518 ATTRIBUTE$
         1      33799 SYS                                   518 ATTRIBUTE$
         1      34498 SYS                                   518 METHOD$
         1      33799 SYS                                   518 METHOD$
         1      34498 SYS                                   518 PARAMETER$
         1      33799 SYS                                   518 PARAMETER$
         1      34498 SYS                                   518 RESULT$
         1      33799 SYS                                   518 RESULT$
         2      10944 SYSMAN                              18992 MGMT_SYSTEM_
                                                                PERFORMANCE_
                                                                LOG
         4        171 BYS                                 21641 TEST
################
     FILE#     DBABLK OWNER                                 OBJ
---------- ---------- ------------------------------ ----------
OBJECT_NAME
----------------------------------------------------------------------------------------------------
         1       3899 SYS                                     8
UET$

         1       3899 SYS                                     8
SEG$

         1       3899 SYS                                     8
C_FILE#_BLOCK#

         2      10944 SYSMAN                              18992
MGMT_SYSTEM_PERFORMANCE_LOG

         4        171 BYS                                 21641
TEST

3.准备实验用的存储过程
###################################         
使用第二步中查出的对象的信息查出ROWID，如这里选用： 1      43407 SYS  444 HISTGRM$           
SYS@ bys3>select DBMS_ROWID.ROWID_CREATE(1,444,1,43407,1) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAAG8AABAAAKmPAAB
在35号会话中执行：
declare
 r number;
begin
  for i in 1 .. 10000000 loop
   select count(*) into r from sys.HISTGRM$ where rowid='AAAAG8AABAAAKmPAAB';
 end loop;
end;
/
############################################
使用第一步查出的表的信息中的ROWID：
ROWID               OBJECT_ID    FILE_ID   BLOCK_ID    ROW_NUM OBJECT_NAME  COLNAME
------------------ ---------- ---------- ---------- ---------- ------------ ----------
AAAFSJAAEAAAACrAAA      21641          4        171          0 TEST         UNDO$

在27号会话中执行：
declare
 vname varchar2(100);
begin
  for i in 1 .. 10000000 loop
   select object_name into vname from bys.test where rowid='AAAFSJAAEAAAACrAAA';
 end loop;
end;
/

4.在42号会话中查询等待事件：--latch: cache buffers比较难模拟，可能需要开多个窗口来同时执行查询内存块中的相应表信息的操作
实验了很久才出现了一次latch: cache buffers 。再次查询已经查询不到。latch: shared pool这个比较容易模拟。
BYS@ bys3>select sid ,event,p1raw,p2raw from v$session where wait_class <> 'Idle' order by event;

       SID EVENT                P1RAW            P2RAW
---------- -------------------- ---------------- ----------------
        42 SQL*Net message to c 0000000062657100 0000000000000001
           lient
        27 latch: shared pool   00000000200AE25C 0000000000000150
BYS@ bys3>select sid ,event,p1raw,p2raw from v$session where wait_class <> 'Idle' order by event;

       SID EVENT                P1RAW            P2RAW
---------- -------------------- ---------------- ----------------
        42 SQL*Net message to c 0000000062657100 0000000000000001
           lient
        27 resmgr:cpu quantum   0000000000000003 000000000000324C
        35 resmgr:cpu quantum   0000000000000003 000000000000324A
BYS@ bys3>select sid ,event,p1raw,p2raw from v$session where wait_class <> 'Idle' order by event;

       SID EVENT                P1RAW            P2RAW
---------- -------------------- ---------------- ----------------
        42 SQL*Net message to c 0000000062657100 0000000000000001
           lient
        35 latch: cache buffers 00000000003a0003 0000000000000003
        chains
        27 resmgr:cpu quantum   0000000000000003 000000000000324C
        35 resmgr:cpu quantum   0000000000000003 000000000000324A
模拟CBC LATCH的过程就是这样了，真的是很难出现。
##########################