CODEWITHSUNDEEP
oracle-databaseplsql
learn_plsql
learn_plsql

Reputation: 1681

how to time an oracle select query

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';

Upvotes: 5

Views: 30412

Answers (5)

Asper
Asper

Reputation: 11

Just do not display query results

SET TERMOUT OFF

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

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 an aggregate for all executions of that SQL. You can do a before/after snapshot and work out the difference if no-one else is running the SQL.

Upvotes: 2

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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.

Upvotes: 8

Alex Poole
Alex Poole

Reputation: 191275

Options that spring to mind:

a) use an outer select, which may not be entirely accurate if the optimizer mangles it but can 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 spool on and termout off (not sure about that one).

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

Upvotes: 4

Mike Meyers
Mike Meyers

Reputation: 2895

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.

Upvotes: 5

Related Questions