数据库版本:
SYS@ orcl > select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production
– 创建对象
drop table HDP.T
create table hdp.t as select * from all_objects
– 确定hdp.t表第一行所在的文件和数据块号
select DBMS_ROWID.ROWID_RELATIVE_FNO(rowid),DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) from hdp.t where rownum=1
-- 7,19043
– 寻找hdp.t表第一行 latch所在的地址
select * from x$bh where file#=7 and dbablk=19043
-- 000000006C6F0F00
– oracle 的cache buffer chains中的latch对象,会同时被几个hash bucket使用,而且,每个所后面会跟着不同的对象,组成hash链表
– 寻找这个latch还锁着哪些对象
select b.OBJECT_ID,a.file#,b.OWNER,b.OBJECT_NAME,a.DBABLK from x$bh a ,dba_objects b where a.OBJ=b.OBJECT_ID and a.HLADDR='000000006C6F0F00'
/*
40 1 SYS I_OBJ5 60286
18 1 SYS OBJ$ 16502
9 1 SYS I_FILE#_BLOCK# 9737
68 1 SYS HIST_HEAD$ 45159
674 3 SYS WRI$_OPTSTAT_OPR_TASKS 20286
76411 7 HDP T 19043
76158 3 SYS WRH$_EVENT_HISTOGRAM_PK 88546
*/
–这里找到如上的几个对象 ,可以使用OBJ$对象来实验latch竞争
– 参考函数
/*
DBMS_ROWID.ROWID_CREATE (
rowid_type IN NUMBER,
object_number IN NUMBER,
relative_fno IN NUMBER,
block_number IN NUMBER,
row_number IN NUMBER)
参数:
rowid_type:rowid类型(restricted或者extended)。设置rowid_type为0时,代表restricted ROWID(此时,将忽略参数object_number):设置rowid_type为1时,代表extended ROWID。
object_number:数据对象编号(仅restricted类型rowid可用)。
relative_fno:所在数据文件编号。
block_number:该数据文件中的数据块编号。
row_number:在该块中的行编号。*/
– get竞争对象OBJ$ 块所在的rowid
select DBMS_ROWID.ROWID_CREATE(1,18,1,16502,1) from dual;
– AAAAASAABAAAEB2AAB
– 代码1
declare
r number;
begin
for i in 1..1000000 loop
select count(*) into r from sys.obj$ where rowid='AAAAASAABAAAEB2AAB';
end loop;
end;
/
–代码2
declare
r number;
begin
for i in 1..1000000 loop
select count(*) into r from hdp.t where rownum=1;
end loop;
end;
/
– 同时运行以上两个脚本
select sid,serial#,event ,p1raw,p2raw from v$session where wait_class!='Idle'
28 33249 latch: shared pool 00000000602DC220 0000000000000227
253 8714 latch: shared pool 00000000602DC220 0000000000000227
其实这里我想测的是latch: cache buffer chains 等待事件,结果在12c里面直接是latch: shared pool ,这个目前待定吧。