From oracle-l-bounce@freelists.org Fri Sep 9 13:45:03 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j89Ij38o024539 for ; Fri, 9 Sep 2005 13:45:03 -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 j89IixIP024518 for ; Fri, 9 Sep 2005 13:45:00 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D14331EB3F1; Fri, 9 Sep 2005 13:44:52 -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 20625-03; Fri, 9 Sep 2005 13:44:52 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4AA411E9BF4; Fri, 9 Sep 2005 13:44:52 -0500 (EST) Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.326 Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: High "transaction rollbacks" value in v$sysstat Date: Fri, 9 Sep 2005 11:44:35 -0700 Message-ID: <04DDF147ED3A0D42B48A48A18D574C450236157F@NT15.oneneck.corp> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: High "transaction rollbacks" value in v$sysstat thread-index: AcW0z87L+R0CXVGdTn+XaSnINPVIGAAAJXHgACdTkAAFrom: "Allen, Brandon" To: X-archive-position: 25242 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Brandon.Allen@OneNeck.com Precedence: normal Reply-To: Brandon.Allen@OneNeck.com X-list: oracle-l From: oracle-l-bounce@freelists.org X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.7 required=5.0 tests=AWL,BAYES_00,NO_REAL_NAME autolearn=no version=2.63 Upon further examination, I don't think the description provided in the Metalink note below could explain what I'm seeing, because if that were the case, then I would expect a large number of 'enqueue waits' accompanying the 'transaction rollbacks', but I'm not seeing that. Here are the stats from the worst one hour period yesterday: Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ enqueue waits 295 0.1 0.0 transaction rollbacks 389,303 108.1 2.1 rollback changes - undo records a 394,309 109.5 2.1 user commits 189,364 52.6 1.0 user rollbacks 0 0.0 0.0 Can anyone think of an explanation for this? How can I have 389,303 transaction rollbacks with 0 user rollbacks??? I'll open a TAR and let y'all know if/when I figure it out, but if you have any ideas, please let me know. Thanks! -----Original Message----- From: Allen, Brandon Sent: Thursday, September 08, 2005 4:58 PM To: 'Mladen Gogala' Cc: Lou Fangxin; oracle-l@freelists.org Subject: RE: High "transaction rollbacks" value in v$sysstat I found this explanation on Metalink, which might explain it: ---------------------------------------------------------------------------- From: Andrew Allen 26-Apr-05 15:29 Subject: Re : User rollbacks higher then user commits Could be blocked updates causing the rollbacks. I do not know the source of the stat in the statspack report, but I will bet that this is it. You see, when you do an update -- especially when more than one row is affected, your transaction could rollback and restart (possibly several times) before it completes. Oracle does not take locks on every row before it does the update. Instead it takes the row lock as it does the update on that row, so if you are updating more than one row in a query, you may successfully change the first few rows, then encounter a row that is locked. In this case your update will rollback and then attempt to do the update again once the lock is cleared. Then next pass may update all the rows or it may encounter another lock further down the line and rollback/restart again. All this happens behind the scenes so you never know about it. HTH, Andrew ---------------------------------------------------------------------------- Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. -- http://www.freelists.org/webpage/oracle-l