any over head with DBMS_STATS.ALTER_DATABASE_TABLE_MONITORING
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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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: a)The results are not corrected on rollback b)Concurrent changes to the count get lost c)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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: any over head with DBMS_STATS.ALTER_DATABASE_TABLE_MONITORING
Too bad Steve Adams' site is not available, cuz that's the place to be. He says it better than I can, so I've appended a bit of info from him. I'd suggest getting to Steve's site as soon as it's up. We have monitoring enabled on our 9.2.0.4 database, but it's not currently heavily used. But so far, it's been great. From Steve Adam's July 2000 newsletter (http://www.ixora.com.au/newsletter/2000_07.htm, line spacing below is mine): Despite the potential for improved statistics gathering, many DBAs have not yet adopted modification monitoring. One of the concerns that people have is that the monitoring might have a significant performance overhead. In fact, this is not the case. The modification counts are maintained in an efficient hash table is the SGA, and are updated without the protection of a latch (although the structure of the hash table itself is protected by the hash table modification latch). Even in heavy OLTP environments, the cost of maintaining the modification counts is likely to be less than 1% of additional CPU usage. However, because of the latch-free nature of the feature, the modification counts are not guaranteed to be accurate. Another source of potential inaccuracy is that if a transaction is rolled back, its changes to the modification counts are not rolled back as well. These inaccuracies have been allowed by Oracle to keep the performance overhead of this feature minimal. Therefore, you can use modification monitoring with confidence that it will not affect performance significantly, while giving you a very helpful indication of which tables may have stale statistics. --- [EMAIL PROTECTED] wrote: 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
