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]