Statistics and automatic gathering

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.

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 Mon Apr 24 2006 - 02:56:55 CDT