Query Run Time

Is there a way to time how long a particular query takes to run. I have two similar queries and want to see which runs faster.



(diving in for the soft ball…) - see the Query Viewer tab in the Editor.

The editor has the execution time in the bottom left hand corner. You can also
see this in View > Query Viewer.

Does your query return more than 500 rows? The time in the editor won’t
show fetch time for the data not returned by the first fetch.

You can force all records to be returned if you execute as script (F5).

The AutoTrace tab can give you better run-time stats, breaking down parse, cpu,
execute, etc.

You can also trace your session and get even more information back.


This can get tricky because once you run one query data will be in cache so the second query will not have to work as hard. So you might have to time it two days in a row.

lookup autotrace it can give trace info and Toad has a tab to support it.

Set timing on
set autotrace traceonly
then run query - no results will be returned but you will get timing

I am not retired yet. I am on holidays until Jan 31. Feb 1 I retire.
So I can still comment.

This was very helpful to me as I did not know about F5. I believe this going to be useful going forward when I have a script that takes a long time to run with a lot of rows. When I used to save the results to Excel after the first batch of rows were saved the query likely had to run again for the next batch of rows, it takes a long time to save and couldn’t use Toad when I was saving such a thing. Also it should be handy when I want to view all rows.

Strangely the main problem turned out to be that the run time in the lower left goes way if you move to another line in the editor script window which I often do so I did not see. I thought I had seen it at one time but then I thought I was imagining things when I did not see it. However knowing this along with the the query viewer window stats should do the trick.