v$session v$process: Map Oracle Sessions to OS PIDs

calendar Dec 9, 2023 / · 10 min read · Oracle Session Monitoring Troubleshooting v$session v$process  ·
Share on: linkedin copy

v$session v$process: Map Oracle Sessions to OS PIDs

Purpose

An operating system tool shows a process with an odd amount of CPU, memory or disk activity. Its name starts with oracle, and the only handle you have is a number: the process ID. That number does not tell you which user, which application or which session is behind it. Before you decide to wait, tune or end anything, you need to turn the PID into a database session.

This query does that in one step. It joins V$SESSION to V$PROCESS and filters on the operating system process ID that you type at the prompt. For the matching session it returns the database user name, the session identifier (SID), the serial number (SERIAL#), the process ID (SPID), the LAST_CALL_ET value and the session STATUS. A DBA runs it when the starting point is the operating system and the answer must come from the database.

Code

 1select s.username
 2, s.sid
 3, s.serial#
 4, p.spid
 5, last_call_et
 6, status
 7from  V$SESSION s
 8, V$PROCESS p
 9where s.PADDR = p.ADDR
10and p.spid='&pid'
11/

Sample SQL*Plus output when no session matches the value typed at the prompt:

1Enter value for pid: 9999
2old  10: and     p.spid='&pid'
3new  10: and     p.spid='9999'
4
5no rows selected
6SQL>

Code Breakdown

The two views

  • V$SESSION s - Oracle describes this view as displaying "session information for each current session". It holds the user name, the session identifier, the serial number, the status and the timing column used here.
  • V$PROCESS p - Oracle describes this view as displaying "information about the currently active processes". It holds the operating system process identifier in the SPID column.

The join

  • where s.PADDR = p.ADDR - PADDR in V$SESSION is the "address of the process that owns the session". ADDR in V$PROCESS is the "address of the process state object". The two values are the link between a session and its process. The query uses a plain inner join, so a row appears only when both sides match.

The filter

  • and p.spid='&pid' - SPID is defined as VARCHAR2(24), the "operating system process identifier". Because it is a character column, the substitution variable sits inside quotes. SQL*Plus replaces &pid with the value you type and prints the old and new lines shown in the sample output. The 10: in those lines is the line number of the and line in the statement.

The select list

  • s.username - the Oracle user name of the session.
  • s.sid - the session identifier.
  • s.serial# - the session serial number. Oracle says it is "used to uniquely identify a session's objects".
  • p.spid - the operating system process identifier, echoed back so you can confirm which process matched.
  • last_call_et - seconds in the current state. The exact meaning depends on STATUS, as explained below.
  • status - the session status.

The last two columns have no table alias. That works because only V$SESSION has columns with those names, so the names are not ambiguous. Writing s.last_call_et and s.status is clearer to the next reader and does not change the result.

Key Points

  • LAST_CALL_ET has two meanings. Oracle documents that when STATUS is ACTIVE, the value is the elapsed time in seconds since the session became active. When STATUS is INACTIVE, it is the elapsed time in seconds since the session became inactive. Read it together with STATUS, never alone.
  • SID alone is not a safe key. Oracle says SERIAL# "guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID". So a SID can be reused. Keep SID and SERIAL# together as a pair.
  • STATUS has five documented values. They are ACTIVE (session currently executing SQL), INACTIVE, KILLED (marked to be terminated), CACHED (temporarily cached for use by Oracle*XA) and SNIPED (an inactive session that has exceeded a configured limit and will not be allowed to become active again).
  • SPID is not always unique. Oracle states that when the multiprocess/multithread feature is enabled on UNIX, RDBMS processes are mapped to threads running in operating system processes, and the SPID identifier is not unique. In that case SPID and STID together identify an RDBMS process. When the feature is not enabled on UNIX, SPID is unique.
  • no rows selected is a valid answer. It means the join found no session for that value. It does not mean the database is healthy or that the process is harmless.

Insights and Best Practices

Start from the operating system, finish in the database

This query runs in the opposite direction of the usual session query. Most session scripts start from a user or an event and end at a process. Here you start with a number from the operating system and end with a user, a SID and a SERIAL#. Once you have those, other views can answer deeper questions. V$SESSION also carries the SQL_ID of the statement being executed, the EVENT the session is waiting for or last waited for, and the OSUSER of the client. Add the columns you need to the select list.

Read STATUS and LAST_CALL_ET as a pair

The same number means different things in different states. A session that is ACTIVE with a LAST_CALL_ET of 3600 has been working on its current call for about an hour. A session that is INACTIVE with the same value has been idle for about an hour. The first may be a long job or a stuck one. The second is usually an application that holds a connection open and is waiting for its next request. Without the status, the number cannot be read.

Match both the pair and the process before you end anything

The ALTER SYSTEM statement identifies a session by two values taken from V$SESSION: SID for the first integer and SERIAL# for the second. Oracle also states that the session to end must be on the same instance as your own unless you give the optional instance ID, which you can find by querying the GV$ views. The statements are:

1ALTER SYSTEM KILL SESSION 'sid,serial#';
2ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

Oracle describes KILL SESSION as marking a session as terminated, rolling back ongoing transactions, releasing all session locks and partially recovering session resources. DISCONNECT SESSION disconnects the session by destroying the dedicated server process. Its POST_TRANSACTION setting lets ongoing transactions finish first, and its IMMEDIATE setting disconnects without waiting for them.

Take the SID and SERIAL# from the output of this query, not from memory or from an older listing. The list changes all the time, and a number reused by a new session would point at the wrong target. Run the query again just before the statement and confirm the user name is the one you expect. For the full set of options and their effects, see the killing-sessions article in the references.

Know when the operating system command is the last resort

A kill from the operating system acts on the process, and the SPID from this query is the number to use. Community guidance on this topic advises you to identify the session first and to escalate to a forced operating system kill only if the process has not stopped after a few minutes. The reason to look first is simple: the database cannot tell you what the session was doing if you remove the process before you read it.

Use a GV$ view on Oracle RAC

The query uses the instance-level views. Oracle's own ALTER SYSTEM page points to the GV$ views as the place to find an instance ID, and the killing-sessions article names the [G]V$SESSION and [G]V$PROCESS pair for this lookup. On a clustered database, use the GV$ form of both views and add inst_id to the join and the select list, so that you know which node owns the process.

Add a short list of extra columns for faster triage

A practical form of this query adds s.osuser, s.sql_id, s.event and s.server to the select list. OSUSER is the operating system user name of the client. SERVER shows whether the session uses a DEDICATED, SHARED, PSEUDO, POOLED or NONE server type. These columns turn a single lookup into a first-pass report, with no second query.

When to Run This

  • An operating system monitor shows a busy Oracle process and you must find the owner.
  • A system administrator reports a PID and asks what it is doing in the database.
  • Before an ALTER SYSTEM KILL SESSION or DISCONNECT SESSION, to confirm the user and the status of the target.
  • After a process was ended at the operating system level, to confirm that the session shows as gone or marked for termination.
  • During an incident review, to attach a user name and a status to a PID found in a log or a trace file name.
  • When you need the SID and SERIAL# of a session and the only thing you have is the process ID.

Troubleshooting Common Issues

The query returns no rows selected. First check the number. The process may have ended since you read it, or the value may be mistyped. The quotes around &pid make the comparison a text match, so extra spaces in the value will also cause a miss. Then check that you are connected to the right instance and the right host, because SPID is an operating system identifier that only means something on the server where the process runs. If the process exists in the operating system but not here, it may not belong to a database session at all.

More than one row returns. With the multiprocess/multithread feature enabled on UNIX, the SPID is not unique, as Oracle documents. Add p.stid to the select list. Oracle states that SPID and STID together uniquely identify an RDBMS process in that case.

The status column reads KILLED or SNIPED. KILLED means the session is marked to be terminated, which is the state after a KILL SESSION request. SNIPED means the session was inactive and exceeded a configured limit, for example a resource limit of the resource manager consumer group or idle_time in the user profile, and it cannot become active again. Both are expected after a limit or a kill, and neither needs a second kill statement at once.

LAST_CALL_ET seems too large or too small. Check STATUS first. The number counts from the moment the session became active or inactive, so a long idle connection shows a large value with INACTIVE. It is not a measure of total CPU or of how long the user has been connected.

An ambiguous column error appears after you edit the query. If you add columns to the select list, qualify each one with the alias s or p. Two views in one join make unqualified names a risk as the select list grows.

References

Posts in this series