The magical uses of V$session table

The magical ^_^ of Session v$session table
Choose from WWW.CNONG.ORG http://www.cnoug.org/viewthread.php?tid=38191

V$session table in the comparison of several commonly used fields description ^_^

1. sid,serial#
Through SID we can query the various statistical information related to this session and process the information.
A. Select * from v$sesstat where sid =: SID;
Query the various statistical information related to the user.
Select A.sid,a.statistic#,b.name,a.value
From V$sesstat A,v$statname b
where a.statistic# = b.statistic#
and a.sid =: SID;

B. Querying user-related various IO statistics
SELECT * from v$sess_io where sid =: SID;

C. Query the cursor variable that the user wants to be open.
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.
SELECT * from v$session_wait where sid =: SID;

E. Query for information about the various events that users are waiting for over a period of time. To understand the bottlenecks encountered by this session ^_^
SELECT * from v$session_event where sid =: SID;

F. Also, when we want to kill the current session can be handled by sid,serial#.
Alter system kill session ': sid,:serail# ';

2. paddr. field, process addr, through this field we can view the current process information, system process ID, operating system user information, and so on.
Select A.pid,a.spid,b.name,b.description,a.latchwait,a.latchspin,a.pga_used_mem,a.pga_alloc_mem,a.pga_freeable_ Mem,a.pga_max_mem
From v$process a,v$bgprocess b
where a.addr = b.paddr (+)
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, through which you can view the transaction information currently being performed by the session, the rollback segment information used, and so on ^_^
Select B.name rollname,a.*
From V$transaction A,v$rollname b
where A.xidusn = B.usn
and a.addr = ' 585ec18c ';

5. The Lockwait field allows you to query for information about the lock that is currently waiting.
SELECT *
From V$lock
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 that the current session is executing.

SELECT * from v$sqltext where address =: sql_address 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
SELECT * from dba_objects where object_id =: row_wait_obj#;
B. According to row_wait_file# can find the corresponding file information.
SELECT * from v$datafile where file# =: row_wait_file#.
C. ROWID information for a locked field constructed from the above four fields.
Select Dbms_rowid. Rowid_create (1,:row_wait_obj#,:row_wait_file#,:row_wait_block#,:row_wait_row#) from dual;

8. Logon_time the current session logon time.
9. Last_call_et the time of the session idle, updated once every 3 seconds ^_^

[Last edited by Jametong on 2004-11-26 at 11:38]