ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 等待模拟-read by other session

等待模拟-read by other session

原创 Linux操作系统 作者:gaopengtttt 时间:2013-06-27 15:43:59 0 删除 编辑
表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-764953/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    681
  • 访问量
    2937230