RE: tkprof elapsed time

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 <http://blog.tanelpoder.com/>  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_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 <http://www.fourthelephant.com/> Blog: http://vsadilovskiy.wordpress.com <http://vsadilovskiy.wordpress.com>  

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 22 2007 - 10:02:00 CST