Finding query execution time using SQL Developer

Question

I am starting with Oracle DB. I want to know the query execution time. This query returns about 20,000 records. When I see SQL Developer, it only displays 50 rows, the maximum - up to 500. And using F5 up to 5000.

I would do by making changes to the application, but redistributing applications is not possible because it works in production. Thus, I am limited to using only SQL Developer. I'm not sure how to get the seconds spent executing the request? Any ideas on this will help me. Thanks.

Regards, JE

Answer 1

If you scroll down to the 50 lines that were originally returned, it gets more. When I want them all, I just click on the first of 50, then press Ctrl End to scroll all the way to the end.

This will update the time display that was used (just above the results, it will say something like “All Rows Fetched: 20,000 in 3,606 seconds”), giving you the exact time for a complete request.

Answer 2

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

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

This is the most accurate way to determine the actual runtime. The presentation of V$SESSION_LONGOPS may also be of interest to you.

If you do not have access to these views, you can also use the cursor loop to start all records, for example

 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 may be more or less accurate, as the data may need to be transferred to the system where your SQL is running.

Answer 3

You can change these restrictions; but you will use some time in transferring data between the database and the client and, possibly, for display; and this, in turn, will depend on the number of rows pulled by each sample. These things also affect your application, though, so viewing your raw runtime may not tell you the whole story anyway.

To change the sheet limit (F5), go to Tools-> Settings-> Database-> Worksheet and increase the "Maximum lines to print in the script value" (and, possibly, "Max lines in Script output '). To change sample size, go to the "Database"> "Advanced" panel in the settings; perhaps to match your application value.

This is not ideal, but if you do not want to see the actual data, just find the time required to run in the database, you can wrap the query to get one row:

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

Usually it executes the entire original query, and then counts the results, which will not add anything meaningful to the time. (Perhaps he could rewrite the request internally, I suppose, but I think this is unlikely, and you could use hints to avoid it if necessary).