Misleading Statistics?

From: D.Y. <dyou98_at_aol.com>
Date: 20 Jun 2002 15:41:07 -0700
Message-ID: <f369a0eb.0206201441.6082733e@posting.google.com>

I just noticed some difference in I/O statistics for identical SQLs,

SQL> select count(*) from my_table where id=100000995927 and type is not null;

COUNT(*)

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=20) 1 0 SORT (AGGREGATE)

   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MY_TABLE'
                (Cost=1 Card=1 Bytes=20)
   4    3         INDEX (RANGE SCAN) OF 'MY_IDX' (NON-UNIQUE)
                  (Cost=9 Card=1)

Statistics

         22  recursive calls
          0  db block gets
         15  consistent gets
          4  physical reads
          0  redo size
        367  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> / COUNT(*)

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=20) 1 0 SORT (AGGREGATE)

   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MY_TABLE'
                (Cost=1 Card=1 Bytes=20)
   4    3         INDEX (RANGE SCAN) OF 'MY_IDX' (NON-UNIQUE)
                  (Cost=9 Card=1)

Statistics

          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        367  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

So when the same SQL is executed a second time, recursive calls and physical reads disappear. That makes perfect sense. But why the difference in logical I/O's (db block gets + consistent gets) and sorts? Any ideas?

Thanks. Received on Thu Jun 20 2002 - 17:41:07 CDT