Re: Slow performance on count(*)?

On Jul 14, 1:29 pm, groups.brob..._at_gmail.com wrote:
> 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?

Solved problem:

SQL> @?/rdbms/admin/sptrunc;

-bdbafh Received on Tue Jul 15 2008 - 12:33:13 CDT