Re: Slow performance on count(*)?

On Jul 15, 10:33 am, bdbafh <bdb..._at_gmail.com> wrote:
> 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-

Take a second look at that - do you see some statements that are not truncates?

I did see a system once that had statspack left going for a year or something, my memory is hazy on the details (9.2, probably), but it did require dropping and recreating the user, things were just too big to get the reports or anything.

jg

--
@home.com is bogus.
http://www.net-security.org/secworld.php?id=6325