Re: queries on collecting schema statistics (oracle 9i)

Answering to your question 1:

Yes, I had seen and I heared a lot of performane complaints when there are stats on system tables. But, in 10g, its mandatory. If you are on 9i, safely you can avoid this.

Answering to your question 2:

If you have many tables and dont want to collects regular stats and wanted to collect only on tables which has significant, like 10% changes, then, set the tables in monitoring and collect statsu with auto option, not stale. Because, stale doesn't collects stats for new tables, collects stats on tables which are already having stats and they are stale.

How regularly stats shold be collected its totally dpends on the behaviour of your application. We collects stats morning and evening and we are satisifed with the performance and oracle behaviour.

Jaffar

nirav wrote:
> Hi All,
>
> I have a few questions on this,
>
>
> 1) In your project do you collect statistics on sys or system account?
> We have the practice of not collecting schema statistics on sys and
> system accounts... probably because there have been some reports of
> systems suffering from performance issues after collecting statistics
> on sys.
>
>
>
> 2) Recommended way to go about collecting schema statistics...in oracle
> 9i with monitoring option and gather stale/auto, it is possible to
> collect statistics only on tables that have significant changes. Do you
> collect statistics regularly on time based event like at daily or
> weekly interval or do you collect statistics based on dml activities?
> (i.e. By turning on the monitoring option etc) In the later case have
> you experienced any problems related to statistics?
>
> With thanks,
> Nirav
Received on Wed Jun 14 2006 - 08:21:27 CDT