Asktom:Thanks for the question regarding "consistent gets -- Very puzzling".

You Asked

Tom:

create table test( a int);
begin
for i in 1..10000 loop
insert into test values (i);
end loop;
end;

set autotrace on
select count(0) from test;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


select * from test where a=10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        360  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


select * from test;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
        686  consistent gets
          0  physical reads
          0  redo size
     185864  bytes sent via SQL*Net to client
      74351  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10001  rows processed



question:
1.
when query count(0), what exactly did oracle do? since we don't have index, did oracle 
went to every block and query the number?
if so, why consistent gets is much lower that select * from test?
both are full scan table.

2.
why select * from test have much higher consistent gets than
select * from test where a=10?
since there are no index, oracle need to go to every block to get the value, it should be 
same consistent gets, although the first one return more rows, but it does't matter.

Regards,

 

and we said...

q1) select count(0) is just like 

select count(*)
  from ( select 0 from t )
/

yes, oracle went to each block to find the rows to give you a zero

q2) its a side effect of your arraysize.  You must have an 8k blocksize cause I 
reproduced this exactly.

We were expecting about 20 consistent gets right?  Well, the default array size in 
sqlplus is 15 rows / fetch.  10000/15 = 666.66666.  Well, 666+20 = 686 -- whoah there -- 
686 is our consistent gets!

Thats what happened.  When you fetched 15 rows, Oracle paused, gave you the data.  When 
you went back to get the next 15, it got the buffer again to resume your query. 

Watch what happens with different array sizes, starting with 15:


ops$tkyte@8i> select * from test;

10000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
        686  consistent gets
          0  physical reads
          0  redo size
     108813  bytes sent via SQL*Net to client
      46265  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte@8i> set arraysize 1000
ops$tkyte@8i> select * from test;

10000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
      86266  bytes sent via SQL*Net to client
        942  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte@8i> set arraysize 5000
ops$tkyte@8i> select * from test;

10000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
     149793  bytes sent via SQL*Net to client
        382  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed



Other interesting thing to note is that as the array size gets too large -- the amount of 
data transferred goes up.  there is a diminishing marginal return on the array size so 
don't go OVERBOARD.

followup to comment one

Set the arraysize to some constant as it will not matter for single row fetches whether 
it is too big but having it too small for lots of rows (NOT just full scans -- lots of 
rows) does impact you.

I am a bit confused now, though you explained it well, but I tested it in my envirnment and got 
different results.
My query returns 122 rows with a select *.

Arraysize of 15 --> number of consistent gets was=1051 (122/15=8) which means 1043 + 8 = 1051 (good 
so far)
so I would assume according to the example above that when I do count(*) number of consistent gets 
would be 1043, right? BUT 
Arrayszie of 15 with count(*) --> consistent gets = 920 I dont understand where this comes from?

I did more testing:
Arraysize of 122 with select * --> consistent gets = 1043
Arraysize of 122 with select count(*) --> consistent gets = 920

Arrayszie of 5000 with select * --> consistent gets = 1043
Arrayszie of 5000 with select count(*) --> consistent gets = 920

I would appreciate if you can explain.
thanks,
ZS
 

Followup May 1, 2002 - 7am Central time zone:

The count(*) returns how many rows?  1.

The array size does even begin to kick in.  That statement is processed in ONE call.

Also, the count(*) may very well *never be touching the table*.  It might be just using an index.

Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
Free Blocks.............................0
Total Blocks............................320
Total Bytes.............................2621440
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................7
Last Used Ext BlockId...................40969
Last Used Block.........................60

PL/SQL procedure successfully completed.

so the table consumes about 316 blocks..


ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;
22905 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
       1823  consistent gets
         46  physical reads
          0  redo size
    2704019  bytes sent via SQL*Net to client
     169811  bytes received via SQL*Net from client
       1528  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      22905  rows processed


22905/15 = 1527+316 = 1843 which is about our consistent gets (what we were expecting)....

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t;


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
        319  consistent gets
         46  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

And the count(*), since it was done in a SINGLE CALL -- no arraysize -- only did a consistent get 
on each block ONE TIME.  Hence it did 319 consistent gets (which is about the size of the table)

Now, adding a primary key to the table:


ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id);

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t;


Statistics
----------------------------------------------------------
         79  recursive calls
          4  db block gets
         82  consistent gets
          1  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

we can see that the consistent gets go way down.  why?  because the optimizer counted the rows in 
the index, not the table in this case.