Millisecond timer in PL/SQL

  • From: scott.hutchinson@xxxxxxxxxxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 30 Sep 2005 15:31:06 +0100


I'm looking for a way to measure the elapsed time (in milliseconds) of 
executing a bunch of functions within a PL/SQL package.  I've written a "timer" 
function that records this by using SYSTIMESTAMP, however is causes the 
sessions to spend a significant amount of time waiting on "cache buffers 

Coding "w_date := SYSTIMESTAMP;" will result in a recursive "SELECT 
SYSTIMESTAMP FROM DUAL", and this is the sql being executed by sessions waiting 
on this latch (from v$session_wait and v$sqlarea).

Does anyone have suggestions for ways that I can improve this?

I have 50 concurrent sessions (batch jobs) running this, and each will call 
the "timer" function about 60 times per second.  Originally I used V$TIMER, 
however the HSECS from this was not granular enough.


Scott Hutchinson
Interact Analysis Ltd

::This message sent using the free Web Mail service from

Other related posts: