Execution time for sql files - batch operation

Question

I have a bunch of SQL queries stored as files on disk.
They are all pure SELECT queries or in other words, they only do read operations.

I am connecting to Oracle 11g database and I want to measure approximate execution time of all these queries. Is there any way to do this programmatically?

The database is on a server which is behind a firewall and as such I can connect to database only through Toad or Oracle SQL developer. So, writing my own java/python code is not an option here.

Answer 1

I can think of a couple of options while using TOAD.

Option 1: This should work in TOAD by pressing F5

TURN SPOOL ON -- Spool the results
SET TERMOUT OFF -- Controls the display of output generated by commands executed from a script.
SELECT * FROM TABLE;
SPOOL OFF

Option 2: This should work in TOAD by pressing F5 But this will not print the results of the query.

SET AUTOTRACE TRACEONLY
YOUR QUERY

Option 3: This should work in TOAD by pressing F5

SET TIMING ON -- Controls the display of timing statistics.
YOUR QUERY

Eg for option 3

SET TIMING ON
SELECT bla FROM bla...
...
Elapsed: 00:00:00:01
SELECT bar FROM foo...
...
Elapsed: 00:00:23:41
SET TIMING OFF

To run all scripts and find overall time

TIMING START allmyscripts
... run all my scripts ...
TIMING STOP
timinig for: allmyscripts
Elapsed: 00:00:08.32

A DBA style find using the following columns in V$SQL

APPLICATION_WAIT_TIME
CONCURRENCY_WAIT_TIME
CLUSTER_WAIT_TIME
USER_IO_WAIT_TIME
PLSQL_EXEC_TIME
CPU_TIME
ELAPSED_TIME

Note: I suggest Option 3