Re: Slow performance on count(*)?

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.

See http://richardfoote.wordpress.com/2008/02/06/differences-and-similarities-between-index-coalesce-and-shrink-space/ and related posts for lots more detail.

jg

--
@home.com is bogus.
http://www.infoworld.com/article/08/07/14/Researcher_to_demonstrate_attack_code_for_Intel_chips_1.html