How can we analyze total wait times in the last 15 minutes on Oracle RDBMS by users?

Performance monitoring and optimization processes are critical for system experts and IT service owners. In this process, I will try to detail one of the queries we use to analyze users’ waits and detect performance problems.

First of all, in order to run the query, our user must have SELECT ANY DICTIONARY authorization.

If we have authorization, let’s create our query step by step.

Step 1: Access Database Views

Oracle RDBMS provides several specialized views for performance monitoring and management. These views give database administrators and system experts access to detailed information to identify, monitor and resolve performance issues.

The GV$ACTIVE_SESSION_HISTORY view contains historical session activities and waits. This view contains information such as when each session started, what operations it performed, and how long it waited. This data is used to understand the workload on the system, the status of sessions and performance issues.

GV$SESSION view contains the current session information. This view contains information such as which user has opened which session, the status of the session, and the program they are running. It provides basic data for monitoring and managing sessions.

SELECT * FROM GV$ACTIVE_SESSION_HISTORY;
SELECT * FROM GV$SESSION;

Step 2: Selecting Data from the Last 15 Minutes

In our query, we select session activities and waits in the last 15 minutes with the condition active_session_history.sample_time > (sysdate-1/(24 * 4)). This time period limits our analysis time and shows the current performance status.

Step 3: Combining Session and Wait Information

By combining the GV$ACTIVE_SESSION_HISTORY and GV$SESSION views, we determine which session each wait belongs to. This way we make sure that each wait is assigned to the correct user.

FROM GV$ACTIVE_SESSION_HISTORY active_session_history,
     GV$SESSION session
WHERE active_session_history.session_id = session.sid
  AND active_session_history.inst_id = session.inst_id
  AND session.username IS NOT NULL

Step 4: Collecting Wait Times by User

Calculating the total wait time for each user is important to identify the users who wait the most. We calculate the total duration of waits using the SUM function.

SELECT session.username,
       SUM(active_session_history.wait_time + active_session_history.time_waited) AS total_WaitTime

Step 5: Grouping and Sorting by User

By grouping the results by user and sorting them in descending order according to the total duration of waits, we highlight the users who wait the longest.

group by session.inst_id, session.sid, session.username
 order by 4 desc

Step 6: Marking and Evaluating Results

When presenting the results to the user, we include an informative text to help them understand them. This text helps to interpret the analysis results correctly and take steps to improve performance.

'Waits by user last 15 minutes' as comment

You can find the full query below.

select t.*,
       CASE
           WHEN ROWNUM <= 5 THEN 'Highest wait: ' || t.username || ' with ' || t.total_WaitTime || ' seconds'
           ELSE 'Waits by user last 15 minutes'
       END as comment
from
(

  select session.inst_id,
         session.sid,
         session.username,
         sum(active_session_history.wait_time +
             active_session_history.time_waited) total_WaitTime
    from gv$active_session_history active_session_history,
         gv$session session
   where active_session_history.sample_time> (sysdate-1/(24 * 4))
     and active_session_history.session_id = session.sid
     and active_session_history.inst_id = session.inst_id
     and session.username is not null
     and rownum < 20
 group by session.inst_id, session.sid, session.username
 order by 4 desc
) t

+---------+------+----------+----------------+-------------------------------------+
| inst_id | sid  | username | total_WaitTime | comment                             |
+---------+------+----------+----------------+-------------------------------------+
| 1       | 123  | user1    | 1780           | Waits by user last 15 minutes       |
| 2       | 456  | user2    | 1290           | Waits by user last 15 minutes       |
| 3       | 789  | user3    | 950            | Waits by user last 15 minutes       |
| 4       | 1011 | user4    | 740            | Waits by user last 15 minutes       |
| 5       | 1213 | user5    | 670            | Waits by user last 15 minutes       |
| 6       | 1415 | user6    | 580            | Waits by user last 15 minutes       |
| 7       | 1617 | user7    | 520            | Waits by user last 15 minutes       |
| 8       | 1819 | user8    | 450            | Waits by user last 15 minutes       |
| 9       | 2021 | user9    | 390            | Waits by user last 15 minutes       |
| 10      | 2223 | user10   | 310            | Waits by user last 15 minutes       |
| 11      | 2425 | user11   | 270            | Waits by user last 15 minutes       |
| 12      | 2627 | user12   | 210            | Waits by user last 15 minutes       |
| 13      | 2829 | user13   | 170            | Waits by user last 15 minutes       |
| 14      | 3031 | user14   | 150            | Waits by user last 15 minutes       |
| 15      | 3233 | user15   | 120            | Waits by user last 15 minutes       |
| 16      | 3435 | user16   | 90             | Waits by user last 15 minutes       |
| 17      | 3637 | user17   | 80             | Waits by user last 15 minutes       |
| 18      | 3839 | user18   | 60             | Waits by user last 15 minutes       |
| 19      | 4041 | user19   | 40             | Waits by user last 15 minutes       |
| 20      | 4243 | user20   | 20             | Waits by user last 15 minutes       |
+---------+------+----------+----------------+-------------------------------------+

If we want to specify the 5 users with the highest wait separately, it will be enough to make a small edit on the query.

 CASE
           WHEN ROWNUM <= 5 THEN 'Highest wait: ' || t.username || ' with ' || t.total_WaitTime || ' seconds'
           ELSE 'Waits by user last 15 minutes'
       END as comment

+---------+-----+----------+-----------------+-------------------------------------------+
| INST_ID | SID | USERNAME | TOTAL_WAITTIME  | COMMENT                                   |
+---------+-----+----------+-----------------+-------------------------------------------+
| 1       | 123 | user1    | 3600 seconds    | Highest wait: user1 with 3600 seconds     |
| 1       | 456 | user2    | 2500 seconds    | Highest wait: user2 with 2500 seconds     |
| 2       | 789 | user3    | 1800 seconds    | Highest wait: user3 with 1800 seconds     |
| 1       | 101 | user4    | 1200 seconds    | Highest wait: user4 with 1200 seconds     |
| 2       | 202 | user5    | 1000 seconds    | Highest wait: user5 with 1000 seconds     |
| ...     | ... | ...      | ...             | Waits by user last 15 minutes             |
+---------+-----+----------+-----------------+-------------------------------------------+

Wait times can be affected by high CPU utilization, reductions in disk access speed and capacity, high memory usage, problems with network traffic or communication status, priorities or queues of certain processes, and high user traffic or heavy workloads. The intervals at which these metrics are collected are also critical. For example, if we are examining a specific time interval, we can only see details and instantaneous spikes (rapid increase or decrease) when we reduce our metric collection interval from 1 hour or daily to 5 or even 1 minute. Only after this detailed analysis can the root cause of performance issues be identified and appropriate solutions developed.

I plan to mention which performance indicators we analyzed in my next articles.

I hope that this article, my first content, will be useful for you. Your feedback is very valuable to me, I would be happy if you contact me about feedbacks. I want to publish my content in series, stay tuned!

I wish everyone a happy day.