read by other session 測試

gaopengtttt發表於2013-06-21
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章