Sessions Page
When an Oracle session issues an I/O request against an Oracle datafile, it must wait while the I/O subsystem performs the request. The Session page shows I/O wait event statistics for each session. Use the Sessions page to see which sessions are currently consuming the most I/O and which sessions are affected by I/O waits.
To open the Sessions page
- Select the Spotlight on Oracle RAC connection in the Spotlight Browser.
-
Click I/O | Sessions.
Actions on the Top Sessions page
| Action | Description | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Select (Highlight) a session |
View detailed information. 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. |
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 choose to show ALL sessions via the Drilldowns page of the Spotlight on Oracle Options window. Spotlight Options Dialog
- You can change the Top Sessions reset interval. Oracle Top Sessions
| Column | Description |
|---|---|
| Instance name | The instance in the RAC cluster where the session is running. |
|
SID |
Session identifier for the session. |
|
Oracle User |
Oracle database account of the started session. |
|
Program |
Program running in the session. |
|
Module |
Currently executing module name set by DBMS_APPLICATION_INFO.SET_MODULE. Note: Available for Oracle 10g and later. |
|
IO waits (ms/s) |
Amount of wait time for all disk I/O wait categories, in milliseconds per second. |
|
% of session time |
Percentage of disk I/O wait to DB time, since the session connected. Note: Available for Oracle 10g and later. |
|
% of total IO time |
Percentage of disk I/O wait in milliseconds per second for that particular session, compared to disk I/O wait in milliseconds per second for all sessions. |
|
IO waits/s |
Number of I/O waits per second for all disk I/O wait categories. |
|
Logical reads/s |
Number of logical reads per second. |
|
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 time (ms/s) |
Amount of CPU time, in milliseconds per second. Note: Available for Oracle 10g and later. |
|
Block changes/s |
Number of block changes the session performed per second. |
| RAC Instance Number | |
|
Block changes |
Total number of block changes the session has performed. |
|
Control file waits |
Total number of waits on control file since the session connected. |
|
Control file waits (ms/s) |
Amount of time waited on control file waits, in milliseconds per second. |
|
Control file waits (s) |
Amount of time waited on control file waits since the session connected, in seconds. |
|
Control file waits/s |
Number of waits per second on control file. |
|
CPU time (s) |
Total amount of CPU time since the session connected. Note: Available for Oracle 10g and later. |
|
DB time (s) |
Total amount of DB time (time spent processing user calls) since the session connected. Note: Available for Oracle 10g and later. |
|
Direct path waits |
Total number of direct path waits, since the session connected. |
|
Direct path waits (ms/s) |
Amount of time waited on direct path waits, in milliseconds per second. |
|
Direct path waits (s) |
Amount of time waited on direct path waits since the session connected, in seconds. |
|
Direct path waits/s |
Number of direct path waits per second. |
|
IO waits (s) |
Total amount of time waited, in seconds, on all disk I/O wait categories since the session connected. |
|
Logical reads |
Number of logical reads. |
|
Machine |
Name of the computer on which the client is running. |
|
Multi block read waits |
Total number of multi block read waits since the session connected. |
|
Multi block read waits (ms/s) |
Amount of time waited on multi block read waits, in milliseconds per second. |
|
Multi block read waits (s) |
Amount of time waited on multi block waits since the session connected, in seconds. |
|
Multi block read waits/s |
Number of multi block read waits, per second. |
|
OS User |
Operating system user for the client. |
|
Physical reads |
Number of physical reads. |
| Physical reads/s |
Number of physical reads per second. |
|
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 PID |
Operating system process ID for the Oracle server process. |
|
Single block read waits |
Total number of single block read waits since the session connected. |
|
Single block read waits (ms/s) |
Amount of time waited on single block read waits, in milliseconds per second. |
|
Single block read waits (s) |
Amount of time waited on single block waits since the session connected, in seconds. |
|
Single block reads waits/s |
Number of single block read waits, per second. |