How to Find Expected Time of Completion of an Oracle Query
Especially in case of poorly performing SQLs, all eyes are on the SQL and we’re eager to see when the SQL would complete. Upon completion, we can view the statistics and find ways to improve it. Wouldn’t it be better if there was a way to find the expected time of completion of an Oracle SQL or Query, so that we can save time rather than just staring at the SQL.
This option became available since Oracle 10g came and it assumes that either a FULL Table Scan or INDEX FAST FULL SCAN are being performed by the database for the SQL in question. 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.
We will use v$session_longops. You can use 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, you can use a variant of the above SQL 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;
