Re: Timing a Procedure/package using tkprof

From: Jonathan Wayne Ingram <jwingram_at_whale.st.usm.edu>
Date: 17 Mar 1995 14:29:55 GMT
Message-ID: <3kc6d3$ahf_at_server.st.usm.edu>


To: aaj_at_phantom.trl.OZ.AU (Tony Jambu) Subject: Re: Timing a Procedure/package using tkprof Newsgroups: comp.databases.oracle

In article <3k8cl8$h7o_at_newsserver.trl.OZ.AU> you wrote:

: Has anyone used tkprof to carry out performance tuning on packages and
: procedures out there?
 

: I am having trouble trying to find the total (elapsed) time spent in a
: procedure. Tkprof does not give the cumulative time spent executing the
: procedure. Has anyone found a was/method to achieve this?

Yes, it's vrey simple. Use the DBMS_Utility.Get_Time function. Either output the value returned via a DBMS_Output line or an insert to a dummy table. Do this in a dummy PL/SQL block that calls your stored procedure. Subtract the first value printed from the second, divide by 100, and (voila!) you now have a total run time to hundreth second precision. It's kludgy but accurate. No overhead seems to be associated (at least none that is measurable by this method) with calling a stored procedure.

: Also does anyone know why tkprof' summary totals does not include
: non-resursive time.

No idea.

: For example. If I had 2 procedures
 

: package.proc_A
: SQL_statment 1
: package.proc_B
: SQL_statement_2
 

: If it took 5 seconds each to execute statment 1 & 2 and say 2 seconds each
: to execute the other parts of the procedure, then the total elapsed time is

: (2+5) + (2+5) = 14 seconds
 

: Tkprof's summary totals only report 4 secods which is the non-recusive
: statements.
 

: Has anyone written their own utility to interpret the trace files?
 

: ta
: tony
 

: --
: _____ ________ / ___ |Tony Jambu, Database Consultant
: /_ _ /_ __ / |Wizard Consulting,Aust (ACN 065934778)
: /(_)/ )(_/ \_/(///(/_)/_( |CIS: 100250.2003_at_compuserve.com FAX: +61-3-2536173
: \_______/ |Email:TJambu_at_wizard.com.au PHONE: +61-3-2536385
Jonathan Ingram
jwingram_at_whale.st.usm.edu Received on Fri Mar 17 1995 - 15:29:55 CET

Original text of this message