Database Reference
In-Depth Information
program
), which machine it was started on (
machine
),
which process ID it has (
process
), and the name of the OS user who started it (
osuser
).
•
The application using the session (
server
) which can be either
DEDICATED
,
SHARED
,
PSEUDO
,
POOLED
or
NONE
, and its address (
paddr
).
•
The type of server-side process (
taddr
).
•
The address of the currently active transaction (
status
) which can be either
ACTIVE
,
INACTIVE
,
KILLED
,
SNIPED
, or
CACHED
and how many seconds it's been in that state for (
last_call_et
). When investigating a
performance problem, you're usually interested in the sessions marked as
ACTIVE
only.
•
The status of the session (
command
), the identification of the cursor related
to it (
sql_address
,
sql_hash_value
,
sql_id
and
sql_child_number
), when the execution
was started (
sql_exec_start
), and its execution ID (
sql_exec_id
). The execution ID is an
integer value that identifies, along with
sql_exec_start
, one specific execution. It's necessary
because the same cursor can be executed several times per second (note that the datatype of
the
sql_exec_start
column is
DATE
).
The identification of the previous cursor that was executed (
•
The type of the SQL statement in execution (
•
prev_sql_address
,
prev_hash_value
,
prev_sql_id
and
prev_child_number
), when the previous execution was
started (
prev_exec_start
), and its execution ID (
prev_exec_id
).
If a PL/SQL call is in execution, the identification of the top-level program and subprogram
•
(
plsql_entry_object_id
and
plsql_entry_subprogram_id
) that was called, and the program
and subprogram (
plsql_object_id
and
plsql_subprogram_id
) that is currently in execution.
Note that
plsql_object_id
and
plsql_subprogram_id
are set to
NULL
if the session is
executing a SQL statement.
•
The session attributes (
client_identifier
,
module
,
action
, and
client_info
), if the
application using the session sets them.
•
If the session is currently waiting (in which case the
state
column is set to
WAITING
), the name
of the wait event it's waiting for (
event
), its wait class (
wait_class
and
wait_class#
), details
about the wait event (
p1text
,
p1
,
p1raw
,
p2text
,
p2
,
p2raw
,
p3text
,
p3
, and
p3raw
), and how
much time the session has been waiting for that wait event (
seconds_in_wait
and, from 11.1
onward,
wait_time_micro
). Be aware that if the
state
column isn't equal to
WAITING
, the
session is on CPU (provided the
status
column is equal to
ACTIVE
). In this case, the columns
related to the wait event contain information about the last wait.
•
Whether the session is blocked by another session (if this is the case,
blocking_session_status
is set to
VALID
) and, if the session is waiting, which session is blocking it (
blocking_instance
and
blocking_session
).
If the session is currently blocked and waiting for a specific row (for example, for a row lock),
•
the identification of the row it's waiting for (
row_wait_obj#
,
row_wait_file#
,
row_wait_block#
, and
row_wait_row#
). If the session isn't waiting for a locked row,
the
row_wait_obj#
column is equal to the value
-1
.
In addition to the
v$session
view, there are other dynamic performance views that are specialized in
providing specific information. For example,
v$session_wait
provides only columns related to wait events, and
v$session_blockers
provides only columns related to blocked sessions.