Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance impact of MONITORING and GATHER_STALE

RE: Performance impact of MONITORING and GATHER_STALE

From: Leng Kaing <>
Date: Thu, 17 Mar 2005 10:30:23 +1100
Message-ID: <>

Hi Niall,

Thanks for the clarification! Ok, now back on track. Yes, agreed. 10% is not necessarily the best rule of thumb but it's better than nothing I guess.



Leng Kaing
Hansen Technologies
2 Frederick St; Doncaster VIC 3108
Tel: +61-3-9840-3832
-----Original Message-----
From: Niall Litchfield []=20 Sent: Wednesday, 16 March 2005 9:12 PM
To: Leng Kaing
Cc: Mladen Gogala; Subject: Re: Performance impact of MONITORING and GATHER_STALE

On Wed, 16 Mar 2005 12:32:55 +1100, Leng Kaing <> wrote:
> Hi Mladen,

> Sorry, I don't quite understand. What do you mean when you say "It
> takes away any purpose from gathering statistics based on STALE


> 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=20 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

Received on Wed Mar 16 2005 - 18:36:22 CST

Original text of this message