Re: Performance impact of MONITORING and GATHER_STALE

On Wed, 16 Mar 2005 12:32:55 +1100, Leng Kaing <Leng.Kaing_at_hsntech.com> wrote:
> Hi Mladen,
>
> Sorry, I don't quite understand. What do you mean when you say "It also
> takes away any purpose from gathering statistics based on STALE status."
>
> I was going to turn on schema MONITORING and then use GATHER_STALE to
> speed up the analyse command. Is this not the correct approach?

I think it rather depends what your goal is. Certainly using GATHER_STALE will speed up the length of time stats gathering takes, I'm not convinced that this is a good goal.

I think that what Mladen was on about was that the idea of monitoring and GATHER_STALE is that you only need to collect statistics on objects that have been subject to a significant amount of DML. The trouble with not subjecting the modification tracking to be part of the transaction is that you can radically overstate the amount of DML that has actually occurred leading to unnecessary analysis.

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.

Incidentally I'm wondering how a strategy of never gathering stats at all, but relying on dynamic sampling at a high level (probably 4) would work, my guess is it would break down in high-load systems, but it might help more normal systems with specific problem sqls because of the differences between dbms_stats and dynamic sampling. If anyone has looked at this idea it would be interesting to know what you found.  

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l