Previous | Table of Contents | Next

Page 168

Job configured, the Trace and Expert repositories on the client, remote intelligent agents on the node where Trace-enabled programs reside, Trace Collection Services on remote nodes (usually linked with the remote application), and Trace Formatter tables on remote nodes. Each application might require configuration as well. For example, at a minimum, Oracle8 must have the ORACLE_TRACE_ENABLE parameter set to TRUE before it will produce trace data. The setup is not trivial and should not be taken lightly. However, Trace is the key to providing the data necessary to take full advantage of the Enterprise Manager Performance Pack.

Managing Tablespaces

Tablespace Manager provides detailed information on storage utilization in tablespaces, data files, and rollback segments. A color-coded map of each piece of storage enables the database administrator to visualize the storage used by every database object, how many extents are used, and where the extents are placed in both the tablespace and data files (see Figure 9.12). This information is invaluable when tracking down I/O contention or tracking the progress of large database loads and index builds.

FIG. 9.12
Selecting an extent in
the graphical display
highlights other extents
in the same object.

In addition to the graphical display, there are two other tabs in the right application pane. The Space Usage tab displays the space utilization statistics, such as the row count and the average row length. The Extent Information tab shows information such as the extent count for the selected object and the data file where the selected extent resides.

Page 169

CAUTION
Most of the information under the Space Usage tab is produced by the ANALYZE SQL command. For objects with no statistics, generate statistics using ANALYZE or the Tablespace Analyzer Wizard. If the database is tuned for rules-based optimization and the CHOOSE optimization mode is employed, generating statistics may have an adverse performance impact. Likewise, if cost-based optimization is implemented and statistics are not available, performance might severely suffer.

Using Job, Tablespace Manager employs four wizards to change and analyze storage configurations. All four wizards submit jobs to the Enterprise Manager Job Scheduling component to perform the requested tasks. The Tablespace Analyzer Wizard analyzes selected tables, partitions, clusters, and indexes using the ANALYZE SQL command. The Tablespace Organizer Wizard defragments tables, partitions, and clusters using export and import utilities and rebuilds the indexes. It can also detect space that has not been used at the end of segments. The Defragmentation Wizard duplicates Tablespace Organizer defragmentation capabilities, but it is easier and quicker to use because it uses default options. The Coalesce Wizard combines adjacent free space into a single block. This is a particularly important technique for active operational databases where object sizes change frequently and temporary objects are routinely created and dropped. Unless a selected object contains adjacent free blocks, this wizard is disabled.

Monitoring Sessions

TopSessions enables database administrators to monitor database sessions and kill them if necessary due to inactivity or over-utilization of resources. This tool monitors all sessions or only the most active based on consumption of specified resources such as memory or I/O. Filtering provides a means to narrow the focus on sessions of interest when analyzing activity.

TIP
Before running TopSessions for Oracle8, run <ORACLE_HOME>/SYSMAN/SMPTSI80.SQL to ensure that tables required to perform all the functions are in place with appropriate permissions. Be aware that this script may not run "as is" and requires some editing to alter the ORACLE_HOME drive and name. It is still necessary to log in as SYS to explain access plans.

TopSessions can drill down into sessions to display exhaustive session statistics on redo, enqueue, cacheing, the operating system, parallel server, SQL, and other miscellaneous information. Examining active or open cursors (see Figure 9.13) reveals the SQL executed by each cursor and the access plan the SQL is using for execution. Locking information, including identification of blocking locks held by the session, is also available.

TIP
TopSessions can identify locks for a particular session, but for in-depth analysis of the entire locking picture, the tool to use is Lock Manager. Lock Manager displays either all locks held on the database or only blocking or waiting locks. Offending sessions can be killed directly from Lock Manager.

Page 170

FIG. 9.13
By drilling down into
a session's open cursors,
detailed access plans,
are available.

Using Oracle Expert

Like Oracle Trace, Oracle Expert is an elaborate product with its own user's guide, consisting of 160 pages in the current release. Expert is more than just a product; it is the implementation of a performance management methodology. While other Performance Pack components provide information for database administrators to analyze, Expert applies a set of rules to the data to develop conclusions and make recommendations to improve performance. Expert considers trends that develop over time as well as current system conditions. As new database releases become available, the rules are updated to make recommendations on how to take advantage of new advanced features. As a result, Expert doubles as a mentor to help database administrators learn more about performance management and Oracle8.

Unfortunately, performance tuning is often neglected in favor of day-to-day requirements until serious performance problems affect the production database environment. Expert provides a means to automatically sift through performance data that might consume a large percentage of even a skilled database administrator's time. Expert doesn't replace the database administrator as a performance tuner. It does free the database administrator from the mundane task of screening mountains of data to find potential problems. With Expert, the performance tuning focus becomes how to deal with what Expert finds and enhancing Expert with more rules based on the experience and knowledge of the database administrator. Performance tuning becomes more efficient, effective, and rewarding.

To start an Expert Tuning session, from the menu, select File, New and start telling Expert about the areas it should analyze. The scope of the tuning session is defined based on requirements for instance tuning, application tuning, and storage structure tuning. The profile of the

Previous | Table of Contents | Next