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

I'm not sure if Solaris has the 'time' command, but here is how I do it under Linux:

bash#: time sqlplus -s user/pass_at_SID @<Script>

You have to put the sql in a script, but here is what the output looks like:

0.50user 0.10system 0:10.85elapsed 5%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (1152major+743minor)pagefaults 0swaps

On Mon, 05 Apr 1999, Joe Condle wrote:
>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

--
To segfault is human, to bluescreen is moronic.

Matthew
matthew_at_mattshouse.com
http://www.mattshouse.com Received on Wed Apr 07 1999 - 11:46:01 CDT