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

Yes I had left the default value of ARRAYS. I've put it to its max (5000) and I get

Statistics

          0  recursive calls
          0  db block gets
       2002  consistent gets
       1360  physical reads
          0  redo size
    4272592  bytes sent via SQL*Net to client
        412  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client      <= pretty better...
          0  sorts (memory)
          0  sorts (disk)
      88438  rows processed

Can you deal with this in PL/SQL blocks ?

And how about a tkprof result file, in
>call count cpu elapsed disk query current rows
>------- ------ -------- ---------- ---------- ---------- ---------- ----------
>[...]

are the values of the columns 'query' and 'current' *biased* as well for similar
reasons ?

> 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.
>
Received on Fri Dec 02 2005 - 09:43:51 CST