Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Misleading Statistics?
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(*)
4
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(*)
4
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