How to find out Expected Time of Completion for an Oracle Query
Too often we become impatient when Oracle Query executed by us does not seem to return any result. But Oracle (10g onwards) gives us an option to check how long a query will run, that is, to find out expected time of completion for a query.
The option is using v$session_longops. Below is a sample query that will give you percentage of completion of a running Oracle query and Expected Time to Complete in minutes,
SELECT opname, target, ROUND( ( sofar/totalwork ), 4 ) * 100 Percentage_Complete, start_time, CEIL( time_remaining / 60 ) Max_Time_Remaining_In_Min, FLOOR( elapsed_seconds / 60 ) Time_Spent_In_Min FROM v$session_longops WHERE sofar != totalwork;
If you have access to v$sqlarea table, then you can use another version of the above query that will also show you the exact SQL running. Here is how to get it,
SELECT opname target, ROUND( ( sofar / totalwork ), 4 ) * 100 Percentage_Complete, start_time, CEIL( time_remaining / 60 ) Max_Time_Remaining_In_Min, FLOOR( elapsed_seconds / 60 ) Time_Spent_In_Min, AR.sql_fulltext, AR.parsing_schema_name, AR.module Client_Tool FROM v$session_longops L, v$sqlarea AR WHERE L.sql_id = AR.sql_id AND totalwork > 0 AND AR.users_executing > 0 AND sofar != totalwork;
This query will give you correct result only if a FULL Table Scan or INDEX FAST FULL SCAN are being performed by the database for your query. In case, there is no full table/index fast full scan, you can force Oracle to perform a full table scan by specifying /*+ FULL() */ hint.