Question:
I want to see the response time for my SQL statement and see
the run time. What tools
does Oracle provide to see how long a query runs?
Answer:
For seeing the elapsed time for an individual query, you can
see individual query response time in SQL*Plus with the “set
timing on” command.
SQL> set timing on;
SQL> select stuff from mytab;
Elapsed: 00:00:02.82
For DBA’s, Oracle
has several tools for
measuring system-wide response time using v$sysmetric,
v$active_session_history, v$sqlarea and v$sysmetric_summary.
You can also query v$sql for the
elapsed_time and
executions columns for
average SQL response time.
·
Application response time:
Within a working application, you can measure response time
with the dbms_support
package to set trace in the session that your application is running
in, and then use tkprof
utility to process the resulting trace file.
·
PL/SQL response time:
If you are using PL/SQL, you can use the
dbms_utility package, the
dbms_utility.get_time and
dbms_utility.set_time
procedures.
·
SQL*Forms response time:
You can also measure transaction
response time in SQL*Forms
using the logs.
Additional information on Oracle response time components is
available
HERE.
If you want to
download working scripts to monitor response time, try the
Oracle script download collection.
