Oracle Database Discussions

Hi

Oracle 10g/11g
Platform : Linux

Users generate jobs which runs 30 to 45 min. Some times the time crosses to 60 to 90 min. User specifies that server is slow and asks us to check the reason.

Normally we can check any locking any space issue, long opps on the server. User says that he started 30 min. back still running. beyond that he may not be able to tell the details. infact he will ask us still how much time it will take to complete the job to plan his activity further.

My questions and assumptions 1 session 1 sql.

1. How to find the total time it was taking for a session to execute ie the current time it was running a particular session for a particular sql ?

so we can identify whether it was started 30 min back or not.

select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et

can give the idle time from the last call.


2. Is it possible in oracle to predict How much time still it will take to complete the total execution of a sql which is running by considering all the load on the database ?

Normally only after execution only we can find the total time it has taken for sql to perform, but during the process any possibility yo predict the completion of the sql.

I hope I am not wrong in asking this question.