RE: Millisecond timer in PL/SQL

If the point of this excersize is timing calls, wouldn't a trace be a good place to start? Or are the times in the trace not fine-grained enough?

Just a thought...

Rich

> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > Sent: Friday, September 30, 2005 10:31 AM
> > Subject: Millisecond timer in PL/SQL
> >
> > All,
> >
> > 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
> >
> > chains".
> >
> > 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.
> >
> > Thanks,
> >
> > Scott Hutchinson
> > Interact Analysis Ltd

--
http://www.freelists.org/webpage/oracle-l