How long will my SQL query run

Have you ever started a query in your favourite Oracle SQL tool and it is just runnin’, and runnin’, and runnin’…? And, you are wondering if the query will let you eat lunch before it finishes…? Well, here is a way you might be able to find out how long your SQL query will take.

V$SESSION_LONGOPS
This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release. To monitor query execution progress, you must be using the cost-based optimizer and you must:

• Set the TIMED_STATISTICS or SQL_TRACE parameter to true
• Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure. ~ From Oracle® Database Reference, 10gR2 (For full information, look here)

Example of v$session_longops usage

1

select *

2

from (

3	select  opname

4

,target

5	,round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) time_remaining

6

,sofar

7	,totalwork

8

,units

9	,elapsed_seconds

10

,message

11	,a.start_time

12	,b.sql_text

13	from    v$sql b

14	,v$session_longops a

15	where b.sql_id (+) = a.sql_id

16	and round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) > 0

17

)

18	order by time_remaining desc

19

;

Note that your ability to use the v$ views in Oracle is based on the rights you are set up with in the database. If you do not know, please get with your DBA. If you are doing this in your personal / private database, look on the internet to find out how to set the correct rights.