A logon storm refers to a situation where there are an unusually high number of logins within a short period of time, which can lead to serious performance issues or even denial of service.

While logging into an Oracle database is expensive, an often overlooked fact is that logging off can be even more costly. This is because Oracle must clean up and release various resources, including relevant objects in the shared pool. This underscores the importance of monitoring both logon and logoff activities.

The view v$session records both active and inactive sessions, providing snapshot of the current state of all sessions in the database. However, immediate investigation during the storm might not be feasible due to the strain on system resources. So, the go-to sources for investigating logon storms are ASH (Active Session History) data and listener logs.

Monitoring Session Creation Activities with ASH

The combination of session_id and session_serial# uniquely identify a session. By counting distinct occurrences of both columns, we can gauge the total number of unique sessions, thereby offering a clear picture of logon and logoff trends.
The query below generates a timeline of distinct session counts over the past 24 hours, segmented by hour. This visualization can help identify spikes in activity.

SELECT 
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24') AS hour,
    COUNT(DISTINCT session_id || '-' || session_serial#) AS distinct_sessions
FROM 
    dba_hist_active_sess_history
WHERE 
    sample_time >= SYSDATE - INTERVAL '1' DAY
GROUP BY 
    TO_CHAR(sample_time, 'YYYY-MM-DD HH24')
ORDER BY 
    hour;

An output might like:

HOUR              DISTINCT_SESSIONS
2024-08-18 15                    15
2024-08-18 16                    19
2024-08-18 17                    21
2024-08-18 18                    18
2024-08-18 19                    18
2024-08-18 20                    15
2024-08-18 21                    37
2024-08-18 22                    25
2024-08-18 23                    27
2024-08-19 00                  1219
2024-08-19 01                    31
2024-08-19 02                    23
2024-08-19 03                    22
2024-08-19 04                    27
2024-08-19 05                    37
2024-08-19 06                    34
2024-08-19 07                    23
2024-08-19 08                    27
2024-08-19 09                    40
2024-08-19 10                    21
2024-08-19 11                    21
2024-08-19 12                    31
2024-08-19 13                    32
2024-08-19 14                    23
2024-08-19 15                    24

There is a prominent spike in activity the midnight (12:00 AM to 1:00 AM) . To delve deeper, we can employ the following query to pinpoint the source of these sessions during the specified timeframe.

SELECT session_id, 
       machine, 
       module, 
       COUNT(DISTINCT session_serial#) AS session_count
FROM   dba_hist_active_sess_history
WHERE  sample_time BETWEEN TRUNC(SYSDATE) 
                       AND TRUNC(SYSDATE) + INTERVAL '1' HOUR
  AND  session_type = 'FOREGROUND'
GROUP BY session_id, 
         machine, 
         module
ORDER BY session_count desc;

An example of output might resemble this:

  SESSION_ID MACHINE           MODULE                                    SESSION_COUNT
        5930 dell.company      oraagent.bin@dell.company(TNS V1-V3)                  1
        5329 DESKTOP-8IUH6S4   SQL Developer                                         1
        9076 dell.company      emagent_SQL_oracle_database                           1
         244 dell.company      JDBC Thin Client                                   1216

As evident from the result, the surge in sessions primarily stemed from the activity on dell.company using the JDBC Thin Client module. To address this, it’s recommended to investigate the the logic of the Java program running on dell.company machine.

Identifying Logon Storms with Listener Logs

Listener logs records connection attempts, making them another a source for investigating logon storms.

The command below provides a summary of how many connections were established during each minute of the specified hour, indicating periods of high connection activity.

oracle@Yuan trace$  fgrep "19-AUG-2024 13" listener.log  |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 }' |sort |uniq -c
# Output (example):
     32 19-AUG-2024 13:46
     57 19-AUG-2024 13:47
     13 19-AUG-2024 13:48
      3 19-AUG-2024 13:52

By analyzing the listener log, we can pinpoint timestamps with unusually high connection establishment counts, signifying potential logon storms.