Chat now with support
Chat with Support

Please note, you may experience access issues between 6am - 7am Eastern time on Saturday, May 28 2022 due to planned maintenance

Spotlight on Oracle 10.4 - Release Notes

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

  1. Select the Spotlight on Oracle RAC connection in the Spotlight Browser.
  2. 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 | …

Option Description
Kill Session End the session after rolling back transactions, releasing locks and blocks, recovering system resources, and waiting for calls to remote databases.
Kill Session Immediate End the session immediately, while performing the "kill session" functions in the background.
Disconnect Session Immediate Disconnect the session and recover the entire session state immediately, without waiting for ongoing transactions to complete.
Disconnect Session Post Transaction Wait for ongoing transactions to complete before disconnecting the session.
Disconnect Session Post Transaction Immediate If there are on-going transactions, wait for the transactions to complete before disconnecting the session. If there are NO on-going transactions, disconnect the session immediately.

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

Option Description
Basic (level 1) Retrieve the basic details of the SQL statements that are currently being processed in the session.
Bind variable (level 4) Retrieve basic details as above, plus bind variable values for the session where bind variables are used.
Wait events (level 8) Retrieve basic details as above, plus wait details for the session where processing waits for an event to complete before continuing.
Waits and binds (level 12) Retrieve basic details as above, plus bind variable and wait details for the relevant 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:

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

 

Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating