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$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.
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.
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.
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.
You can find the full query below.
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.
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.
