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

From: Spendius <spendius_at_muchomail.com>
Date: 2 Dec 2005 06:41:14 -0800
Message-ID: <1133534474.675585.66000@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. Received on Fri Dec 02 2005 - 08:41:14 CST