Re: Performance impact of MONITORING and GATHER_STALE

On Wed, 16 Mar 2005, Niall Litchfield wrote:

> My view is that whilst gather_stale is a step in the right direction,
> it is predicated upon a bad assumption (or rather a rule of thumb)
> that changes of 10% are significant for all tables and changes of less
> than 10% aren't. If you could do ALTER TABLE T MONITORING THRESHOLD x%
> then I'd be more inclined to go along with it.

Well even if Oracle failed to include this, we can do it ourselves. I just have a little PL/SQL block that does this manually, generating a list of tables where ( inserts+updates+deletes > num_rows/2

    or truncated = 'YES'
    or sysdate - last_analyzed > 30 )

Then I iterate over the list of tables and estimate or compute with dbms_stats based on size.

--
Jeremiah Wilton
ORA-600 Consulting
Emergencies - Seminars - Hiring
http://www.ora-600.net

--
http://www.freelists.org/webpage/oracle-l