Re: any over head with DBMS_STATS.ALTER_DATABASE_TABLE_MONITORING

The number of rows affected by an SQL statement is something that has been available to Oracle for a long time. Monitoring just records that number in a memory structure.

I'd guess the memory structure is a hash table, and there are no latches protecting it (so I've heard, and I can't see any in x$ksllt) so the memory update is (a) rapid (b) subject to lost data.

At regular intervals (3 hours for older versions, 15 minutes for newer) smon copies the data from memory to the mon_mod$ table.

The overhead is small.

But:

1. The results are not corrected on rollback
2. Concurrent changes to the count get lost
3. There was at least one bug relating to partitioned tables with large numbers of partitions reported on metalink some time ago.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG - v$ and x$  March 2004 Hotsos Symposium - The Burden of Proof  March 2004 Charlotte NC OUG - CBO Tutorial  April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 22, 2004 1:44 PM

> Especially on high transaction tables? I dont have any numbers for
transactions/second since we are not live. Any known issues? how does the monitoring work? Does it use an internal trigger and then use SQL to write the data? or does the monitoring data bypass the SQL layer and get written directly to the block?
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).