Re: Statistics and automatic gathering

<schonlinner_at_yahoo.com> wrote in message news:1145865415.407266.182880_at_g10g2000cwb.googlegroups.com...
> 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
>

Since you are on 10.1 - one thing you could do after the export/import of statistics that you are currently doing is to finish off with a call to 'lock statistics'.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html