Re: Does SQL*Plus' SET AUTOT TRACE STAT show correct # of gets ?

"Spendius" <spendius_at_muchomail.com> wrote in message news:1133534474.675585.66000_at_g47g2000cwa.googlegroups.com...
> Good afternoon,
>
> I'm in 9i. Has someone an explanation to this discrepancy ??:
>
> SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
> 2 from tname;
>
> BLOCKS
> ----------
> 1649
>
> My block size is 4 kilos, so I have about 6.5 megs of bytes used:
> SQL> select 1649*4096 size from dual;
>
> SIZE
> ----------
> 6754304
>
> SQL> select count(1) from tname;
>
> COUNT(1)
> ----------
> 88438
>
> Now if I switch to autotrace + statistics in my session, here is what I
> get:
>
> SQL> set autot trace exp stat
> SQL> select * from tname;
>
> 88438 rows selected.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=122 Card=88438
> Bytes=6809726)
> 1 0 TABLE ACCESS (FULL) OF 'TNAME' (Cost=122 Card=88438
> Bytes=6809726)
>
> Statistics
> ----------------------------------------
> 0 recursive calls
> 0 db block gets
> 7669 consistent gets
> 1975 physical reads
> 0 redo size
> 4567982 bytes sent via SQL*Net to client
> 41540 bytes received via SQL*Net from client
> 5897 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 88438 rows processed
>
> How come it displays that more than 7600 blocks are traversed in memory
> plus 1975 on disks when I only have 1649 really occupied in my table ?
>
> In advance, thanks.
>

You did

    select * from tname
look at the

    5897 SQL*Net roundtrips to/from client

You have probably got the default value of 15 as your arraysize, so Oracle does a
consistent read, get 15 rows, and release the block. You fetch the next 15 rows, so Oracle does another consistent read of
the SAME block to get the rows. Every
round-trip will have done at least one
consistent get, and many probably did two.

Set your arraysize to something larger,
and you will see the roundtrips and the
consistent gets drop.

-- 
Regards

Jonathan Lewis

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

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005