oracle查看latch锁控制多少个hashbucke实验

浏览： ℃

字体：大中小

发布时间：2013-12-12 14:50:59

来源：

创建一个测试表
SQL> create table cache_chain (id_a varchar2(20), r_name varchar2(20));
Table created.

往表里插入10W条记录
begin
for r in 1 .. 100000 loop
insert into gyj_test values(r,'pl'||r);
end loop;
commit;
end;
/

看一下第1行的rowid 后面拿来验证是不是对的
SQL> select rowid,a.* from cache_chain a where a.id_a=1;

ROWID ID_A R_NAME
------------------ -------------------- --------------------
AAAEMqAAEAAAAEGAAA 1 pl1

AAAEMq 是数据对象编号
AAE是相关文件编号
AAAAEG是块编号
AAA 是行编号

使用dbms_rowid包找出 id_a=1 的那一行文件号和块号
SQL> select id_a,r_name,dbms_rowid.rowid_relative_fno(rowid) file# ,
dbms_rowid.rowid_block_number(rowid) block# from cache_chain where id_a=1;

ID_A R_NAME FILE# BLOCK#
-------------------- -------------------- ---------- ----------
1 pl1 4 262

然后通过x$bh视图找出latch
SQL> select hladdr from x$bh where file#=4 and dbablk=262;
HLADDR
--------
4499C7F8
4499C7F8

查看我的数据库一把lacth锁控制多少个 hash bucket (我这里是5个)
SQL> select a.file# ,a.dbablk ,b.owner , a.obj ,b.object_name
from x$bh a ,dba_objects b
where hladdr='4499C7F8' and a.obj=b.data_object_id;

FILE# DBABLK OWNER OBJ OBJECT_NAME
---------- ---------- ---------- ---------- --------------------
1 20643 SYS 237 I_IDL_UB21
1 3291 SYS 501 I_OID1
2 19711 SYS 6205 WRH$_SQL_PLAN
4 262 SCOTT 17194 CACHE_CHAIN
4 262 SCOTT 17194 CACHE_CHAIN

转换成rowid

SQL> select dbms_rowid.rowid_create(1,17194,4,262,0) from dual; (1表示类型,对象号,文件号,块号,行号) orale里面0就是1

DBMS_ROWID.ROWID_C
------------------

AAAEMqAAEAAAAEGAAA

可以看到与上面的一模一样

往被一把latch锁控制的几个表狂插数据查看是否会产生 cache buffer chain 等待事件验证是一个latch锁不能同时对多个hash bucket进行控制
declare
r number;
begin
for i in 1 .. 10000000 loop
select count(*) into r from SYS.WRH$_SQL_PLAN where rowid='AAAEMqAAEAAAAEGAAA';
end loop;
end;
/

declare
vname varchar2(100);
begin
for i in 1 .. 10000000 loop
select r_name into vname from SYS. I_IDL_UB21where rowid='AAAEiyAAFAAAAEvAAA';
end loop;
end;
/

declare
vname varchar2(100);
begin
for i in 1 .. 10000000 loop
select r_name into vname from SCOTT. CACHE_CHAIN where rowid='AAAEiyAAFAAAAEvAAA';
end loop;
end;
/

使用下面的语句查看是否有 cache buffer chain 这个等待