Oracle - timed sampling from v$session_longops

Question

I am trying to track performance on some procedures that run too slow (and seem to keep getting slower). I am using v$session_longops to track how much work has been done, and I have a query (sofar/((v$session_longops.LAST_UPDATE_TIME-v$session_longops.start_time)*24*60*60)) that tells me the rate at which work is being done.

What I'd like to be able to do is capture the rate at which work is being done and how it changes over time. Right now, I just re-execute the query manually, and then copy/paste to Excel. Not very optimal, especially when the phone rings or something else happens to interrupt my sampling frequency.

Is there a way to have script in SQL*Plus run a query evern n seconds, spool the results to a file, and then continue doing this until the job ends?

(Oracle 10g)

Answer 1

If you can live with running PL/SQL instead of a SQL*Plus script, you could consider using the Oracle Scheduler. See chapters 26, 27, and 28 of the Oracle Database Administrator's Guide.

Answer 2

Tanel Poder's snapper script does a wonderful job of actively monitoring performance. It has parameters for

<seconds_in_snap> - the number of seconds between taking snapshots
<snapshot_count>  - the number of snapshots to take ( maximum value is power(2,31)-1 )

It uses PL/SQL and a call to DBMS_LOCK.SLEEP