SQL Trace Reports CR for Segment Header Read [Update 2009-11] Fuyuncat's posting at http://www.hellodba.com/Doc/logic_io_secret_FTS_1.html demonstrates that setting _trace_pin_time to non-zero unambiguously pinpoints the buffers read in current mode. This is a better way to find current mode reads than checking x$bh.tch. Unfortunately Oracle does not have an event to trace current mode read. > select count(*) from tmp; > Logical read: 283; physical read: 280. What are the missing 3 blocks? > > WAIT #4: nam='db file sequential read' ela= 70 file#=4 block#=5595 blocks=1 obj#=11556 tim=1227365247270117 > WAIT #4: nam='db file scattered read' ela= 351 file#=4 block#=5596 blocks=5 obj#=11556 tim=1227365247270783 > ... > WAIT #4: nam='db file scattered read' ela= 867 file#=4 block#=417 blocks= 32 obj#=11556 tim=1227365247285384 > > FETCH #4:c=16002,e=15857,p=280,cr=283,cu=0,mis=0,r=1,dep=0,og=1,tim=1227365247285550 I can reproduce this on my 10.2.0.4 database. My table segment only has 43 blocks. So when the table is completely outside of buffer cache (i.e. on disk only), the 10046 trace shows 43 blocks read. But it has 46 CR gets. I use 10202 event to trace CR gets. It shows 45 buffers. The two additional ones are found to be SYS.AUD$ and SYS.I_AUD1, because I have auditing turned on (audit_trail set to DB). But there's still one more buffer read not shown in the 10202 trace, and is considered as a CR instead of CU get in the 10046 SQL trace file. Remember a few days ago we had the discussion about this (see http://www.itpub.net/viewthread.php?tid=1230329&extra=&page=2 msg#16 from 3rd bullet down). We tentatively concluded that the segment header read is incorrectly added to CR statistics in SQL trace, beginning with later versions of 9i (some or all 9iR2). The way we find it is to check tch of x$bh for the segment header (dbarfile and dbablk matching header_file and header_block of dba_segments). [Original discussion at http://www.itpub.net/thread-1234382-1-1.html]