How long should statspack.snap take to run?

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 Received on Fri Jul 14 2006 - 12:55:32 CDT