Re: Tracking query response time

lokp_at_tdbank.ca wrote:
>
> Hi, all..
>
> We're running a data warehouse database in our company but seems to have
> problem with tracking queries response times. Since each user can in fact
> submit a new query right after the completion of the previous one without
> logging off.
>
> Does Oracle keep track of how many queries being run and how long it took
> to complete?
>
> Is there a query estimator facility with Oracle??
>
> Is there something a user could run to see how "expensive" a query would
> be?
>
> Any input is greatly appreciated...
>
> Peggy

You can turn on tracing for a session with the statement:-

ALTER SESSION SET SQL_TRACE = TRUE; This will cause Oracle to log statistics for each sql statement performed by the current session (until trace is turned off or the session finishes) into a trace file. A human readable report can then be generated by running tkprof, ie:

tkprof <inputfile> <outputfile> explain=<user>/<password>

The 'explain=...' is optional, it will cause the execution plan for each sql statement to be included in the report. The location for the trace file and max size limit should be specified in the init.ora file for the database (parameters user_dump_dest and max_dump_file_size). Also, the timed_statistics parameter should be set to TRUE.

If desired, trace can also be turned on for the entire database by setting the sql_trace parameter in the init.ora file to TRUE and restarting the database. Beware though, this may generate a lot of trace output, as it will log statistics for ALL sql statements issued by ALL sessions active on the database. Each new session will result in a new trace file.

If you just want trace output for a particular sql, you could turn trace on immediately before the statement, and off again immediately afterwards, then run tkprof on the resulting trace file.

The report from tkprof shows the text of each sql, along with statistics for the parse, execute and fetch cycles - such as cpu time, elapsed time, no. of occurrences and no. of rows. The execution plan (if chosen) will show you if statements are using indexes or performing table scans etc.

Hope this helps.

-- 
Mike Biggin
DENR, South Australian Government
E-mail: mbiggin_at_denr.sa.gov.au
Phone: +61 8 8204 9217; Fax: +61 8 8204 9017

Unless explicitly attributed, the opinions expressed are personal
and not those of DENR or the South Australian Government.