Release 1.4.0
A53699_01
 Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Performance Monitoring User's Guide Release 1.4.0 A53699_01 |
|
This chapter covers how you use Oracle TopSessions, including the following topics:
Before using Oracle TopSessions, it is best to ensure that the following views exist on the database instance:
The first four tables of the list are essential; Oracle TopSessions cannot run without them. The rest of the tables are required to use certain features of Oracle TopSessions; however, you can still run Oracle TopSessions without them.
The smptsixx.sql scripts have been provided by Oracle Enterprise Manager to create the required tables and views on each database you want to connect to from Oracle TopSessions. The xx in the file name identifies the version of the database against which the script should be run. For example, if you have an Oracle 7.3 database, you would run the smptsi73.sql script against it, and if you have an Oracle 8.0 database, you would run the smptsi80.sql script against it. The script for each database version is located in the $ORACLE_HOME\SYSMAN\ADMIN directory. For more information about running these scripts, see the section on setting up Oracle TopSessions in the Performance Pack Configuration chapter of the Oracle Enterprise Manager Configuration Guide.
You start Oracle TopSessions as you would any typical Oracle Enterprise Manager application, as described in "Starting Performance Monitoring Applications".
| Attention:
If an error message appears when you attempt to start Oracle TopSessions, see "Before You Start Oracle TopSessions". |
As Figure 9-1 shows, when the Oracle TopSessions main window is displayed, the Sort page of the Options property sheet is also is displayed automatically.
If you want the top n sessions in the main display to be sorted by the default statistic Memory (CPU used by this session), click OK. Otherwise, change the sort statistic and/or statistic filter as desired and click OK.
Data will appear in the main display. For more information about using the Options property sheet, see "Customizing the Session Information You Display".
If you are monitoring multiple databases, it is possible to start multiple Oracle TopSessions connections to multiple database instances. The system resources of the management console are the only constraining factors.
As Figure 9-1 shows, the Oracle TopSessions main window includes the following components:
The following sections describe each of these components.
The title bar of the Oracle TopSessions main window displays the name of the application and the database instance to which an Oracle TopSessions connection has been established. If no connection to a database instance currently exists, the message "No database connection" is displayed in the title bar.
The toolbar of the Oracle TopSessions main window contains icons representing certain Oracle TopSessions menu items, including the following: Connect, Details, Refresh, Kill, Options, Refresh Mode, and Help. If no Oracle TopSessions connection to a database instance currently exists, all tools except the Connect and Help tools are disabled. If a database connection does exist, the Connect, Refresh, Options, Refresh Mode, and Help tools are enabled.
The Refresh Mode tool performs the same function as the Manual and Automatic buttons on the Refresh page of the Options property sheet. When the Refresh Mode tool is not pressed (default), manual refresh mode is enabled. When the Refresh Mode tool is pressed, automatic refresh mode is enabled.
Enabling automatic refresh mode with the Refresh Mode tool enables whatever refresh interval is set on the Refresh page of the Options property sheet. To adjust this interval, go to the Refresh page itself. For more information, see "Customizing the Session Information You Display".
The status bar of the Oracle TopSessions main window can include the following information:
When a menu item has been selected, an explanation of the menu item appears in this section of the status bar.
Displays the time of the last data refresh of the Oracle TopSessions main display.
The menu bar of the Oracle TopSessions main window includes the following pull-down menus:
The File menu items allow you to create new database connections and exit Oracle TopSessions.
The File menu includes the following commands:
Displays the Login Information dialog box, enabling you to connect to a database instance of your choice. For more information, see the description of this dialog box in "Starting in Standalone Mode".
Exits Oracle TopSessions. See "Exiting Oracle TopSessions" for more information.
The View menu items allow you to show/hide the toolbar or statusbar.
The View menu includes the following commands:
Allows you to show/hide the toolbar.
Allows you to show/hide the statusbar.
The Sessions menu items allow you to specify what session statistics will be displayed in the Oracle TopSessions main display, and how that information will be refreshed. For more information on these menu items, see "Customizing the Session Information You Display".
The Sessions menu includes the following commands:
Displays the Session Details window for the session selected from the Oracle TopSessions main display. This menu item is disabled when no session in the Oracle TopSessions main display has been selected.
If manual refresh mode (default) is enabled, you can choose this command to refresh (immediately) the session information displayed in the Oracle TopSessions main window.
Kills the user session selected on the Oracle TopSessions main display.
When a session has been killed, a red "X" marks the username of that session. This symbol is like the red "X" on the Kill tool icon.
Displays the Options property sheet, from which you can specify the sorting criterion, the refresh mechanism, and the number of entries for statistics displayed in the Oracle TopSessions main window.
The length of time the data refresh requires varies, depending on the number of sessions connected to the database instance. An instance with 700 to 800 sessions logged on would require about 10 seconds to refresh.
If no Oracle TopSessions connection to a database instance currently exists, all of these Session menu items are disabled.
The Help menu items allow you to obtain help on Oracle TopSessions menu items or property sheets.
Displays an overview of the Oracle Performance Monitoring applications.
Displays an alphabetical list of Help topics.
Displays information about using the Help system.
Displays the version number of this application.
Once populated with data, the Oracle TopSessions main display includes a multi-column list of summary information for each session connected to the database instance, or for each of the top n sessions, as measured by the selected sort statistic. This information is obtained from the V$SESSION view, as follows:
Oracle session ID for this session.
Oracle user name using this session.
Operating system user name.
This fourth column varies, depending on the sort statistic you have selected.
Last Oracle command executed by this session.
Status of this session: IDLE, ACTIVE, KILLED, or BLOCKED.
Identifier of the machine using this session.
Name of the client program executing this session.
For information about defining top n sessions, see "Customizing the Session Information You Display".
For information on the V$ views from which these statistics are derived, see the Oracle Server Reference. For information on how to make use of these statistics, see Oracle Server Tuning and Oracle Server Administrator's Guide.
In the Oracle TopSessions main display, you can click on a username with the right mouse button, and then select one of the following menu items:
Displays the Session Details window for that session. For more information, see "Viewing Details About a Given Session".
Kills the selected session. This menu item works like the Kill menu item of the Session menu.
By default, the value of the sort statistic determines the order of session entries in the Oracle TopSessions main display. (The sort statistic is the fourth column of the multi-column list.) However, you can use any field in the list to sort the order of displayed session entries, by clicking on any column header in the list.
To obtain more information about a given session, you can drill down by double clicking on an SID in the Oracle TopSessions main display. A Session Details window for that session appears. This section covers the following topics:
The Session Details window includes the following pages:
To select one of these pages, click on the appropriate tab title near the top of the Session Details window.
The General page of the Session Details window provides detailed information about a session. This information is obtained from the V$SESSION view.
For information on the V$ views from which these statistics are derived, see the Oracle Server Reference. For information on how to make use of these statistics, see Oracle Server Tuning and Oracle Server Administrator's Guide.
While the contents of the General page can vary, depending on the Oracle7 or Oracle8 server release, for an Oracle 7.3 server, the General page contains the following:
Session address of this session.
Oracle session ID of this session.
Serial number of this session. Together with the SID, this number provides a unique key for this session.
Auditing session ID.
Address of the Oracle process using this session.
ID of the user using this session.
User name of the user using this session.
Last Oracle command executed.
Address of the transaction state object.
Address of lock waiting for; null if none.
Status of this session: ACTIVE, IDLE, KILLED, or BLOCKED.
Server type: DEDICATED, SHARED, PSEUDO, or NONE.
Schema user identifier.
Schema user name.
Operating system user name.
Process ID.
Identifier of the machine using this session.
Operating system terminal name.
Name of the client program using this session.
Session type.
Together withthe SQL hash value, identifies the SQL statement that is currently being executed.
Together with the SQL address, identifies the SQL statement that is currently being executed.
Together with the previous SQL hash value, identifies the SQL statement that executed previous to the SQL statement currently executing.
Together with the previous SQL address, identifies the SQL statement that executed previous to the SQL statement currently executing.
Name of the currently executing module as set by calling the procedure named DBMS_APPLICATION_INFO.SET_MODULE.
The hash value of the above module name.
Name of the currently executing action as set by calling the procedure named DBMS_APPLICATION_INFO.SET_ACTION.
The hash value of the above action name.
Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure.
Number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table.
Object ID for the table containing the row ID specified in ROW_WAIT_ROW#.
Identifier for the datafile containing the row ID specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero.
Identifier for the block containing the row ID specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero.
Current row ID being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is non-zero.
Logon timestamp for the session.
Last call made during the session.
The Statistics page of the Session Details window provides a large number of performance statistics for the selected session. The particular statistics this page displays can vary, depending on the Oracle server instance. These statistics are obtained from the V$SESSTAT view.
For information about these statistics and the V$ views from which they are derived, see the Oracle Server Reference. For information on how to make use of these statistics, see Oracle Server Tuning.
The Cursors page of the Session Details window provides information about each shared cursor in the shared SQL area for the selected session.
The Cursors page contains the following:
Click on an item in this list box to display SQL statement(s) for the selected user session. Options include:
Current Cursor: Displays the SQL statement currently executing. This is the default.
AllCursors: Displays all SQL statements that have executed or will execute on the database server for the selected user session.
Click on this button to display the explain plan for the SQL statement in the Cursors page display box which you have selected. For more information, see "Displaying an Explain Plan for a User Session".
Displays one or more SQL statements for the selected user session. If All Cursors has been selected, the first SQL statement in the list is the statement currently executing on the server for the session.
| Attention:
If no SQL statement appears in this display box, but you expect one to exist for the session, a problem may exist with an important database table. For more information, see "Before You Start Oracle TopSessions". |
To display an explain plan for a user session, from the Cursors page of the Session Details window, select a SQL statement and click on the Show Explain Plan button. The Explain Plan For Session xx:y window appears.
The title bar of this window includes the SID (xx) of the session and the number of windows opened on that session (y). This window includes the following elements.
Displays the SQL statement for which you want to view the explain plan.
The folders allow you to collapse or expand the level of detail of the explain plan. This feature is particularly useful when analyzing long and/or complicated explain plans.
| Note:
The Expected Rows column only appears when an Oracle 7.3 database is being monitored. |
The Locks page of the Session Details display provides information about locks held or requested by a given session. This information is obtained from the V$LOCK and V$ROLLNAME views.
For information about the V$ views from which these statistics are derived, see the Oracle Server Reference. For information on how best to interpret and use these statistics, see Oracle Server Tuning.
The Locks page includes the following list box:
Clicking on an item in this list box allows you to select the type of lock by which the sessions will be displayed. Options include All Locks or Blocking/Waiting Locks.
The Locks page contains the following fields:
Oracle user name using the session.
Oracle session ID for the session.
Type of lock, as follows: MR (Media Recovery); RT (Redo Thread); UN (User Name); TX (Transaction); TM (DML); UL (PL/SQL User Lock); DX (Distributed Xaction); CF (Control File); IS (Instance State); FS (File Set); IR (Instance Recovery); ST (Disk Space Transaction); TS (Temp Segment); IV (Library Cache Invalidation); LS (Log Start or Switch); RW (Row Wait); SQ (Sequence Number); TE (Extend Table); and TT (Temp Table).
Mode in which the lock is currently held by the session, as follows: None; Null; Row-S (SS); Row-X (SX); Share; S/Row-X (SSX); and Exclusive.
Mode in which the lock is being requested by the process, as follows: None; Null; Row-S (SS); Row-X (SX); Share; S/Row-X (SSX); and Exclusive.
Name of the object, rollback segment, table or view, being locked. If the lock type is TM, the object is a table or view. If the lock type is TX, the object is a rollback segment.
Owner of the object that has been locked by the session. The session user, listed in the Username field of the Session Details window General page, may be different than the owner of the object being locked.
Object type.
Unique identifier of the object.
For certain types of locks, this value is the object ID or rollback segment number.
Undocumented.
| Attention:
If no information is displayed on the Locks page, it is likely that no locks currently exist for the session. However, if you suspect that locks exist, but no information is displayed on the Locks page, a problem may exist with an important database table. For more information, see "Before You Start Oracle TopSessions". |
Like the status bar of the Oracle TopSessions main display, the status bar of the Session Details display also shows the refresh time for the information it displays. This time reflects the refresh time of the Oracle TopSessions main display at the time the Session Details display is created. Even when the Oracle TopSessions main display is subsequently refreshed, the information in a Session Details window that remains open during the main display refresh will not itself be refreshed.
Likewise, even though the refresh time in the status bar of the main window is updated to reflect the most recent refresh, the refresh time in the status bar of an open Session Details window is not updated simultaneously. Thus, the refresh time in the Session Details window status bar continues to reflect the time the static information in the Session Details window was captured.
Should you want to refresh the information for a given session in the Session Details window, close the window and then double-click on the SID for that session in the Oracle TopSessions main window. The resulting Session Details window will reflect the latest refresh time of the Oracle TopSessions main display.
You can display, minimize (iconify), and maximize Session Details displays for multiple sessions as you track down problems and work to resolve them.
To customize how session information is displayed in the Oracle TopSessions main window, choose Options from the Session menu. The Options property sheet that appears includes the following pages:
To select one of these pages, click on the appropriate tab title near the top of the Options property sheet.
Use the Sort page of the Options property sheet to specify how session information displayed in the Oracle TopSessions main window will be sorted, and what time interval the data will reflect. The Sort page contains the following:
Click on an item in this list box to specify the group of statistics from which to then select a particular sort statistic in the Sort Statistic list box.
These groups of statistics are labeled as follows: Predefined (default), User, Redo, Enqueue, Cache, Operating System, Parallel Server, SQL, Debug, Other, and All.
Selecting one of these items makes available a different group of statistics in the Sort Statistics box. With the exception of the Predefined group, the mapping between a statistic and its statistic filter group is derived from the CLASS column of the dynamic performance table V$STATNAME.
The Predefined statistics are defined or calculated from statistics displayed on the Statistics page of the Session Details display, as follows: CPU Usage (CPU used by this session); File I/O (physical reads + physical writes); Memory (session pga memory); Open Cursors (opened cursors current); User Transactions (user commits + user rollbacks).
Click on this list box to select the statistic by which the sessions displayed in the Oracle TopSessions main window will be sorted. The contents of this list box vary, depending on the Statistic Filter that has been selected.
Current: Click on this button to specify that statistics displayed in the Oracle TopSessions main window be based on the most recent data refresh.
Delta: Click on this button to specify that displayed statistics reflect the difference (delta value) between the most recent data refresh and the data refresh previous to that.
Use the Refresh page of the Options property sheet to specify how you want session information displayed in the Oracle TopSessions main window to be refreshed. The length of time the data refresh requires will vary, depending on the number of sessions connected to the database instance. An instance with 700 to 800 sessions logged on would require about 25 seconds to refresh.
The Refresh page contains the following:
Click on this button (default) to enable manual refreshing of the session information displayed in the Oracle TopSessions main window. You can then use the Refresh command from the Session menu or the Refresh tool to refresh this information when you so choose.
Click on this button to enable automatic refresh of session information displayed in the Oracle TopSessions main window; the Refresh Interval group of list boxes is then enabled for use.
Click on the appropriate combination of the Seconds, Minutes and Hours list boxes to specify the refresh interval for session information displayed in the Oracle TopSessions main window. The default refresh interval is 10 seconds.
Use the Count page of the Options property sheet to specify the number of sessions to be displayed in the Oracle TopSessions main window. The Count page contains the following:
Click this button to display all sessions connected to the database instance. As this option is very resource-intensive in almost all cases, Oracle Corporation recommends that you use the default option Display Top N Sessions.
Click on this button (default) to display the top n sessions that meet the sort statistic criterion specified in the Sort page of the Options property sheet. The default number of sessions is 10. Click on the scroll box to the right of this button to specify the value of n.
To exit Oracle TopSessions, choose Exit from the File menu. A dialog box is displayed, confirming that you want to close your Oracle TopSessions session.
