RE: Performance impact of MONITORING and GATHER_STALE
- From: "Leng Kaing" <Leng.Kaing@xxxxxxxxxxx>
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Thu, 17 Mar 2005 10:30:23 +1100
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@xxxxxxxxx]=20
Sent: Wednesday, 16 March 2005 9:12 PM
To: Leng Kaing
Cc: Mladen Gogala; oracle-l@xxxxxxxxxxxxx
Subject: Re: Performance impact of MONITORING and GATHER_STALE
On Wed, 16 Mar 2005 12:32:55 +1100, Leng Kaing <Leng.Kaing@xxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
