There is table T with 3 number columns (a,b,c) and index on column A.
Table is small and has a few rows.
I executed
alter session set optimizer_dynamic_sampling=0;
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
ALTER SESSION SET EVENTS '10200 trace name context forever, level 1';
insert into t values(2,null,null);
And tracefile contains:
*** ACTION NAME:() 2011-10-02 19:16:01.923
*** MODULE NAME:(SQL*Plus) 2011-10-02 19:16:01.923
*** SERVICE NAME:(SYS$USERS) 2011-10-02 19:16:01.923
*** SESSION ID:(1.60022) 2011-10-02 19:16:01.922
WAIT #5: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=826883 tim=9504546816500
WAIT #5: nam='SQL*Net message from client' ela= 63577 driver id=1413697536 #bytes=1 p3=0 obj#=826883 tim=9504546881660
=====================
PARSING IN CURSOR #6 len=68 dep=0 uid=2500 oct=42 lid=2500 tim=9504546882069 hv=2008936627 ad='0'
ALTER SESSION SET EVENTS '10200 trace name context forever, level 1'
END OF STMT
PARSE #6:c=0,e=228,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=9504546882065
EXEC #6:c=0,e=261,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=9504546882526
WAIT #6: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=826883 tim=9504546882562
WAIT #6: nam='SQL*Net message from client' ela= 63661 driver id=1413697536 #bytes=1 p3=0 obj#=826883 tim=9504546946264
=====================
PARSING IN CURSOR #4 len=33 dep=0 uid=2500 oct=2 lid=2500 tim=9504546948575 hv=3365441265 ad='ba6358c8'
insert into t values(2,null,null)
END OF STMT
PARSE #4:c=10000,e=2054,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=2,tim=9504546948570
WAIT #4: nam='db file sequential read' ela= 773 file#=224 block#=506129 blocks=1 obj#=1807603 tim=9504546949661
WAIT #4: nam='db file sequential read' ela= 334 file#=224 block#=506130 blocks=1 obj#=1807603 tim=9504546950075
WAIT #4: nam='db file sequential read' ela= 338 file#=88 block#=113105 blocks=1 obj#=0 tim=9504546950527
WAIT #4: nam='db file sequential read' ela= 8527 file#=88 block#=59778 blocks=1 obj#=0 tim=9504546959170
WAIT #4: nam='db file sequential read' ela= 515 file#=224 block#=506258 blocks=1 obj#=1807604 tim=9504546959940
EXEC #4:c=0,e=11346,p=5,cr=1,cu=5,mis=0,r=1,dep=0,og=2,tim=9504546960051
WAIT #4: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=1807604 tim=9504546960229
As we can see tracefile does not contain extra info about consistent gets (10200).
My aim to find out which blocks are read in cr mode.
In this exact case 1 block, in other the same inserts sometimes I see more than one block, for example 3.
physical reads were:
1. table header
2. table block
3. undo header
4. undo block
5. index leaf block
How to find what was read in cr mode?