RE: Performance impact of MONITORING and GATHER_STALE

  • From: "Leng Kaing" <Leng.Kaing@xxxxxxxxxxx>
  • To: "Mladen Gogala" <mgogala@xxxxxxxxxxxxxxxxxxxx>
  • Date: Wed, 16 Mar 2005 12:32:55 +1100

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?

On a related but slightly diverting topic... I understand that turning
on MONITORING will write data to some dictionary tables. What if we
don't do the GATHER_STALE and just turn on MONITORING just to see how
much DMLs are generated, and if indexes are used. How do we control the
growth of the dictionary tables without having to do a



Leng Kaing
Hansen Technologies
2 Frederick St; Doncaster VIC 3108
Tel: +61-3-9840-3832

-----Original Message-----
From: Mladen Gogala [mailto:mgogala@xxxxxxxxxxxxxxxxxxxx]=20
Sent: Wednesday, 16 March 2005 2:27 AM
To: Leng Kaing
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Performance impact of MONITORING and GATHER_STALE

Leng Kaing wrote:

>Hi guys,
>Apologies if I'm revisiting a beaten path but I've tried to search the
>archive, metalink and google and couldn't find my answer (or it may
>been hiding). So I'll ask the question (again)...
>What is the performance impact of turning on MONITORING at the table
>level? Ie. ALTER TABLE x MONITORING. Will it have a negative impact on
>our production system?=20
> =20
Leng, I am running on Solaris, all my production tables are in=20
the monitoring mode and there
are no adverse effects on my production database. The explanation is=20
When tables are in monitoring mode (default in 10g), the only things=20
that are update are memory tables (X$).
As Mr. Litchfield has shown, those tables are not protected by the=20
transaction mechanisms, which makes
updating them much cheaper. Unless you already have CPU bound system,=20
you will not suffer from performance
degradation if you enable monitoring for 250 tables, like I did.
It also takes away any purpose  from gathering statistics based on STALE

status. Niall has demonstrated  that SYS.DBA_TAB_MODIFICATIONS, the=20
entity behind "dbms_stats.gather_stale"  will record inserts, even if
are eventually rolled back. A single failed load with SQL*Loader can=20
have no effect whatsoever on the table itself,
but cant invalidate your statistics and trigger an expensive DBMS_STATS
Unrelated to that, I've been warned about the mistake I was consistently

making in our communications
and I have to humbly apologize.

Mladen Gogala
Oracle DBA
Ext. 121


Other related posts: