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

"Jining Han" <jining.han_at_gmail.com> wrote in message news:1153154645.685710.316180_at_35g2000cwc.googlegroups.com...
> Don't know if your original question was answered. 15 minutes is
> definitely very long, and it may have everything to do with disabling
> the PK key (the same bug has occurred to me).
>
> Creating an index may be a good idea, but I am not sure about
> gathering stats on the PERFSTAT schema/object(s). STATSPACK seems to
> have been designed to use RBO.
>
> It got so bad for me one time I deleted the dups from STATS$SQL_SUMMARY
> and re-enabled the PK.
>

This is from the Oracle 9i Performance Tuning Guide, page 621 :

Gathering Optimizer Statistics on the PERFSTAT Schema For best performance when running the performance reports, collect optimizer statistics for tables and indexes owned by PERFSTAT. You should do this whenever
there is significant change in data volumes in PERFSTAT's tables. To collect optimizer statistics on the PERFSTAT schema, use DBMS_STATS or DBMS_
UTILITY, and specify the PERFSTAT user. For example: EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'PERFSTAT',CASCADE=>TRUE); or
EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('PERFSTAT','COMPUTE'); Matthias Received on Mon Jul 17 2006 - 12:00:48 CDT