http://www.dba-oracle.com/art_otn_waits.htm
v$session.paddr = v$process.addr
v$session.sid = v$session_wait.sid
v$session.sql_address = v$sqlarea.address
v$session.sql_hash_value = v$sqlarea.hash_value
v$session.sql_id = v$sqlares.sql_id
v$session.server shows Dedicated or MTS configuration.
The fields module and action of v$session can be set with dbms_application_info.set_module.
The field client_info can be set with dbms_application_info.set_client_info
Join sid with v$sesstat if you want to get some statistical information for a particular sesssion.
A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session).
What a session is waiting for can be queried with v$session_wait. However, with Oracle 10g, this is not nessessary anymore,
as v$session_wait’s information will be exposed within v$session as well.
P1 refers sometimes to the datafile number. If this number is greater than db_files, it refers to a temp file.
********************************
V$SESSION_WAIT – Unlike the previous view, this view does not contain aggregated statistics; it contains statistics on the event the session
is waiting on at the moment. The P(n) columns are an important piece of information used to help determine where the problem exists.
———————–
SID – Session ID.
SEQ# – Sequence number that uniquely identifies the wait event. This sequence is incremented with each wait event completion.
EVENT – Name of wait event.
P1TEXT – A description of what the P1 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.
P1 – This value definition is dependent on the event. Use PARAMETER1 value from V$EVENT_NAME to describe what this value is used for.
P1RAW – Same value as P1 except in hexadecimal format.
P2TEXT – A description of what the P2 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.
P2 – This value definition is dependent on the event. Use PARAMETER2 value from V$EVENT_NAME to describe what this value is used for.
P2RAW – Same value as P2 except in hexadecimal format.
P3TEXT – A description of what the P3 value is used for. Not reliable, use PARAMETER(n) value from V$EVENT_NAME to determine.
P3 – This value definition is dependent on the event. Use PARAMETER3 value from V$EVENT_NAME to describe what this value is used for.
P3RAW – Same value as P3 except in hexadecimal format.
WAIT_CLASS_ID – ID of the wait class. Column is in 10g.
WAIT_CLASS# – Number of the class. Column is in 10g.
WAIT_CLASS – The name of the wait class (Idle, Network, System I/O, etc.). Column is in 10g.
WAIT_TIME – The last amount of time that the session waited for an event. A 0 value means the session is currently waiting on the event.
This value of 0 will not increase until the session completes the wait event. Time is in centiseconds.
SECONDS_IN_WAIT – If WAIT_TIME equals 0, then this is the number of seconds spent waiting on the event so far. This can give you complicated
results when a timeout occurs like with enqueues because it will reset to 0, but this is beyond the scope of this article.
STATE – State of the wait event: WAIT_TIME=0 – WAITING, the process is in the state of waiting on an event. WAIT_TIME=-1 – WAITED SHORT TIME, wait time
completed in less than a centisecond. WAIT_TIME=-2 – WAITED UNKOWN TIME, duration of wait is unknown because TIME_STATISTICS was set to false.
WAIT_TIME>0 – WAITED KNOWN TIME, wait completed. WAIT_TIME is the duration of the wait event in centiseconds.