Oracle query execution time

oracle sqlplus execution
user429743 picture user429743 · Aug 24, 2010 · Viewed 120.9k times · Source

I would like to get the query execution time in Oracle. I don't want the time Oracle needs to print the results - just the execution time.

In MySQL it is easy to get the execution time from the shell.

How can I do this in SQL*Plus?

Answer

Adam Musch picture Adam Musch · Aug 24, 2010

One can issue the SQL*Plus command SET TIMING ON to get wall-clock times, but one can't take, for example, fetch time out of that trivially.

The AUTOTRACE setting, when used as SET AUTOTRACE TRACEONLY will suppress output, but still perform all of the work to satisfy the query and send the results back to SQL*Plus, which will suppress it.

Lastly, one can trace the SQL*Plus session, and manually calculate the time spent waiting on events which are client waits, such as "SQL*Net message to client", "SQL*Net message from client".

Related questions

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More
How do I spool to a CSV formatted file using SQLPLUS?

I want to extract some queries to a CSV output format. Unfortunately, I can't use any fancy SQL client or any language to do it. I must use SQLPLUS. How do I do it?

Read More
How to display databases in Oracle 11g using SQL*Plus

With help of this command show databases; I can see databases in MySQL. How to show the available databases in Oracle?

Read More