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

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?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jul 15 2006 - 12:02:44 CDT