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.
>
> 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

check out dbms_stats.set_param

but more generally, maybe think about not collecting stats at all? ie, if things are running good, then *do nothing*...

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------