Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, December 5, 2008

Long running queries

Query to check Long running SQLs. The column mintogo gives an estimated time the sql is going to run

select s.sid,o.opname,s.sql_hash_value,o.sofar,o.totalwork,o.elapsed_seconds,
round(o.time_remaining/60,2) mintogo,
round(o.sofar/o.totalwork * 100,0) pctdone, o.message
from v$session_longops o, v$session s
where o.sid = s.sid
and sofar < totalwork
and (o.sid = &trgtsid or &trgtsid = 0)

1 comment:

Harry Potter said...

Hi Vikram,

I have got a doubt regarding concurrent program - unix parameters.

$5 and $6... are used as user input parameters. In a case where both the parameters are 'not required', user can enter null values.

Suppose, a user enters NULL value for 1st parameter and some value X for the second, then in unix $5 will have the value X instead of $6having that value.

The solution that I came up with was to provide a dummy parameter, which wont be displayed to user ...but it will help us to distinguish between the input values.

Please let me know if there is any other approach to this.

Thank you