33

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?

Beryllium
  • 12,808
  • 10
  • 56
  • 86
user429743
  • 331
  • 1
  • 3
  • 3

5 Answers5

28

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".

Max Nanasy
  • 5,871
  • 7
  • 33
  • 38
Adam Musch
  • 13,286
  • 2
  • 28
  • 32
22

Use:

set serveroutput on
variable n number
exec :n := dbms_utility.get_time;
select ......
exec dbms_output.put_line( (dbms_utility.get_time-:n)/100) || ' seconds....' );

Or possibly:

SET TIMING ON;

-- do stuff

SET TIMING OFF;

...to get the hundredths of seconds that elapsed.

In either case, time elapsed can be impacted by server load/etc.

Reference:

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Hi, thanks for a good answer. I would like just notice my issue that I had with the first solution. If select lasted less than 1 sec. I received sth. like e.g. ".65 seconds....'. Fixed line looks like `exec dbms_output.put_line(TO_CHAR(( DBMS_UTILITY.GET_TIME - :n ) / 100, 'FM999990.00') || ' seconds....' );` and result has zero at the beginning ;) Cheers! – Gandalf the Gay Dec 03 '18 at 21:18
11
select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elapsed from v$sql
  order by LAST_LOAD_TIME desc

More complicated example (don't forget to delete or to substitute PATTERN):

select * from (
  select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' as TIME,
         MODULE, SQL_TEXT from SYS."V_\$SQL"
    where SQL_TEXT like '%PATTERN%'
    order by LAST_LOAD_TIME desc
  ) where ROWNUM <= 5;
gavenkoa
  • 45,285
  • 19
  • 251
  • 303
  • This method seems effective. Can you explain more about how initial and subsequent runs contribute to the elapsed time? It seems (in Oracle 19c at least) that the elapsed time is cumulative, with each run of the same/similar query (or used of the same cached parsed statement?) taking a fraction of the initial time. – em_bo Aug 05 '20 at 14:46
  • Very useful, thanks. Suggestion: include `SQL_ID` and `PLAN_HASH_VALUE` in the columns to select, and also, divide the time with 1,000,000 so that you got seconds. `to_char(ELAPSED_TIME/1000000, '999,999,999.000') || ' s'` – WesternGun Nov 18 '21 at 14:55
  • That's incorrect, because ELAPSED_TIME is cumulative value for all executions of the query. – Okloks Jan 09 '23 at 10:50
  • Probably you need `elapsed_time / executions` or `select ELAPSED_TIME into TS` between 2 executions, like `end_TS - start_TS`. – gavenkoa Jan 09 '23 at 11:17
3

I'd recommend looking at consistent gets/logical reads as a better proxy for 'work' than run time. The run time can be skewed by what else is happening on the database server, how much stuff is in the cache etc.

But if you REALLY want SQL executing time, the V$SQL view has both CPU_TIME and ELAPSED_TIME.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • Also because SQL can be shared you need to also look at the number of executions and divide this to work out an average otherwise you will find the ELAPSED_TIME for all the executions and may leave you wondering why a simple 1 second SQL consumed hours of elapsed time. – Stellios Aug 25 '10 at 02:51
0
set timing on
spool /home/sss/somefile.txt
set termout off
select ...
set termout on
set timing off
spool off

Save it to a script.sql, and do this in terminal:

sqlcl user@host/dbname @/path/to/your/script.sql
WesternGun
  • 11,303
  • 6
  • 88
  • 157