col name format a26
select n.name,s.sid,s.VALUE from v$sesstat s,v$statname n where n.name='session logical reads' and sid=467 and n.statistic#=s.statistic#;
NAME SID VALUE
-------------------------- ---------- ----------
session logical reads 467 1407
alter system flush buffer_cache;
SYS@tstdb1-SQL> set pagesize 20
SYS@tstdb1-SQL> select file#,dbablk,tch from x$bh where obj=41299;
FILE# DBABLK TCH
---------- ---------- ----------
5 157 0
5 157 0
5 154 0
5 154 0
5 159 0
5 159 0
5 156 0
5 156 0
5 153 0
5 158 0
5 158 0
5 155 0
5 155 0
5 152 0
跟踪session 2的sid，tracefile里可以记录逻辑读访问哪些块，前提是必须保证这些块不在buffer cache里，可以先flush buffer_cache
exec DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id=>467,serial_num=>3927,waits=>TRUE,plan_stat=>NULL);
---session 2: 执行select * from scott.t11
set arraysize 21
select /*+ dynamic_sampling(0) */ * from scott.t11 ;
---session 3：记录session 2的执行后的SQL*NET等待事件，session logical reads次数
set linesize 120
col event format a40
select sid,event,total_waits,time_waited from v$session_event where sid=467 and event in ('SQL*Net message to client','SQL*Net message from client','SQL*Net more data to client');
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
467 SQL*Net message to client 45 0
467 SQL*Net message from client 44 136331
col name format a30
select n.name,s.sid,s.VALUE from v$sesstat s,v$statname n where n.name='session logical reads' and sid=467 and n.statistic#=s.statistic#;
NAME SID VALUE
------------------------------ ---------- ----------
session logical reads 467 1415
结束session 2的跟踪
exec DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id=>467,serial_num=>3927);
生成的Tracefile里包含的内容：
*** 2016-01-14 14:48:52.745
WAIT #4574029704: nam='SQL*Net message from client' ela= 279270707 driver id=1650815232 #bytes=1 p3=0 obj#=41299 tim=11207996794058 <--第1次"SQL*Net message from client"等待
CLOSE #4573414144:c=0,e=25,dep=0,type=0,tim=11207996794949
CLOSE #4574029704:c=0,e=48,dep=0,type=3,tim=11207996795047
=====================
PARSING IN CURSOR #4573414144 len=51 dep=0 uid=0 oct=3 lid=0 tim=11207996795258 hv=3660860863 ad='7000001b8499ec0' sqlid='0ckm0urd38mdz'
select /*+ dynamic_sampling(0) */ * from scott.t11
END OF STMT
PARSE #4573414144:c=0,e=156,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=711494848,tim=11207996795257
EXEC #4573414144:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=711494848,tim=11207996795393
WAIT #4573414144: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=41299 tim=11207996795436 <--第1次"SQL*Net message to client"等待
WAIT #4573414144: nam='db file sequential read' ela= 466 file#=5 block#=154 blocks=1 obj#=41299 tim=11207996796009 <--发生1次session logical reads
WAIT #4573414144: nam='db file scattered read' ela= 433 file#=5 block#=155 blocks=5 obj#=41299 tim=11207996796755 <--又发生5次session logical reads
FETCH #4573414144:c=0,e=1384,p=6,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=711494848,tim=11207996796853
WAIT #4573414144: nam='SQL*Net message from client' ela= 292 driver id=1650815232 #bytes=1 p3=0 obj#=41299 tim=11207996797184 <--第2次"SQL*Net message from client"等待
WAIT #4573414144: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=41299 tim=11207996797220 <--第2次"SQL*Net message to client"等待
FETCH #4573414144:c=0,e=50,p=0,cr=1,cu=0,mis=0,r=20,dep=0,og=1,plh=711494848,tim=11207996797260
STAT #4573414144 id=1 cnt=21 pid=0 pos=1 obj=41299 op='TABLE ACCESS FULL T11 (cr=8 pr=6 pw=0 time=1375 us cost=2 size=819 card=21)'
WAIT #4573414144: nam='SQL*Net message from client' ela= 1157 driver id=1650815232 #bytes=1 p3=0 obj#=41299 tim=11207996798516 <--第3次"SQL*Net message from client"等待
PARSE #4574029704:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=11207996798594
WAIT #4574029704: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=41299 tim=11207996798997 <--第3次"SQL*Net message to client"等待
EXEC #4574029704:c=0,e=345,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=11207996799039
~
通过跟踪基表x$bh也可以发现，scott.t11表里有154~159六个block被访问过了：
SYS@tstdb1-SQL> select file#,dbablk,tch from x$bh where obj=41299;