From oracle-l-bounce@freelists.org Wed Feb 2 14:50:43 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j12KohLf022517 for ; Wed, 2 Feb 2005 14:50: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 j12Kohem022512 for ; Wed, 2 Feb 2005 14:50:43 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 95D296C5AA; Wed, 2 Feb 2005 14:49:47 -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 27913-07; Wed, 2 Feb 2005 14:49:47 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1108A6C2DB; Wed, 2 Feb 2005 14:49:47 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=tbvISt72r2IaotrNBUZ3PnGsFrU2FX6PLyqba0lK58JdLYFZXdjxz5mQppTAE2l2LGn67pN9qx9ZXnqZWg6XFBBMO38U1IKLDK6DJOoxQcDNxGbxklFlbjDiDAu7aAGNSI2MAQcwwfnMC6nj3kz+6Tqs/WlB6YYbDY11WX0ID6U= Message-ID: <7765c897050202114826016ae4@mail.gmail.com> Date: Wed, 2 Feb 2005 19:48:07 +0000 From: Niall Litchfield To: mgogala@allegientsystems.com Subject: Re: how to monitor the progress of inserts Cc: elkinsl@flash.net, "Oracle-L (E-mail)" In-Reply-To: <4200409A.3020700@allegientsystems.com> Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: <4200409A.3020700@allegientsystems.com> X-archive-position: 15700 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: niall.litchfield@gmail.com Precedence: normal Reply-To: niall.litchfield@gmail.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: On Tue, 01 Feb 2005 21:53:14 -0500, Mladen Gogala wrote: > Larry Elkins wrote: > > >I've found V$SESSION_LONGOPS to be very useful, especially if you take the > >time to watch it while a pig of a query is running you can track how much > >time is being spent in each part of a query. For example you might notice a > >lot of time is spent in a sort merge phase, or on a full table scan, etc. > > > > > > Now, this is a neat idea that I havent thought about before. Thanks. You should use a GUI occasionally - oh alright I'll stop swearing. So far as I can tell (and lets face it I hate OEM) V$SESSION_LONGOPS is only used in one place in the java OEM for 9i. It isn't used to say how long the whole query will take, but it is used in conjunction with an explain plan tab (that I really hope is a query on V$SQL_PLAN) to explain how how long the *current stage* of the execution plan will take. I've aborted jobs that are scheduled to take another hour just on the intermediate sort/merge stage because, well it was a great tip off that I'd screwed up on the sql. I don't know of any other way to get this info *while the query is still running*. 10046 will tell me all the gory details, but only *after* I have suffered the consequences (or been paid the overtime for my own bad estimate - you decide). longops can tell me I screwed up big time* mid error*. Previously it was obviously the fault of a) the network or b) the desktop build team Niall > > >I have seen occasional cases where the TOTALWORK column (estimated number of > >blocks for example) was off, so the estimate of time remaining was off. This > >has primarily been in the case of hash and sort joins, so you end up seeing > >the time remaining start going negative as the SOFAR value begins to exceed > >to TOTALWORK value. That's frustrating ;-) > > > > > I noticed that. I somehow stopped trusting that table when I saw the > negative time for the first time. > The next thing that I expected to see in the OPNAME column was "Being > beamed up, by Scottie". > > >But when dealing with large complex queries it can be very helpful in > >tracking how much time is being spent in the various operations -- e.g. hash > >join, sort output, an fts, combinations of those, etc. > > > > > > Thanks again for this suggestion. I haven't thought of using the table > for optimization purposes. > I was using it for "are we there yet" questions. The answer > approximately as precise as the usual "soon". > > -- > Mladen Gogala > Oracle DBA > > -- > http://www.freelists.org/webpage/oracle-l > -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com -- http://www.freelists.org/webpage/oracle-l