Re: How to time a SQL script and return the time in a variable

Joe Condle wrote in message <37091462.97F512A6_at_mars-systems.com>...
>I am running Oracle version 8 for solaris. All I want to do is time an
>sql script. After the script completes I would like to get the time it
>took to run and then use it in a if statement to mail me if it took
>longer than a specified time. I am relatively new to this. The
>following is what I have come up with so far. I want to get the
>starttime and subtract it from the end time. When using a PL/SQL block
>in sqlplus how are variable passed.? The part between the dotted lines
>is the original script before I started experimenting with the PL/SQL
>block to add the timing.
>
>sqlplus user/user >/dev/null <<EOF
>
>DECLARE
> CURSOR c_time IS
> SELECT sysdate
> FROM dual;
>
> v_time c_time%ROWTYPE;
> starttime DATE;
>
>BEGIN
> OPEN c_time;
> FETCH c_time INTO v_time;
>
> if c_time%FOUND THEN
> starttime := sysdate;
> end if;
>----------------------------------------------------------------------
> spool JOE.tmp
> SELECT v.patienttype, v.unit, v.domain, v.dischargedate, v.admitdate,
> p.lname||', '||p.fname||' '||p.mname AS name,
>p.mrn,v.attdocname
> FROM visit v, patient p
> WHERE p.acctid = v.acctid
> AND p.source = v.source
> AND trunc(v.dischargedate) = trunc(sysdate-1)
> AND v.domain IN ( 'PUH', 'SSH', 'BVH', 'SMH' )
> AND v.patienttype IN ('I', 'S')
> AND v.attdocno IN ( '039175','001356','010900',
>'019457','002062','001236' )
> ORDER BY domain, patienttype, attdocname, name;
> spool off
>--------------------------------------------------------------------------
> spool JOEtime.tmp
> SELECT sysdate-starttime FROM DUAL;
> spool off
>END;
>.
>/
>
>--
>Joseph P. Condle Jr.
>Senior Systems Engineer
>condle_at_mars-systems.com
>Medical ARchival Systems, Inc. (MARS)
>3500 Victoria Street Pgh. Pa. 15261 USA
>Office 412-383-1379
>Fax 412-647-9661

You can use DBMS_UTILITY.GET_TIME for delta-times in 1/100 seconds.

Finn Received on Tue Apr 06 1999 - 03:41:19 CDT