Finding Execution time of query using SQL Developer

Question

I am beginner with Oracle DB. I want to know execution time for a query. This query returns around 20,000 records. When I see the SQL Developer, it shows only 50 rows, maximum tweakable to 500. And using F5 upto 5000.

I would have done by making changes in the application, but application redeployment is not possible as it is running on production. So, I am limited to using only SQL Developer. I am not sure how to get the seconds spent for execution of the query ? Any ideas on this will help me. Thank you.

Regards, JE

Answer 1

You can change those limits; but you'll be using some time in the data transfer between the DB and the client, and possibly for the display; and that in turn would be affected by the number of rows pulled by each fetch. Those things affect your application as well though, so looking at the raw execution time might not tell you the whole story anyway.

To change the worksheet (F5) limit, go to Tools->Preferences->Database->Worksheet, and increase the 'Max rows to print in a script' value (and maybe 'Max lines in Script output'). To change the fetch size go to the Database->Advanced panel in the preferences; maybe to match your application's value.

This isn't perfect but if you don't want to see the actual data, just get the time it takes to run in the DB, you can wrap the query to get a single row:

select count(*) from (
  <your original query
);

It will normally execute the entire original query and then count the results, which won't add anything significant to the time. (It's feasible it might rewrite the query internally I suppose, but I think that's unlikely, and you could use hints to avoid it if needed).