Oracle V$session Detailed

V$session is a synonym for the sys.v_$session view under the apps user.

In this view, each session that is connected to a database instance has a record. including user session and background process such as dbwr,lgwr,arcchiver and so on.

v$session the common columns in

V$session is the basic information view that is used to find the user SID or Saddr. However, it also has some columns that change dynamically and can be used to check the user. As an example:

Sql_hash_value,sql_address: These two columns are used to identify the SQL statements that are executed by the session by default. If NULL or 0, it means that the session did not execute any SQL statements. The Prev_hash_value and prev_address two columns are used to identify the last statement executed by the session.

Note: When selecting with Sql*plus, make sure that you redefine the column widths not less than 11 to see the full value.

Status: This column is used to determine the session status:

L achtive: Executing SQL statement (waiting for/using a Resource)

L Inactive: Wait for operation (that is, wait for the SQL statement to be executed)

L Killed: Marked for deletion

The following columns provide information about the session and can be used to find the session when one or more combination are unknown.

Session Information

L sid:session identification, commonly used to connect other columns

L serial#: If a SID is used by another session then this value is incremented (when one session ends and the other session starts and the same SID is used).

L AUDSID: review session ID uniqueness, confirm that it is usually also used when looking for parallel query patterns

L USERNAME: The user name of the current session in Oracle.

Client Information

The database session is initiated by a client process running on a database server or from a server or even desktop connected to a database through Sql*net, and the following columns provide information for this client

L Osuser: Client operating system user name

L Machine: Machines executed by client

L TERMINAL: Terminal run by client

L Process: The ID of the client process

L Program: client-side programs executed by client

To display the terminal, Osuser of the PC to which the user is connected, you need to set the keyword terminal,username in oracle.ini or Windows on that PC.

Application Information

Call the Dbms_application_info package to set some information to differentiate the user. This displays the following columns.

L Client_info:dbms_application_info Set in

L Action:dbms_application_info Set in

L Module:dbms_application_info Set in

The following v$session columns may also be used:

L row_wait_obj#

L row_wait_file#

L row_wait_block#

L row_wait_row#

v$session the connection column in

2. Column View Joined column (s)
6. Sid V$session_wait,v$sesstat,v$lock,v$session_event,v$open_cursor Sid
10. (Sql_hash_value, sql_address) V$sqltext, V$sqlarea, V$sql (Hash_value, ADDRESS)
14. (Prev_hash_value, prev_sql_address) V$sqltext, V$sqlarea, V$sql (Hash_value, ADDRESS)
18. Taddr v$transaction ADDR
22. Paddr v$process ADDR

Example:

1. Find your session information

1. Selectsid, Osuser, USERNAME, Machine, PROCESS
3. Fromv$sessionwhereaudsid = Userenv (' SESSIONID ');

2. Find session when Machine is known

1. Selectsid, Osuser, USERNAME, Machine, TERMINAL
3. Fromv$session
5. whereterminal = 'pts/tl ' andmachine =' RGMDBS1 ';

3. Find the SQL statement that is currently running by a specified session. Suppose SessionID is 100

1. Selectb.sql_text
3. Fromv$session A,v$sqlarea b
5. wherea.sql_hash_value=b.hash_valueanda.sid=100

Finding the SQL statement executed by the specified session is a public requirement, and if the session is the main cause of the bottleneck, it can see what the session is doing based on the statement it is currently executing.

View Application:

V$session table of the use of the V$session table more commonly used in several field description:

1. sid,serial#
By SID we can query various statistics related to this session and process the information.

1. SELECT * from v$sesstat where sid =: SID;

Query the various statistical information related to the user.

1. SELECT A.sid, a.statistic#, B.name, A.value
2. From V$sesstat A, v$statname b
3. WHERE a.statistic# = b.statistic#
4. and a.sid =: SID;

B. Querying various IO statistics related to users

1. SELECT * from v$sess_io where sid =: SID;

C. Query the cursor variable that the user wants to open.

1. SELECT * from v$open_cursor where sid =: SID;

D. Query the user's current wait information. To see why the current statement is so slow/waiting for what resources.

1. SELECT * from v$session_wait where sid =: SID;

E. Query information about the various events that users have been waiting for over a period of time. To understand the bottleneck that this session encountered

1. SELECT * from v$session_event where sid =: SID;

F. What's more, we can deal with sid,serial# when we want to kill the current session.

1. Alter system kill session ': sid,:serail# ';

2. paddr. field, Process addr, this field allows us to view information about the current process, system process ID, operating system user information, and so on.

2. SELECT A.pid,
4. A.spid,
6. B.name,
8. B.description,
10. A.latchwait,
12. A.latchspin,
14. A.pga_used_mem,
16. A.pga_alloc_mem,
18. A.pga_freeable_mem,
20. A.pga_max_mem
22. From V$process A, v$bgprocess b
24. WHERE a.addr = b.paddr (+)
26. and a.addr =:p addr

3. Command field, indicating the type of statement being executed by the current session. Please refer to reference.

4. Taddr the address of the current transaction, this field can be used to view the current session of the transaction information, the use of the rollback segment information, etc.

1. SELECT b.name Rollname, a.*
2. From V$transaction A, v$rollname b
3. WHERE A.xidusn = B.usn
4. and a.addr = ' 585ec18c ';

5. lockwait field, this field allows you to query the information about the lock that is currently waiting.

1. SELECT *
2. From v$lock
3. Where (ID1, id2) = (SELECT id1, id2 from v$lock WHERE kaddr = ' 57c68c48 ')

6. (sql_address,sql_hash_value) (Prev_sql_addr,prev_hash_value) based on these two sets of fields, we can query the details of the SQL statement being executed by the current session.

1. SELECT *
2. From V$sqltext
3. WHERE address =: sql_address
4. and Hash_value =: sql_hash_value;

7.row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#
You can use these fields to query information about the table that is now being locked. ^_^
A. Get the information that is locked first

1. SELECT * from dba_objects WHERE object_id =: row_wait_obj#;

B. According to row_wait_file# can find the corresponding file information.

1. SELECT * from v$datafile WHERE file# =: row_wait_file#.

C. ROWID information for the locked field is constructed based on the above four fields.

2. SELECT Dbms_rowid. Rowid_create (1,
4. : row_wait_obj#,
6. : row_wait_file#,
8. : row_wait_block#,
10. : row_wait_row#)
12. from dual;

8. Logon_time the logon time of the current session.
9. Last_call_et The session idle time, updated every 3 seconds. Reprint: http://blog.csdn.net/cunxiyuan108/article/details/5999220

Oracle V$session Detailed