Re: Measuring SQL execution time ?

On 12 Jun 1996 19:42:43 GMT, ceham_at_w3eax.umd.edu (Maurice De Vidts NE3S) wrote:

>Hi,
>
>I am interested in timing some SQL calls for a hardware comparison,
>and I am aware of the timing function in SQL. The problem is
>this is actual clocked time, from start to finish. It includes
>time the CPU may have used to do other things and serve other users.
>
>Is there any other way to measure CPU cycle associated with this call >?
>

Look at the tkprof utility (documented in the application developers guide).

If you set time_statistics=true in your init.ora and either - set sql_trace=true in init.ora
- issue "alter session set sql_trace=true" in applications

You can use tkprof on the generated trace files. Tkprof will generate output like the following:  

select *
from
 emp, dept where emp.deptno = dept.deptno  

call count cpu elapsed disk query current rows -------- ------- -------- --------- -------- -------- ------- ----------

Parse           1      0.08       0.18        9       55       4           0
Execute         1      0.00       0.00        0        0       0           0
Fetch           4      0.01       0.01        3        3       6          45
 
--------  -------  --------  --------- -------- -------- -------  ----------
total           6      0.09       0.19       12       58      10          45
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
     45    MERGE JOIN
      5      SORT (JOIN)
      5        TABLE ACCESS (FULL) OF 'DEPT'
     45      SORT (JOIN)
     45        TABLE ACCESS (FULL) OF 'EMP'
 

As you can see, you get a lot of information including cpu times spent during the various PHASES of query execution.

>
>I am also wondering if there is an easy way to simulate a large user load
>and make timing measurements "under load".
>

The only real way to do this is to use a Remote Terminal Emulator (RTE). This is what most vendors use to drive TPC-C/D benchmarks for example. You can use these to simulate hundreds/thousands of users.

>Any pointers, and sugestions will be appreciated !
>
>Maurice De Vidts
>ceham_at_w3eax.umd.edu

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Jun 21 1996 - 00:00:00 CEST