CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Eve
Eve

Reputation: 514

Check status sql query or percentage

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

Upvotes: 6

Views: 25082

Answers (3)

Rajesh Singam
Rajesh Singam

Reputation: 11

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.

Upvotes: 1

Superdooperhero
Superdooperhero

Reputation: 8096

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

Upvotes: 2

N West
N West

Reputation: 6819

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.

Upvotes: 4

Related Questions