Re: Measuring query performance - Newbie

gabby97_at_my-deja.com wrote:
>
> I would like to measure the performance of a SQL statement using
> SQL/Plus. I would like to measure the time it took to execute the query
> and not the time it took for the query to display in SQL/Plus. How do I
> do that?

You have to use tkprof for more detailed information. Step 1:
Set the paramter
timed_statistics=yes
in the init file of the database.

Step2:
Insert the line:
ALTER SESSION SET SQL_TRACE TRUE;
before your SQL statement and run it.

Step3:
Find out where the trace file is located: select value from v$parameter where name='user_dump_dest';

Step4:
Find the trace file in the directory. Usually it is the latest one.

Step5:
Run tkprof:

tkprof <trace_file> <output_file> sort=execpu sys=no explain=your_connect_string

You will find now your slowest statement at the top in the <output_file>. If your using Winooze it is probably tkprof80.

tkprof has many more parameters, the one I showed you show also the execution plan of the query. This is very usefull for tuning.

Have fun

Kai

--
$f

Unix, WinNT and MS-DOS. The Good, The Bad and The Ugly. Kai Poitschke MailTo:kai.poitschke[at]computer.org Date/Time: $d/$t Received on Mon Sep 13 1999 - 17:46:19 CDT