Tuning a Wait Session

When I was about to bring down the server in an immediate mode for the start of the weekend maintenace window, I saw a user session is still active where he was trying to run a query. This query, in the past, took a minute or so and now its more than half hour. All I could do is kill it.

The thing I am wondering about is, if time permits, the approach towards tuning it. How do I find out which object is he accessing, is there a wait for any latches or is it undergoing any full table scans or is it waiting for any resource to be freed and etc..

----->

The first thing you should do is select * from v$session_wait where sid = [his session ID]. We can go from there. Run that query multiple times a few seconds apart.

----->

****************

To find out what sql the problem session(s) are executing, run the following query:

select s.sid, event, wait_time, w.seq#, q.sql_text
from v$session_wait w, v$session s, v$process p, v$sqlarea q
where s.paddr=p.addr and
s.sid=&p and
s.sql_address=q.address;

****************

I suggest you drop v$process from that query because you're not using anything in it. And suggest you change v$sqlarea to v$sql because the latter is much less expensive.

To be more accurate, v$sql (or v$sqlarea) contains the SQL the session either is running or last ran, depending on the info in v$session_wait.