Slow performance on count(*)?

Has anyone seen this before? It seems new to me:

We're running Oracle 10.2.0.3.0 on Solaris. We had statspack turned on; now we wanted to turn it off. I removed the job, and then I wanted to clean out the statspack tables. After doing so by simply running "delete from perfstat.stats$snapshot" (and having restarted the database many times since), I wanted to see which tables were now zero-sized. Running count(*) against the table led to some strange results, performance-wise.

Specifically, this statement:

select count(*) from perfstat.STATS$BG_EVENT_SUMMARY ;

COUNT(*)

                0

Elapsed: 00:01:09.72

produced these stats:

Statistics

	799  recursive calls
	 12  db block gets
       4149  consistent gets
       3954  physical reads
	  0  redo size
	619  bytes sent via SQL*Net to client
	488  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	 17  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

What's up with that? 1 minute, and 3954 physical reads to find out I have 0 rows in the table? And this is AFTER I ran a "analyze table perfstat.STATS$BG_EVENT_SUMMARY estimate statistics".

Since when do tables with 0 rows require so much physical i/o to scan? Received on Mon Jul 14 2008 - 12:29:09 CDT