Playing Around with v$session

I recently came upon Arup Nanda's series on performance tuning, and it contains some really good info especially for someone like me who hasn't really done any tuning for day-to-day performance issues. The first article focuses on gleaming info from v$session regarding states, blocking sessions, etc. So this is just condensing most of the queries used in the article, which might seem like things he already posted, but I guess it's mostly just for my reference for future use :)

To get the most basic information based on the username:

select SID, state from v$session where username='<username>';

select SID, state, event from v$session where username='<username>';

Great little script to nicely display what sessions are doing:

select sid,

decode(state,   

'WAITING','Waiting',

'Working') state,

decode(state,

'WAITING','So far '||seconds_in_wait,

'Last waited '||wait_time/100)||

' secs for '||event "Description",

from v$session

where username='<username>';

The above script considers anything other than 'WAITING' to be 'working'. With regards to the EVENT value, there are two values that can be possibly ignored. 

• SQL*Net message from client - Can be misleading, could be idle, could be network slow. 
• rdbms ipc message - event status for sessions that are really idle.

To show who's blocking who:

select blocking_session, blocking_instance

from v$session

where sid=<n>;

To find out information about the object being contested upon:

select row_wait_obj#,

row_wait_file#,

row_wait_block#,

row_wait_row#

from v$session

where sid=<n>;

select owner,

object_type,

object_name,

data_object_id

from dba_objects

where object_id=<row_wait_block#);

And you can combine the 2 queries if wanted:

select owner,

object_type,

object_name,

data_object_id

from dba_objects

where object_id=(

select row_wait_obj# from v$session where sid=<n>;

To find the query that was being run:

select sql_fulltext

from v$sql

where sql_id = (

select sql_id from v$session where sid=<n>;

The query above may show no results, as the query in question may have been aged out of the shared pool. Was unlucky first few times running this, it took me awhile to confirm that it was indeed aged out, even though I had just run the query a few seconds before.