How to find out Expected Time of Completion for an Oracle Query

Details: Category: Database Oracle; Published on Tuesday, 08 June 2010 15:43; Written by Akash Mitra; Hits: 669

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,

Script

  
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 sofartotalwork;

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 sofartotalwork;

NOTE

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.