create table test_buf(id number,name char(1000))
tablespace test;
begin
for idx in 1..100 loop
insert into test_buf values(idx,'aa');
end loop;
end;
select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),count(*) from test_buf group by dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER( COUNT(*)
------------------------------ ------------------------------ ----------
6 23661 7
6 23666 7
6 23670 7
6 23679 7
6 23668 7
6 23663 7
6 23662 7
6 23665 7
6 23680 2
6 23667 7
6 23672 7
6 23660 7
6 23669 7
6 23671 7
6 23664 7
create or replace procedure test_buf_pr
is
begin
for n in 1..1000 loop
for idx in 2..10 loop
update test_buf set name='TT'
where id=10*(idx-1);
commit;
end loop;
end loop;
end;
var job_no number;
begin
for idx in 1..100 loop
dbms_job.submit(:job_no,'test_buf_pr;');
end loop;
commit;
end;
同时本会话也执行查看等待事件
execute test_buf_pr
最后可以观察到这就是UPDATE UPDATE buffer busy wait争用
select * from dba_hist_active_sess_history a,(select SID from v$mystat where rownum'Idle' order by SAMPLE_TIME desc;
是CLASS=1也就是DATA BLOCK,这个可以通过v$waitstat来查看。