Re: number of blocks reads in FTS

mark wrote:
> Guys,
> I am get confused with number of blocks read in FTS. The HWM is 58
blocks,
> but the FTS reads much, much more blocks.
> Why is that?
>
> The sqlplus output:
>
> SQL> analyze table klient compute statistics for table;
> SQL> select blocks from user_tables where table_name='KLIENT';
>
> BLOCKS
> ----------
> 58
>
> SQL> set autotr on exp stat
> SQL> select * from klient;
> ...
>
> Plan wykonywania
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14 Card=16384
Byte
> s=344064)
>
> 1 0 TABLE ACCESS (FULL) OF 'KLIENT' (TABLE) (Cost=14
Card=1638
> 4 Bytes=344064)
>
> Statystyki
> ----------------------------------------------------------
> 158 recursive calls
> 0 db block gets
> 1171 consistent gets
> 0 physical reads
> 0 redo size
> 514940 bytes sent via SQL*Net to client
> 12524 bytes received via SQL*Net from client
> 1094 SQL*Net roundtrips to/from client
> 4 sorts (memory)
> 0 sorts (disk)
> 16384 rows processed

Consistent gets are directly related to the arraysize set in SQL*Plus, the default being 15. This means each fetch gets 15 rows of data. You have 16384 rows of data, with an 8k block size; 16384 divided by 15 gives 1092, or 1092 fetches (consistent gets) to return all of your data. Add 58 (the number of blocks in your table) and we have 1150, which is close to the 1171 you see reported. Try this with different arraysize settings and you'll see the consistent gets reduced in number. Also, note that the number of rows / arraysize (1092) pretty much matches with the SQL*Net roundtrips (1094).

You're not reading any more than 58 blocks from your table; you don't have any more than that to read.

David Fitzjarrell Received on Wed Feb 16 2005 - 11:47:48 CST