Re: Slow performance on count(*)?

On Jul 14, 5:08 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jul 14, 1:47 pm, groups.brob..._at_gmail.com wrote:
>
>
>
> > 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.
>
> Try truncating the table. I bet the HWM applies.
>
> Seehttp://richardfoote.wordpress.com/2008/02/06/differences-and-similari...
> and related posts for lots more detail.
>
> jg
> --
> @home.com is bogus.http://www.infoworld.com/article/08/07/14/Researcher_to_demonstrate_a...

Truncating the table did fix the problem - now I'm just trying to understand the nature of the problem, and how it might affect other apps I write. We've written Oracle apps for years that delete large amounts of data from tables with many rows, but I've never seen this behavior before, although we rarely do count(*) in our application logic. Maybe this is just a degenerate case we haven't come across. Received on Mon Jul 14 2008 - 16:33:54 CDT