Like the oracle pick time request

Question

What is the best way to find out how long an oracle select statement takes. I have the following query for which I want to find out the time, however, since this query contains four thousand records, and it takes time to display these 4 thousand records on the screen, the indicated elapsed time may be incorrect.

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

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

There are several ways that I can think of.

I usually do this by running it in a table with CREATE TABLE AS SELECT.... , which means that I often force my schema with many tables called MIKE_TEMP_1 .

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

Answer 2

Parameters that spring:

a) use an external choice, which may not be entirely accurate if the optimizer does this, but may give a good idea:

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

b) put it in a script, run it from the command line and redirect the output to a file.

c) turn the coil on and off (not sure about this).

d) set autotrace traceonly (which @MikeyByCrikey beat me).

Answer 3

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

 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 time information for each application separately.

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 separate timers - TIMING STOP pops the last timer from the stack.

Answer 4

You can go to V $ SQL, where you have the following columns:

 APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME CPU_TIME ELAPSED_TIME 

but they are cumulative for all executions of this SQL. You can do before / after the snapshot and make a difference if no one is running SQL.

Answer 5

Just don't show query results

 SET TERMOUT OFF