Re: consistent gets not equal to number of rows

<bdurrettccci_at_yahoo.com> wrote in message news:1170361605.628702.69330_at_v45g2000cwv.googlegroups.com...
> 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
>

Tablescans are a particularly easy example - especially if you do a simple aggregate. As you guessed, Oracle gets the block once and the picks rows out of it while "pinning" it.

If you did a tablescan returning actual rows, and set a small arraysize for the fetch, you would see Oracle getting the block, pinning it to fill the array, then letting it go; then coming back to pin it for the next fetch.

There's some information I wrote about the whole process here:

    http://www.dizwell.com/prod/node/342

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html