how to time an oracle select query

Question

what is the best way to find out how much time an oracle select statement takes. I have the following query for which I want to find out the time, however, since this query brings four thousand records and it takes time to display those 4 thousand records on the screen, the elapsed time stated might not be correct.

Is there a way I can wrap this into a cursor and then run it from sql plus so that I get the correct time it takes to execute this?

SELECT a.code, NVL(a.org, ' '), NVL(a.office_number, ' '), SUBSTR(a.code, 0, 2) 
FROM PARTICIPANT a WHERE a.type_code = 'PRIME';

Answer 1

In SQL*Plus you can also use the simple TIMING option:

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

This will report timing information for each statement individually.

Another option is to set up individual timers:

SQL> TIMING START mytimer
SQL> ... run all my scripts ...
SQL> TIMING STOP
timinig for: mytimer
Elapsed: 00:00:08.32

You can even nest these individual timers - the TIMING STOP pops the most recent timer off a stack.

Answer 2

There are a couple of ways I can think of.

I normally do this sort of thing by running it into a table with CREATE TABLE AS SELECT...., which means I often litter my schema with many tables named MIKE_TEMP_1.

Other option is in SQL*Plus to use SET AUTOTRACE TRACEONLY which should run all the query but suppress the printing of the results.