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

Matthias Hoys wrote:
>
> 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

Thanks for the information. This is certainly good to know.

However, my PERFSTATS has absolutely NULL stats, and my STATS$SQL_SUMMARY has 14,787,373 records, and spreport.sql and statspack.snap both runs in a few seconds. Prior to re-enabling the PK key on STATS$SQL_SUMMARY, spreport.sql hang forever, and statspack.snap ran over 15 minutes.

So at least in my experience, STATS on PERFSTAT schema is non-significant, but that PK is.

JH Received on Mon Jul 17 2006 - 12:55:28 CDT