learning v$ view-v$session

 

Oracle9i Database Performance Tuning Guide and Reference

V$SESSION

This view has one row for every session connected to the database instance. The sessions include user sessions, as well as background processes like DBWR, LGWR, archiver.

See Also: Oracle9i Database Concepts

Useful Columns for V$SESSION

V$SESSION is basically an information view used for finding the SID or SADDR of a user. However, it has some columns that change dynamically and are useful for examining a user. For example:

SQL_HASH_VALUE, SQL_ADDRESS: These identify the SQL statement currently being executed by the session. If NULL or 0, then the session is not executing any SQL statement. PREV_HASH_VALUE and PREV_ADDRESS identify the previous statement being executed by the session.

Note: When selecting from SQL*Plus, make sure that you have the column defined with adequate width (11 numbers wide) to see the complete number.

STATUS: This column identifies if the session is:

• Active: executing a SQL statement (waiting for/using a resource)
• Inactive: waiting for more work (that is, SQL statements)
• Killed: marked to be killed

The following columns provide information about the session and can be used to find a session when a combination (one or more) of the following are known:

Session Information

• SID: Session identifier, used to join to other columns
• SERIAL#: Counter, which is incremented each time a SID is reused by another session (when a session ends and another session starts and uses the same SID)
• AUDSID: Auditing session ID uniquely identifies a session over the life of a database. It is also useful when finding the parallel query slaves for a query coordinator (during the PQ execution they have the same AUDSID)
• USERNAME: The Oracle user name for the connected session

Client Information

The database session is initiated by a client process that could be running on the database server or connecting to the database across SQL*Net from a middle tier server or even a desktop. The following columns provide information about this client process:

• OSUSER: Operating system user name for the client process
• MACHINE: Machine where the client process is executing
• TERMINAL: Terminal (if applicable) where the client process is running
• PROCESS: Process ID of the client process
• PROGRAM: Client program being executed by the client process

To display TERMINAL, OSUSER for users connecting from PCs, set the keys TERMINAL, USERNAME in ORACLE.INI or the Windows registry on their PCs if they are not showing up by default.

Application Information

Call the package DBMS_APPLICATION_INFO to set some information to identify the user. This shows up in the following columns:

• CLIENT_INFO: Set in DBMS_APPLICATION_INFO
• ACTION: Set in DBMS_APPLICATION_INFO
• MODULE: Set in DBMS_APPLICATION_INFO

The following V$SESSION columns are also useful:

• ROW_WAIT_OBJ#
• ROW_WAIT_FILE#
• ROW_WAIT_BLOCK#
• ROW_WAIT_ROW#

Join Columns for V$SESSION

Table 24-16 is a list of several columns that can be used to join to other fixed views.

Table 24-16 Join Columns for V$SESSION

Column	View	Joined Column(s)
SID	V$SESSION_WAIT, V$SESSTAT, V$LOCK, V$SESSION_EVENT, V$OPEN_CURSOR	SID
(SQL_HASH_VALUE, SQL_ADDRESS)	V$SQLTEXT, V$SQLAREA, V$SQL	(HASH_VALUE, ADDRESS)
(PREV_HASH_VALUE, PREV_SQL_ADDRESS)	V$SQLTEXT, V$SQLAREA, V$SQL	(HASH_VALUE, ADDRESS)
TADDR	V$TRANSACTION	ADDR
PADDR	V$PROCESS	ADDR

Example 24-21 Finding Your Session

SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
  FROM V$SESSION
 WHERE audsid = userenv('SESSIONID');

  SID OSUSER     USERNAME    MACHINE     PROCESS
----- ---------- ----------- ----------- --------
  398 amerora    PERFSTAT    rgmdbs1     26582

Example 24-22 Finding a Session When the Machine Is Known

SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
  FROM V$SESSION
 WHERE terminal = 'pts/tl'
   AND machine  = 'rgmdbs1';

 SID OSUSER    USERNAME  MACHINE    TERMINAL
---- --------- --------- ---------- ----------
 398 amerora   PERFSTAT  rgmdbs1    pts/tl

Example 24-23 Finding the SQL Statement Currently Being Run by a Session

It is a common requirement to find the SQL statement currently being executed by a given session. If a session is experiencing or responsible for a bottleneck, then the statement explains what the session might be doing.

col hash_value form 99999999999
SELECT sql_hash_value hash_value
  FROM V$SESSION WHERE sid = 406;

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653
SQL> /

HASH_VALUE
----------
4249174653

This example waited for five seconds, executed the statement again, and repeated the action couple of times. The same hash_value comes up again and again, indicating that the statement is being executed by the session. As a next step, find the statement text using the view V$SQLTEXT and statement statistics from V$SQLAREA.