Blocking and Waiting Sessions
To view blocking and waiting sessions:
-
Open the Performance Hub page from the database details page, if not already open.
Note
For more information about how to navigate to the Performance Hub page, see:
-
The Time Range field displays a time period, which is the total amount of time available for analysis. You can adjust the time slider to select the time period in the Activity Summary.
For more information about the time selector fields, see Time Range.
-
Click Blocking Sessions to display details about current blocking and waiting sessions.Note
The Blocking Sessions option does not support analysis of historical sessions.
-
Click a link in each column to view details of the listed blocking and waiting sessions, as shown in the following table.
Tab Column Description User Name
The name of the user.
Status
Indicates whether the session is active, inactive, or expired.
Lock
The lock type for the session. Click lock type to display a table with more information about the session lock. It lists the Lock Type, Lock Mode, Lock Request, Object Type, Subobject Type, Time, ID1, ID2, Lock Object Address, and Lock Address of the selected session.
User Session
Lists the Instance, SID, and Serial number.
SQL ID
The ID of the SQL associated with the session.
Wait Event
The wait event for the session. Click wait event to show additional wait event details.
Object Name
The name of the locked database object.
Blocking Time
The time that a blocking session has been blocking a session.
Wait Time
The time that a session is waiting.
Set the Minimum Wait Time
The minimum wait time works as a filter for the Blocking Sessions information. It sets the minimum time that a session must wait before it is displayed in the tab.
The minimum wait time default setting is three seconds.
Terminate a Session
-
Click the check box at the left of the session User Name to select a session. The Kill Session button is enabled.
-
Click the Kill Session button. A confirmation dialog box is displayed for terminating the session.
-
Click the Kill Session button in the dialog box to end the session.
Display Lock Details
-
In the session Lock column, click the name of the lock type (Lock or Exclusive Lock) for the session. The Wait Event Details message box is displayed.
-
You can note the information and take action, as needed.
Display Wait Event Information
-
In the session Wait Event column, click the name of the wait event for the selected session. The Session Lock Information table is displayed.
-
You can note the information and take action, as needed.
Display Session Details
-
In the session User Session column, click Session Identifier for the session. The Performance Hub Session Details page is displayed.
Optionally move the time slider to display a specific time range of the session.
-
Use the Session Details page to explore additional details about the session.
Display SQL Details
-
In the session SQL ID column, click the SQL ID associated with the session. The Performance Hub SQL Details page is displayed.
Optionally move the time slider to display a specific time range of the session.
-
Select one or more of the following tabs. You can note the information and take action, as needed.
-
Summary - displays the SQL Overview and Source details
-
ASH Analytics - displays the SQL average active sessions
-
Execution Statistics - displays the SQL plans and plan details
-
SQL Monitoring - displays information about monitored SQL executions
-
SQL Text - displays the SQL
-