Monitoring Active and Idle User Sessions in Oracle Database

calendar Nov 15, 2023 / · 10 min read · sessions connected-users oracle v$session  ·
Share on: linkedin copy

Show all the Oracle users connected to the database

Purpose

A user calls and says the application has been slow since lunch. You need to know who is connected to the database right now, which sessions are working, and which ones are only holding a connection open. A short query against V$SESSION answers all three questions in one screen.

The script below lists every session that has an Oracle username. For each one it shows the user name, a session key in the form sid,serial#, the status, and the number of seconds since that status began. Sessions that have been busy for a long time and sessions that have been idle for a long time both rise to the top of their group.

Use this view for connected sessions. It is not the place to look for user accounts that exist in the database but are not logged on. That question needs a different data source.

Code

 1set lines 100 pages 999
 2col ID format a15
 3col USERNAME format a20
 4select username
 5,      sid || ',' || serial# "ID"
 6,      status
 7,      last_call_et "Last Activity"
 8from   v$session
 9where  username is not null
10order by status desc
11,        last_call_et desc
12/

Sample output from a small test database:

 1USERNAME             ID              STATUS   Last Activity
 2-------------------- --------------- -------- -------------
 3ORDS_PUBLIC_USER     286,58632       INACTIVE          1709
 4ORDS_PUBLIC_USER     52,56550        INACTIVE          1708
 5ORDS_PUBLIC_USER     53,20873        INACTIVE          1708
 6SYS                  15,1554         ACTIVE            1843
 7SYS                  237,34880       ACTIVE              81
 8SYS                  36,39003        ACTIVE               0
 9
106 rows selected.

A wider variant adds the client details. Use it when you need to know which machine and program own a session.

 1set lines 200 pages 999
 2col username format a20
 3col machine  format a25
 4col program  format a30
 5select username
 6,      sid || ',' || serial# "ID"
 7,      status
 8,      last_call_et "Last Activity"
 9,      machine
10,      program
11,      sql_id
12from   v$session
13where  username is not null
14order by status desc
15,        last_call_et desc
16/

Code Breakdown

Formatting output

  • set lines 100 pages 999 sets a line width of 100 characters and a page length of 999 lines. SQL*Plus counts heading lines in the page length, so output of up to 999 lines prints as one block with no repeated headings. A longer result starts a new page with a new heading.
  • col ID format a15 and col USERNAME format a20 fix the width of two columns. This keeps the layout stable when a user name is long.

Selecting the columns

  • username is documented as "Oracle username". It is the database account that owns the session.
  • sid || ',' || serial# "ID" joins two columns into one text value. The SID column is "Session identifier". The SERIAL# column is described as "Session serial number. Used to uniquely identify a session's objects. 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." This pair is also the pair you need if you ever have to end a session, which is why the script prints it in the sid,serial# form.
  • status shows the state of the session. The two values you will see most are ACTIVE (session currently executing SQL) and INACTIVE (a session which is inactive and either has no configured limits or has not yet exceeded the configured limits).
  • last_call_et is shown with the heading "Last Activity". Its meaning depends on the status. For an ACTIVE session it is the elapsed time in seconds since the session became active. For an INACTIVE session it is the elapsed time in seconds since the session became inactive.

Filtering and sorting

  • where username is not null keeps only the rows that have a user name. In the sample output, only user sessions appear. Background processes are not in the list. Check your own output to see if this holds for your database and version.
  • order by status desc sorts the status text from Z to A. INACTIVE comes before ACTIVE, as the sample output shows.
  • last_call_et desc sorts inside each status group. In the ACTIVE group the longest-running sessions come first. In the INACTIVE group the longest-idle sessions come first.

The wider variant

The second query adds three columns. machine is the "Operating system machine name". program is the "Operating system program name". sql_id is the "SQL identifier of the SQL statement that is currently being executed". Together they tell you where a session comes from and what it is running.

Key Points

  • One number, two meanings. last_call_et counts from the start of the current state. A value of 1843 on an ACTIVE row means the session has been busy for 1843 seconds. The same value on an INACTIVE row means it has been idle for that long. Always read it together with the status.
  • A high number is a clue, not a fault. An application account that shows many idle rows with similar times, like the ORDS_PUBLIC_USER rows in the sample, may simply be a pool of connections. Confirm that with the application owner. A long ACTIVE time on an interactive account deserves a closer look.
  • There are more statuses than two. The view also documents KILLED ("Session marked to be terminated"), CACHED ("Session temporarily cached for use by Oracle*XA") and SNIPED. The query shows all of them, because it has no filter on status.
  • The view shows the sessions that exist now. The documentation states that V$SESSION "displays session information for each current session". It is a snapshot, so run it again to see a trend.
  • The sort order is plain text order. status is a text column, so desc puts KILLED, INACTIVE and ACTIVE in reverse alphabetical order. Read the whole list, not only the top rows.

