Top Sessions Page
The Top Sessions page shows all users connected to the Oracle database.
To open the Top Sessions page
-
Select the Spotlight on Oracle RAC connection in the Spotlight Browser.
-
Click Top Sessions.
Actions on the Top Sessions grid
| Action | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Select (Highlight) a session |
View detailed information on the session. See: | ||||||||||||
| Save the grid |
Right click the grid content and select Save As (Save To File) or Copy To Clipboard. | ||||||||||||
| Kill a session |
Right-click the session and select Kill Session | …
A message appears that contains the session identifier and serial number of the session to be killed, and the number of rollback segment blocks used. Click Yes to kill the session. Note: Not all users have permission to kill sessions. This permission is granted by the Database Administrator. It is set when the user is created using the Oracle User Wizard. | ||||||||||||
| Trace a session |
Collect performance statistics and diagnostic data for the session. To Trace a session Right-click the session and select Trace a Session | ...
The details of the selected session are now shown in a "highlight" color (red by default). For more information see your Database Administrator. To end a trace session Right-click the session and select End Trace Session The details of the session are now displayed in the "normal" color (black by default). For more information see your Database Administrator. | ||||||||||||
| For further information | For information on Oracle sessions that may be associated with rollback segment consumption, view the Activity | Transactions Page | Used Blocks column. |
The Top Sessions Grid
Notes:
- Not all columns are visible by default. Use the Column Organizer to view hidden columns. Show, Hide & Order Columns
- Initially the grid is sorted in logical I/O request order. Click a column heading in the table to change the sort order. For example, click SQL Text to show at the top of the grid the last SQL statement executed by a user.
- By default the Top Sessions page is filtered to display only ACTIVE sessions. This improves Spotlight's performance; you can show ALL sessions. Spotlight Options Dialog
- You can change the Top Sessions reset interval. Oracle Top Sessions
- For information on Oracle sessions that may be associated with rollback segment consumption, view the Activity | Transactions Page | Used Blocks column.
-
If there are dead sessions in the Top Sessions drilldown the solution is to reset the drilldown. Click Filter. Select Reset. Select the appropriate frequency.
The Filter icon can also be used to hide background type sessions. Note that for a session of type "BACKGROUND" we will get a value like "SYS(username)" for the "oracle user" column. If a "oracle name" is "SYS" the session type is not necessarily background; but "SYS()" is a background type session.
| Column | Description |
|---|---|
| Instance Name | The instance in the RAC cluster where the session is running. |
| Oracle User | Oracle database account of the started session. |
| OS User | Operating system user for the client. |
| Machine | Name of the computer on which the client is running. |
| DB time (ms/s) |
Amount of DB time (time spent processing user calls), in milliseconds per second. Note: Available for Oracle 10g and later. |
| CPU (ms/s) |
Amount of CPU utilization (ms/s) over the past sampling interval. |
| Log Reads/s | Number of logical reads per second in the past sampling interval. This includes all requests for database blocks, irrespective of whether they were found in the database cache. |
| Disk Reads/s | Number of disk reads per second in the preceding sampling interval. |
| PGA Memory (Bytes) | The amount of PGA memory used by the session. |
| Container Name |
The container for the pluggable database. Note: Applicable to Oracle 12c. |
| Action | The currently executing action name set by DBMS_APPLICATION_INFO.SET_MODULE. |
| Block changes | Total number of block changes the session has performed. |
| Block gets | Total number of current (update) mode reads since the session was established. |
| Client info | Contains the information set by DBMS_APPLICATION_INFO.SET_CLIENT_INFO. |
| Client PID | Operating system process ID for the client program. |
| Client program | The client's program. |
| Consistent gets | Total number of consistent (query) mode reads since the session was established. |
| Failed over? | In a Real Application Clusters environment, a user can connect to any one of the multiple instances in a cluster. If the connected instance fails, the session can be reconnected automatically to another available instance. There are two types of failover, as this process is known –Connect-time Failover and Transparent Application Failover (TAF). This grid displays information only for TAF failovers. |
| Failover Method |
This specifies how fast a failover occurs from the primary node to the backup node. The failover method can be:
|
| Failover Type |
In a Transparent Application Failover, which occurs when the connected listener fails, the failover can be one of these types:
|
| Fixed table sequence | A number used to identify sessions that have not been idle. The fixed table sequence increases when the session completes a call to a database and a SELECT operation has been performed on a dynamic table. |
| Hit rate | Percentage of read requests that did not require disk I/O. |
| Logon time | The date and time that the user logged on to the database. |
| Module | The currently executing module name set by DBMS_APPLICATION_INFO.SET_MODULE. |
| Resource consumer group | The name of the session's current resource consumer group. |
| Serial # | Serial number of the session. SIDs can be reused after the session disconnects, but the combination of SID and Serial number is always unique. |
| Server | The server type (DEDICATED, SHARED, PSEUDO or NONE). |
| Server PID | Operating system process ID for the Oracle server process. |
| Server program | Oracle server program that supports this session. This may be a dedicated server, shared server, or dispatcher. |
| Service name | Service name of the session. |
| SID | Session identifier for the session. |
| SQL text | Displays the last SQL statement executed by the user. |
| Status | Status of the session, ACTIVE or INACTIVE. |
| User name | Oracle account name of the session. |
| Wait state/time | The status of the wait, and how long it has waited. |
| Waiting location | The details of the wait. |
| Type |
Distinguish sessions of type Background from type User. Note: For a session of type "BACKGROUND" we will get a value like "SYS(username)" for the "oracle user" column. If a "oracle name" is "SYS" the session type is not necessarily background; but "SYS()" is a background type session.
|