RE: Performance impact of MONITORING and GATHER

From: Leng Kaing <Leng.Kaing_at_hsntech.com>
Date: Thu, 17 Mar 2005 10:30:23 +1100
Message-ID: <18D551B1B928FF47A65B2D91F705906A0123E41F@HSNDON-EX01.hsntech.int>

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.

Ta,

Leng.

Leng Kaing
Hansen Technologies
2 Frederick St; Doncaster VIC 3108
=20
Tel: +61-3-9840-3832
=20
=20
-----Original Message-----
From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com]=20 Sent: Wednesday, 16 March 2005 9:12 PM
To: Leng Kaing
Cc: Mladen Gogala; oracle-l_at_freelists.org Subject: 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,

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

>=20

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

=20
--=20
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

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

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

RE: Performance impact of MONITORING and GATHER_STALE