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

Charles Hooper wrote:
> nomorekiss_at_gmail.com wrote:
> > You can try to gather statistics on SYS schema
> > exec DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
> >
> > If that doesn't help try to delete statistics on that schema:
> > exec DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
> >
> >
> > Since you are using 9i there shouldn't be any serious problems unless
> > applications itself rely on dictionary views or sys tables.
> >
> > You might notice some performance changes with tools used to monitor
> > database (TOAD etc.) or utilities (exp for example) after changes
> > mentioned above.
>
> Regarding gathering stats on the SYS schema, from:
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1154434873552
> Sept 23, 2004
>
> "In 8i, it would not be recommended to analyze the dictionary.
>
> In 9i, as long as you do it from day one (eg: from development on out),
> it is
> safe, and supported. But, if you are already in production -- don't
> just start
> doing it (it is like making a big code change, you do that in TEST TEST
> TEST)
>
> In 10g, it'll just be analyzed out of the box."
>
> I have read that some people recommend gathering schema stats on the
> SYS schema starting with 9i, and others suggesting that there are bugs
> in 9i that will cause problems when the statistics are gathered on the
> SYS schema.
>
> Based on your experience, would gathering and/or dropping schema stats
> of the SYS schema on 9i cause problems?

We are running 9.2.0.6 currently and my advice is to stay away from statistics for SYS schema.

10 does some of that automatically but for fixed tables you still need think about when and if you want to gather them. ( Fixed tables are ones that reside in memory not disk ). Received on Sat Jul 15 2006 - 15:30:57 CDT