CODEWITHSUNDEEP
sqloracle-sqldeveloper
java_enthu
java_enthu

Reputation: 2327

Finding Execution time of query using SQL Developer

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

Upvotes: 13

Views: 57535

Answers (3)

Trinimon
Trinimon

Reputation: 13957

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.

Upvotes: 5

user2404501
user2404501

Reputation:

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.

Upvotes: 17

Alex Poole
Alex Poole

Reputation: 191275

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

Upvotes: 3

Related Questions