Re: Slow performance on count(*)?
Date: Mon, 14 Jul 2008 13:47:33 -0700 (PDT)
Message-ID: <67632b38-8d4d-440f-94f4-89604128f0d4@d45g2000hsc.googlegroups.com>
On Jul 14, 3:16 pm, sybra..._at_hccnet.nl wrote:
> On Mon, 14 Jul 2008 10:29:09 -0700 (PDT), 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?
>
> 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
The table is indexed, and the stats have been computed on it, but the showplan on the query is:
| Id | Operation | Name | Rows | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 1 | 0 (0)| | 1 | SORT AGGREGATE | | 1 | | | 2 | INDEX FULL SCAN| STATS$BG_EVENT_SUMMARY_PK | 1 | 0(0)|
Index Full Scan - not Full Table scan, so I'm not sure your comment about the high water mark applies. The optimizer_mode of this db is "ALL_ROWS", so the rules-based optimizer is not in effect. Received on Mon Jul 14 2008 - 15:47:33 CDT