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 introduces the optional performance monitoring applications of the Oracle Enterprise Manager. It covers the following topics:
Administrators of complex, dynamic database environments must confront daily the large volume of data their systems generate. Analyzing and responding to this data can be very time consuming. Often, suboptimal solutions stem from the pressure for immediate results.
The performance monitoring applications described in this guide graphically depict database performance and storage statistics in real-time to aid in managing these environments. This optional group of tools is fully integrated with Oracle Enterprise Manager.
Oracle Performance Manager is a flexible, extensible, and easy-to-use tool for monitoring database performance in real-time. It provides dozens of predefined charts for displaying a wide variety of database performance statistics regarding users, throughput, tablespaces, redo logs, buffers, caches and I/O, for example. Oracle Performance Manager also allows you to record statistics and play them back at a later time.
You can display these statistics in a number of formats, including tables, line charts, area charts, horizontal and vertical bar charts, cube charts, and pie charts. Moreover, all chart formats can be displayed in either two or three dimensions. You can drill down for more detail on some predefined charts. You can display multiple charts in a single chart window, and choose which ones you want to display together. You can also customize many other aspects of a chart-----including its axes, scale, and legend, for example. Moreover, in 3D charts you can modify the X and Y angle rotation, depth, and shadowing of objects.
Because monitoring requirements vary significantly among environments and over time, Oracle Performance Manager also lets you design new charts to meet current and evolving needs. In fact, creating a chart is as easy as filling in a property sheet. You can link charts to enable drill down for more detail, and invoke Oracle TopSessions from a chart to find out how individual sessions are contributing to database performance.
The Oracle Advanced Events are a set of predefined events that run on the Oracle Enterprise Manager Event Management System. You can use the Oracle Advanced Events to monitor specific event conditions that occur in your network environment. To use the Oracle Advanced Events, you choose predefined events on nodes, databases, or listeners, then select the threshold parameters for which you want to be notified. You can notify specific system administrators when an event condition occurs. For some events, you can also choose to execute a job that automatically corrects the problem.
The predefined events for the database, listener, and node service types are grouped into the following categories:
This category of events monitors for catastrophic conditions on the system, such as a database, node, or listener is down, which require immediate action by an administrator.
This category of events tracks possible space problems, such as running out of space on a disk or archive device.
This category of events tracks possible resource problems, such as exceeding datafile or lock limits.
This category of event monitors the system for performance problems, such as excessive CPU load or library cache miss rate.
The UpDown events in the Fault Management Event category are included with Oracle Enterprise Manager. The rest of the predefined events are Oracle Advanced Events, which are available only to Oracle Enterprise Manager Performance Pack Users.
For more information on the Oracle Enterprise Manager Event Management System, predefined events, and Oracle Advanced Events, see the chapter on Event Management in the Oracle Enterprise Manager Administrator's Guide.
Oracle Lock Manager is a tool for monitoring locks in a database, particularly blocking and waiting sessions.
Oracle TopSessions is a tool for monitoring how connected sessions use database instance resources in real-time. You can obtain an overview of session activity, by displaying the top n sessions sorted by a statistic of your choosing. For any given session, you can then drill down for more detail. You can further customize the information you display by specifying manual or automatic data refresh, the rate of automatic refresh, and the number of sessions to display.
In addition to these useful monitoring capabilities, Oracle TopSessions provides a methodology for identifying and correcting certain database performance problems. For example, when sudden file I/O load is detected, you can first identify the sessions contributing most to the problem, and then isolate the executing SQL statements in user applications for those sessions. You can then analyze the SQL explain plans for those SQL statements to determine how best to resolve the problem.
Oracle Tablespace Manager is a tool for monitoring and managing database storage. You can display an overview of tablespace usage information, either for all of the tablespaces in a database, or for the datafile(s) within a tablespace. To find out more about a given tablespace or datafile, you can drill down to graphically display how storage has been allocated for its segments. You can then use the Tablespace Manager tools to defragment segments (using either default or custom parameters), reorganize data, or deallocate unused space. Additionally, you can join adjacent free blocks using the Coalesce Free Extents option.
Oracle Performance Manager, Oracle Lock Manager, Oracle TopSessions, and Oracle Tablespace Manager can be started from the console or in standalone mode.:
To start these applications from the Oracle Enterprise Manager Console, first select a database instance in the Oracle Enterprise Manager navigator tree with which a connection will be established. Then, you can proceed in one of two ways:
You can also launch a performance monitoring application as a separate executable in standalone mode, just as you would launch any other Windows application.
The standard Login Information dialog box for Oracle Enterprise Manager applications is displayed. This dialog box contains the following:
Enter your Oracle username for the database to which you are connecting.
Enter your Oracle password for the database to which you are connecting.
Enter the SQL*Net service name for the database to which you are connecting.
This service name becomes part of the title for each window associated with that connection. If you do not specify a service name, the title of each window associated with the connection begins with the word "Untitled." This scheme distinguishes windows associated with different connections.
Click on this combo box to select the permission level of your login. Options include: NORMAL (default), SYSOPER, and SYSDBA.
Check this box to save the username, password and service you have entered for automatic display the next time the Login Information dialog box is invoked.
Click on this button to initiate a connection and display the main window of the application.
Click on this button to exit the dialog box without connecting.
After either Oracle Lock Manager, Oracle TopSessions or Oracle Tablespace Manager have successfully connected to a database instance, the main window for that application appears. For information on the Oracle Lock Manager main window, see "Using Oracle Lock Manager". For information on the Oracle TopSessions main window, see "Using Oracle TopSessions". For information on the Oracle Tablespace Manager main window, see "Using Oracle Tablespace Manager".
Starting Oracle Performance Manager in standalone mode, however, requires an extra step. See "Starting Oracle Performance Manager" for more information.
The following scenario illustrates how you can use Oracle Enterprise Manager performance monitoring applications to monitor database performance and solve performance problems.
Suppose, for example, that you are the DBA for an accounting firm with two Oracle databases running in remote locations. Several hundred user sessions typically connect to each database.
While calls from users often alert you to performance problems, using Oracle Enterprise Manager and these optional performance monitoring tools, you can now isolate and resolve problems proactively. Each morning, you establish Oracle Enterprise Manager connections to the two remote databases. Using these connections, you then start Oracle Performance Manager to monitor real-time database performance.
As you have found the Oracle Performance Manager predefined Overview chart particularly useful, you usually display this chart for each database in two background windows on your management console.
One morning, a remote user calls to complain of slow response time when entering general ledger data at one of the remote database sites. A quick glance at the corresponding Overview chart confirms the existence of a problem: you find a number of large spikes in physical writes per second on the File I/O Rate section of the Overview chart. Drilling down to the File I/O Rate Details chart, you are able to identify the particular datafile experiencing the increase in File I/O Rate activity.
Given that dozens of user sessions access the general ledger at any one time, it would be useful to identify the session(s) causing the file access problems. To do so, you establish an Oracle TopSessions connection to the database and display the top 10 sessions, based on file I/O activity. Selecting the session with greatest file I/O activity, you drill down for more detail, and then review the SQL statement currently executing for that session, as well as the explain plan for that statement. A quick review of the explain plan shows that a sequential search routine employs indexing improperly.
While fixing the SQL code is the long-term solution, you decide to use the Oracle Enterprise Manager Instance Manager application to disconnect the problem session. For information on using Instance Manager, see the Oracle Enterprise Manager Administrator's Guide.
