Re: Slow performance on count(*)?

On Mon, 14 Jul 2008 10:29:09 -0700 (PDT), groups.broberg_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?

select count(*)
either uses an Index Fast full scan (Cost Based Optimizer, provided the table is indexed) or a FULL TABLE scan (RBO, which is automatically used when no statistics are present)

Full table scan will always proceed until the High Water Mark of the table. The High Water Mark is reset only when you TRUNCATE the table, instead of DELETE it.
This behavior has probably always been there from Oracle 5.1.17 (which is the earliest version I have see) onwards. You should have TRUNCATEd the tables.

-- 
Sybrand Bakker
Senior Oracle DBA