Re: Performance impact of MONITORING and GATHER_STALE
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: Leng.Kaing@xxxxxxxxxxx
- Date: Wed, 16 Mar 2005 10:12:19 +0000
On Wed, 16 Mar 2005 12:32:55 +1100, Leng Kaing <Leng.Kaing@xxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
