Re: consistent gets not equal to number of rows

bdurrettccci_at_yahoo.com wrote:
> I've been puzzling over the "consistent gets" number that comes out of
> sqlplus when you set autotrace on. I understand consistent gets to be
> the most common type of logical read. But, the number of consistent
> gets appears to equal the number of blocks accessed by a query and not
> the number of rows. So, it would seem that Oracle batches up accesses
> to rows by block. Or it just counts the first access to a block by
> the query as a consistent get and doesn't count subsequent accesses.
> Anyway, if you can shed more light on the subject or refer to an
> article, book, manual, etc. that explains it that would be great.
>
> Here is an example:
>
> create table test as select rownum A from dba_objects;
>
> execute dbms_stats.gather_table_stats('','TEST');
>
> select num_rows,blocks from user_tables where table_name='TEST';
>
> NUM_ROWS BLOCKS
> ---------- ----------
> 100049 153
>
> set autotrace on statistics
>
> select sum(A) from test;
>
> Statistics
> ----------------------------------------------------------
> 1 recursive calls
> 0 db block gets
> 157 consistent gets
> 154 physical reads
> 0 redo size
> 413 bytes sent via SQL*Net to client
> 396 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> So, my question is why does consistent gets equal 157, approximately
> the number of blocks, and not 100049, the number of rows?
>
> Thanks,
> Bobby
>

The block is the smallest unit of I/O the database can perform. The block may hold one row of data or 10 rows of data or more. Oracle only needs to access that block once to get that one row or all of the rows in the block, or somewhere in between.

Consistent gets refer to the number of block accesses, not the number of rows returned.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown