Measure run time for Oracle SQL

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.