Re: How long should statspack.snap take to run?

<jon.fife_at_gmail.com> wrote in message
news:1152899732.831601.124570_at_b28g2000cwb.googlegroups.com...
> Hi all,
>
> I've been tossed into a vacant DBA role and I'm trying to pick up the
> pieces the former DBA left in his wake. It doesn't seem that he had any
> sort of history for performance baselines, so I've been playing around
> with statspack. When I do the statspack.snap it takes around 15 minutes
> to complete and I was just wondering if that was outrageous or typical.
> It is a 9.2.0.4 server with roughly 150GB of data and 150 concurrent
> users during the day doing OLTP. I've done some googling to see if the
> time spent was sane, but didn't find much there. It seems some people
> set up statspack to run every 30 minutes or so, which makes 15 minutes
> seem high...
>
> Another thing to note: when I first did the snap I was getting the
> following:
> ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
>
> I found a fix for that in Metalink (Note:267244.1) that had me disable
> the primary key constraint on PERFSTAT.STATS$SQL_SUMMARY. Since that
> invalidated the STATS$SQL_SUMMARY_PK index I created a new index named
> $STATS$SQL_SUMMARY_PK on STATS$SQL_SUMMARY(SNAP_ID, DBID,
> INSTANCE_NUMBER, HASH_VALUE, TEXT_SUBSET) and did a compute statistics
> on it. I don't know if that would have a huge impact on the execution
> time, since there are only 3700 rows in STATS$SQL_SUMMARY.
>
> Thanks for any advice,
>
> Jon
>

Did you try to compute the statistics of the whole statspack schema ?

Matthias Received on Sat Jul 15 2006 - 04:30:36 CDT