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?
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;
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
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
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
If you want to
download working scripts to monitor response time, try the
Oracle script download collection.