Check status sql query or percentage

Question

Does anyone know if there is a way to check the status of a job currently running in the database or the status of how many % has actually been executed.

I am running a job but the job is taking a long time so I would like to check how many % has been executed from the query. I am running the query in Oracle sqldeveloper

Solution 1

Due to the set-based nature of SQL and database processing, you can't really get a "% complete" of a query since the oracle engine doesn't really know for sure. You could try looking at the view v$session_longops to see how far along parts of your SQL has gone (a large hash join or full table scan may show up here). Take a look at this Ask Tom for more info.

If your job has multiple SQL statements and you're trying to track how far along you are after each one, you could add some code to insert status updates on a control table after each statement.

Solution 2

The output_rows column in this query might be useful if your query is not in v$session_longops. The problem is you don't know what the total output rows is.

select plan_operation, plan_options, plan_object_name, plan_object_type, plan_bytes, plan_time, plan_cpu_cost, plan_io_cost, output_rows, workarea_mem
from   v$sql_plan_monitor
where  sid = 2646
and    status = 'EXECUTING'
order  by plan_line_id

Solution 3

You can check gv$session_longops where time_remaining>0;

select sid,target_desc,(Sofar*100)/totalwork as percentage_complete from gv$session_longops

would give you the percentage.