Slow performance on count(*)?
Date: Mon, 14 Jul 2008 10:29:09 -0700 (PDT)
Message-ID: <d72b2eac-c0fa-4a37-9d6b-cf37949fc2a7@2g2000hsn.googlegroups.com>
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