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

> Based on your experience, would gathering and/or dropping schema stats
> of the SYS schema on 9i cause problems?

My primary role is a development DBA and I frequently collect and delete statistics on our test databases. I also take care of some production databases and I have done that (gather stats on SYS) on most of them without any consequences for OLTP applications.

The reasons are usually slow export processes or slow behaviour of monitoring tools.

I observed that gathering/deleting stats on SYS speeds up some queries and slows down some others at the same time. That applies to Oracle9i instances and only to monitoring tools and export operations ( I haven't observed that with our applications yet though it might be quite possible as mentioned on ask-tom).

For instance if applications query the dictionary for some reason, performance could be influenced by collecting stats on SYS schema.

I suggested collectiong/deleting stats because if they don't have statistics collected, he can try collecting them and if it works, watch the system (operative applications) for any negative impacts. If any show up, then just delete stats.
On the other hand, if the stats are collected, they could be stale. They can try to delete them and see if snapshots finish any faster. If not, just recollect the statistics.

That is what I would certainly try, because I don't believe you can resolve performance problems with tools that create performance problems (statspack in this case). Received on Sat Jul 15 2006 - 15:52:42 CDT