From oracle-l-bounce@freelists.org Mon Apr 18 21:42:21 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3J2gL76023170 for ; Mon, 18 Apr 2005 21:42:21 -0500 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 j3J2gL4Z023166 for ; Mon, 18 Apr 2005 21:42:21 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 98473184BD6; Mon, 18 Apr 2005 20:40:03 -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 21742-04; Mon, 18 Apr 2005 20:40:03 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 20168184B92; Mon, 18 Apr 2005 20:40:03 -0500 (EST) Message-ID: <003301c54481$139acff0$54369c18@vv4xpaygpndtkd> From: "Ranko Mosic" To: "sol beach" Cc: , References: <041820050056.12602.426305BD000CD45C0000313A2205886360079D9A00000E09A1020E979D@comcast.net> <40a16b36050417181841ee051f@mail.gmail.com> Subject: Re: Subject: Batch load freezes every couple of minutes Date: Mon, 18 Apr 2005 21:42:41 -0400 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-archive-position: 18582 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mosicr@rogers.com Precedence: normal Reply-To: mosicr@rogers.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=ham version=2.63 Isn't maxtrans of 255 ( default )sufficient ? ----- Original Message ----- From: "sol beach" To: Cc: ; Sent: Sunday, April 17, 2005 9:18 PM Subject: Re: Subject: Batch load freezes every couple of minutes >Instance runs 20 parallel load processes You might be encountering enqueue waits. Typically INITRANS=1 for tables & 2 for indexes (YMMV). Unless & until the session holding the Interested Transaction Lock releases it, other sessions will quietly get into line waiting for it to be released. This is NOT a deadlock & won't be reported as one. HTH & HAND! On 4/17/05, ryan_gaffuri@comcast.net wrote: > Please provide your definition of 'hang'? To see if your processes are still running look in v$sess_io, if the values there are incrementing, then processing is still going on. > you need to find out what the processes are doing at the point that they are freezing? Best way is to get a 10046 trace of one of the processes dumped to a file. Another alternative is to use statspack of before the 'hang' and then another snapshot afterward and look at what sql is processing. > > you mentioned that the times that you have problems are when you have lock waits. you could have 1 process doing something for a long time and aquiring a lock on a row. the rest of the processes require that row and have to wait for a commit or rollback. > > the v$ tables(other than a quick look at v$sess_io) probably won't be helpful here. you need to know exactly what is going on when this occurs. > > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l