Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 26182 invoked from network); 23 Nov 2007 00:08:27 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 23 Nov 2007 00:08:26 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 27C497C0147; Fri, 23 Nov 2007 01:08:27 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09788-03; Fri, 23 Nov 2007 01:08:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 922707C0630; Fri, 23 Nov 2007 01:08:26 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 23 Nov 2007 00:21:32 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 18C2B7C0495 for ; Fri, 23 Nov 2007 00:21:32 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04238-08 for ; Fri, 23 Nov 2007 00:21:31 -0500 (EST) Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.237]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B5FB57BFEC5 for ; Fri, 23 Nov 2007 00:21:31 -0500 (EST) Received: by wx-out-0506.google.com with SMTP id i27so1238106wxd for ; Thu, 22 Nov 2007 21:21:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=kDUyK5KJx89SbnqkMrOP2OAey4+wDBiZaS53uMst/78=; b=dW1u+yhqjmB9RTqInt03apwS7f9mnS3VE65iQiy3Hp97Ee0NGfqBckPi3mocSoJDZFqCNk2bk2GxbmHT68n+yEKwmQ+OXmEYVmJiMEcad34ybMP4BZTkeIx9O9IiZkJxiZyxLMU0045A+nziRal+2tJ6pxXA2akKsHMbzT7exZM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=H4+oXeoZcJRgZsyJxHpiGCD5p7BRFty49oKBjP8yQ//jphGAQz3s5FccYpbMuZP8sCoch3C55PVtGkKJ8cAFvVRNT2FhiN544ilOEgHzdKwPV+XKgUTx4Mk+tICga2ZJKEqFeCreyOzRPTVpvUsdOqLqBL+WOPBrzibT5Ehu/cI= Received: by 10.70.49.1 with SMTP id w1mr535680wxw.1195795290507; Thu, 22 Nov 2007 21:21:30 -0800 (PST) Received: by 10.90.94.20 with HTTP; Thu, 22 Nov 2007 21:21:30 -0800 (PST) Message-ID: Date: Fri, 23 Nov 2007 00:21:30 -0500 From: "Vlad Sadilovskiy" To: tanel.poder.003@mail.ee Subject: Re: tkprof elapsed time Cc: oracle-l In-Reply-To: <002501c82d21$04480aa0$3201a8c0@windows01> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7755_28903515.1195795290495" References: <002501c82d21$04480aa0$3201a8c0@windows01> X-archive-position: 3445 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: vlovsky@gmail.com Precedence: normal Reply-to: vlovsky@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_7755_28903515.1195795290495 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Tanel, Thanks for taking time commenting on this issue and for the insight. I wouldn't argue if you call commit call "*cursor-less call*". And you are most probably right, it is not the same as seeing cursor slot allocated under which there are waits but there are no call information. The main point I was trying to make that in the situation with missing call information Tkprof or any other utility doesn't have a good choice when assigning waits. It is actually fortunate if they can at least gather these waits under one of the ancestors. In the scope of suggested by you ASSM bug, the "*log file sync*" would be gathered under cursor for *insert *statement and would be properly reflected in this cursor "*elapsed time*". Which we don't see in the case that Alex provided. Hopefully, you see the difference. Vlad Sadilovskiy Oracle Database Tools Web site: http://www.fourthelephant.com Blog: http://vsadilovskiy.wordpress.com . On 11/22/07, Tanel Poder wrote: > > Vlad, I would rather name it "cursorless call" as sqlplus'es autocommit > is a separate OPI call which doesn't parse/execute any cursors. > > > On a loosely related topic - there are few ASSM bugs which can cause a > plain insert (without commits) to extensively wait on log file sync events. > This is because lots of unsuccessful ASSM recursive space management > transactions, which have to be rolled back - and rollbacks also need to wait > for log file sync before returning. > > The symptom would be lots of waiting for log file syncs, high "transaction > rollbacks" statistic increase without any accompanying "user rollbacks". > > One such bug is bug # 4664513 , but there's more. > -- > Regards, > Tanel Poder > http://blog.tanelpoder.com > > > ------------------------------ > *From:* oracle-l-bounce@freelists.org [mailto: > oracle-l-bounce@freelists.org] *On Behalf Of *Vlad Sadilovskiy > *Sent:* Thursday, November 22, 2007 03:28 > *To:* amonte > *Cc:* oracle-l > *Subject:* Re: tkprof elapsed time > > > Alex, > > This insert statement should not produce any "*log file sync*" waits. This > is the *commit's* job :). SQL*Plus autocommit does not create a "commit" > statement as such. The "action" for this "commit" would not have associated > cursor slot. That's what I call " *call-less*" cursor. However, it would > cause waits to occur with the #0 cursor or in come cases the slot number of > a call that would has just been completed. Tkprof usually associates these > waits with the call that follows. > > Try reproducing it by issuing "COMMIT" explicitly. > Regards, > > Vlad Sadilovskiy > Oracle Database Tools > Web site: http://www.fourthelephant.com > Blog: http://vsadilovskiy.wordpress.com > > > > ------=_Part_7755_28903515.1195795290495 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Tanel,
 
Thanks for taking time commenting on this issue and for the insight.
 
I wouldn't argue if you call commit call "cursor-less call". And you are most probably right, it is not the same as seeing cursor slot allocated under which there are waits but there are no call information.
 
The main point I was trying to make that in the situation with missing call information Tkprof or any other utility doesn't have a good choice when assigning waits. It is actually fortunate if they can at least gather these waits under one of the ancestors. In the scope of suggested by you ASSM bug, the " log file sync" would be gathered under cursor for insert statement and would be properly reflected in this cursor "elapsed time". Which we don't see in the case that Alex provided. Hopefully, you see the difference.
 
Vlad Sadilovskiy
Oracle Database Tools
Web site: http://www.fourthelephant.com
Blog: http://vsadilovskiy.wordpress.com

 
.
 
On 11/22/07, Tanel Poder <tanel.poder.003@mail.ee> wrote:
Vlad, I would rather name it "cursorless call" as sqlplus'es autocommit is a separate OPI call which doesn't parse/execute any cursors.
 
 
On a loosely related topic - there are few ASSM bugs which can cause a plain insert (without commits) to extensively wait on log file sync events. This is because lots of unsuccessful ASSM recursive space management transactions, which have to be rolled back - and rollbacks also need to wait for log file sync before returning.
 
The symptom would be lots of waiting for log file syncs, high "transaction rollbacks" statistic increase without any accompanying "user rollbacks".
 
One such bug is bug # 4664513 , but there's more.
--
Regards,
Tanel Poder
http://blog.tanelpoder.com
 

From: oracle-l-bounce@freelists.org [mailto: oracle-l-bounce@freelists.org] On Behalf Of Vlad Sadilovskiy
Sent: Thursday, November 22, 2007 03:28
To: amonte
Cc: oracle-l
Subject: Re: tkprof elapsed time

 
Alex,
 
This insert statement should not produce any "log file sync" waits. This is the commit's job :). SQL*Plus autocommit does not create a "commit" statement as such. The "action" for this "commit" would not have associated cursor slot. That's what I call " call-less" cursor. However, it would cause waits to occur with the #0 cursor or in come cases the slot number of a call that would has just been completed. Tkprof usually associates these waits with the call that follows.

Try reproducing it by issuing "COMMIT" explicitly.

Regards,

Vlad Sadilovskiy
Oracle Database Tools
Web site: http://www.fourthelephant.com
Blog: http://vsadilovskiy.wordpress.com


 

------=_Part_7755_28903515.1195795290495-- -- http://www.freelists.org/webpage/oracle-l