The CR number in the "Row Source Operation" figures is the cumulative total of the CRs below it - for
one execution of the query.
The disk/query/current figures are the totals for all executions in the tracefile.
For example, I create a table which has 1 row/value. Except 1,000. There are 20 rows with this value. The rows are "wide". So these 20 rows are spread across a few blocks.
create table t (
x number,
stuff varchar2(1000)
);
insert into t select rownum, lpad('x', 1000, 'x') from dual connect by level <= 980;
insert into t select 1000, lpad('x', 1000, 'x') from dual connect by level <= 20;
create index i on t(x);
alter session set tracefile_identifier = chris;
alter session set sql_trace = true;
var v number;
exec :v := 1;
select * from t where x = :v;
exec :v := 1000;
select * from t where x = :v;
alter session set sql_trace = false;
tkprof only reports one CR for the table access in the row source though (4 - 3 from the index). This is because it's using the stats from the first execution. The query column reports the total buffer gets across both executions (11):
select *
from
t where x = :v
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 11 0 21
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 11 0 21
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 121
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=120 us cost=2 size=515 card=1)
1 1 1 INDEX RANGE SCAN I (cr=3 pr=0 pw=0 time=99 us cost=1 size=0 card=1)(object id 131860)
So based on what you can see we can say:
- one execution used 30 consistent reads
- On average each execution took 221218 / 14057 ~ 16 reads.
The variations will be because different executions read more or less data based on the bind values and any DML run between executions.
Chris