表TEST是一个130M的表。
create or replace procedure test_buffer_read
is
begin
for x in (select * from test) loop
null;
end loop;
end;
var job_no number;
begin
for idx in 1..20 loop
dbms_job.submit(:job_no,'test_buffer_read;');
end loop;
commit;
end;
然后手动执行一次 execute test_buffer_read
select * from v$session_event a,(select SID from v$mystat where rownum
where a.SID=b.sid;
对于这样的，很可能会出现很多等待，
EVENT
----------------------------------------------------------------
latch: cache buffers chains
latch: cache buffers chains
latch: cache buffers chains
db file scattered read
read by other session
read by other session
db file sequential read
latch: cache buffers chains
read by other session
db file scattered read
read by other session
job scheduler coordinator slave wait
往往这些等待同时出现，需要知道他们是息息相关的
可以用如下语句查看，当前会话中的等待对象如果等待的是data block的话，但是速度很慢。。
SELECT a.segment_name,b.wait_time,'LOG READ' FROM DBA_EXTENTS a,
(select p1,p2,WAIT_TIME from v$session_wait where event in ('read by other session','buffer busy waits') and p3=1) b
where a.file_id=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1
union all
SELECT a.segment_name,b.wait_time,'phy READ' FROM DBA_EXTENTS a,
(select p1,p2,WAIT_TIME from v$session_wait where event in ('db file scattered read','db file sequential read')) b
where a.file_id=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1
order by wait_time;
