read by other session 测试

表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<=1) b

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; 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-764533/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-764533/