Re: Statistics and automatic gathering

schonlinner_at_yahoo.com wrote:
> Hi,
>
> we are using Oracle 10g Rel 1 (10.1.0.4).
>
> The problem which we are currently facing is that the automatic
> statistics gathering job needs too much time to gather statistics.
>
> I would like to tell the job that the sample percent parameter should
> be set to a much smaller value.
>
> The base problem most llikely will be due to the use of transportable
> tablespaces: We use this mechanism in order to duplicate a complete
> schema in the same database (by use of the remap_schema parameter).
> This works, but unfortunately we cannot clone the statistics due to an
> Oracle bug (thus we must use the exclude=Statistics when using impdp).
> Thus we perform a mechanism of export/import statistics (using
> dbms_stats.export_schema_stats, etc.) in order to copy the statistics.
> But this mechanism seems to make all statistics stale (they "work",
> they are correct, the optimizer is happy...), thus the statistics
> gathering job wants to gather statistics for the complete schema, and
> this takes so much time.

What about just doing table level statistics exports and imports instead of schema?

>
> Is there a way for us to help the job? I.e. how can I see what
> table/index statistics are in a stale state? Can I reset the stale flag
> if I know that the statistics on global/partition level are still
> valid?
>
> How can I change the parameters which the statistics gathering job is
> using?
>
> Best regards,
> Alex
>
Received on Tue Apr 25 2006 - 18:28:11 CDT