Oracle Cloud Infrastructure Documentation

Blocking and Waiting Sessions

To view blocking and waiting sessions:

  1. 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:

  2. 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.

  3. Click Blocking Sessions to display details about current blocking and waiting sessions.
    Note

    The Blocking Sessions option does not support analysis of historical sessions.

  4. 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.

For example, if the minimum wait time is set to three seconds, and a session has waited only two seconds, then it is not displayed in the table. If you change the minimum wait time to one second, then the session that waited only two seconds is added to the display.
Note

The minimum wait time default setting is three seconds.

Terminate a Session

  1. Click the check box at the left of the session User Name to select a session. The Kill Session button is enabled.

  2. Click the Kill Session button. A confirmation dialog box is displayed for terminating the session.

  3. Click the Kill Session button in the dialog box to end the session.

Display Lock Details

  1. 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.

  2. You can note the information and take action, as needed.

Display Wait Event Information

  1. In the session Wait Event column, click the name of the wait event for the selected session. The Session Lock Information table is displayed.

  2. You can note the information and take action, as needed.

Display Session Details

  1. 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.

  2. Use the Session Details page to explore additional details about the session.

Display SQL Details

  1. 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.

  2. 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