OracleTuts Oracle Training, Tutorials, and Videos from Beginner to Advanced
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
select *
from (
select opname
,target
,round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) time_remaining
,sofar
,totalwork
,units
,elapsed_seconds
,message
,a.start_time
,b.sql_text
from v$sql b
,v$session_longops a
where b.sql_id (+) = a.sql_id
and round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) > 0
)
order by time_remaining desc
;
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.
Hope this helps.
If you have a different way you like to do this, please let us all know so we all can grow in experience.
