Tuning problems with a curent session

First we can check if the current session is waiting on an 'idle' wait.
In other words we can say that the session is not waiting on the database.

select sid, serial#, username, event,
blocking_session, seconds_in_wait, sql_id
from v$session
where state = 'WAITING'
and wait_class = 'Idle';

If the session is not waiting on a idle wait. find the users SID and
check where it has been waiting in the past

select wait_class_id, wait_class#, wait_class,
total_waits, time_waited
from v$session_wait_class
where sid = ###;

Look at the total waits, has the session been waiting for
the same wait class.

Look at the last waits for the session using v$session_wait_history. This view shows the last 10 wait events for each session.

select event, WAIT_TIME_MICRO, TIME_SINCE_LAST_WAIT_MICRO
from v$session_wait_history
where sid = ###;

If the wait events are associated with 'User I/O' or CPU we would probably want to see what sql statement is running.

select sid, sql_id from v$session where sid = ###;

You could now query v$sql to find more details about the sql statement.

Too see all the stats the session has accumulated since it started we can query v$sesstat.

select sid, class, name, value from v$sesstat ss, v$statname sn where ss.statistic# = sn.statistic# and ss.value > 0 and ss.sid = ### order by value desc;

******************************************
keywords: wait session performance
******************************************
rdbms version: 10g
******************************************