Chat now with support
Chat with Support

Spotlight on Oracle 11.0 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Troubleshooting: Connection Problems

Top Sessions Page

The Top Sessions page shows all users connected to the Oracle database.

To open the Top Sessions page

  1. Select the Spotlight on Oracle RAC connection in the Spotlight Browser.

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

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.

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:

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

  • BASIC – Establishes connections at failover time. This option requires almost no work on the backup database server until failover time.
  • PRECONNECT – Pre-establishes connections. This provides faster failover but requires that the backup instance be able to support all connections from every supported instance.
  • NONE – The default type, where NO failover functionality is used. This can also be specified explicitly to prevent failover from happening.
Failover Type

In a Transparent Application Failover, which occurs when the connected listener fails, the failover can be one of these types:

  • SESSION – A new session is automatically created for the user on backup. This type of failover does not attempt to recover selects.
  • SELECT – Users with open cursors can continue fetching on them after failure. This mode involves overhead on the client side in normal select operations.
  • NONE – The default type, where NO failover functionality is used. This can also be specified explicitly to prevent failover from happening.
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.

can be used to hide background sessions.

 

Related Topics

Spotlight on Oracle RAC Drilldowns

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating