This post is to provide a set of queries to monitor any user session on an Oracle Instance more efficiently.

Below query (Lets name it Query1 for the sake of discussion) gives you an overall status of what all sessions are active, what wait event they are waiting on and what are they executing.

set lines 190 
set pages 500
col STATE form a15
col WAIT_CLASS form a20
col EVENT form a30
col ACTION form a15
col USER form a10
col sid form 9999
col username form a10
col OSUSER form a10
col username form a20
set lines 190
select sid, USERNAME, OSUSER, status, sql_id, prev_sql_id, event, wait_class, state, LOGON_TIME
from v$session where status = 'ACTIVE' and type != 'BACKGROUND' 
order by user;

In the output we can see the SQL_ID to know what that session is currently executing, you can use below query (Query2) to see the SQL text from SQL_ID:

set long 999999999 
select sql_fulltext from v$sql where sql_id='&sql_id';

Another column to focus in Query1 is EVENT, which is the wait event on which the query has waited (If the State Column is WAITED SHORT TIME) or currently waiting (If the State Column is WAITING). This is the most important information to troubleshoot any performance issue as it tells exactly where a query is waiting.

Assuming that the Query1 assisted you in identifying the SID of the session that is having performance issue, you can now run below query (Query3) to get more details of the session:

col STATE form a15
col WAIT_CLASS form a20
col EVENT form a30
col ACTION form a15
col USER form a10
set lines 190
select user, status, sql_id, prev_sql_id, fixed_table_sequence, last_call_et, seq#, event, wait_class, wait_time, seconds_in_wait, state 
from v$session where SID=&SID;

With Query3 you get a few more columns, suppose a session’s STATE column is WAITING that means it is waiting on the wait event specified in the EVENT column and to know for how long the SQL_ID is waiting on this particular wait event check the column SECONDS_IN_WAIT. Similarly you get LAST_CALL_ET & FIXED_TABLE_SEQUENCE the usage of these columns would be explained in subsequent performance tuning posts.

The next step is to get an overview of all the major wait events so that from the output of above queries and seeing the wait event you can get a picture where the performance issue is, please follow the posts under Oracle Wait Event to get a grasp over the major wait events.