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

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.


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Oracle UTL_FILE

    The Oracle supplied PL/SQL package UTL_FILE used to read and write operating system files that are located on the database server.

  • Oracle Analytic Functions

    Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. This article explains how we can unleash the full potential of this.

  • How to find out Which User is Running what SQL Query in Oracle database?

    Do you wonder how to get information on all the active query in the Oracle database? Do you want to know what query is executed by which user and how long is it running? Here is how to do it!

  • 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.

  • Different Methods to move data from Oracle to External File

    How to push data from Oracle Table to external files? Well, external tables in Oracle provides a way to move data in as well as out of the database with the help of SQL*Loader and Data Pump functionality.

  • Oracle AUTOTRACE Explained - A 10 Minute Guide

    AUTOTRACE is a beautiful utility in Oracle that can help you gather vital performance statistics for a SQL Query. You need to understand and use it for SQL Query Tuning. Here is how!

  • Learn Oracle Server Architecture in 10 minutes

    Here is an easy to understand primer on Oracle architecture. Read this first to give yourself a head-start before you read more advanced articles on Oracle Server Architecture.

  • Understanding Oracle QUERY PLAN - A 10 minutes guide

    Confused about how to understand Oracle Query Execution Plan? This 10 minutes step by step primer is the first of a two part article that will teach you exactly the things you must know about Query Plan.

  • Database Performance Tuning

    This article tries to comprehensively list down many things one needs to know for Oracle Database Performance Tuning. The ultimate goal of this document is to provide a generic and comprehensive guideline to Tune Oracle Databases from both...

  • Oracle External Tables

    The Oracle external tables feature allows us to access data in external sources as if it is a table in the database. This is a very convenient and fast method to retrieve data from flat files outside Oracle database.