Troubleshoot Active Sessions in SQL Server vs. Oracle

By: Pablo Echeverria | Updated: 2023-05-01 | Comments (1) | Related: > Monitoring

Problem

Mostly, when firefighting performance issues, you need to determine which SQL statements are currently running in response to one of the following:

The performance has degraded
CPU or memory has exceeded a threshold
Transactions are slow
Batch jobs are not completing after the expected amount of time

Even when the best approach is proactive monitoring, at this moment, it's important that you find what statements are currently running and how much resources they're taking so you can truly determine if it's a database issue and what action needs to be taken.

Solution

Every database management system includes views and system tables allowing you to view active sessions running, the SQL statements, and resource usage. You can query them easily, but interpreting the results can be challenging when you don't know how they're measured.

Here you will find the queries you need to run and an explanation of how they can be interpreted fast and easily. In case it's not the database, the next step is to check if there have been recent infrastructure changes, recent code changes, or external issues with the network, domain, storage, etc.

Oracle Query to Find Current Active Sessions

The views that provide the required information are v$session, v$sql_monitor, v$sqlarea, and v$sql_plan_monitor and are put together in the below query.

The query is provided below:

SELECT 
	M.SQL_TEXT "Text", 
	S.STATUS||' '||S.LAST_CALL_ET||'s' "Status", 
	ROUND(M.ELAPSED_TIME/1000000,2) "TimeSec", 
	ROUND(M.CPU_TIME/1000000,2) "CpuTimeSec",
 	ROUND(M.CONCURRENCY_WAIT_TIME/1000000,2) "ParallelTimeSec", 	ROUND(M.CLUSTER_WAIT_TIME/1000000,2) "ClusterTimeSec",
  	ROUND(M.USER_IO_WAIT_TIME/1000000,2) "IOtimeSec", 	DECODE(S.WAIT_TIME,0,S.SECONDS_IN_WAIT,0) "WaitTimeSec", 
	PM.PLAN_TIME "PlanTimeSec",
 	ROUND((M.PHYSICAL_READ_BYTES+M.PHYSICAL_WRITE_BYTES)/1024/1024/1024,2) "IOinGB", 	M.DISK_READS/1000 "DiskReadsK",
 	M.PX_MAXDOP "DOP",
 	ROUND(A.SHARABLE_MEM/1024/1024/1024,2) "SharableMemGB", 	ROUND(A.PERSISTENT_MEM/1024/1024/1024,2) "PersistentMemGB",
 	ROUND(A.RUNTIME_MEM/1024/1024/1024,2) "RuntimeMemGB", 	ROUND(PM.WORKAREA_MEM/1024/1024/1024,2) "WorkareaMemGB",
 	A.SORTS/1000 "SortsK", A.VERSION_COUNT "VersionCount",
  	ABS(ROUND(A.EXECUTIONS/EXTRACT(SECOND FROM SYSTIMESTAMP-TO_TIMESTAMP(A.FIRST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS')),2)) "Calls/Sec",
 	A.LOADS "Loads", 
	S.USERNAME "User",
	S.EVENT "Event",
	S.WAIT_CLASS "Wait",
	S.STATE "State", 
	M.STATUS "SqlStatus",
 	PM.PLAN_OPERATION||' '||PM.PLAN_OPTIONS "Operation", 
	PM.PLAN_OBJECT_OWNER||DECODE(PM.PLAN_OBJECT_OWNER,NULL,'','.')||PM.PLAN_OBJECT_NAME "Object",
 	'ALTER SYSTEM KILL '''||S.SID||','||S.SERIAL#||',@'||S.INST_ID||''' IMMEDIATE;' "Kill",
  	'SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('''||S.SQL_ID||''','||S.SQL_CHILD_NUMBER||',''ALL +PEEKED_BINDS''));' "ViewPlan"
FROM GV$SESSION S
LEFT JOIN GV$SQL_MONITOR M ON M.SID = S.SID AND M.SESSION_SERIAL# = S.SERIAL# AND M.INST_ID = S.INST_ID
LEFT JOIN GV$SQLAREA A ON A.SQL_ID = M.SQL_ID AND A.INST_ID = M.INST_ID
LEFT JOIN GV$SQL_PLAN_MONITOR PM ON PM.KEY = M.KEY AND PM.SQL_ID = S.SQL_ID AND PM.INST_ID = S.INST_ID AND PM.STATUS = 'EXECUTING'
WHERE S.SQL_ID IS NOT NULL
ORDER BY (
    SELECT MAX(COLUMN_VALUE) FROM SYS.ODCINUMBERLIST(
       M.ELAPSED_TIME/1000000, M.CPU_TIME/1000000, M.CONCURRENCY_WAIT_TIME/1000000, M.CLUSTER_WAIT_TIME/1000000, M.USER_IO_WAIT_TIME/1000000,
       DECODE(S.WAIT_TIME,0,S.SECONDS_IN_WAIT,0), PM.PLAN_TIME, (M.PHYSICAL_READ_BYTES+M.PHYSICAL_WRITE_BYTES)/1024/1024/1024, M.DISK_READS/1000,
       M.PX_MAXDOP, A.SHARABLE_MEM/1024/1024/1024, A.PERSISTENT_MEM/1024/1024/1024, A.RUNTIME_MEM/1024/1024/1024, PM.WORKAREA_MEM/1024/1024/1024, A.SORTS/1000,
       A.VERSION_COUNT, ABS(A.EXECUTIONS/EXTRACT(SECOND FROM SYSTIMESTAMP-TO_TIMESTAMP(A.FIRST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS'))), A.LOADS)) DESC;

A sample output is below:

The columns are as follows:

Text: The first 2K characters to identify the query being run.
Status: Session status (active or inactive) and how many seconds the session is in the current state.
- In the example above, an UPDATE session has been active for 7.3 hours (26572 s) doing a lot of IO (406 GB), so it can be a source of blocking, storage contention, and network slowness.
TimeSec: Number of seconds the last statement has been running.
- In the example above, the UPDATE has been running for 3.8 hours (13699 s).
CpuTimeSec: Number of seconds the last statement has been using CPU.
- In the example above, the UPDATE has been using CPU for 2.5 hours (8966 s).
ParallelTimeSec: Number of seconds the last statement has been coordinating with other threads.
ClusterTimeSec: Number of seconds the last statement has been coordinating with other nodes.
IOtimeSec: Number of seconds the last statement has been waiting on I/O.
- In the example above, the UPDATE has been waiting on I/O for 1.3 hours (4788 s).
WaitTimeSec: Number of seconds the session has been waiting.
PlanTimeSec: Estimated number of seconds for the current operation.
- The example above shows a SELECT with an expected time of 9.2 hours (33257 s). Also, the most intensive queries have NULL, which indicates the optimizer is using a rule-based approach (maybe it is using compatibility, or the plan is forced, but the query needs improvement).
IOinGB: Amount of I/O in GB for both reads and writes.
DiskReadsK: Number of reads divided by 1000.
- The example above shows cases with high reads and low I/O, meaning the database is reading more data than it is processing. Note: If the column OPERATION indicates SORT or INDEX, you need to check the indexes; if it doesn't show anything, you need to check other sources (e.g., checkpoint, redo, log transfer, etc.).
DOP: Degree of parallelism.
SharableMemGB: The amount of memory used by the execution plan.
PersistentMemGB: The fixed amount of memory used by the last statement.
RuntimeMemGB: The fixed amount of memory required by the last statement.
WorkareaMemGB: The amount of memory used by the last statement for sort, hash-join, group-by, etc.
SortsK: Number of sorts divided by 1000. High values translate to high CPU and TEMP usage and can be fixed by correcting the indexes.
VersionCount: Number of execution plans for the same statement. High numbers can cause library cache contention and be fixed with bind variables and reusable code (not generated on the fly). You can find why multiple versions are querying V$SQL_SHARED_CURSOR with the SQL_ID and the CHILD_NUMBER.
Calls/Sec: Number of executions divided by the number of seconds since the first load time. If this value is high, you will likely benefit from improving this query to run as fast as possible with the smallest amount of resources.
Loads: Number of times the plan has been loaded into memory. If there are several statements with high values, there is memory pressure, which can slow down the overall time to complete them.
User: User executing the statement.
Event: Wait event for the current session. You can search it to determine the action to take: Descriptions of Wait Events.
Wait: Wait class for the current session. You can search it to determine the action to take: Classes of Wait Events.
State: Indicates if the session is waiting or not.
SqlStatus (i.e., statement execution status): Queued, executing, done (error), done (first n rows), done (all rows), or done. You should investigate why the ones with error or first n rows failed and why the queued and executing are still in that status.
Operation: Detailed operation from the execution plan being performed.
Object: Name of the table or index on which the statement is currently working.
Kill: Statement to kill the session (should not be used unless absolutely needed).
ViewPlan: Statement to view the execution plan of the statement. With this SQL_ID, you can also run the SQL Tuning Advisor to find ways to improve it: How Can we Run SQL Tuning Advisor For A SQL ID In Oracle Database.

SQL Server Query to Find Current Active Sessions

The system management views which provide you with the required information are sys.dm_exec_query_stats, sys.dm_exec_connections, and sys.dm_exec_sessions.

The query is below, but note that the columns [max_dop], [max_reserved_threads], [max_used_threads], [max_grant_kb], [max_used_grant_kb] are only available in SQL Server 2016 and greater:

SELECT 
   [st].*, 
   [s].[session_id], 
   [s].[original_login_name], 
   [s].[status], 
   [s].[login_time]
FROM (
   SELECT DB_NAME(ISNULL([t].[dbid], 
         (SELECT 
             CAST([value] AS SMALLINT) FROM [sys].[dm_exec_plan_attributes]([st].[plan_handle]) WHERE [attribute] = 'dbid'))) [DatabaseName],
             ISNULL(OBJECT_NAME([t].[objectid], [t].[dbid]),'{AdHocQuery}') [Proc/Func],
             MIN(SUBSTRING([t].[text], ([st].[statement_start_offset]/2)+1, ((CASE [st].[statement_end_offset] WHEN -1 THEN DATALENGTH([t].[text]) ELSE [st].[statement_end_offset] END - [st].[statement_start_offset])/2)+1)) [Text],
             MAX([st].[max_rows]) [Rows],
             SUM([st].[execution_count])/(SELECT MAX(v) FROM (VALUES (DATEDIFF(ss,MIN([st].[creation_time]),GETDATE())), (1)) AS VALUE(v)) [Calls/Sec],
             MAX([st].[max_elapsed_time])/1000000 [TimeSec],
             MAX([st].[max_worker_time])/1000000 [CpuTimeSec],
             MAX([st].[max_logical_reads]+[st].[max_logical_writes])*8/1024/1024 [IOinGB],
             MAX([st].[max_dop]) [DOP],
             MAX([st].[max_reserved_threads])-MAX([st].[max_used_threads]) [ThreadsExceeded],
             (MAX([st].[max_grant_kb])-MAX([st].[max_used_grant_kb]))/1024 [MemoryExceededMb],
             'SELECT [query_plan] FROM [sys].[dm_exec_query_plan](0x'+CONVERT(VARCHAR(MAX),[st].[plan_handle],2)+')' [ViewPlan],
             [st].[sql_handle]
          FROM [sys].[dm_exec_query_stats] [st]
          CROSS APPLY [sys].[dm_exec_sql_text]([st].[sql_handle]) [t]
          GROUP BY [st].[sql_handle], [st].[query_hash], [st].[plan_handle], [t].[dbid], [t].[objectid]) [st]
   INNER JOIN [sys].[dm_exec_connections] [c] ON [c].[most_recent_sql_handle]=[st].[sql_handle]
   INNER JOIN [sys].[dm_exec_sessions] [s] ON [s].[session_id]=[c].[most_recent_session_id]
ORDER BY (
   SELECT MAX(v) 
   FROM (VALUES ([Calls/Sec]), ([TimeSec]), ([CpuTimeSec]), ([IOinGB]), ([DOP]), ([ThreadsExceeded]), ([MemoryExceededMb])) AS VALUE(v)) DESC,
   [s].[login_time];

A sample output is below:

It shows the following columns:

DatabaseName:The name of the database where the query is running.
Proc/Func: The name of the procedure or function, or {AdHocQuery} if it's sent directly.
Text: The text of the current statement being run.
Rows: The number of returned rows.
- In the example above, a query has been running for 4 minutes (240 s), which only returned 1 row, so it needs to be looked at.
Calls/Sec:The number of executions divided by the number of seconds since the plan creation time (if available, otherwise 1). If this value is high, you will likely benefit from improving this query to run as fast as possible with the smallest amount of resources.
TimeSec: The number of seconds this statement has been running.
- In the example above, a SELECT statement has been running for 1 hour (3785 s), returning around 300K rows.
CpuTimeSec: The number of seconds this statement has been using CPU.
IOinGB: The amount of I/O in GB for both reads and writes.
DOP: The degree of parallelism.
- In the example above, a query is using 3 CPUs.
ThreadsExceeded: The difference between reserved threads and used threads. Anything above 0 indicates you must examine the execution plan because what the engine expects is not what it is doing. You need the estimated execution plan to match as close as possible with the real execution plan.
MemoryExceededMb: The difference between the expected memory and the used memory. Anything above 0 indicates you must examine the execution plan because what the engine expects is not what it is doing. You need the estimated execution plan to match as close as possible with the real execution plan.
ViewPlan: Statement to view the execution plan of the statement and find ways to improve it.
Sql_handle: The hash value based on the SQL text.
Session_id: A unique identifier for the session in case you need to kill it.
Original_login_name: The username of the session.
Status: The status of the session, either running or sleeping.
Login_time: The time when the session was created to identify long-running ones.

Conclusion

In the examples above, depending on where the slowness is, you can take immediate action (such as killing rogue statements for a specified user) or explain to the user the cause of the slowness and what needs to be done to fix it (more often than not, query tuning and rewriting). At this point, you know exactly what the current state of your database is and act accordingly.

For Oracle, there is much more information to analyze, so it can be confusing to pinpoint precisely the most expensive queries. You also have the option to run SQL Tuning Advisor, which gives you excellent recommendations in almost all cases except when the query must be rewritten.

In SQL Server, the information is reduced, so it's easier to analyze, but you need other tools to know about the waits, reads, tasks, sorts, tempdb usage, and context switches. An excellent tool for that is sp_whoisactive from Adam Machanic. Also, note that analyzing the execution plan is a manual task that requires quite an effort, and you must know how to do it properly. An excellent book to assist with this is Learn T-SQL Querying by Pedro Lopes and Pam Lahoud, reviewed by Joel Redman, Bob Ward, Tim Chapman, and Argenis Fernandez. All of them are from Microsoft, and you may have already heard about these masters.

Next Steps

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-05-01

SQL Server vs Oracle: Active Session Queries