From oracle-l-bounce@freelists.org Wed Feb 2 18:07:26 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j1307Qu3013134 for ; Wed, 2 Feb 2005 18:07:26 -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 j1307Iem013119 for ; Wed, 2 Feb 2005 18:07:18 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A9E4C6CD2A; Wed, 2 Feb 2005 18:06:21 -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 13941-07; Wed, 2 Feb 2005 18:06:21 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 21FD96CD2F; Wed, 2 Feb 2005 18:06:21 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: how to monitor the progress of inserts Date: Wed, 2 Feb 2005 15:04:33 -0800 Message-ID: <32EFAEA4102B7041842430B3B393857305E5F39B@sl-mail2a.spde.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: how to monitor the progress of inserts Thread-Index: AcUIxNIbdROTlIrlTaq5CwGnEPjT2AAtrFtA From: "Yen, Eric" To: , "Mladen Gogala" Cc: , "Niall Litchfield" , "Oracle-L (E-mail)" X-OriginalArrivalTime: 02 Feb 2005 23:04:34.0151 (UTC) FILETIME=[8F67F770:01C5097B] X-archive-position: 15713 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: EYen@sonypictures.com Precedence: normal Reply-To: EYen@sonypictures.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=none autolearn=ham version=2.60 X-Spam-Level: Here is what I use: -- import progress=20 select to_char(rows_processed, '999,999,999') as "COUNT", sysdate, sql_text from ( select rows_processed, sysdate, sql_text from v$sqlarea where sql_text like 'INSERT %INTO %' AND COMMAND_TYPE =3D2 AND OPEN_VERSIONS > 0 order by 1 desc )=20 where rownum =3D 1 Eric Yen -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Wolfgang Breitling Sent: Tuesday, February 01, 2005 5:09 PM To: Mladen Gogala Cc: MGogala@allegientsystems.com; 'Niall Litchfield'; Oracle-L (E-mail) Subject: Re: how to monitor the progress of inserts At 03:50 PM 2/1/2005, Mladen Gogala wrote: > > As Niall demonstrated so beautifully, there are no relational rules when > > it comes to v$ views (actually the x$ "tables" upon which the views are > > built). > >Yes, but then this statistics is meaningless. What does it mean to have >1000 inserts without any consequence to the table? You have to use the v$ views in the context they are meant for. This=20 particular table is meant to help dbms_stats to decide whether the=20 statistics are "stale" and should be refreshed. It is NOT an audit view. If=20 you talk about useless views this is a truly useless view, even if it=20 didn't suffer from the demonstrated shortcoming. What does it mean to have=20 1000 inserts, even if those inserts actually made into the table??=20 Statistics could be "stale" if much less than 10% of the content has=20 "changed" and for other tables nothing changes materially in the statistics=20 even if you exchange 100% for new data. I would never base statistics=20 gathering on the content of this view. PS. there are people who are very knowledgeable about Oracle who consider=20 the data in many of the "cherished" v$ views as useless, meaningless, even=20 misleading. >I wouldn't generalize just like that. I don't find V$SESSION_LONGOPS very=20 >useful, >period. It has never helped me with anything. It most certainly did not >help me >to see how much is left of any large job. While V$ tables in general are=20 >remarkably >useful, this particular one is useless. Obviously our mileages vary. I use it frequently to see the progress of=20 full scans and even calculate the estimated finish time. I generally find=20 it more precise than Microsoft's progress bar. And as I said. if the=20 progress is not linear but slows down ( who cares if it speeds up :-) )=20 then that is a good piece of information as well to go and find the cause=20 for the slowdown. Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com=20 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l