Insights and Best Practices

Read the status and the time together

Start with the ACTIVE rows that have a large last_call_et. Those are sessions that have been executing for a long time. Note the SID and SERIAL#, then use the wider query to get the sql_id, the machine and the program. With that you can ask the application owner about the job instead of guessing.

Then look at the INACTIVE rows with a large time. If a person left a tool open over lunch, the time will be large and harmless. If an application opened a transaction and then went quiet, the time will also be large, but the effect is not harmless. The query does not show open transactions, so treat a long idle time as a reason to ask a question, not as proof of a problem.

Add the blocking columns when users report a hang

When people say the system is stuck, a long idle list is rarely the answer. Add blocking_session to the select list. The documentation says this column holds the "Session identifier of the blocking session" and that it "is valid only if BLOCKING_SESSION_STATUS has the value VALID". The status column takes four values: VALID, NO HOLDER, NOT IN WAIT and UNKNOWN. Select blocking_session_status next to it, and ignore the blocker column unless the status says VALID.

The event column shows the resource or event for which the session is waiting. If the session is not in a wait, it shows the event for which the session most recently waited. Do not read it as a current wait without checking.

Use profile limits for idle sessions, not manual cleanup

If idle sessions are a standing problem, a profile can limit them. IDLE_TIME is the permitted period of continuous inactive time during a session, in minutes. The documentation adds that "Long-running queries and other operations are not subject to this limit." and that when you set a timeout, the session will take a couple of additional minutes to be terminated. The documentation says you "can specify resource limits for users regardless of whether the resource limits are enabled" but that Oracle Database "does not enforce the limits until you enable them". The RESOURCE_LIMIT parameter controls this. In 19c its default value is true, so unless someone set it to FALSE, an IDLE_TIME limit starts ending sessions as soon as the profile is assigned to a user. Check the parameter with show parameter resource_limit before you assign the profile. Test this on a non-production database first, because it ends sessions.

This also explains the SNIPED status. It is an inactive session that has exceeded a configured limit, and such sessions are not allowed to become active again.

Know the cost of ending a session

If you must end a session, use the SID and SERIAL# from the script. The ALTER SYSTEM KILL SESSION clause, in the words of the reference, "lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources". For the first number you give the SID value and for the second you give the SERIAL# value.

Be aware that the call may not finish right away. If the session must complete some activity first, such as rolling back a transaction, then Oracle Database "waits for this activity to complete, marks the session as terminated, and then returns control to you". While a session is marked in this way, expect its status to read KILLED in the output of this query. Ask the owner first, and record the SID, SERIAL#, user and program before you act.

Save the output

Run the query on a schedule during an incident and keep each result in a file with the time in the name. A single snapshot shows a state. A series of snapshots shows which sessions grow older while others come and go.

When to Run This

  • When users report slow response and you need to see who is connected.
  • Before a planned restart, to see which accounts will be affected.
  • Before a change to a user account, to see if that account has live sessions.
  • When a connection limit is close and you need to know which accounts use the connections.
  • During a security review, to look for unexpected user names or client programs (use the wider query).
  • After a deployment, to confirm that the application pool opened its expected number of connections.

Troubleshooting Common Issues

The query returns no rows or fails. If the query fails with a privilege error, the account you use may not be allowed to read the view. This is an access problem. Ask your DBA to grant read access, or run the query from a DBA account. If it returns no rows at all, check that you are connected to the right instance or container.

A session you expect is missing. The filter username is not null hides any row without a user name. Remove the filter for one run to see all rows, then compare. In a multitenant database, the CON_ID column tells you the container that a row belongs to. The reference says the value 0 is used for rows that pertain to the entire CDB and for rows in a non-CDB.

A session shows KILLED and stays in the list. The reference defines this status as "Session marked to be terminated". As described above, the database waits for activity such as a rollback to finish. A large transaction can take time to roll back. Wait and run the query again before you take any other step.

A session does not end after a kill command. The kill command does not remove the session at once. The IMMEDIATE option tells the database to "roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately". Use it with care and only after you know what the session was doing.

The instance is part of a cluster. The ALTER SYSTEM reference says the target session must be on the same instance unless you give a third value, the instance ID. It also states that you "can find the instance ID by querying the GV$ tables". Run the session query on the right instance, or use the global view, before you act on a SID.

References

Posts in this series