| Oracle Enterprise Manager Performance Monitoring User's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Performance Monitoring User's Guide | Library |
Product |
Contents |
Index |
This chapter covers how you use Oracle TopSessions, including the following topics:
If V$SESSION, V$STATNAME, V$SESSTAT, V$OPEN_CURSOR, V$ROLLNAME, V$SQL$TEXT, V$LOCK, or DBA_OBJECTS do not exist, run CATALOG.SQL as SYS. Make sure to grant the SELECT privilege to PUBLIC on all columns of these views.
If PLAN_TABLE does not exist, run UTLXPLAN.SQL as SYS. Make sure to grant INSERT, DELETE and SELECT privileges to PUBLIC on all columns of this view.
Attention:
If an error message appears when you attempt to start Oracle TopSessions, see Before You Start Oracle TopSessions on page 9-1.
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.
Figure 9-1: Oracle TopSessions Main Window
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. 
Note:
Whatever options were selected when you last exited Oracle TopSessions are automatically loaded the next time you start Oracle TopSessions. These options are saved in ORACLE_HOME\SYSMAN\TEMP\TOPSESS.OPT. Oracle TopSessions Main Window
As Figure 9-1 shows, the Oracle TopSessions main window includes the following components:
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 on page 9-16.
Attention:
The length of time a data refresh requires varies, depending on the number of sessions connected to the database instance. A database instance with 700 to 800 sessions logged on would require about 10 seconds to refresh. Thus, an instance with perhaps 1,000 sessions logged on, when automatically refreshed at the default 10 second interval, would be in perpetual refresh mode. Thus, if you plan to use the automatic refresh mode, it is very likely that you will want to adjust the refresh interval to reflect your particular environment. Status Bar
The status bar of the Oracle TopSessions main window can include the following information: Left section
When a menu item has been selected, an explanation of the menu item appears in this section of the status bar. Right section
Displays the time of the last data refresh of the Oracle TopSessions main display. Oracle TopSessions Menu Bar
The menu bar of the Oracle TopSessions main window includes the following pull-down menus:
The File menu includes the following commands:
Exit
Exits Oracle TopSessions. See Exiting Oracle TopSessions on page 9-18 for more information.
View Menu
The View menu items allow you to show/hide the toolbar or statusbar. Toolbar
Allows you to show/hide the toolbar. Statusbar
Allows you to show/hide the statusbar. Sessions Menu
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 on page 9-16.
The Sessions menu includes the following commands:
Details
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. Refresh
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. Kill
Kills the user session selected on the Oracle TopSessions main display. Options
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. Help Menu
The Help menu items allow you to obtain help on Oracle TopSessions menu items or property sheets. Contents
Displays an overview of the Oracle Performance Monitoring applications. Search for Help On
Displays an alphabetical list of Help topics. Using Help
Displays information about using the Help system. About Oracle TopSessions
Displays the version number of this application. Obtaining an Overview of Session Activity
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: SID
Oracle session ID for this session. USERNAME
Oracle user name using this session. OSUSER
Operating system user name. Sort Statistic
This fourth column varies, depending on the sort statistic you have selected. COMMAND
Last Oracle command executed by this session. STATUS
Status of this session: IDLE, ACTIVE, KILLED, or BLOCKED. MACHINE
Identifier of the machine using this session. PROGRAM
Name of the client program executing this session.
For information on the V$ views from which these statistics are derived, see the Oracle7 Server Reference. For information on how to make use of these statistics, see Oracle7 Server Tuning and Oracle7 Server Administrator's Guide.
Using the Right Mouse Button on the Main Display
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: Details
Displays the Session Details window for that session. For more information, see Viewing Details About a Given Session on page 9-8.
Kill
Kills the selected session. This menu item works like the Kill menu item of the Session menu. Sorting the Order of Entries in the Main Display
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. Viewing Details About a Given Session
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:
For information on the V$ views from which these statistics are derived, see the Oracle7 Server Reference. For information on how to make use of these statistics, see Oracle7 Server Tuning and Oracle7 Server Administrator's Guide.
While the contents of the General page can vary, depending on the Oracle7 server release, for an Oracle 7.3 server, the General page contains the following:
For information about these statistics and the V$ views from which they are derived, see the Oracle7 Server Reference. For information on how to make use of these statistics, see Oracle7 Server Tuning.
The Cursors page contains the following:
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.
Display box
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 on page 9-1.
Attention:
Only SQL statements containing the commands SELECT, INSERT, UPDATE and DELETE will produce explain plans for you to view in this display box; any other SQL statement will not produce an explain plan for you to view. Displaying an Explain Plan for a User Session
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. SQL Statement
Displays the SQL statement for which you want to view the explain plan. 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. Session Details Locks Page
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. Lock Types
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. User Name
Oracle user name using the session. Session ID
Oracle session ID for the session. Lock Type
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 Held
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 Requested
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. Object Name
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. Object Owner
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
Object type. Object ID
Unique identifier of the object. Resource ID 1
For certain types of locks, this value is the object ID or rollback segment number. Resource ID 2
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 on page 9-1.
Session Details Display Refresh Time
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. Viewing Session Details for Multiple Sessions
You can display, minimize (iconify), and maximize Session Details displays for multiple sessions as you track down problems and work to resolve them. Customizing the Session Information You Display
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:
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).
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.
The Refresh page contains the following:
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |