Finding Execution time of query using SQL Developer

If you scroll down past the 50 rows initially returned, it fetches more. When I want all of them, I just click on the first of the 50, then press CtrlEnd to scroll all the way to the bottom.

This will update the display of the time that was used (just above the results it will say something like "All Rows Fetched: 20000 in 3.606 seconds") giving you an accurate time for the complete query.

If your statement is part of an already deployed application and if you have rights to access the view V$SQLAREA, you could check for number of EXECUTIONS and CPU_TIME. You can search for the statement using SQL_TEXT:

SELECT CPU_TIME, EXECUTIONS 
  FROM V$SQLAREA
 WHERE UPPER (SQL_TEXT) LIKE 'SELECT ... FROM ... %';

This is the most precise way to determine the actual run time. The view V$SESSION_LONGOPS might also be interesting for you.

If you don't have access to those views you could also use a cursor loop for running through all records, e.g.

CREATE OR REPLACE PROCEDURE speedtest AS
   count number;

   cursor c_cursor is
     SELECT ...;
BEGIN
   -- fetch start time stamp here
   count := 0;

   FOR rec in c_cursor
   LOOP
     count := count +1;
   END LOOP;   
   -- fetch end time stamp here
END;

Depending on the architecture this might be more or less accurate, because data might need to be transmitted to the system where your SQL is running on.

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).