From oracle-l-bounce@freelists.org Wed Mar 16 10:02:45 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2GG2hJK014772 for ; Wed, 16 Mar 2005 10:02:43 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j2GG2Xem014716 for ; Wed, 16 Mar 2005 10:02:43 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 59D3D87083; Wed, 16 Mar 2005 10:00:37 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 19286-03; Wed, 16 Mar 2005 10:00:37 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C87D385459; Wed, 16 Mar 2005 10:00:36 -0500 (EST) X-Server-Uuid: E506CC0A-1472-4699-B4CF-B6BEE0793AD9 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: RE: Performance impact of MONITORING and GATHER_STALE Date: Wed, 16 Mar 2005 08:58:00 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Performance impact of MONITORING and GATHER_STALE Thread-Index: AcUpc2wuoqmxhBg3TBC4fcS3tjthpgAUXrhwABy1EFA= From: "Dirschel, Steve" To: Leng.Kaing@hsntech.com, oracle-l@freelists.org X-OriginalArrivalTime: 16 Mar 2005 14:58:00.0957 (UTC) FILETIME=[8C4192D0:01C52A38] X-WSS-ID: 6E2696F21T41622087-01-01 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-archive-position: 17358 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Steve.Dirschel@bestbuy.com Precedence: normal Reply-To: Steve.Dirschel@bestbuy.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: You would speed up analyze by not analyzing all tables- only tables with ~10% data change. The actual analyzing of the table is the same if you use monitoring or don't. If you turn on table monitoring and don't gather stale all that happens is the insert/update/delete statistics in DBA_TAB_MODIFICATIONS don't get zeroed out. When you analyze a table (any way you analyze- ANALYZE table, DBMS_STATS) the insert/update/delete statistics get set to 0. Monitoring tables only related to tracking insert/updates/deletes against the table. It's not related to index usage. There's a monitor index usage option that will let you know if indexes get used or not (9i+). Even when that's turned on- it flags an internal table that the index was used- it's not like it inserts a row into a dictionary table every time it's used. You don't need to worry about the growth of dictionary tables with either MONITORING feature. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Leng Kaing Sent: Tuesday, March 15, 2005 7:33 PM To: Mladen Gogala Cc: oracle-l@freelists.org Subject: RE: Performance impact of MONITORING and GATHER_STALE 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 GATHER_SCHEMA_STATS?=3D20 Ta, Leng. ---------------------------------------------- Leng Kaing Hansen Technologies 2 Frederick St; Doncaster VIC 3108 =3D20 Tel: +61-3-9840-3832 =3D20 =3D20 -----Original Message----- From: Mladen Gogala [mailto:mgogala@allegientsystems.com]=3D20 Sent: Wednesday, 16 March 2005 2:27 AM To: Leng Kaing Cc: oracle-l@freelists.org Subject: Re: Performance impact of MONITORING and GATHER_STALE Leng Kaing wrote: >=3D20 >Hi guys, > >=3D20 > >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 have >been hiding). So I'll ask the question (again)... > >=3D20 > >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?=3D20 > =3D20 > Leng, I am running 9.2.0.5 on Solaris, all my production tables are in=3D20 the monitoring mode and there are no adverse effects on my production database. The explanation = is=3D20 simple: When tables are in monitoring mode (default in 10g), the only = things=3D20 that are update are memory tables (X$). As Mr. Litchfield has shown, those tables are not protected by the=3D20 transaction mechanisms, which makes updating them much cheaper. Unless you already have CPU bound = system,=3D20 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=3D20 entity behind "dbms_stats.gather_stale" will record inserts, even if those are eventually rolled back. A single failed load with SQL*Loader = can=3D20 have no effect whatsoever on the table itself, but cant invalidate your statistics and trigger an expensive DBMS_STATS job. Unrelated to that, I've been warned about the mistake I was consistently making in our communications and I have to humbly apologize. --=3D20 Mladen Gogala Oracle DBA Ext. 121 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l