In Oracle database performance tuning, identifying resource-intensive sessions is crucial. A well-crafted query provides insights into active sessions, highlighting CPU usage, disk I\/O, and wait events. By analyzing the top 10 sessions based on CPU time, you can address performance bottlenecks and optimize your database.<\/p>\n\n\n\n
Below is a SQL query to fetch details on the top 10 active Oracle sessions by CPU usage, with an explanation of each part for better understanding.<\/p>\n\n\n\n
SET LINESIZE 300\nSET PAGESIZE 50\nCOL session_id FORMAT A12 HEADING \"Session ID\"\nCOL username FORMAT A15 HEADING \"DB Username\"\nCOL osuser FORMAT A15 HEADING \"OS Username\"\nCOL hostname FORMAT A25 HEADING \"Host Name\"\nCOL job_name FORMAT A25 HEADING \"Program|Job Name\"\nCOL sql_id FORMAT A15 HEADING \"SQL ID\"\nCOL sql_text FORMAT A70 HEADING \"SQL Text\"\nCOL login_time FORMAT A25 HEADING \"Login Time\"\nCOL status FORMAT A10 HEADING \"Session|Status\"\nCOL event FORMAT A35 HEADING \"Wait Event\"\nCOL cpu_time_sec FORMAT A10 HEADING \"CPU Time|(Sec)\"\nCOL pga_used_mb FORMAT A10 HEADING \"PGA Used|(MB)\"\nCOL reads FORMAT A12 HEADING \"Disk Reads\"\nCOL writes FORMAT A12 HEADING \"Disk Writes\"\nCOL blocking_session FORMAT A12 HEADING \"Blocking|Session\"\nSELECT \n s.sid || ',' || s.serial# AS session_id,\n s.username AS db_username,\n s.osuser AS os_username,\n s.machine AS hostname,\n s.program AS job_name,\n s.logon_time AS login_time,\n s.status,\n s.blocking_session,\n s.event AS wait_event,\n q.sql_id,\n SUBSTR(q.sql_text, 1, 70) AS sql_text,\n ROUND(s.cpu_time \/ 1000000, 2) AS cpu_time_sec,\n ROUND(s.pga_used_mem \/ 1024 \/ 1024, 2) AS pga_used_mb,\n ROUND(s.reads, 2) AS disk_reads,\n ROUND(s.writes, 2) AS disk_writes\nFROM \n v$session s\nLEFT JOIN \n v$sql q ON s.sql_id = q.sql_id\nWHERE \n s.username IS NOT NULL -- Exclude background sessions\nORDER BY \n s.cpu_time DESC\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\nSample Output<\/h2>\n\n\n\n
Session ID\tDB Username\tOS Username\tHost Name\tProgram\/Job Name\tSQL ID\tSQL Text\tLogin Time\tSession Status\tWait Event\tCPU Time (Sec)\tPGA Used (MB)\tDisk Reads\tDisk Writes\tBlocking Session
123,4567\tUSER1\tosuser1\tserver1\tSQL*Plus\tabc123\tSELECT * FROM employees\t22-DEC-2024 10:15:30\tACTIVE\tdb file sequential read\t120.50\t45.75\t1500\t2000\tNULL
124,4568\tUSER2\tosuser2\tserver2\tJavaApp\txyz456\tINSERT INTO orders (id, value)\t22-DEC-2024 10:20:05\tACTIVE\tlog file sync\t90.25\t32.50\t500\t1000\t123,4567
125,4569\tUSER3\tosuser3\tserver3\tSQL Developer\tdef789\tUPDATE products SET price = 100\t22-DEC-2024 09:50:20\tINACTIVE\tdirect path read\t85.10\t12.30\t2000\t3000\t124,4568
126,4570\tUSER4\tosuser4\tserver4\tPL\/SQL Job\tghi012\tDELETE FROM logs WHERE created < ‘2023’\t22-DEC-2024 09:45:10\tACTIVE\tdb file scattered read\t70.15\t40.00\t1000\t500\tNULL
127,4571\tUSER5\tosuser5\tserver5\tAppServer\tjkl345\tSELECT COUNT(*) FROM sales\t22-DEC-2024 09:30:00\tACTIVE\tSQL*Net message to client\t65.30\t10.20\t1200\t1500\t125,456<\/p>\n\n\n\n
Query Explanation:<\/h2>\n\n\n\n1. Session Details and Formatting:<\/h3>\n\n\n\n
The query starts with a series of commands that format the output for better readability:<\/p>\n\n\n\n
\n- SET LINESIZE 300<\/strong>: Sets the line width for better output visibility.<\/li>\n\n\n\n
- SET PAGESIZE 50<\/strong>: Limits the number of rows per page, preventing the result set from being too large to view at once.<\/li>\n\n\n\n
- COL statements<\/strong>: These set custom headings for each column in the result set, improving clarity and presentation.<\/li>\n<\/ul>\n\n\n\n
2. Columns Selected:<\/h3>\n\n\n\n\n- session_id<\/strong>: A unique identifier for the session, combining the session ID (sid) and serial number (serial#) to handle session uniqueness.<\/li>\n\n\n\n
- db_username<\/strong>: The database user associated with the session.
os_username: The OS user executing the session.<\/li>\n\n\n\n- hostname<\/strong>: The machine name from which the session is running.<\/li>\n\n\n\n
- job_name<\/strong>: The program or job name associated with the session.<\/li>\n\n\n\n
- login_time<\/strong>: When the session was logged in.
status: Current status of the session (e.g., ACTIVE, INACTIVE).<\/li>\n\n\n\n- blocking_session<\/strong>: If any other session is blocking the current session, it will show up here.<\/li>\n\n\n\n
- wait_event<\/strong>: The event on which the session is currently waiting (e.g., I\/O, lock contention).<\/li>\n\n\n\n
- sql_id<\/strong>: The SQL identifier of the SQL statement being executed by the session.<\/li>\n\n\n\n
- sql_text<\/strong>: The actual SQL text, truncated to 70 characters for clarity.<\/li>\n\n\n\n
- cpu_time_sec<\/strong>: The CPU time consumed by the session in seconds, calculated by dividing the cpu_time (in microseconds) by 1 million.<\/li>\n\n\n\n
- pga_used_mb<\/strong>: The memory used by the session’s Program Global Area (PGA), converted into megabytes.<\/li>\n\n\n\n
- disk_reads<\/strong> and disk_writes<\/strong>: The number of disk reads and writes performed by the session.<\/li>\n<\/ul>\n\n\n\n
3. Joins and Filters:<\/h2>\n\n\n\n\n- The query joins the v$session view with the v$sql view to get the SQL details for active sessions.<\/li>\n\n\n\n
- It filters out background sessions (s.username IS NOT NULL), focusing on user sessions that are actively interacting with the database.<\/li>\n<\/ul>\n\n\n\n
4. Sorting and Limiting Results:<\/h2>\n\n\n\n\n- 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.<\/li>\n\n\n\n
- 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.<\/li>\n<\/ul>\n\n\n\n
5. Why This Query is Useful:<\/h2>\n\n\n\n
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\u2019s how you can use the output:<\/p>\n\n\n\n
\n- High CPU Time<\/strong>: If a session is consuming an excessive amount of CPU, it could indicate inefficient queries or potential optimizations that can be made.<\/li>\n\n\n\n
- Blocking Sessions<\/strong>: If a session is blocking others, you can investigate the cause of the lock and resolve it to improve overall database performance.<\/li>\n\n\n\n
- Wait Events<\/strong>: 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.<\/li>\n<\/ul>\n\n\n\n
Conclusion:<\/h2>\n\n\n\n
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\u2019s toolkit for performance monitoring and tuning. Be sure to explore more queries and methods to keep your Oracle databases optimized!<\/p>\n","protected":false},"excerpt":{"rendered":"
In Oracle database performance tuning, identifying resource-intensive sessions is crucial. A well-crafted query provides insights into active sessions, highlighting CPU usage, disk I\/O, and wait events. By analyzing the top 10 sessions based on CPU time, you can address performance bottlenecks and optimize your database. Below is a SQL query to fetch details on the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-509","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/509","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=509"}],"version-history":[{"count":5,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/509\/revisions"}],"predecessor-version":[{"id":1508,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/509\/revisions\/1508"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}