Track Top 10 CPU-Consuming Oracle Sessions

In Oracle database performance tuning, one of the most useful queries to run is one that helps you identify the most resource-intensive sessions. This query can give you valuable insights into the active sessions on your Oracle database, providing details on CPU usage, disk reads/writes, and wait events. By understanding the sessions consuming the most resources, you can address potential performance bottlenecks and optimize your database’s performance.

Below is a SQL query designed to fetch detailed information about the top 10 active Oracle sessions based on CPU time. Let’s break down the query and explain each part of it to help you understand how to extract relevant session data effectively.

SET LINESIZE 300
SET PAGESIZE 50
COL session_id FORMAT A12 HEADING "Session ID"
COL username FORMAT A15 HEADING "DB Username"
COL osuser FORMAT A15 HEADING "OS Username"
COL hostname FORMAT A25 HEADING "Host Name"
COL job_name FORMAT A25 HEADING "Program|Job Name"
COL sql_id FORMAT A15 HEADING "SQL ID"
COL sql_text FORMAT A70 HEADING "SQL Text"
COL login_time FORMAT A25 HEADING "Login Time"
COL status FORMAT A10 HEADING "Session|Status"
COL event FORMAT A35 HEADING "Wait Event"
COL cpu_time_sec FORMAT A10 HEADING "CPU Time|(Sec)"
COL pga_used_mb FORMAT A10 HEADING "PGA Used|(MB)"
COL reads FORMAT A12 HEADING "Disk Reads"
COL writes FORMAT A12 HEADING "Disk Writes"
COL blocking_session FORMAT A12 HEADING "Blocking|Session"
SELECT 
    s.sid || ',' || s.serial# AS session_id,
    s.username AS db_username,
    s.osuser AS os_username,
    s.machine AS hostname,
    s.program AS job_name,
    s.logon_time AS login_time,
    s.status,
    s.blocking_session,
    s.event AS wait_event,
    q.sql_id,
    SUBSTR(q.sql_text, 1, 70) AS sql_text,
    ROUND(s.cpu_time / 1000000, 2) AS cpu_time_sec,
    ROUND(s.pga_used_mem / 1024 / 1024, 2) AS pga_used_mb,
    ROUND(s.reads, 2) AS disk_reads,
    ROUND(s.writes, 2) AS disk_writes
FROM 
    v$session s
LEFT JOIN 
    v$sql q ON s.sql_id = q.sql_id
WHERE 
    s.username IS NOT NULL -- Exclude background sessions
ORDER BY 
    s.cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

Sample Output

Session ID DB Username OS Username Host Name Program/Job Name SQL ID SQL Text Login Time Session Status Wait Event CPU Time (Sec) PGA Used (MB) Disk Reads Disk Writes Blocking Session
123,4567 USER1 osuser1 server1 SQL*Plus abc123 SELECT * FROM employees 22-DEC-2024 10:15:30 ACTIVE db file sequential read 120.50 45.75 1500 2000 NULL
124,4568 USER2 osuser2 server2 JavaApp xyz456 INSERT INTO orders (id, value) 22-DEC-2024 10:20:05 ACTIVE log file sync 90.25 32.50 500 1000 123,4567
125,4569 USER3 osuser3 server3 SQL Developer def789 UPDATE products SET price = 100 22-DEC-2024 09:50:20 INACTIVE direct path read 85.10 12.30 2000 3000 124,4568
126,4570 USER4 osuser4 server4 PL/SQL Job ghi012 DELETE FROM logs WHERE created < ‘2023’ 22-DEC-2024 09:45:10 ACTIVE db file scattered read 70.15 40.00 1000 500 NULL
127,4571 USER5 osuser5 server5 AppServer jkl345 SELECT COUNT(*) FROM sales 22-DEC-2024 09:30:00 ACTIVE SQL*Net message to client 65.30 10.20 1200 1500 125,456

Query Explanation:

1. Session Details and Formatting:

The query starts with a series of commands that format the output for better readability:

• SET LINESIZE 300: Sets the line width for better output visibility.
• SET PAGESIZE 50: Limits the number of rows per page, preventing the result set from being too large to view at once.
• COL statements: These set custom headings for each column in the result set, improving clarity and presentation.

2. Columns Selected:

• session_id: A unique identifier for the session, combining the session ID (sid) and serial number (serial#) to handle session uniqueness.
• db_username: The database user associated with the session.
  os_username: The OS user executing the session.
• hostname: The machine name from which the session is running.
• job_name: The program or job name associated with the session.
• login_time: When the session was logged in.
  status: Current status of the session (e.g., ACTIVE, INACTIVE).
• blocking_session: If any other session is blocking the current session, it will show up here.
• wait_event: The event on which the session is currently waiting (e.g., I/O, lock contention).
• sql_id: The SQL identifier of the SQL statement being executed by the session.
• sql_text: The actual SQL text, truncated to 70 characters for clarity.
• cpu_time_sec: The CPU time consumed by the session in seconds, calculated by dividing the cpu_time (in microseconds) by 1 million.
• pga_used_mb: The memory used by the session’s Program Global Area (PGA), converted into megabytes.
• disk_reads and disk_writes: The number of disk reads and writes performed by the session.

3. Joins and Filters:

• The query joins the v$session view with the v$sql view to get the SQL details for active sessions.
• It filters out background sessions (s.username IS NOT NULL), focusing on user sessions that are actively interacting with the database.

4. Sorting and Limiting Results:

• The ORDER BY s.cpu_time DESC clause sorts the sessions based on CPU time in descending order, highlighting the most CPU-intensive sessions first.
• The FETCH FIRST 10 ROWS ONLY limits the result set to the top 10 sessions, making it easier to focus on the highest priority sessions.

5. Why This Query is Useful:

This query is essential for Oracle DBAs and performance tuning specialists because it provides a snapshot of the most resource-intensive sessions in your database. By focusing on CPU time and session wait events, you can identify slow or problematic queries that might be causing performance issues.

Here’s how you can use the output:

• High CPU Time: If a session is consuming an excessive amount of CPU, it could indicate inefficient queries or potential optimizations that can be made.
• Blocking Sessions: If a session is blocking others, you can investigate the cause of the lock and resolve it to improve overall database performance.
• Wait Events: By identifying common wait events (e.g., disk I/O waits), you can pinpoint where the system is under stress, whether it’s in CPU processing, disk operations, or network latency.

Conclusion:

Regularly running queries like this one helps you monitor active sessions, identify bottlenecks, and keep your Oracle database running smoothly. By analyzing session performance data, you can proactively address issues before they negatively impact users or applications.

This is just one of the many tools in the DBA’s toolkit for performance monitoring and tuning. Be sure to explore more queries and methods to keep your Oracle databases optimized!