Docstoc

A57689

Document Sample
A57689 Powered By Docstoc
					Oracle® Enterprise Manager


Performance Monitoring User’s Guide



Release 1.5.0




December 1997
Part No. A57689-01
Oracle Enterprise Manager Performance Monitoring User’s Guide

Part No. A57689-01

Release 1.5.0

Copyright © 1997, Oracle Corporation. All rights reserved.

The program contains proprietary information of Oracle Corporation; it is provided under a license agree-
ment containing restrictions on use and disclosure and is also protected by copyright patent and other
intellectual property law. Reverse engineering of the software is prohibited.

The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free.

Restricted Rights Legend Programs delivered subject to the DOD FAR Supplement are ‘commercial
computer software’ and use, duplication and disclosure of the programs shall be subject to the licensing
restrictions set forth in the applicable Oracle license agreement. Otherwise, programs delivered subject to
the Federal Acquisition Regulations are ‘restricted computer software’ and use, duplication and disclo-
sure of the programs shall be subject to the restrictions in FAR 52.227-14, Rights in Data -- General, includ-
ing Alternate III (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.

The programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee’s responsibility to take all appropriate fail-safe, back up,
redundancy and other measures to ensure the safe use of such applications if the programs are used for
such purposes, and Oracle disclaims liability for any damages caused by such use of the programs.

Oracle, Oracle Enterprise Manager, Oracle Enterprise Manager Performance Pack, Oracle Server Man-
ager, Oracle Trace, and SQL*Net are registered trademarks of Oracle Corporation. Net8, Oracle Expert,
Oracle Parallel Server, Oracle7, Oracle8, and PL/SQL are trademarks of Oracle Corporation. Windows,
Windows NT, Visual C++, and OLE are trademarks of Microsoft Corporation.

All other company or product names mentioned are used for identification purposes only and may be
trademarks of their respective owners.
                                                                                                                                        Contents

Send Us Your Comments ................................................................................................................... ix

Preface............................................................................................................................................................ xi

1 Performance Monitoring Applications
        Performance Monitoring Overview ...............................................................................................                            1-1
            Oracle Performance Manager .....................................................................................................                       1-1
            Oracle Advanced Events .............................................................................................................                   1-2
            Oracle Lock Manager ...................................................................................................................                1-3
            Oracle TopSessions.......................................................................................................................              1-3
            Oracle Tablespace Manager ........................................................................................................                     1-3
        Starting Performance Monitoring Applications...........................................................................                                    1-4
            Starting from the Console............................................................................................................                  1-4
            Starting in Standalone Mode ......................................................................................................                     1-4
        A Typical Performance Monitoring Scenario ...............................................................................                                  1-5

Part I          Using Oracle Performance Manager

2 Oracle Performance Manager Overview
        Terminology ........................................................................................................................................       2-1
        Starting Oracle Performance Manager ...........................................................................................                            2-2
        Oracle Performance Manager Main Window ...............................................................................                                     2-3
            Title Bar ..........................................................................................................................................   2-4
            Status Bar .......................................................................................................................................     2-4



                                                                                                                                                                    iii
        Menu Bar....................................................................................................................... .................        2-4
            File Menu ................................................................................................................................           2-4
            Display Menu.........................................................................................................................                2-6
            Charts Menu ...........................................................................................................................              2-8
            Record Menu ..........................................................................................................................               2-8
            Help Menu..............................................................................................................................              2-9
     Using the Right Mouse Button.......................................................................................................                        2-10
     Chart Types ........................................................................................................................................       2-10

3 Using Predefined Charts
     Default Features of Predefined Charts ...........................................................................................                           3-1
     Overview of Predefined Charts .......................................................................................................                       3-2
        Buffer Cache Hit % .......................................................................................................................               3-2
        Buffer Gets Rate ............................................................................................................................            3-3
        Circuit .............................................................................................................................................    3-3
        Data Dict Cache Hit %..................................................................................................................                  3-3
        Dispatcher ......................................................................................................................................        3-3
        File I/O Rate ..................................................................................................................................         3-3
        File I/O Rate Details.....................................................................................................................               3-4
        Free List Hit %...............................................................................................................................           3-4
        Latch ...............................................................................................................................................    3-4
        Library Cache Details...................................................................................................................                 3-4
        Library Cache Hit % .....................................................................................................................                3-5
        Lock.................................................................................................................................................    3-5
        Memory Allocated ........................................................................................................................                3-5
        Memory Sort Hit %.......................................................................................................................                 3-5
        Network Bytes Rate ......................................................................................................................                3-5
        Network I/O Rate.........................................................................................................................                3-6
        No. of Users Active.......................................................................................................................               3-6
        No. of Users Logged On ..............................................................................................................                    3-6
        No. of Users Running...................................................................................................................                  3-6
        No. of Users Waiting ....................................................................................................................                3-6
        No. of Users Waiting For Locks..................................................................................................                         3-7
        Overview........................................................................................................................................         3-7
        Parse Ratio .....................................................................................................................................        3-7



iv
         Process........................................................................................................................ ....................    3-8
         Queue .............................................................................................................................................     3-8
         Read Consistency Hit % ..............................................................................................................                   3-8
         Redo Allocation Hit % .................................................................................................................                 3-8
         Redo Statistics Rate ......................................................................................................................             3-8
         Rollback Nowait Hit % ................................................................................................................                  3-9
         Session ............................................................................................................................................    3-9
         Shared Server ................................................................................................................................          3-9
         Sort Rows Rate ..............................................................................................................................           3-9
         SQL Area........................................................................................................................................        3-9
         System I/O Rate .........................................................................................................................              3-10
         System Stats .................................................................................................................................         3-10
         Table Access ................................................................................................................................          3-10
         Table Scan Rows Rate ................................................................................................................                  3-10
         Tablespace....................................................................................................................................         3-10
         Tablespace Free Space................................................................................................................                  3-11
         Throughput Rate.........................................................................................................................               3-11

4 Customizing Display Elements of Charts
   Customizing the Chart Display .......................................................................................................                         4-1
      Displayed 3DChart.......................................................................................................................                   4-2
      Scaling of a Displayed Chart.......................................................................................................                        4-3
   Customizing the Chart Legend ........................................................................................................                         4-4
      Chart Legend Menu .....................................................................................................................                    4-4
      Chart Legend Properties Other than Location .........................................................................                                      4-5

5 Defining or Modifying Charts
   Defining a New Chart........................................................................................................................                  5-1
      SQL Statement Page .....................................................................................................................                   5-1
      Display Options Page...................................................................................................................                    5-3
   Example of Defining a New Chart ..................................................................................................                            5-6
   Modifying the Basic Definition of a User-defined SQL Chart................................................                                                   5-10
   Modifying the Basic Definition of a Predefined Chart.............................................................                                            5-10




                                                                                                                                                                   v
6 Displaying Charts in Chart Windows
     Chart Window Components .............................................................................................................                      6-1
        Window Menu ..............................................................................................................................              6-2
        Refresh Menu ................................................................................................................................           6-2
     Defining a New Chart Window .......................................................................................................                        6-3
     Modifying an Existing Chart Window ...........................................................................................                             6-4

7 Working with Oracle Performance Manager Data
     Saving and Reusing Chart and Chart Window Definitions.......................................................                                               7-1
        How Chart and Chart Window Definitions are Saved ...........................................................                                            7-1
        Sharing Chart and Chart Window Definitions.........................................................................                                     7-2
     Working with Historical Data ..........................................................................................................                    7-3
        Collection .......................................................................................................................................      7-3
        Display............................................................................................................................................     7-5
        Formatting with the Standalone Executable.............................................................................                                  7-6
        Deleting an Historical Data Collection ......................................................................................                           7-7

Part II       Additional Performance Monitoring Applications

8 Using Oracle Lock Manager
     Before You Start Oracle Lock Manager ..........................................................................................                            8-1
     Starting Oracle Lock Manager..........................................................................................................                     8-2
     Oracle Lock Manager Main Window ..............................................................................................                             8-2
         Title Bar ..........................................................................................................................................   8-3
         Toolbar............................................................................................................................................    8-3
         Status Bar .......................................................................................................................................     8-4
          Menu Bar.......................................................................................................................................       8-4
              File Menu ................................................................................................................................        8-4
              View Menu .............................................................................................................................           8-4
         Locks Menu....................................................................................................................................         8-5
              Help Menu..............................................................................................................................           8-5
          Main Display ................................................................................................................................         8-5
     Identifying Waiting Sessions ...........................................................................................................                   8-7




vi
9 Using Oracle TopSessions
     Before You Start Oracle TopSessions .............................................................................................                           9-1
     Starting Oracle TopSessions.............................................................................................................                    9-2
     Oracle TopSessions Main Window.................................................................................................                             9-3
         Title Bar ..........................................................................................................................................    9-4
         Toolbar ...........................................................................................................................................     9-4
         Status Bar .......................................................................................................................................      9-5
         Oracle TopSessions Menu Bar ....................................................................................................                        9-5
              File Menu ................................................................................................................................         9-5
              View Menu .............................................................................................................................            9-6
              Sessions Menu........................................................................................................................              9-6
              Help Menu..............................................................................................................................            9-7
     Obtaining an Overview of Session Activity..................................................................................                                 9-7
         Using the Right Mouse Button on the Main Display ..............................................................                                         9-8
         Sorting the Order of Entries in the Main Display ....................................................................                                   9-8
     Viewing Details About a Given Session........................................................................................                               9-9
         Session Details Pages ...................................................................................................................               9-9
         Session Details General Page ......................................................................................................                     9-9
         Session Details Statistics Page...................................................................................................                     9-12
         Session Details Cursors Page ....................................................................................................                      9-13
         Displaying an Explain Plan for a User Session ......................................................................                                   9-14
         Session Details Locks Page........................................................................................................                     9-14
         Session Details Display Refresh Time .....................................................................................                             9-16
         Viewing Session Details for Multiple Sessions ......................................................................                                   9-16
     Customizing the Session Information You Display ..................................................................                                         9-16
         Options Sort Page .......................................................................................................................              9-17
         Options Refresh Page.................................................................................................................                  9-18
         Options Count Page ...................................................................................................................                 9-19
     Exiting Oracle TopSessions ............................................................................................................                    9-19

10   Using Oracle Tablespace Manager
     Starting Oracle Tablespace Manager ............................................................................................                            10-1
     Oracle Tablespace Manager Main Window ................................................................................                                     10-1
         Title Bar ........................................................................................................................................     10-2
         Toolbar .........................................................................................................................................      10-3


                                                                                                                                                                  vii
           Status Bar ..................................................................................................................... ................    10-3
           Main Display ...............................................................................................................................         10-3
           Menu Bar......................................................................................................................................       10-4
       Obtaining an Overview of Tablespace Storage ..........................................................................                                   10-6
       Obtaining an Overview of Datafiles in a Tablespace................................................................                                       10-6
       Monitoring Extents and Segments of a Tablespace ...................................................................                                      10-7
           Segments and Extents Information Property Sheet ...............................................................                                      10-8
       Reorganizing Objects in a Tablespace........................................................................................                            10-11
               Defining Jobname and Type of Organization ...............................................................                                       10-12
               Selecting Objects to Reorganize.......................................................................................                          10-12
               Selected Objects with Properties Page............................................................................                               10-12
               Defining Tablespace Reorganization Options...............................................................                                       10-14
               Defining Scheduling Options ..........................................................................................                          10-15
               Viewing the Tablespace Reorganization Summary Page ............................................                                                 10-17
       Deallocating Unused Space ..........................................................................................................                    10-17
               Default Options..................................................................................................................               10-17
               Selecting Objects to Deallocate Space .............................................................................                             10-17
               Selected Objects with Properties .....................................................................................                          10-18
               Setting Job and Schedule Options ...................................................................................                            10-18
               Viewing the Tablespace Deallocation Wizard Summary Page...................................                                                      10-18
       Analyzing a Tablespace .................................................................................................................                10-19
               Defining Default Options .................................................................................................                      10-19
               Selecting Objects to Analyze ............................................................................................                       10-20
               Selected Objects with Properties .....................................................................................                          10-20
               Defining Scheduling Options ..........................................................................................                          10-20
               Viewing the Tablespace Analyzer Summary Page.......................................................                                             10-21
       Joining Adjacent Free Extents in the Database .........................................................................                                 10-21

Index




viii
                                           Send Us Your Comments
Oracle Enterprise Manager Performance Monitoring User’s Guide, Release 1.5.0
Part No. A57689-01


Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this
publication. Your input is an important part of the information used for revision.
    s   Did you find any errors?
    s   Is the information clearly presented?
    s   Do you need more information? If so, where?
    s   Are the examples correct? Do you need more examples?
    s   What features did you like most about this manual?

If you find any errors or have any other suggestions for improvement, please indicate the chapter,
section, and page number (if available). You can send comments to us in the following ways:
    s   electronic mail - nedc_doc@us.oracle.com
    s   FAX - 603-897-3317. Attn: Oracle Performance Manager Documentation
    s   postal service:
        Oracle Corporation
        Oracle Performance Manager Documentation
        One Oracle Drive
        Nashua, NH 03062
        U.S.A.
If you would like a reply, please give your name, address, and telephone number below.




                                                                                                     ix
x
                                                                               Preface

            This section describes the purpose and organization of this guide: the Oracle Enter-
            prise Manager Performance Monitoring User’s Guide. Specifically, it covers the fol-
            lowing topics:
            s   Purpose of this Guide
            s   Audience
            s   How this Guide is Organized
            s   How to Use this Guide
            s   Conventions Used in this Guide
            s   Related Publications
            s   Your Comments Are Welcome


Purpose of this Guide
            This guide describes how to use the optional performance monitoring applications
            of the Oracle Enterprise Manager. Specifically, these applications are:
            s   Oracle Performance Manager
            s   Oracle Advanced Events
            s   Oracle Lock Manager
            s   Oracle TopSessions
            s   Oracle Tablespace Manager




                                                                                               xi
                Information in this guide applies to these performance monitoring applications run-
                ning on all platforms. When functionality differs among platforms, this guide refers
                you to your operating system-specific documentation.


Audience
                This guide is written for those who wish to use Oracle Performance Manager, Ora-
                cle Advanced Events, Oracle Lock Manager, Oracle TopSessions, and Oracle
                Tablespace Manager to monitor database instance performance.

Knowledge Assumed of the Reader
              This guide assumes you are familiar with the performance monitoring tasks you
              wish to perform. If you are not, refer to the Oracle Server documentation set. The
              Oracle Server documentation set contains specific and thorough descriptions of
              these tasks. In addition, the Oracle Server documentation set provides recommen-
              dations on how to tune your database optimally based on performance monitoring
              statistics.
                This guide also assumes that you are familiar with the operation of Microsoft Win-
                dows. Refer to the Windows documentation for your system, if necessary.


How this Guide is Organized
                The Oracle Enterprise Manager Performance Monitoring User’s Guide is divided
                into parts and chapters, as described below.
                Chapter 1, Performance Monitoring Applications
                This chapter introduces the Oracle Performance Manager, Oracle Advanced Events,
                Oracle Lock Manager, Oracle TopSessions, and Oracle Tablespace Manager,
                explains how to start these applications, and includes a common scenario for using
                them.

Part I: Using Oracle Performance Manager
                 Chapter 2, Oracle Performance Manager Overview
                This chapter introduces Oracle Performance Manager. It also describes the compo-
                nents of the Oracle Performance Manager main window, how you use the right
                mouse button in this application, and the basic chart types you can use to display
                database statistics.
                Chapter 3, Using Predefined Charts




xii
                This chapter describes some default characteristics of the predefined charts
                included with Oracle Performance Manager, and briefly describes each of these
                charts.
                Chapter 4, Customizing Display Elements of Charts
                This chapter describes how you customize the display elements of charts (such as
                chart type, scale, or 3D rotation) to best meet the needs of your particular environ-
                ment.
                Chapter 5, Defining or Modifying Charts
                This chapter describes how you define a new chart or modify the basic definition of
                a chart, such as the SQL statement used to gather data for display. It also includes
                an example of defining a new chart.
                Chapter 6, Displaying Charts in Chart Windows
                This chapter covers the components of an Oracle Performance Manager chart win-
                dow, and describes how you use such a window to display one or more Oracle Per-
                formance Manager charts.
                Chapter 7, Working with Oracle Performance Manager Data
                This chapter describes how you manage current and historical data for display in
                Oracle Performance Manager charts.

Part II: Using Other Performance Monitoring Applications
                 Chapter 8, Using Oracle Lock Manager
                This chapter covers how you monitor locks in a database particularly blocking
                and waiting sessions.
                Chapter 9, Using Oracle TopSessions
                This chapter covers prerequisites to using Oracle TopSessions, starting Oracle
                TopSessions, the components of the Oracle TopSessions main window, viewing
                details about a given session, customizing the session information you display, and
                exiting Oracle TopSessions.
                Chapter 10, Using Oracle Tablespace Manager
                This chapter covers starting Oracle Tablespace Manager, the components of the Ora-
                cle Tablespace Manager main window, obtaining an overview of tablespaces and
                datafiles, monitoring segments in a tablespace, and managing storage in a
                tablespace.




                                                                                                    xiii
How to Use this Guide
            The Oracle Enterprise Manager Performance Monitoring User’s Guide has been
            designed to be used closely with the Oracle Server documentation set. While this
            guide teaches you how to use Oracle Performance Manager, Oracle TopSessions,
            Oracle Lock Manager, and Oracle Tablespace Manager to monitor Oracle databases
            and systems, the Oracle Server documentation set describes the reasons for and the
            implications of such performance monitoring. Consequently, you should refer to
            the Oracle Server documentation set while using these applications.
            Before using one of these performance monitoring applications, you should read
            “Overview of the Performance Monitoring Applications.” After reading, you may
            choose to proceed directly to those chapters that are relevant to the tasks you plan
            to perform using one or more of these tools.


Conventions Used in this Guide
            Special icons are provided to alert you to particular information within the body of
            this guide and within other manuals.

                    Attention: The attention icon highlights information that is impor-
                    tant to remember when performing the described task.



                    Suggestion: The suggestion icon signifies suggestions and practi-
                    cal hints that can be helpful when using these applications.



                    Warning: The warning icon indicates information that you
                    should be aware of before you perform the action described in the
                    current section.



Related Publications
            This section covers the following topics:
            s   Oracle Enterprise Manager documentation set
            s   Other related Oracle publications




xiv
Oracle Enterprise Manager Documentation
                The Oracle Enterprise Manager Performance Monitoring User’s Guide is one of sev-
                eral Oracle Enterprise Manager documents.
                s   The Oracle Enterprise Manager Readme provides important notes regarding the
                    online documentation, updates to the software, and other late-breaking news.
                s   The Oracle Enterprise Manager Installation CD-ROM Insert provides information
                    about installing Oracle Enterprise Manager.
                s   The Oracle Enterprise Manager Administrator’s Guide explains how to use Oracle
                    Enterprise Manager, Oracle’s systems management console, common services,
                    and integrated platform tool.
                s   The Oracle Enterprise Manager Concepts Guide provides an overview of the Ora-
                    cle Enterprise Manager.
                s   The Oracle Enterprise Manager Configuration Guide explains how to configure
                    Oracle Enterprise Manager.
                s   The Oracle Enterprise Manager Application Developer’s Guide describes the pro-
                    gramming external interfaces to the Oracle Enterprise Manager console.
                s   The Oracle Enterprise Manager Messages Manual describes the Oracle Enterprise
                    Manager error messages and methods for diagnosing the messages.
                s   The Oracle Enterprise Manager Oracle Trace User’s Guide explains how to use the
                    Oracle Trace application to capture and use historical data to monitor Oracle
                    databases. The guide also offers guidelines for instrumenting your application
                    with Trace routines.
                s   The Oracle Enterprise Manager Oracle Expert User’s Guide explains how to use
                    Oracle Expert to optimize the performance of your database environment, dur-
                    ing initial configuration, as well as during ongoing database operation.
                s   The Oracle Enterprise Manager Performance Monitoring User’s Guide explains how
                    to use Oracle Performance Manager, Oracle Advanced Events, Oracle Lock
                    Manager, Oracle TopSessions, and Oracle Tablespace Manager to monitor the
                    performance of Oracle databases.

Other Related Oracle Publications
                The Oracle Enterprise Manager Performance Monitoring User’s Guide is one of
                many books that explain various parts of an Oracle database system.




                                                                                                     xv
      In many sections of this guide, the information is general because the guide is writ-
      ten to apply to Oracle Performance Manager, Oracle Lock Manager, Oracle TopSes-
      sions, and Oracle Tablespace Manager working under any operating system and
      configuration. Therefore, you will also find many references in this book to impor-
      tant information in related publications. The related Oracle7 and Oracle8 books
      referred to in this guide are listed below. Note that these manuals are provided
      with both the Oracle7 and Oracle8 documentation set; you should use the manuals
      supplied with the version of the database you are using.
      s   For general information about the Oracle Server and how it works, see Oracle
          Server Concepts.
      s   For information about the database performance statistics mentioned in this
          guide, see Oracle Server Tuning.
      s   For information about the V$ views and specific fields from which these perfor-
          mance statistics are obtained, see the Oracle Server Reference.
      s   For information about administering the Oracle Server, see the Oracle Server
          Administrator’s Guide.
      s   For information about administering Oracle Parallel Servers, see the Oracle Par-
          allel Server Support for the Oracle Enterprise Manager Console Guide.
      s   For information about developing database applications within the Oracle
          Server, see the Oracle Server Application Developer’s Guide.
      s   For information on Oracle’s SQL commands and functions, see the Oracle
          Server SQL Reference.
      s   For information about Oracle’s procedural language extension to SQL, PL/
          SQL, see the PL/SQL User’s Guide and Reference.
      s   For information about Oracle messages and codes, refer to Oracle Server Mes-
          sages.
      s   For information specific to the Oracle Server working on your host operating
          system, see your operating system-specific Oracle documentation (specific
          book titles vary by operating system) and system release bulletins, if available.
      Oracle Corporation also publishes several ASCII files, which are available on your
      distribution media. These files describe differences between minor releases of Ora-
      cle software that are not accompanied by new manuals. These files are usually
      named README.DOC, BUGFIX.DOC, BUGHST.DOC, and RESTRICT.DOC. Read
      these files to learn about changes to the software that are not described in the regu-
      lar manuals.




xvi
          The release notes for Enterprise Manager are also available on your distribution
          media. The file is commonly called README.TXT.

                  Additional Information: The exact names and locations of the files
                  mentioned above may vary, depending on your operating system. See
                  your operating system-specific Oracle documentation for information
                  about these files.



Your Comments Are Welcome
          We value and appreciate your Comments as an Oracle user and reader of the manu-
          als. As we write, revise, and evaluate our documentation, your opinions are the
          most important input we receive. After the table of contents in our printed manuals
          is a Reader’s Comment Form, which we encourage you to use to tell us what you
          like and dislike about this manual or other Oracle manuals. If the form is not avail-
          able, please use the following address, FAX number, or electronic mail address.
              Oracle Corporation
              Oracle Performance Manager Documentation
              One Oracle Drive
              Nashua, NH 03062
              U.S.A.

              FAX - 603-987-3317 Attn: Oracle Performance Manager Documentation
              Electronic mail - nedc_doc@us.oracle.com




                                                                                             xvii
xviii
                                                                                              1
           Performance Monitoring Applications

             This chapter introduces the optional performance monitoring applications of the
             Oracle Enterprise Manager. It covers the following topics:
             s   Performance Monitoring Overview
             s   Starting Performance Monitoring Applications
             s   A Typical Performance Monitoring Scenario


Performance Monitoring Overview
             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 pres-
             sure 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
             Oracle Performance Manager is a flexible, extensible, and easy-to-use tool for moni-
             toring 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. More-



                                                            Performance Monitoring Applications   1-1
Performance Monitoring Overview



                   over, 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 TopSes-
                   sions from a chart to find out how individual sessions are contributing to database
                   performance.

Oracle Advanced Events
                   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 environ-
                   ment. 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:
                   s   Fault Management Events
                       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.
                   s   Space Management Events
                       This category of events tracks possible space problems, such as running out of
                       space on a disk or archive device.
                   s   Resource Management Events
                       This category of events tracks possible resource problems, such as exceeding
                       datafile or lock limits.
                   s   Performance Management Events




1-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                       Performance Monitoring Overview



                 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 Sys-
             tem, predefined events, and Oracle Advanced Events, see the chapter on Event
             Management in the Oracle Enterprise Manager Administrator’s Guide.

Oracle Lock Manager
             Oracle Lock Manager is a tool for monitoring locks in a database, particularly block-
             ing and waiting sessions.

Oracle TopSessions
             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 prob-
             lems. 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 state-
             ments 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
             Oracle Tablespace Manager is a tool for monitoring and managing database stor-
             age. 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 dis-
             play 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.




                                                             Performance Monitoring Applications   1-3
Starting Performance Monitoring Applications



Starting Performance Monitoring Applications
                    Oracle Performance Manager, Oracle Lock Manager, Oracle TopSessions, and Ora-
                    cle Tablespace Manager can be started from the console or in standalone mode.:

Starting from the Console
                    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:
                    s    From the Performance Pack launch palette, click on the appropriate application
                         icon.
                    s    From the Tools menu, select Performance Pack, and then the application name.

Starting in Standalone Mode
                    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 applica-
                    tions is displayed. This dialog box contains the following:

                    User Name
                    Enter your Oracle username for the database to which you are connecting.

                    Password
                    Enter your Oracle password for the database to which you are connecting.

                    Service
                    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 associ-
                    ated with the connection begins with the word ”Untitled.” This scheme distin-
                    guishes windows associated with different connections.

                    Connect As
                    Click on this combo box to select the permission level of your login. Options
                    include: NORMAL (default), SYSOPER, and SYSDBA.

                    Use As Preference
                    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.




1-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                            A Typical Performance Monitoring Scenario



           OK
           Click on this button to initiate a connection and display the main window of the
           application.

           Cancel
           Click on this button to exit the dialog box without connecting.
           After either Oracle Lock Manager, Oracle TopSessions or Oracle Tablespace Man-
           ager 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” on page 8-1. For information on the Oracle
           TopSessions main window, see “Using Oracle TopSessions” on page 9-1. For infor-
           mation on the Oracle Tablespace Manager main window, see “Using Oracle
           Tablespace Manager” on page 10-1.
           Starting Oracle Performance Manager in standalone mode, however, requires an
           extra step. See “Starting Oracle Performance Manager” on page 2-2 for more infor-
           mation.


A Typical Performance Monitoring Scenario
           The following scenario illustrates how you can use Oracle Enterprise Manager per-
           formance monitoring applications to monitor database performance and solve per-
           formance 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 con-
           nect to each database.
           While calls from users often alert you to performance problems, using Oracle Enter-
           prise Manager and these optional performance monitoring tools, you can now iso-
           late and resolve problems proactively. Each morning, you establish Oracle
           Enterprise Manager connections to the two remote databases. Using these connec-
           tions, 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 back-
           ground 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 corre-
           sponding 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




                                                          Performance Monitoring Applications    1-5
A Typical Performance Monitoring Scenario



                    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 cur-
                    rently 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 ses-
                    sion. For information on using Instance Manager, see the Oracle Enterprise Manager
                    Administrator’s Guide.




1-6   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                           Part I
Using Oracle Performance Manager

This part describes the optional Oracle Performance Manager application of Oracle
Enterprise Manager, and includes the following chapters:
s   Chapter 2, Oracle Performance Manager Overview
s   Chapter 3, Using Predefined Charts
s   Chapter 4, Customizing Display Elements of Charts
s   Chapter 5, Defining or Modifying Charts
s   Chapter 6, Displaying Charts in Chart Windows
s   Chapter 7, Working with Oracle Performance Manager Data
Beta Draft
                                                                                        2
      Oracle Performance Manager Overview

          This chapter covers how you use Oracle Performance Manager, including the fol-
          lowing topics:
          s   Terminology
          s   Starting Oracle Performance Manager
          s   Oracle Performance Manager Main Window
          s   Using the Right Mouse Button
          s   Chart Types


Terminology
          Oracle Performance Manager uses the term ”chart” to refer to a graph, and the
          term ”chart window” to refer to the frame window containing one or more charts.
          When you create a new chart, a new chart window containing that chart is automat-
          ically created also. For more information on creating charts, see “Defining or Modi-
          fying Charts” on page 5-1.




                                                     Oracle Performance Manager Overview   2-1
Starting Oracle Performance Manager



Starting Oracle Performance Manager
                   To start Oracle Performance Manager from the Oracle Enterprise Manager Console,
                   see “Starting from the Console” on page 1-4.

                            Attention: When you start Oracle Performance Manager from the
                            Console, or when you start Oracle Performance Manager while
                            Oracle Enterprise Manager is running, Oracle Performance Man-
                            ager will attempt to connect to the Console repository, and any
                            user-defined charts and chart windows located in that repository
                            are automatically loaded from it. This also means that unless you
                            first choose to change the repository connection, any user-defined
                            or modified charts and chart windows you save will be saved to
                            that Console repository.


                   To start Oracle Performance Manager in standalone mode, perform the following
                   steps:
                   1.   Start Oracle Performance Manager as you would start any Oracle Enterprise
                        Manager performance monitoring application in standalone mode, as
                        described in “Starting in Standalone Mode” on page 1-4.
                        After you complete the Login Information dialog box, if the Console is not run-
                        ning, the Repository Login dialog box displays. The Repository Login dialog
                        box allows you to choose the repository from which you will load user-defined
                        charts and chart windows that you created in previous sessions for use during
                        this session. This repository will also serve as the location for saving new or
                        modified chart and chart window definitions from this Oracle Performance
                        Manager session. The Repository Login dialog box includes the same fields as
                        does the Login Information dialog box.
                   2.   If the Repository Login dialog box appears, fill in the fields and click OK.
                        The Oracle Performance Manager main window displays, the repository con-
                        nection is made, and any user-defined charts and chart windows in that reposi-




2-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                         Oracle Performance Manager Main Window



              tory are automatically loaded for your use during this Oracle Performance
              Manager session.

                  Attention: If you click Cancel on the Repository Login dialog box,
                  the Oracle Performance Manager main window still displays, but
                  you have no current Oracle Performance Manager repository con-
                  nection. This means that you can use predefined charts, but must
                  first choose Change Repository Connection from the File menu to
                  load user-defined charts and chart windows. Without a current
                  repository connection, you can also create and modify chart and
                  chart window definitions, but in order to save them for use in a
                  subsequent Oracle Performance Manager session, you would first
                  be prompted to establish a repository connection.



Oracle Performance Manager Main Window
          After the Oracle Performance Manager is started, its main window appears.
          Figure 2–1 illustrates the Oracle Performance Manager main window.

          Figure 2–1 Oracle Performance Manager Main Window




          As Figure 2–1 shows, the Oracle Performance Manager main window includes a:
          s   Title bar
          s   Status bar




                                                    Oracle Performance Manager Overview    2-3
Oracle Performance Manager Main Window



                   s   Menu bar

                              Note: For information on the components of an Oracle Perfor-
                              mance Manager chart window, see “Chart Window Components”
                              on page 6-1.


Title Bar
                   As with other Oracle Enterprise Manager applications, the Oracle Performance
                   Manager title bar includes the name of the application and the database instance to
                   which a connection has been made.

Status Bar
                   The Oracle Performance Manager status bar includes an explanation of any
                   selected menu item.
                   The following section describes the Oracle Performance Manager menu bar.

Menu Bar
                   The Oracle Performance Manager menu bar includes the following pull-down
                   menus:
                   s   File
                   s   Display
                   s   Charts
                   s   Record
                   s   Help

                   File Menu
                   The File menu items allow you to load user-defined charts and chart windows for
                   use in the current Oracle Performance Manager session, save user-defined charts
                   and chart windows for use in subsequent Oracle Performance Manager sessions,
                   change monitored database and repository connections, choose a machine on
                   which to monitor MIB variables, set up your printing environment, and exit your
                   current Oracle Performance Manager session.
                   The File menu includes the following menu items:




2-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                Oracle Performance Manager Main Window



Load Charts
Allows you to access charts and chart windows defined or modified from previous
sessions.

        Attention: Choosing Load Charts overwrites any new or modi-
        fied chart or chart window definitions not yet saved during the cur-
        rent Oracle Performance Manager session. If you want to save
        these definitions for use in a subsequent Oracle Performance Man-
        ager session, choose Save Charts.


Because choosing Load Charts overwrites any new or modified chart or chart win-
dow definitions not yet saved during the current Oracle Performance Manager ses-
sion, you can use Load Charts as a convenient way to discard chart or chart
window definitions you created or modified during the current Oracle Perfor-
mance Manager session, but no longer want.

        Attention: The repository tables required by the Load Charts and
        Save Charts features are created when the Oracle Enterprise Man-
        ager Performance Pack repository is created. For more informa-
        tion, see the Oracle Enterprise Manager Configuration Guide.


For more information, see “Saving and Reusing Chart and Chart Window Defini-
tions” on page 7-1.

Save Charts
Allows you to save to the current Oracle Performance Manager repository the user-
defined charts and chart windows you have defined or modified during the current
Oracle Performance Manager session for reuse in the future. Any changes you have
made to predefined charts, such as modifying the data polling interval, will not be
saved.

        Attention: Choosing Save Charts overwrites the chart and chart
        window definitions that were previously saved using the Save
        Charts menu item.


The Save Charts menu item is disabled when no user-defined charts or chart win-
dows have been created, modified, or loaded during this Oracle Performance Man-
ager session.




                                           Oracle Performance Manager Overview    2-5
Oracle Performance Manager Main Window



                   For more information, see “Saving and Reusing Chart and Chart Window Defini-
                   tions” on page 7-1.

                   Change Database Connection
                   If a connection to a monitored database has already been made, choosing this menu
                   item displays a prompt asking if you want to drop the current connection and close
                   all active windows. If you choose to do so, the Login Information dialog box
                   appears so that you can connect to a database instance and open an Oracle Perfor-
                   mance Manager main window to monitor that database. For information on the
                   Login Information dialog box, see “Starting Performance Monitoring Applications”
                   on page 1-4.
                   If no database connection currently exists, choosing this menu item immediately
                   displays the Login Information dialog box.

                   Change Repository Connection
                   Allows you to connect to a repository. For more information, see “Saving and Reus-
                   ing Chart and Chart Window Definitions” on page 7-1.

                   Print Setup
                   Displays the Print Setup dialog box, from which you can select a printer to print
                   your charts or chart windows, select a remote network in order to access a remote
                   printer, and modify other properties of the print job. (The Print menu item itself is
                   located on the Window menu of each chart window, as described in “Displaying
                   Charts in Chart Windows” on page 6-1.

                   Exit
                   Allows you to exit Oracle Performance Manager. If you launched Oracle Perfor-
                   mance Manager from the Console, you return to the Console.

                   Display Menu
                   The Display menu items allow you to display chart windows containing charts. For
                   more information on the predefined charts listed in this section, see “Using Pre-
                   defined Charts” on page 3-1.
                   The Display menu includes the following menu items:

                   Contention
                   Allows you to display predefined charts for monitoring database contention statis-
                   tics.
                   Options include: Circuit; Dispatcher; Free List Hit %; Latch; Lock; Queue; Redo
                   Allocation Hit %; Rollback NoWait Hit %; and Shared Server.




2-6   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                 Oracle Performance Manager Main Window



Database_Instance
Allows you to display charts for monitoring usage statistics regarding the database
instance.
Options include: Process; Session; System Statistics; Table Access; Tablespace;
Tablespace Free Space; # Users Active; # Users Logged On; # Users Waiting; # Users
Waiting for Locks; and # Users Running.

I/O
Allows you to display charts for monitoring input/output activity of the database
instance.
Options include: File I/O Rate; File I/O Rate Details; Network I/O Rate; and Sys-
tem I/O Rate.

Load
Allows you to display charts for monitoring the activity level of the database
instance.
Options include: Buffer Gets Rate; Network Bytes Rate; Redo Statistics Rate; Sort
Rows Rate; Table Scan Rows Rate; and Throughput Rate.

Memory
Allows you to display charts for monitoring memory usage of the database
instance.
Options include: Buffer Cache Hit %; Data Dict Cache Hit %; Library Cache Hit %;
Library Cache Details; SQL Area; Memory Allocated; Memory Sort Hit %; Parse
Ratio; and Read Consistency Hit %.

Overview
Allows you to display a composite of the following charts: # Users Active; # Users
Logged On; # Users Running; # Users Waiting for Locks; Buffer Cache Hit; Data
Dict Cache Hit %; File I/O Rate; Library Cache Hit %; Memory Allocated; Memory
Sort Hit %; Rollback Nowait Hit %; System I/O Rate; and Throughput.

User Defined
Displays the Select User-defined Window dialog box, from which you can choose
to display a chart window you have already defined.
The User Defined menu item is disabled if no user-defined charts or chart windows
have been created or loaded during this Oracle Performance Manager session.




                                            Oracle Performance Manager Overview     2-7
Oracle Performance Manager Main Window



                   Charts Menu
                   The Charts menu items allow you to define and modify charts and chart windows.
                   The Charts menu includes the following menu items:

                   New SQL Chart
                   Displays the New SQL Chart property sheet, from which you can enter SQL state-
                   ments to gather realtime database statistics, and then specify how these statistics
                   will be displayed in a chart. For more information, see “Defining or Modifying
                   Charts” on page 5-1.

                   Modify SQL Chart
                   Displays the Modify SQL Chart property sheet, from which you can modify the
                   SQL statement and display options for an existing chart. Using this property sheet,
                   you can also delete a user-defined chart, or display the SQL statement and display
                   options for a predefined chart. For more information, see “Defining or Modifying
                   Charts” on page 5-1.

                   New Window
                   Displays the New Window property sheet, from which you can define a new chart
                   window to display one or more user-defined or multiple predefined charts. For
                   more information, see “Displaying Charts in Chart Windows” on page 6-1.

                   Modify Window
                   Displays the Modify Window property sheet, from which you can add or delete
                   existing charts from an existing user-defined chart window. You can also delete
                   user-defined chart windows using this property sheet. For more information, see
                   “Displaying Charts in Chart Windows” on page 6-1.

                   Record Menu
                   The Record menu items allow you to record data you can then display in Oracle
                   Performance Manager charts. For more information, see “Working with Historical
                   Data” on page 7-3.

                           Attention: In order to use the Record menu items, you must first
                           create the Oracle Trace formatter tables in any database in which
                           you want to store recorded historical data. (This could be the same
                           database as the Oracle Performance Manager repository.) For more
                           information, see the section on creating Oracle Trace formatter
                           tables in the Performance Pack chapter of the Oracle Enterprise Man-
                           ager Configuration Guide.




2-8   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                    Oracle Performance Manager Main Window



The Record menu includes the following menu items:

Start Recording
Allows you to begin recording data. This menu item is disabled when a data record-
ing session is currently in progress.

        Attention: Do not modify a chart or window using the Modify
        Chart or Modify Window property sheets while you are recording
        data for that chart or window.


Stop Recording
Allows you to stop recording data. This menu item is enabled when a data record-
ing session is currently in progress.

Playback
Allows you to play back on a displayed chart data you have previously collected.

Delete Recorded Data
Allows you to delete a named historical data collection. For more information, see
“Deleting an Historical Data Collection” on page 7-7.

Help Menu
The Help menu items allow you to obtain information on using Oracle Perfor-
mance Manager.
The Help menu includes the following menu items:

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 Performance Manager
Displays version information for this release of Oracle Performance Manager.




                                                Oracle Performance Manager Overview   2-9
Using the Right Mouse Button



Using the Right Mouse Button
                   Clicking the right mouse button on a displayed chart allows you to drill down from
                   or customize the chart. For more information, see “Customizing the Chart Display”
                   on page 4-1.
                   Clicking the right mouse button on a chart legend displays a menu that allows you
                   to customize the chart legend. For more information, see “Customizing the Chart
                   Legend” on page 4-4.


Chart Types
                   You can change the presentation format of any chart to any chart type that Oracle
                   Performance Manager provides. The easiest way is to click on a displayed chart
                   using the right mouse button, select Type, and then select one of the chart type
                   options. You can also change the chart type using the Chart Properties property
                   sheet. For more information, see “Customizing Display Elements of Charts” on
                   page 4-1.
                   Oracle Performance Manager provides the following chart types.

                   Area
                   Area charts show the relative importance of values over a period of time.
                   You can use 3D effects, zoom in, and rotation features with Area charts.

                   Line
                   Line charts show trends or changes in data over a period of time. This is the default
                   format for predefined charts displaying rates per unit of time.
                   You can use 3D effects, zoom in, and rotation features with Line charts.
                   When you first display a Line chart, you will notice the addition over time of tics at
                   the bottom of the chart. Each of these tics represents a polling event. A timestamp
                   marks the tenth polling event, so that the time interval between each timestamp
                   spans 10 polling events.

                   Horizontal Bar
                   Bar charts show discrete measurements over a period of time.
                   You can use 3D effects, zoom in, and rotation features with Horizontal Bar charts.

                   Pie
                   Pie charts show the relationship or proportions of parts to a whole. This is the
                   default format for predefined charts displaying ratios.




2-10 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                            Chart Types



Oracle Performance Manager allows you to display several variations of Pie charts,
including: standard Pie; 2D exploding Pie (in which you can drag one or more
slices out from the pie for emphasis); and 3D versions of these Pie charts.
You can use the rotation feature with Pie charts.

Bar
Vertical bar charts show discrete changes over time.
You can use 3D effects, zoom in, and rotation features with Columns charts.

Cube
Cube charts show the relative importance of values over a period of time. Cubes
replace bars as data markers. You can display the cube data markers as 3D cubes,
conics, or cylinders.
You can use the zoom in, special 3D effects, and rotation features with Cube charts.

Table
Table charts present text information, or a large number of instances, in a format
that is easy to review. This is the default format for predefined charts displaying
such information. The Table chart type is also the default format for a new chart
you define yourself.

        Attention: If you cannot see the entire contents of a field in a
        Table chart, you can double-click on the field, and use the arrow
        keys to scroll in the field.




                                           Oracle Performance Manager Overview    2-11
Chart Types




2-12 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                            3
                                       Using Predefined Charts

            Oracle Performance Manager includes a wide variety of predefined charts to graph-
            ically depict database performance statistics of common interest. This section cov-
            ers the following topics:
            s   Default Features of Predefined Charts
            s   Overview of Predefined Charts


Default Features of Predefined Charts
            By default, Oracle Performance Manager presents information in predefined charts
            in the following ways:
            s   Charts showing rates per unit of time are presented as line charts, in order to
                best show trends over time. The predefined chart File I/O Rate is an example.
            s   Charts showing ratios are presented as pie charts, in order to best compare the
                relative measurement of one variable to another variable. The predefined chart
                Library Cache Hit % is an example.
            s   Charts consisting primarily of text information are presented as tables, in order
                to best display that text. The predefined chart Tablespace is an example.
            s   Charts displaying a large number of rows and columns are also presented as
                tables, in order to best summarize that large amount of data. The predefined
                chart Session is an example.
            s   Other charts are presented as bar charts. The predefined chart No. of Users
                Logged On is an example.
            However, regardless of the information content, you can change the presentation
            format of any chart to any chart type that Oracle Performance Manager provides.
            The easiest way is to click on a displayed chart using the right mouse button, select



                                                                      Using Predefined Charts   3-1
Overview of Predefined Charts



                    Type, and then select one of the chart type options. You can also change the chart
                    type using the Chart Properties property sheet. For more information, see “Custom-
                    izing Display Elements of Charts” on page 4-1.
                    The X and Y axes of Oracle Performance Manager charts automatically scale to fit
                    the data being presented. However, you can change the scale of any chart if you so
                    choose. For more information, see “Customizing Display Elements of Charts” on
                    page 4-1.
                    You can change the dimension of any chart type (except a table) to be either 2D or
                    3D. The easiest way is to click on a displayed chart using the right mouse button,
                    select Dimensions, and then select 2D or 3D. You can also change the dimension of
                    a chart using the Chart Properties property sheet. For more information, see “Cus-
                    tomizing Display Elements of Charts” on page 4-1.
                    You can display any predefined chart by selecting from the Monitor menu, or you
                    can define a new chart window containing one or more predefined charts. For infor-
                    mation on displaying or defining chart windows that contain predefined charts, see
                    “Displaying Charts in Chart Windows” on page 6-1.


Overview of Predefined Charts
                    This section includes an alphabetical listing of the predefined Oracle Performance
                    Manager charts. Each chart is described briefly. The V$ view(s) from which the
                    chart statistics were derived is listed. When you can drill down for more detail, that
                    information is also included.
                    For information about the statistics these charts display and the V$ views from
                    which these statistics are derived, see the Oracle Server Reference. For information on
                    how best to interpret these statistics, see Oracle Server Tuning.

                                Additional Information: For information on charts for an Oracle
                                Parallel Server, see the Oracle Parallel Server Support for the Oracle
                                Enterprise Manager Console Guide.


Buffer Cache Hit %
                    The Buffer Cache Hit % chart displays information about the buffer cache hit per-
                    centage of the database instance.
                    This chart displays statistics from the following view:
                    s   V$SYSSTAT




3-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                             Overview of Predefined Charts



Buffer Gets Rate
                The Buffer Gets Rate chart displays information about the buffer gets rate of the
                database instance.
                This chart displays statistics from the following view:
                s   V$SYSSTAT

Circuit
                The Circuit chart displays information about virtual circuits. A virtual circuit is a
                connection to a database instance through a dispatcher and the shared server cur-
                rently processing the user’s request.
                The Circuit chart displays statistics from the following views:
                s   V$CIRCUIT
                s   V$DISPATCHER
                s   V$SESSION
                s   V$SHARED_SERVER

Data Dict Cache Hit %
                The Data Dict Cache Hit % chart displays information about data dictionary cache
                hits of the database instance.
                This chart displays statistics from the following view:
                s   V$ROWCACHE

Dispatcher
                The Dispatcher chart displays activity statistics for dispatcher processes of the data-
                base instance.
                This chart displays statistics from the following view:
                s   V$DISPATCHER

File I/O Rate
                The File I/O Rate chart displays the number of physical reads and writes per sec-
                ond for all files of the database instance. Only information about Oracle files is




                                                                           Using Predefined Charts    3-3
Overview of Predefined Charts



                    shown in this chart. You can drill down from the File I/O Rate chart to display the
                    File I/O Rate Details chart.
                    This chart displays statistics from the following view:
                    s   V$FILESTAT

File I/O Rate Details
                    The File I/O Rate Details chart displays the number of physical reads and writes
                    per second for individual files of the database instance.
                    This chart displays statistics from the following views:
                    s   V$FILESTAT
                    s   V$DBFILE

Free List Hit %
                    The Free List Hit % chart displays data block contention statistics for the database
                    instance.
                    This chart displays statistics from the following views:
                    s   V$WAITSTAT
                    s   V$SYSSTAT

Latch
                    The Latch chart displays latch activity statistics for processes of the database
                    instance. Latches are low-level locks on shared internal structures.
                    This chart displays statistics from the following views:
                    s   V$LATCH
                    s   V$LATCHHOLDER
                    s   V$LATCHNAME

Library Cache Details
                    The Library Cache Details chart displays library cache performance statistics for
                    the library cache. The library cache contains shared cursors and is part of the
                    shared pool in the SGA of the database instance.
                    This chart displays statistics from the following view:




3-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                          Overview of Predefined Charts



              s     V$LIBRARYCACHE

Library Cache Hit %
              The Library Cache Hit % chart displays the percentages of library cache hits and
              library cache misses for the database instance. You can drill down from the Library
              Cache Hit % chart to display the Library Cache Details chart.
              This chart displays statistics from the following view:
              s     V$LIBRARYCACHE

Lock
              The Lock chart displays information about locks that are held and locks that are
              requested for the database instance.
              This chart displays statistics from the following views:
              s     V$LOCK
              s     V$SESSION

Memory Allocated
              The Memory Allocated chart displays the amount of memory (in bytes) allocated
              for current sessions in the database instance.
              This chart displays statistics from the following views:
              s     V$STATNAME
              s     V$SESSTAT

Memory Sort Hit %
              The Memory Sort Hit % chart displays the percentages of memory sort hits and
              memory sort misses for the database instance.
              This chart displays statistics from the following view:
              s     V$SYSSTAT

Network Bytes Rate
              This chart displays statistics from the following view:
              s     V$SYSSTAT




                                                                         Using Predefined Charts   3-5
Overview of Predefined Charts



Network I/O Rate
                    The Network I/O Rate chart displays the number of message packets per second
                    the database server and its clients exchange over SQL*Net.
                    This chart displays statistics from the following view:
                    s   V$SYSTEM_EVENT

No. of Users Active
                    The No. of Users Active chart displays the number of user sessions actively using
                    the database instance at the current time.
                    This chart displays statistics from the following view:
                    s   V$SESSION

No. of Users Logged On
                    The No. of Users Logged On chart displays the number of concurrent user sessions
                    logged on to the database instance, whether or not activity is being generated.
                    This chart displays statistics from the following view:
                    s   V$LICENSE

No. of Users Running
                    The No. of Users Running chart displays the number of concurrent user sessions
                    logged on to the database instance and currently running a transaction.
                    This chart displays statistics from the following view:
                    s   V$SESSION_WAIT

No. of Users Waiting
                    The No. of Users Waiting chart displays the number of users currently logged on to
                    the database instance, but waiting (for whatever reason) to perform a task.
                    This chart displays statistics from the following view:
                    s   V$SESSION_WAIT




3-6   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                           Overview of Predefined Charts



No. of Users Waiting For Locks
              The No. of Users Waiting For Locks chart displays the number of users currently
              logged on to the database instance, but waiting for the release of a lock.
              This chart displays statistics from the following view:
              s   V$SESSION

Overview
              The default Overview chart window includes a group of charts that display key
              performance statistics to indicate the general health of the database instance. Specif-
              ically, the Overview chart window includes the following charts:
              s   # Users Active
              s   # Users Logged On
              s   # Users Running
              s   # Users Waiting for Locks
              s   Buffer Cache Hit
              s   Data Dict Cache Hit %
              s   File I/O Rate
              s   Library Cache Hit %
              s   Memory Allocated
              s   Memory Sort Hit %
              s   Rollback Nowait Hit %
              s   System I/O Rate
              s   Throughput
              Choose New Window from the Create menu to create a customized version of the
              Overview chart window, in order to include the charts most relevant to your partic-
              ular environment. For more information, see “Displaying Charts in Chart Win-
              dows” on page 6-1.

Parse Ratio
              The Parse Ratio chart displays the parse ratio for the database instance.
              This chart displays statistics from the following views:



                                                                         Using Predefined Charts    3-7
Overview of Predefined Charts



                    s   V$SYSSTAT

Process
                    The Process chart displays information about currently active background and user
                    processes for the database instance.
                    When the username or terminal are unidentified in V$PROCESS, the information
                    from V$SESSION is displayed if it exists. If the username is taken from V$SES-
                    SION, it is displayed in parentheses.
                    This chart displays statistics from the following views:
                    s   V$PROCESS
                    s   V$SESSION

Queue
                    The Queue chart displays activity statistics about queues used in the multi-
                    threaded server.
                    This chart displays statistics from the following view:
                    s   V$QUEUE

Read Consistency Hit %
                    The Read Consistency Hit % chart displays the percentages of read consistency hits
                    and read consistency misses for the database instance.
                    This chart displays statistics from the following view:
                    s   V$SYSSTAT

Redo Allocation Hit %
                    The Redo Allocation Hit % chart displays the percentages of redo allocation hits
                    and redo allocation misses for the database instance.
                    This chart displays statistics from the following view:
                    s   V$LATCH

Redo Statistics Rate
                    The Redo Statistics Rate chart displays the number of entries, space requests, and
                    synch writes per second for the database instance.




3-8   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                            Overview of Predefined Charts



                This chart displays statistics from the following view:
                s   V$SYSSTAT

Rollback Nowait Hit %
                The Rollback Nowait Hit % chart displays the percentages of hits and misses for
                online rollback segments of the database instance.
                This chart displays statistics from the following view:
                s   V$ROLLSTAT

Session
                The Session chart displays information about user sessions logged onto the data-
                base instance.
                This chart displays statistics from the following views:
                s   V$PROCESS
                s   V$SESSION

Shared Server
                The Shared Server chart displays queuing activity statistics for shared server pro-
                cesses of the database instance.
                This chart displays statistics from the following view:
                s   V$SHARED_SERVER

Sort Rows Rate
                The Sort Rows Rate chart displays information about the sort rows rate for the
                entire database instance.
                This chart displays statistics from the following view:
                s   V$SYSSTAT

SQL Area
                The SQL Area chart displays information about shared cursors in the library cache.
                Each shared cursor contains information used to execute its SQL statement or PL/
                SQL block.




                                                                           Using Predefined Charts   3-9
Overview of Predefined Charts



                    This chart displays statistics from the following view:
                    s   V$SQLAREA

System I/O Rate
                    The System I/O Rate chart displays I/O statistics for the database instance, includ-
                    ing buffer gets, block changes, and physical reads per second.
                    This chart displays statistics from the following view:
                    s   V$SYSSTAT

System Stats
                    The System Stats chart displays 121 statistics for the database instance. This chart is
                    appears in table format by default to aid in locating statistics of interest.
                    This chart displays statistics from the following view:
                    s   V$SYSSTAT

Table Access
                    The Table Access chart displays information about database objects being accessed
                    and the sessions accessing them in the database instance.
                    This chart displays statistics from the following view:
                    s   V$ACCESS

Table Scan Rows Rate
                    The Table Scan Rows Rate chart displays the number of table scan rows per second
                    for the database instance.
                    This chart displays statistics from the following view:
                    s   V$SYSSTAT

Tablespace
                    The Tablespace chart displays statistics for tablespaces in the database instance,
                    including status, total number of file blocks, and total quota blocks.
                    This chart displays statistics from the following tables:
                    s   DBA_DATA_FILES




3-10 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                           Overview of Predefined Charts



             s    DBA_TABLESPACES
             s    DBA_TS_QUOTAS

Tablespace Free Space
             The Tablespace Free Space chart displays the number of free file blocks and number
             of free quota blocks in each tablespace of the database instance.
             This chart displays statistics from the following table:
             s    DBA_FREE_SPACE

Throughput Rate
             The Throughput Rate chart displays the number of user calls and transactions per
             second for the database instance.
             This chart displays statistics from the following view:
             s    V$SYSSTAT




                                                                        Using Predefined Charts    3-11
Overview of Predefined Charts




3-12 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                            4
     Customizing Display Elements of Charts

           You can customize a number of chart elements-from major display elements such
           as chart type, dimension, and scale-to details concerning shadowing, angles, and
           legend. This chapter covers the following topics:
           s   Customizing the Chart Display
           s   Customizing the Chart Legend


Customizing the Chart Display
           Click the right mouse button on a displayed chart to display a menu with the fol-
           lowing menu items:

           Dimensions
           Allows you to change the dimension of any chart type (except a table).
           Options include: 2D and 3D.

           Drilldown
           Allows you to display another chart providing more detail. If this menu item is
           greyed out, no drilldown chart is available for this chart. If this menu item can be
           selected, selecting it displays the drilldown chart.
           Predefined charts with drilldown capabilities include File I/O Rate and Library
           Cache Hit %. “Defining or Modifying Charts” on page 5-1 describes how to define
           a new chart with drilldown capability.

           Properties
           Allows you to change a number of properties of the displayed 3D chart. For more
           information, see “Displayed 3DChart” on page 4-2.




                                                       Customizing Display Elements of Charts     4-1
Customizing the Chart Display



                    Scale
                    Allows you to customize measurement units and their scaling for the displayed
                    chart. For more information, see “Scaling of a Displayed Chart” on page 4-3.

                    Type
                    Allows you to change the chart type of a chart.
                    Options include: Area, Bar, Cube, Horizontal Bar, Line, Pie, and Table. See “Chart
                    Types” on page 2-10 for a description of these chart types.

                                Attention: If a blank chart is displayed, a 3D chart has probably
                                been selected, but the chart window containing the chart is not
                                high or wide enough to display it.


Displayed 3DChart
                    To customize the 3D properties of a displayed 3D chart, click the right mouse but-
                    ton on the chart and choose Properties. The 3D View Properties property sheet dis-
                    plays. It contains the following elements:

                    Slide bar
                    Modify the slide bar to change the depth of the 3D chart.

                    X Angle
                    Modify the value in this box to specify the number of degrees to rotate the X angle
                    of the chart. The chart will then be viewed from a different perspective. The default
                    is 0.

                    Y Angle
                    Modify the value in this box to specify the number of degrees to rotate the Y angle
                    of the chart. The chart will then be viewed from a different perspective. The default
                    is 0.

                    3D
                    Check this box to display the 3D version of the chart.

                    Full 3D View
                    Check this box to enable the X Angle and Y Angle check boxes. 3D chart rotation is
                    then enabled.

                    Shadows
                    Check this box to turn off/on marker shadows when in 3D mode. The default is on.




4-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                             Customizing the Chart Display



               Click the Apply button to view any changes you have made to these 3D properties
               of the chart, while still remaining on the 3D View Properties property sheet. Click
               the OK button to save these changes and exit this property sheet.

Scaling of a Displayed Chart
               To customize the scaling of a displayed chart, click the right mouse button on the
               chart. The Scale and Numbers property sheet displays. It contains the following ele-
               ments:

               Main Y Axis
               This button is always selected to indicate that only the Y axis of a displayed chart
               can be customized.

               X Axis
               This button is always disabled to indicate that only the Y axis of the displayed chart
               can be customized.

               Secondary Y Axis
               This button is not implemented.

               Main Y Axis
               Minimum: Modify the value in this box to specify the minimum value for the
               selected axis. The default is 0.
               Maximum: Modify the value in this box to specify the maximum value for the
               selected axis.
               Scale Unit: Oracle Performance Manager automatically selects the scale. Modify the
               value in this box to override the automatic selection.
               Decimals: Modify the value in this box to specify the number of decimals displayed
               for data point values. The default is 0 (no decimals displayed).

               Increment (gap)
               Automatic: Automatic is the default increment gap (or tick scale). This means that
               Oracle Performance Manager automatically selects the gap used in the axis.
               Fixed: Check this box to specify the increment gap (or tick scale) used in the axis.
               You can then modify the value in the box to the right.

               Show zero axis
               Click this box to show the zero axis on a chart that includes negative values.




                                                           Customizing Display Elements of Charts     4-3
Customizing the Chart Legend



                   Axis Scale
                   Linear: Click this button to choose a linear scale for the Y axis. Linear is the default
                   Y axis scale.
                   Logarithmic: Click this button to choose a logarithmic scale for the Y axis. You can
                   then enter the log base to be used in the box to the right (the default is base 10).
                   Click the Apply button to view any changes you have made to these scalar proper-
                   ties of the chart, while still remaining on the Scale and Numbers property sheet.
                   Click the OK button to save these changes and exit this property sheet.


Customizing the Chart Legend
                   This section covers the following topics:
                   s      Chart Legend Menu
                   s      Chart Legend Properties Other than Location

                               Suggestion: In some cases, you may need to drag the bottom of
                               the chart down in order to view the legend in its default location.


Chart Legend Menu
                   To customize the legend of a displayed chart, first click the right mouse button on
                   that legend. The menu that is displayed includes the following menu items:

                   Options
                   Allows you to customize the chart legend by completing the Options property
                   sheet, as described in the following section.

                   Left
                   Positions the legend to the left of the chart.

                   Top
                   Positions the legend on top of the chart.

                   Right
                   Positions the legend to the right of the chart.

                   Bottom
                   Positions the legend below the chart (default).




4-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                            Customizing the Chart Legend



              Float
              Places the legend in a box which you can drag to position anywhere on the monitor
              screen.

              Fixed
              Fixes the position of a floating legend so that it cannot be moved.

Chart Legend Properties Other than Location
              After you click the right mouse button on the chart legend and select Options, the
              Options property sheet is displayed. Use this property sheet to customize proper-
              ties of the chart legend other than its position relative to the chart display. The
              Options property sheet contains the following:

              Draw Border
              Uncheck this box to remove the border surrounding a floating chart legend, or a
              fixed chart legend positioned outside of its default location under the chart itself.
              This box is checked by default.

              Skip Empty
              Uncheck this box to remove chart legend entries for variables when their value is 0.
              This box is unchecked by default.

              Draw Splitter
              Uncheck this box to remove part of the frame separating the chart legend from the
              chart display. This box is checked by default.

              Multiline
              Uncheck this box to force a chart legend entry onto a single line in the chart legend.
              This box is checked by default.

              3D Frame
              Check this box to add a third dimension to the frame of the chart legend. This box
              is unchecked by default.




                                                           Customizing Display Elements of Charts     4-5
Customizing the Chart Legend




4-6   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                               5
                              Defining or Modifying Charts

             In addition to the wide variety of predefined charts and customizing features that
             Oracle Performance Manager provides, you can also define your own charts or
             modify the basic chart definition. This chapter covers the following topics:
             s   Defining a New Chart
             s   Example of Defining a New Chart
             s   Modifying the Basic Definition of a User-defined SQL Chart
             s   Modifying the Basic Definition of a Predefined Chart


Defining a New Chart
             You can define new SQL charts.
             To define a new SQL chart, select New SQL Chart from the Charts menu.
             The resulting New SQL Chart property sheet includes the following pages:
             s   SQL Statement
             s   Display Options
             To select one of these pages, click on the appropriate tab title near the top of the
             New Chart property sheet. You can select the Display Options page only after you
             have completed the SQL Statement page.

SQL Statement Page
             Use the SQL Statement page of the New SQL Chart property sheet to enter a SQL
             statement that will be used to gather statistics for the chart you are defining. For
             more information on SQL, see Oracle Server SQL Reference.




                                                                    Defining or Modifying Charts    5-1
Defining a New Chart



                   The SQL Statement page contains the following:

                   Chart Name
                   Enter a unique name for the new chart you are defining.

                   Drilldown Window
                   Click on this combo box to choose among existing (user-defined, as well as pre-
                   defined) chart windows to be displayed when the user drills down to a more
                   detailed level. Specifying the drilldown feature is optional. (For information on
                   drilling down, see “Using the Right Mouse Button” on page 2-10..
                   For example, if you were to define a chart similar to the predefined chart named
                   File I/O Rate, you might want to select the option FILE I/O RATE DETAILS from
                   the Drilldown combo box. The predefined chart File I/O Rate Details would then
                   be displayed when the user drills down from the chart you are defining.
                   You can also specify one of five Oracle TopSessions drilldown options. In this case,
                   the user drills down from an Oracle Performance Manager chart in the usual man-
                   ner, but instead of displaying another chart, the user displays the Oracle TopSes-
                   sions main window. By clicking OK on the Oracle TopSessions Sessions Options
                   page, which is then displayed automatically, the user will display the top x sessions
                   sorted by one of the five Oracle TopSessions predefined sort statistics the chart cre-
                   ator selected as the drilldown option for the chart. For more information on these
                   predefined sort statistics, see “Options Sort Page” on page 9-17.
                   For example, if you were to define a chart similar to the predefined chart named
                   File I/O Rate, you might want to select the option TOPSESSIONS - FILE I/O from
                   the Drilldown combo box. The Oracle TopSessions main window would then be
                   displayed when the user drills down from the chart you are defining. By choosing
                   Refresh from the Oracle TopSessions Sessions menu, the user would display the top
                   x sessions sorted by the File I/O sort statistic.

                   SQL Statement
                   Enter in this box the SQL statement that will be used to gather statistics to be dis-
                   played in the chart you are defining. Make sure that the SQL statement does not
                   end with a semicolon (;).

                   Execute
                   Click on this button to execute the SQL statement you have entered in the SQL
                   Statement box, and display its result in the Result box.

                   Result
                   After you click on the Execute button, this box displays the result of the SQL state-
                   ment you entered in the SQL Statement box. If the SQL statement returns an error,



5-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                    Defining a New Chart



              the title bar of the Result box displays the error. If the SQL statement is correct but
              returns no data, the title bar of the Result box indicates that no data is available.
              You must enter a valid SQL statement that returns data in order to create a chart.

              Apply
              Disabled.

              OK
              Click on this button to apply the chart definitions you have specified, exit the prop-
              erty sheet, and display the new chart you have just defined. The chart is automati-
              cally displayed within a chart window with the same title (name) as the chart itself.
              (If you then want to modify the chart, choose Modify Chart from the Modify menu.)
              If you click OK on the SQL Statement page before you specify any items on the Dis-
              play Options page, the new chart you are defining will be displayed using default
              display options, as defined in the following section. By default, all data the query
              returns is displayed in a table chart without any functions applied.

              Cancel
              Click on this button to cancel the chart definitions you have specified and exit the
              property sheet.

Display Options Page
              Use the Display Options page of the New SQL Chart property sheet to customize
              how the resulting data from the SQL statement entered on the SQL Statement page
              will be displayed in chart form. The Display Options page contains the following:

              Label
              Enter text in this box to label a variable you are presenting as a column in your
              chart.

              Simple
              Click this button (default) to indicate that the chart will display a simple query
              against a table or view, or a calculation on a simple query against a table or view. A
              simple function f(a) contrasts with a complex function f(a) operation f(b), where oper-
              ation is one of the operations of the Operations combo box.
              Clicking the Simple button disables the Operations combo box and the second
              Function and second Column combo boxes of the Display Options page. File I/O
              Rate and virtually all of the other predefined charts are examples of simple charts.




                                                                      Defining or Modifying Charts   5-3
Defining a New Chart



                   Complex
                   Click this button to perform a more complex operation on two queried values from
                   a table or view. A complex function takes the form f(a) operation f(b), where opera-
                   tion is one of the operations of the Operation combo box. This complex function
                   contrasts with a simple function f(a), as selected using the Simple button.
                   Clicking on the Complex button enables the Operation and second Function and
                   second Column combo boxes of the Display Options page. Buffer Cache Hit % and
                   Library Cache Hit % are examples of complex predefined charts. (Almost all of the
                   other predefined charts are simple.)

                   Function
                   Click this first Function combo box to select the type of function to be used on one
                   or more retrieved values from a table or view.
                   The following function definitions refer to current and/or previous retrieved val-
                   ues. The current retrieved value is the value from the most recent data refresh. The
                   previous retrieved value is the value from the data refresh previous to the most
                   recent data refresh.
                   Options include: <none> (to display the current retrieved value without applying a
                   function to it; this is the default); Delta (to display the current retrieved value
                   minus the previous retrieved value); Normalize (to display the current retrieved
                   value minus the retrieved value when the chart was first displayed); Pct of Total (to
                   display the current retrieved value as a percentage of the sum of all values in the
                   column); and Rate (Delta) (to display the value of Delta divided by the amount of
                   time between the current and previous retrieved values).

                   Column
                   Click on this first Column combo box to select which variable will be represented in
                   a column of the chart. This box contains column names from the result of the SQL
                   statement you entered in the SQL Statement box.

                   Add
                   Click on this button to add the column(s) you have defined to the chart, and dis-
                   play these definitions in the Selections to Display box on this page.

                   Operation
                   Click on this second Operation combo box to select the operation to perform on
                   two variables to produce data for a column in a complex chart.
                   Options include: + (addition); - (subtraction); * (multiplication); / (division); and /
                   (* 100 %). This box is enabled only when the Complex button has been selected.




5-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                      Defining a New Chart



Function
Click on this second Function combo box to select the type of function to be used
on the second operand to produce data for a column in a complex chart.
Options include: <none>, Delta, Normalize, Pct of Total, and Rate (Delta). See the
section describing the first Function combo box of the Display Options page for def-
initions of these functions. This second Function combo box is enabled only when
the Complex button has been selected.

Column
Click on this second Column combo box to select the second variable on which the
operation is to be performed to produce data for a column in a complex chart. This
second Column combo box is enabled only when the Complex button has been
selected.

Selections to Display
This box displays the label/function mapping defined for each column name of the
chart.

Delete
Click on this button to delete the selected column from the Selections to Display
box. The chart you are defining will not display the column you have just removed.

Clear All
Clears all entries in the Selections to Display field, so that you can enter a new
label/function mapping for each columnname of the chart.

Row Label
Click on this combo box to specify a column from which data will be used to label
the rows of the chart. The default is <none>.

Chart Type
Click on this combo box to select the chart type for the chart you are defining.
Options include: TABLE (default), AREA, BAR, CUBE, HORIZONTAL BAR, LINE,
and PIE. If you have chosen to display non-numeric (string) data in the chart, you
must select the TABLE chart type. For more information on these chart types, see
“Chart Types” on page 2-10.

Transpose Chart Axes
Click on the No button (default) of this button group to accept the default mapping
of variables in your SQL statement as columns in the chart.




                                                        Defining or Modifying Charts   5-5
Example of Defining a New Chart



                   Click on the Yes button of this button group to map the variables in your SQL state-
                   ment as rows in the chart.

                   Apply
                   Disabled.

                   OK
                   Click on this button to apply the chart definitions you have specified on pages of
                   the New SQL Chart property sheet, exit the property sheet, and display the new
                   chart you have just defined. (If you then want to modify the chart, choose Modify
                   SQL Chart from the Charts menu.)

                   Cancel
                   Click on this button to cancel the chart definitions you have specified on both pages
                   of the New SQL Chart property sheet and exit the property sheet.


Example of Defining a New Chart
                   This section illustrates how you would define a simple chart with two additional
                   features: transposed axes and drilldown capability. This section explains the steps
                   you would take if you were to define a chart similar to the predefined chart File I/
                   O Rate.
                   Figure 5–1 shows the SQL Statement page of the New Chart property sheet, com-
                   pleted as if one were to define the File I/O Rate chart from scratch.




5-6   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                          Example of Defining a New Chart



Figure 5–1 SQL Statement Page for a New Chart




As Figure 5–1 suggests, to define this chart you would complete the SQL Statement
page as follows:
1.   Enter a unique name for the new chart.
2.   From the Drilldown Window combo box, select the drilldown option (chart
     window or Oracle TopSessions option) that will appear when the user drills
     down from the chart you are defining.
     In Figure 5–1, the drilldown option TOPSESSIONS - FILE I/O has been
     selected. Thus, when the chart user drills down from the chart you are defin-
     ing, Oracle TopSessions will be launched, and the Oracle TopSessions main
     window will display the top 10 sessions sorted according to level of file I/O
     activity.
3.   In the SQL Statement box, enter the SQL statement used to gather the statistics
     the chart will display.
4.   Click on the Execute button to execute this SQL statement and display the
     result in the Result box.
5.   In the Result box, verify that the SQL statement produces the results you want.
     (If an error message were displayed in the title bar of the Result box, you
     would reenter the SQL statement in the SQL Statement box and click on the




                                                      Defining or Modifying Charts   5-7
Example of Defining a New Chart



                        Execute button again until you had obtained the results you wanted. If the mes-
                        sage, ”No Data Available,” appeared in the title bar of the Result box, it would
                        indicate that the SQL statement was correct but returned no data.)
                   6.   Click on the Display Options tab at the top of the property sheet.
                   Figure 5–2 shows the Display Options page of the New Chart property sheet, com-
                   pleted as if one were to define the File I/O Rate chart from scratch.

                   Figure 5–2     Display Options Page for a New Chart




                   As Figure 5–2 suggests, to define this chart you would complete the Display
                   Options page as follows:
                   1.   To clear the Selections to Display box of the default Label, Function and Col-
                        umn selections, click on the Clear All button.
                   2.   In the Label box, enter ”Physical Reads/s” as the label for the first chart vari-
                        able you are defining.
                   3.   Accept the default selection of the Simple button to define a simple chart.
                   4.   From the first Function combo box, select ”Rate (Delta),” in order to show the
                        rate of change of the variable (physical reads, in this case) over time.
                   5.   From the first Column combo box, select the first variable, ”SUM(PHYRDS),”
                        to correspond to the label you have specified.



5-8   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                           Example of Defining a New Chart



6.   Click on the Add button to add this first label/function/column mapping to
     the Selections to Display box.
     The above six steps have defined the first variable of the chart. The next five
     steps define the second variable of the chart.
7.   In the Label box, enter ”Physical Writes/s” as the label for the second variable
     you are defining.
8.   Accept the default selection of the Simple button to define a simple chart.
9.   From the first Function combo box, select ”Rate (Delta)” in order to show the
     rate of change of the variable (physical writes, in this case) over time.
10. From the first Column combo box, select the second variable, ”SUM(PHY-
     WRTS)” to correspond to the label you have specified.
11. Click on the Add button to add this second label/function/column mapping to
     the chart.
     The next two steps specify display options for the chart as a whole.
12. In the Chart Type combo box, click on ”LINE” to define the chart format as a
     line chart.
13. In the Transpose Chart Axes button group, check the Yes button, so that the
     two variables you have defined will be plotted as rows, rather than as columns,
     in the chart. (Plotting variables as columns is the default.)
     The next steps complete the process of defining a new chart.
14. Click the OK button to save the chart, exit the New Chart property sheet, and
     display the saved version of the new chart.
15. Close the chart to return to the Oracle Performance Manager main window.

Figure 5–3 shows the chart created from these two pages of the New Chart prop-
erty sheet, as completed in Figure 5–1 and Figure 5–2.




                                                        Defining or Modifying Charts   5-9
Modifying the Basic Definition of a User-defined SQL Chart



                     Figure 5–3      Example of a Simple Chart with Transposed Axes




Modifying the Basic Definition of a User-defined SQL Chart
                     To modify the basic definition of an existing user-defined SQL chart, it is best to dis-
                     play the chart, as described in “Displaying Charts in Chart Windows” on page 6-1,
                     and then choose Modify SQL Chart from the Charts menu.
                     The resulting Modify SQL Chart property sheet is virtually identical to the New
                     SQL Chart property sheet described in a previous section, ”Defining a New Chart,”
                     but for the following exception:

                     Chart Name
                     On the Modify SQL Chart property sheet, Chart Name is a combo box from which
                     you select the existing user-defined chart whose basic definition you want to mod-
                     ify.


Modifying the Basic Definition of a Predefined Chart
                     In the initial release of the Oracle Performance Manager, only user-defined charts
                     can be modified using the Modify Chart property sheet, as described in the previ-
                     ous section. If you want to create a modified version of a predefined chart, you can
                     perform the following steps:




5-10 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                           Modifying the Basic Definition of a Predefined Chart



1.   Display the predefined chart, as described in “Displaying Charts in Chart Win-
     dows” on page 6-1.
2.   In order to review its basic definition, choose Modify SQL Chart from the
     Charts menu.
3.   From the Chart Name combo box, select the name of the predefined chart.
4.   Take note of those definitions. For predefined charts, these definitions are
     greyed out and are displayed for informational purposes only.
5.   Define a new chart that is a modified version of the predefined chart, and
     assign it a unique name. For more information, see “Defining a New Chart” on
     page 5-1.




                                                        Defining or Modifying Charts      5-11
Modifying the Basic Definition of a Predefined Chart




5-12 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                          6
          Displaying Charts in Chart Windows

          This chapter describes how to work with Oracle Performance Manager chart win-
          dows in order to display Oracle Performance Manager charts. Specifically, it covers
          the following topics:
          s   Chart Window Components
          s   Defining a New Chart Window
          s   Modifying an Existing Chart Window
          You can think of a chart window as a frame containing one or more charts. Oracle
          Performance Manager charts are always displayed within chart windows.

                  Attention: If a blank chart is displayed, either the chart window
                  containing the chart is not high or wide enough to display it, or the
                  chart window contains too many charts for its size. Either resize
                  the window or remove charts from it.



Chart Window Components
          A chart window includes the following components:

          Title bar
          Displays the chart window title and the name of the database to which a connec-
          tion has been made. By default, the title of a chart window containing a single chart
          is the same as the title of that chart.

          Menu bar
          Includes the Window, Refresh and Help menus. The Help menu also appears in the
          Oracle Performance Manager main window, and is described in “Oracle Perfor-




                                                         Displaying Charts in Chart Windows   6-1
Chart Window Components



                   mance Manager Overview” on page 2-1. The Window and Refresh menus are
                   described in the following sections,

                   Toolbar
                   Includes the Interval min and sec combo boxes which you can click on to change
                   the automatic refresh interval for the chart(s) in this chart window. (The default is
                   30 seconds.)

                   Display area
                   Displays one or more charts. The chart title is displayed on the chart itself. If a drill-
                   down chart window is available for a chart, this chart title is displayed with ”...”
                   appended to it.

                   Status bar
                   Describes the operation currently in progress.

Window Menu
                   The Window menu items allow you to print and close the displayed chart window.
                   The Window menu includes the following menu items:

                   Print
                   Prints the displayed chart window.

                   Close
                   Closes the chart window.

Refresh Menu
                   The Refresh menu items allow you to refresh the statistics displayed in the chart(s)
                   of a chart window.
                   The Refresh menu includes the following menu items:

                   Automatic
                   Allows you to toggle between automatic and manual refresh mode. When a chart
                   window is displayed, manual refresh mode is set by default.
                   When you toggle to automatic refresh mode, the statistics displayed in a chart win-
                   dow are automatically updated at the time interval shown in the Interval min and
                   sec combo boxes in the chart window tool bar. The default automatic data refresh
                   interval is 30 seconds.




6-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                        Defining a New Chart Window



           A check mark next to the Automatic menu item indicates that automatic refresh
           mode is enabled. The absence of this check mark indicates that manual refresh
           mode is enabled.

           Update Now
           Executes the query and updates the statistics displayed in the chart window. This
           menu item is enabled only when manual refresh mode is enabled.


Defining a New Chart Window
           To define a new chart window, select New Window from the Charts menu. The
           resulting New Window property sheet contains the following:

           Window Name
           Enter a unique name for the chart window you are defining.

           Selected Charts
           Displays the charts to be associated with this new Window Name. To add a chart to
           the Selected Charts box, select a chart in the Available Charts combo box and click
           on the Add button. To remove a chart from the Selected Charts box, select a chart
           name in this box and click on the Remove button.

           Add
           Click on this button to add a selected chart in the Available Charts combo box to
           the list of charts in the Selected Charts box.

           Remove
           Click on this button to remove a selected chart in the Selected Charts box from the
           list of charts associated with the Window Name. The chart will no longer be associ-
           ated with the Window Name.

           Available Charts
           Lists predefined charts and user-defined charts that you can add to the list of charts
           associated with this Window Name. Click on a chart name and click the Add but-
           ton to add a chart to the Selected Charts box.

           OK
           Click on the OK button to exit the property sheet and display the chart window
           you have defined.

           Cancel
           Click on the Cancel button to exit the property sheet without performing a task.




                                                           Displaying Charts in Chart Windows   6-3
Modifying an Existing Chart Window



                    Apply
                    Click on the Apply button to display the chart window you are defining without
                    exiting the New window property sheet.

                    Interval
                    Click on the min and sec combo boxes in the Interval group to change the auto-
                    matic refresh interval for the chart(s) displayed in this chart window. The default
                    automatic refresh interval is 30 seconds.

                    Layout of charts in window
                    Enter the number of charts to be displayed in a row in the chart window. (The
                    default is 1.) For example, if two charts are to be displayed in the chart window
                    and the value in this box is 1, the charts would be displayed vertically; but if the
                    value were 2, the charts would be displayed horizontally.


Modifying an Existing Chart Window
                    To modify an existing chart window, select Modify Window from the Charts menu.
                    The resulting Modify Window property sheet is virtually identical to the New Win-
                    dow property sheet described in the previous section, ”Defining a New Chart Win-
                    dow,” but for the following exception:

                    Window Name
                    On the Modify Window property sheet, Window Name is a combo box you can use
                    to select the user-defined chart window whose basic definition you want to modify.
                    When you select a Window Name from this combo box, the chart(s) associated with
                    this chart window are automatically displayed in the Selected Charts box.




6-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                              7
 Working with Oracle Performance Manager
                                    Data

              This chapter describes how you can collect and access data for display in Oracle
              Performance Manager charts. Specifically, it covers the following topics:
              s   Saving and Reusing Chart and Chart Window Definitions
              s   Working with Historical Data


Saving and Reusing Chart and Chart Window Definitions
                      Attention: The repository tables required by the Load Charts and
                      Save Charts features are created when the Oracle Enterprise Man-
                      ager Performance Pack repository is created. For more informa-
                      tion, see the Oracle Enterprise Manager Configuration Guide.


              For information on deleting named historical data collections, see the Oracle Enter-
              prise Manager Oracle Trace User’s Guide.

How Chart and Chart Window Definitions are Saved
              If you create or modify a chart or chart window, and you want to make it available
              for reuse it in a future Oracle Performance Manager session, you must use the Save
              Charts menu item before exiting the current Oracle Performance Manager session.
              Otherwise, that chart or chart window would not be saved for reuse in the future.
              If you have launched Oracle Performance Manager from Oracle Enterprise Man-
              ager, your changes are automatically saved to the current Oracle Enterprise Man-
              ager repository. However, if you launched Oracle Enterprise Manager as a




                                                  Working with Oracle Performance Manager Data   7-1
Saving and Reusing Chart and Chart Window Definitions



                   standalone application, the first time you choose Save Charts for that session, the
                   Repository Login dialog box is displayed. This dialog box allows you to specify the
                   repository to which you want to save chart and chart window definitions for that
                   session. After that first save, subsequent saves are saved to that same repository by
                   default.
                   When you use the Save Charts menu item, you are saving user-defined charts and
                   chart windows for reuse in future Oracle Performance Manager sessions in the cur-
                   rent repository user’s schema. For example, if you save a new chart while logged
                   into the MarketingII repository as the SYSTEM user, you would have to log into the
                   MarketingII repository as the same user name in order to access that chart defini-
                   tion in the future. You can think of these changes as being written to a file that can
                   be accessed by a specific login to a specific repository over time.

Sharing Chart and Chart Window Definitions
                   Suppose you are a member of a group of DBAs managing all of the databases for a
                   large corporation. As all of you perform similar tasks, you want to be able to
                   exchange the charts you create and modify for your environment. Also, because
                   each of you manage more than one database, you want to monitor these databases
                   using many of the same user-defined and modified charts. In such cases, it is recom-
                   mended that a shared username and password be created for the express purpose
                   of sharing charts and chart windows. In order to access this set of charts, members
                   of the group would then establish a repository connection using the shared user-
                   name and password.
                   In cases when you want to share with another user the chart and chart window def-
                   initions created under your personal username and password, you could perform
                   the following steps:
                   1.   Log into Oracle Performance Manager. and connect to the Oracle Performance
                        Manager using the same credentials used when the charts and chart windows
                        were saved.
                        Doing so will allow you to access these chart and chart window definitions.
                   2.   Choose Load Charts from the File menu.
                   3.   Choose Change Repository Connection from the File menu, and log in with the
                        user name and password of the user you want to allow to use your chart and
                        chart window definitions.
                   4.   As this other user, choose Save Charts from the File menu.
                        Doing so allows a copy of the user-defined charts and chart windows you
                        loaded under your usual login to also be saved under the other user’s login.



7-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                              Working with Historical Data



                  This other user now has a copy of your charts which he or she can display,
                  modify, or add to.

                      Attention: If the receiver of your user-defined charts and chart
                      windows has previously saved his or her own user-defined charts
                      and chart windows, these will be overwritten by those you save to
                      the receiver’s repository under the receiver’s login. It is important
                      that the receiver understand this fact.



Working with Historical Data
             Oracle Performance Manager allows you to collect performance statistics over time
             and replay them. This section describes how you do so.

                      Attention: In order to use the Record menu items, you must first
                      create the Oracle Trace formatter tables in any database in which
                      you want to store recorded historical data. For more information,
                      see the section on creating Oracle Trace formatter tables in the Per-
                      formance Pack chapter of the Oracle Enterprise Manager Configura-
                      tion Guide.


Collection
             To collect historical data for future display in a chart, perform the following steps:
             1.   Display the chart window(s) containing the data you would like to record.
                  To collect data for multiple chart windows during a single data collection ses-
                  sion, display each of those chart windows at this time.
             2.   On the displayed chart window(s), if desired, use the Interval min and sec
                  combo boxes to adjust the polling interval for each chart window displayed
                  during this data collection session.
                  If you are displaying multiple charts for a data collection session, it is possible
                  to set a different polling interval for each of these charts.




                                                   Working with Oracle Performance Manager Data       7-3
Working with Historical Data



                     3.   To begin the data collection session, choose Start Recording from the Record
                          menu.

                               Attention: Do not modify a chart or window using the Modify
                               Chart or Modify Window property sheets while you are recording
                               data for that chart or window.


                     4.   In the resulting Data Collection Name dialog box, enter a name for this histori-
                          cal data collection, and then click OK.
                          Data collection is now taking place on a realtime basis, at the polling interval
                          specified by the Interval min and sec combo boxes on each displayed chart win-
                          dow. The data is saved to the file \ORACLE_HOME\SYSMAN\TRACE\Data-
                          CollectionName.DAT. Additional information is stored in
                          DataCollectionName\.CDF, DataCollectionNamennn\.CHF, and VMMnnn.FDF,
                          where nnn is an internally generated number. These files are used in the follow-
                          ing steps to format the data from the .DAT file to the repository. The .CHF files
                          must remain in their current directory for playback purposes, in order to initial-
                          ize the charts.

                               Attention: These files are not automatically deleted after the data
                               has been formatted to the repository. If you wish to remove them,
                               you need to do so manually using the operating system. Note that
                               since the .CHF files are needed for playback purposes, you should
                               only delete them if the corresponding collection will no longer be
                               played back.


                     5.   When a sufficient period for data collection has transpired, choose Stop Record-
                          ing from the Record menu.
                          The length of this time period will vary, depending on your goals for collecting
                          the data. For example, if you wanted to monitor file I/O during the peak usage
                          period following lunch, it might be appropriate to collect data between 1 PM
                          and 2 PM.
                     6.   In the resulting Format/Playback Login dialog box, enter the connect string for
                          the historical data collection repository that was set up during product installa-
                          tion, and click OK.
                          On subsequent recording sessions during the current Oracle Performance Man-
                          ager session, the connect string you entered for the first recording session is dis-




7-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                           Working with Historical Data



               played by default. In such cases, you could accept this default or enter the
               connect string for a different historical data collection repository that was set
               up during product installation.
               After you click OK on the Format/Playback Login dialog box, the Oracle Trace
               Formatter Status window automatically displays. This window provides infor-
               mation concerning the formatting of the historical data collection for display in
               Oracle Performance Manager charts. It includes the following elements:
               Format Done: Displays a graphic showing formatting progress while that opera-
               tion takes place.
               Formatter Messages: Displays error messages concerning the formatting of this
               historical data collection for display in Oracle Performance Manager charts. If
               no messages display, no errors occurred during the formatting process.

                   Attention: If error messages do appear in the Formatter Messages
                   box, the Oracle Trace formatter tables may not have been validated
                   (upgraded) during product installation. For more information, see
                   the Oracle Enterprise Manager Configuration Guide. It is also possible
                   to run a standalone executable to format the data. For more infor-
                   mation, see “Formatting with the Standalone Executable” on
                   page 7-6.


          7.   To exit the Format/Playback Login dialog box, click Cancel.

Display
          To display historical data in one or more charts, perform the following steps:
          1.   Choose Playback from the Record menu.
          2.   In the resulting Format/Playback Login dialog box, enter the connect string for
               the historical data collection repository to which you saved the data collection
               you now want to play back.
               After the first time you play back historical data during the current Oracle Per-
               formance Manager session, the Format/Playback Login connect string from the
               previous playback session appears in the Format/Playback Login dialog box
               by default.
          3.   In the resulting Select a Collection for Playback dialog box, select the historical
               data collection you want to play back, and then click OK.




                                                Working with Oracle Performance Manager Data       7-5
Working with Historical Data



                          Notice that each entry in this dialog box includes the name, date and the start-
                          ing time of the historical data collection.
                          The appropriate chart window(s) displays, and data from the selected histori-
                          cal data collection is played back. Notice that the title bar of a chart window dis-
                          played during playback includes the chart name, indicates that a playback
                          session is in progress, and lists the name of the historical data collection being
                          played back. It also includes the name of the database being monitored during
                          the data recording session, even if that database is different from the database
                          currently being monitored.
                          Playback occurs in realtime, meaning that a data collection session of 10 minute
                          duration would take 10 minutes to play back. However, you can speed up (or
                          slow down) the playback of an historical data collection by adjusting the Inter-
                          val sec and min combo boxes during playback. For example, if the polling inter-
                          val was 10 seconds for a data collection session of 10 minutes duration, you
                          could halve the playback time to 5 minutes by adjusting the polling interval to
                          5 seconds during playback.
                          When the playback session is finished, the text, ”Playback Complete” displays
                          in the title bar of the chart window.
                     4.   At any time during the playback session, you can terminate the playback ses-
                          sion by closing the chart window.

Formatting with the Standalone Executable
                     If the Oracle Trace formatter tables were validated (upgraded) during product
                     installation, but the errors still appeared in the Formatter Messages box of the For-
                     mat/Playback Login dialog box, you may want to run the standalone formatting
                     executable. Running \orant\bin\otrcfmtw.exe in a Windows environment displays
                     the Oracle Trace Data Formatter dialog box. It contains the following elements.

                     Collection Data File
                     Enter the name of the data collection file you want to format for playback.

                     Username
                     Enter the username for logging into the trace repository.

                     Password
                     Enter the password for this username.

                     Connect String
                     Enter the database containing the trace repository.




7-6   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                  Working with Historical Data



               Full
               Check this box to format all of the data in the data collection file. It is recom-
               mended that you do so.
               Commit Interval
               Enter how often you want to flush data from buffers to the database.

               Delete CDF/Data Files
               Check this box to delete the formatting files after the data collection has been for-
               matted.

               Format
               Click this button to format the data collection.

Deleting an Historical Data Collection
               To delete a named historical data collection, perform the following steps:
               1.     Choose Delete Recorded Data from the Record menu.
               2.     In the resulting Format/Playback Login dialog box, enter the connect string for
                      the historical data collection repository to which you saved the data collection
                      you now want to play back.
               3.     In the resulting Select a Collection to Delete dialog box, select the historical
                      data collection you want to delete, and then click OK.




                                                       Working with Oracle Performance Manager Data       7-7
Working with Historical Data




7-8   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                        Part II
    Additional Performance Monitoring
                         Applications

This part describes three additional performance monitoring applications of Oracle
Performance Manager. This part includes the following chapters:
s    Chapter 8, Using Oracle Lock Manager
s    Chapter 9, Using Oracle TopSessions
s    Chapter 10, Using Oracle Tablespace Manager
Beta Draft
                                                                                            8
                               Using Oracle Lock Manager

           This chapter describes how to use Oracle Lock Manager to monitor all current locks
           in the database, and identify those sessions blocking access by other waiting ses-
           sions. The topics included in this chapter are:
           s   Before You Start Oracle Lock Manager
           s   Starting Oracle Lock Manager
           s   Oracle Lock Manager Main Window
           s   Identifying Waiting Sessions
           For more information on the tasks Oracle Lock Manager performs, see the Oracle
           Server Administrator’s Guide. For information about standard elements of your oper-
           ating system, see your operating system-specific documentation.

                   Attention: While Oracle Lock Manager allows you to obtain an
                   overview of all locks for all current sessions of the database, Oracle
                   TopSessions allows you to view the same information for a given
                   session, and also allows you to kill a problem blocking session. For
                   more information, see “Using Oracle TopSessions” on page 9-1.



Before You Start Oracle Lock Manager
           Before using Oracle Lock Manager, it is best to ensure that the following views exist
           on the database instance:
               –   DBA_OBJECTS
               –   V$LOCK
               –   V$SESSION




                                                                  Using Oracle Lock Manager   8-1
Starting Oracle Lock Manager



                   These tables are essential; Oracle Lock Manager cannot run without them.
                   If V$SESSION, 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.


Starting Oracle Lock Manager
                   To start Oracle Lock Manager, select a database object from the Navigator tree list.
                   Then click on the Oracle Lock Manager icon in the Performance Pack Launch Pal-
                   ette, or choose Performance Pack and then Oracle Lock Manager from the Tools
                   menu.

                               Attention: In order to perform all Oracle Lock Manager tasks,
                               you must have SYSDBA privileges.


                   s    If you have already connected to a database, the Oracle Lock Manager main
                        window displays.
                   s    If a valid database connection has not been made, the Connect dialog box dis-
                        plays. This dialog box also appears when you launch Oracle Lock Manager by
                        itself.
                   After Oracle Lock Manager has successfully connected to a database, the Oracle
                   Lock Manager main window displays.


Oracle Lock Manager Main Window
                   Figure 8–1 illustrates the Oracle Lock Manager main window.




8-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                 Oracle Lock Manager Main Window



            Figure 8–1      Oracle Lock Manager Main Window




            As Figure 8–1 shows, the Oracle Lock Manager main window includes the follow-
            ing components:
            s   Title bar
            s   Menu bar
            s   Toolbar
            s   Main display
            s   Status bar
            The following sections describe each of these components.

Title Bar
            The title bar of the Oracle Lock Manager main window displays the name of the
            application and the database instance to which an Oracle Lock Manager connection
            has been established.

Toolbar
            The toolbar of the Oracle Lock Manager main window contains icons representing
            certain Oracle Lock Manager menu items, including the following: Change Data-
            base Connection, Refresh, and Help. The toolbar also includes the following combo



                                                                Using Oracle Lock Manager   8-3
Oracle Lock Manager Main Window



                   box that allows you to display all locks of the database instance (default), or only
                   blocking and waiting sessions.

Status Bar
                   The status bar of the Oracle Lock Manager main window can include the following
                   information:

                   Left section
                   When a menu item or tool has been selected, its explanation appears in this section
                   of the status bar.

                   Right section
                   Displays the time of the last data refresh of the Oracle Locks Manager main display.

Menu Bar
                   The menu bar of the Oracle Lock Manager main window includes the following
                   pull-down menus:
                   s      File
                   s      View
                   s      Locks
                   s      Help

                   File Menu
                   The File menu items allow you to create new database connections and exit Oracle
                   Lock Manager.
                   The File menu includes the following commands:

                   Change Database Connection
                   Displays the Login Information dialog box, enabling you to connect to a database
                   instance of your choice. For more information about the Login Information dialog
                   box, see “Starting in Standalone Mode” on page 1-4.

                   Exit
                   Exits Oracle Lock Manager.

                   View Menu
                   The View menu items allow you to show/hide the toolbar or status bar.




8-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                     Oracle Lock Manager Main Window



               The View menu includes the following commands:

               Toolbar
               Allows you to show/hide the toolbar.

               Status Bar
               Allows you to show/hide the statusbar.

Locks Menu
               The Locks menu includes the following item:

               Refresh
               Refreshes the data displayed in the multi-column list of the Oracle Lock Manager
               main window.

               Kill Session
               Kills the session selected in the Oracle Lock Manager main window.

               Help Menu
               The Help menu items allow you to obtain help on Oracle Lock Manager 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 Lock Manager
               Displays the version number of this application.

Main Display
               The Oracle Lock Manager main window displays a multi-column list that includes
               a row for each current lock in the database. This information is obtained from
               SYS.DBA_OBJECTS, V$LOCK and V$SESSION. You can resize any of these col-
               umns by positioning the cursor over the right border of a column header. When the
               cursor becomes a cross, click the left mouse button and drag.




                                                                    Using Oracle Lock Manager     8-5
Oracle Lock Manager Main Window



                   The columns of the list are:

                   User Name
                   Oracle user name using this session.

                   Session ID
                   Oracle session ID for this 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); 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); Exclusive.

                   Object Name
                   Name of the object, such as table or view, that is 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 seg-
                   ment.

                   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.

                   Resource ID 1
                   For certain types of locks, this value is the object ID.

                   Resource ID 2
                   Undocumented.




8-6   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                         Identifying Waiting Sessions




Identifying Waiting Sessions
            To identify waiting sessions whose access to the database is blocked by a blocking
            session, double-click on an icon in the User Name column. (If no icons appear in
            the User Name column, no sessions are currently blocking access by other sessions
            to the database.) The entry expands to show the waiting session(s) being blocked
            by the blocking session. For example, in Figure 8–1, SYS is a blocking session block-
            ing two users, SYSTEM and SCOTT, from accessing the database.

                    Attention: To kill a blocking session in order to enable waiting ses-
                    sions to access the database, use Oracle TopSessions. For more
                    information, see “Oracle TopSessions Menu Bar” on page 9-5.




                                                                  Using Oracle Lock Manager      8-7
Identifying Waiting Sessions




8-8   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                           9
                                 Using Oracle TopSessions

           This chapter covers how you use Oracle TopSessions, including the following top-
           ics:
           s   Before You Start Oracle TopSessions
           s   Starting Oracle TopSessions
           s   Oracle TopSessions Main Window
           s   Obtaining an Overview of Session Activity
           s   Viewing Details About a Given Session
           s   Customizing the Session Information You Display
           s   Exiting Oracle TopSessions


Before You Start Oracle TopSessions
           Before using Oracle TopSessions, it is best to ensure that the following views exist
           on the database instance:
           s   V$SESSION
           s   V$STATNAME
           s   V$SESSTAT
           s   ALL_TAB_COLUMNS
           s   V$OPEN_CURSOR
           s   V$SQLTEXT
           s   V$LOCK
           s   SYS.DBA_OBJECTS



                                                                   Using Oracle TopSessions   9-1
Starting Oracle TopSessions



                    s   V$ROLLNAME
                    s   PLAN_TABLE
                    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 TopSes-
                    sions; however, you can still run Oracle TopSessions without them.
                    The smptsixx.sql scripts have been provided by Oracle Enterprise Manager to cre-
                    ate 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 data-
                    base, 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 data-
                    base 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.


Starting Oracle TopSessions
                    You start Oracle TopSessions as you would any typical Oracle Enterprise Manager
                    application, as described in “Starting Performance Monitoring Applications” on
                    page 1-4.

                              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.




9-2   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                    Oracle TopSessions Main Window



          Figure 9–1   Oracle TopSessions Main Window




          If you want the top n sessions in the main display to be sorted by the default statis-
          tic Memory (CPU used by this session), click OK. Otherwise, change the sort statis-
          tic 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” on
          page 9-16.

                  Note: Whatever options were selected when you last exited Ora-
                  cle TopSessions are automatically loaded the next time you start
                  Oracle TopSessions. These options are saved in
                  ORACLE_HOME\SYSMAN\TEMP\TOPSESS.OPT.


          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.


Oracle TopSessions Main Window
          As Figure 9–1 shows, the Oracle TopSessions main window includes the following
          components:




                                                                   Using Oracle TopSessions   9-3
Oracle TopSessions Main Window



                   s   Title bar
                   s   Menu bar
                   s   Toolbar
                   s   Main display
                   s   Status bar
                   The following sections describe each of these components.

Title Bar
                   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.

Toolbar
                   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 connec-
                   tion 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




9-4   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                       Oracle TopSessions Main Window



             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:
             s   File
             s   View
             s   Sessions
             s   Help

             File Menu
             The File menu items allow you to create new database connections and exit Oracle
             TopSessions.
             The File menu includes the following commands:




                                                                      Using Oracle TopSessions    9-5
Oracle TopSessions Main Window



                   Change Database Connection
                   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” on page 1-4.

                   Exit
                   Exits Oracle TopSessions. See “Exiting Oracle TopSessions” on page 9-19 for more
                   information.

                   View Menu
                   The View menu items allow you to show/hide the toolbar or statusbar.
                   The View menu includes the following commands:

                   Toolbar
                   Allows you to show/hide the toolbar.

                   Status Bar
                   Allows you to show/hide the statusbar.

                   Sessions Menu
                   The Sessions menu items allow you to specify what session statistics will be dis-
                   played in the Oracle TopSessions main display, and how that information will be
                   refreshed. For more information on these menu items, see “Customizing the Ses-
                   sion 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 Ora-
                   cle 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.
                   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.




9-6   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                Obtaining an Overview of Session Activity



            Options
            Displays the Options property sheet, from which you can specify the sorting crite-
            rion, 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 ses-
            sions 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.

            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.




                                                                      Using Oracle TopSessions       9-7
Obtaining an Overview of Session Activity



                    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 about defining top n sessions, see “Customizing the Session Infor-
                    mation You Display” on page 9-16.
                    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.

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-9.

                    Kill
                    Kills the selected session. This menu item works like the Kill menu item of the Ses-
                    sion 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 dis-
                    played session entries, by clicking on any column header in the list.




9-8   Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                     Viewing Details About a Given Session




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 win-
              dow for that session appears. This section covers the following topics:
              s     Session Details pages
              s     Session Details data refresh time
              s     Viewing session details for multiple sessions simultaneously

Session Details Pages
              The Session Details window includes the following pages:
              s     General
              s     Statistics
              s     Cursors
              s     Locks
              To select one of these pages, click on the appropriate tab title near the top of the Ses-
              sion Details window.

Session Details General Page
              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 fol-
              lowing:

              SADDR
              Session address of this session.

              SID
              Oracle session ID of this session.




                                                                        Using Oracle TopSessions      9-9
Viewing Details About a Given Session



                    SERIAL#
                    Serial number of this session. Together with the SID, this number provides a
                    unique key for this session.

                    AUDSID
                    Auditing session ID.

                    PADDR
                    Address of the Oracle process using this session.

                    USER#
                    ID of the user using this session.

                    USERNAME
                    User name of the user using this session.

                    COMMAND
                    Last Oracle command executed.

                    TADDR
                    Address of the transaction state object.

                    LOCKWAIT
                    Address of lock waiting for; null if none.

                    STATUS
                    Status of this session: ACTIVE, IDLE, KILLED, or BLOCKED.

                    SERVER
                    Server type: DEDICATED, SHARED, PSEUDO, or NONE.

                    SCHEMA#
                    Schema user identifier.

                    SCHEMANAME
                    Schema user name.

                    OSUSER
                    Operating system user name.

                    PROCESS
                    Process ID.




9-10 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                    Viewing Details About a Given Session



MACHINE
Identifier of the machine using this session.

TERMINAL
Operating system terminal name.

PROGRAM
Name of the client program using this session.

TYPE
Session type.

SQL_ADDRESS
Together withthe SQL hash value, identifies the SQL statement that is currently
being executed.

SQL HASH VALUE
Together with the SQL address, identifies the SQL statement that is currently being
executed.

PREV_SQL_ADDR
Together with the previous SQL hash value, identifies the SQL statement that exe-
cuted previous to the SQL statement currently executing.

PREV_HASH_VALUE
Together with the previous SQL address, identifies the SQL statement that executed
previous to the SQL statement currently executing.

MODULE
Name of the currently executing module as set by calling the procedure named
DBMS_APPLICATION_INFO.SET_MODULE.

MODULE_HASH
The hash value of the above module name.

ACTION
Name of the currently executing action as set by calling the procedure named
DBMS_APPLICATION_INFO.SET_ACTION.

ACTION_HASH
The hash value of the above action name.




                                                      Using Oracle TopSessions      9-11
Viewing Details About a Given Session



                    CLIENT_INFO
                    Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO proce-
                    dure.

                    FIXED_TABLE_SEQUENCE
                    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.

                    ROW_WAIT_OBJ#
                    Object ID for the table containing the row ID specified in ROW_WAIT_ROW#.

                    ROW_WAIT_FILE#
                    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.

                    ROW_WAIT_BLOCK#
                    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.

                    ROW_WAIT_ROW#
                    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_TIME
                    Logon timestamp for the session.

                    LAST_CALL_ET
                    Last call made during the session.

Session Details Statistics Page
                    The Statistics page of the Session Details window provides a large number of per-
                    formance statistics for the selected session. The particular statistics this page dis-
                    plays 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.




9-12 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                    Viewing Details About a Given Session



Session Details Cursors Page
              The Cursors page of the Session Details window provides information about each
              shared cursor in the shared SQL area for the selected session.

                      Attention: In order to view explain plans for SQL statements
                      accessing V$ views, you must log in as SYS. In addition, you must
                      have privileges to access the objects in the SQL statement for which
                      you want to view an explain plan.


              The Cursors page contains the following:

              Show Cursors
              Click on an item in this list box to display SQL statement(s) for the selected user ses-
              sion. 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.

              Show Explain Plan
              Click on this button to display the explain plan for the SQL statement in the Cur-
              sors page display box which you have selected. For more information, see “Display-
              ing an Explain Plan for a User Session” on page 9-14.

              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 exe-
              cuting 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.




                                                                      Using Oracle TopSessions      9-13
Viewing Details About a Given Session



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

                    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.
                    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 statis-
                    tics, see Oracle Server Tuning.
                    The Locks page includes the following list box:

                    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.
                    The Locks page contains the following fields:

                    User Name
                    Oracle user name using the session.

                    Session ID
                    Oracle session ID for the session.




9-14 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                       Viewing Details About a Given 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 seg-
ment.

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.




                                                         Using Oracle TopSessions      9-15
Customizing the Session Information You Display



                    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 Ses-
                    sion 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.

Viewing Session Details for Multiple Sessions
                    You can display, minimize (iconify), and maximize Session Details displays for mul-
                    tiple 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:
                    s    Sort




9-16 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                            Customizing the Session Information You Display



              s     Refresh
              s     Count
              To select one of these pages, click on the appropriate tab title near the top of the
              Options property sheet.

                       Note: Whatever options are selected when you exit Oracle
                       TopSessions are automatically loaded the next time you start Ora-
                       cle TopSessions. These options are saved in oracle_home\sys-
                       man\temp\topsess.opt.


Options Sort Page
              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:

              Statistic Filter
              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).

              Sort Statistic
              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.

              Display Value
              Current: Click on this button to specify that statistics displayed in the Oracle TopSes-
              sions main window be based on the most recent data refresh.



                                                                        Using Oracle TopSessions      9-17
Customizing the Session Information You Display



                    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.

Options Refresh Page
                    Use the Refresh page of the Options property sheet to specify how you want ses-
                    sion 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:

                    Manual
                    Click on this button (default) to enable manual refreshing of the session informa-
                    tion 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 informa-
                    tion when you so choose.

                    Automatic
                    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.

                    Refresh Interval
                    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.

                             Attention: The refresh interval is the time interval measured from
                             the beginning of a refresh to the beginning of the refresh following
                             it. Thus, a database instance with perhaps 1,000 sessions logged on,
                             and being 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.




9-18 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                             Exiting Oracle TopSessions



Options Count Page
             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:

             Display All Sessions
             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 recom-
             mends that you use the default option Display Top N Sessions.

             Display Top N Sessions
             Click on this button (default) to display the top n sessions that meet the sort statis-
             tic 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.


Exiting Oracle TopSessions
             To exit Oracle TopSessions, choose Exit from the File menu. A dialog box is dis-
             played, confirming that you want to close your Oracle TopSessions session.




                                                                      Using Oracle TopSessions    9-19
Exiting Oracle TopSessions




9-20 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                    10
               Using Oracle Tablespace Manager

           This chapter covers how you use Oracle Tablespace Manager, including the
           following topics:
           s   Starting Oracle Tablespace Manager
           s   Oracle Tablespace Manager Main Window
           s   Obtaining an Overview of Tablespace Storage
           s   Obtaining an Overview of Datafiles in a Tablespace
           s   Monitoring Extents and Segments of a Tablespace
           s   Reorganizing Objects in a Tablespace
           s   Deallocating Unused Space
           s   Analyzing a Tablespace
           s   Joining Adjacent Free Extents in the Database


Starting Oracle Tablespace Manager
           You start Oracle Tablespace Manager as you would start any typical application
           integrated with Oracle Enterprise Manager. See “Starting Performance Monitoring
           Applications” for a description of how to do so.


Oracle Tablespace Manager Main Window
           After you start Oracle Tablespace Manager, the main window is displayed.
           Figure 10–1 shows the Oracle Tablespace Manager main window.




                                                         Using Oracle Tablespace Manager   10-1
Oracle Tablespace Manager Main Window



                  Figure 10–1 Oracle Tablespace Manager Main Window




                  The Oracle Tablespace Manager main window includes the following components:
                  s   Title bar
                  s   Toolbar
                  s   Status bar
                  s   Menu bar
                  s   Main window

Title Bar
                  The title bar of the Oracle Tablespace Manager main window displays the name of
                  the application and the name of the database instance to which a connection has
                  been made.




10-2 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                 Oracle Tablespace Manager Main Window



Toolbar
               The Oracle Tablespace Manager toolbar includes icons that enable you to perform
               the following menu commands: Change Database, Refresh, Filter Folder, Analyze
               Objects, Deallocate Used Space, Reorganize Objects, Coalesce, and Help.

Status Bar
               The Oracle Tablespace Manager status bar displays information about the current
               operation on the left.

Main Display
               As Figure 10–1 shows, the Oracle Tablespace Manager main window includes a
               tree list in the left pane of the window. The Oracle Tablespace Manager tree list
               operates like the Oracle Enterprise Manager tree list, except that it only provides
               information about the tablespaces of the database instance.
               When you start Oracle Tablespace Manager and the main window appears, the top
               container in the tree list shows the database instance being monitored. The
               Tablespaces container containing the tablespaces of that instance is also displayed.
               From the Tablespaces container you can drill down as follows:
               s


               s   Single-clicking on the Tablespaces container in the left pane displays a
                   multi-column list in the right pane that shows space usage information for each
                   tablespace in the instance.
               s   Double-clicking on the Tablespaces container displays containers representing
                   each individual tablespace in the instance.
                   –   Single-clicking on a tablespace object displays the Segments and Extents
                       Information page for that tablespace.
                   –   Double-clicking on a tablespace object either expands or collapses the
                       object’s list of datafiles and also displays the object’s Segments and Extents
                       Information page.
                   –   Single-clicking on a datafiles object in the left pane displays a multi-column
                       list in the right pane that shows space usage information for each datafile
                       in the tablespace.
                   –   Single-clicking on an individual datafile displays the Segments and Extents
                       Information page for that datafile.




                                                               Using Oracle Tablespace Manager    10-3
Oracle Tablespace Manager Main Window



                  For more information on how a tree list is populated, see ”Navigator” in the Oracle
                  Enterprise Manager Administrator’s Guide.

Menu Bar
                  The Oracle Tablespace Manager menu bar includes the following menus:
                  s      File
                  s      View
                  s      Tools
                  s      Help

File Menu
                  The File menu items allow you to change the database connection and exit the
                  Oracle Tablespace Manager application.
                  The File menu includes the following menu items:

                  Change Database Connection
                  Allows you to connect to another database instance.

                  Enable Roles
                  Displays the Enable Roles dialog box, from which you can select roles to enable.
                  For more information, see ”Overview of Database Tools” in the Oracle Enterprise
                  Manager Administrator’s Guide.

                  Exit
                  Exits the Oracle Tablespace Manager application.

View Menu
                  The View menu items allow you to change what is displayed in the window. For
                  more information, see ”Overview of Database Tools” in the Oracle Enterprise
                  Manager Administrator’s Guide.
                  The View menu includes the following menu items:

                  Refresh
                  Refreshes the data displayed by Oracle Tablespace Manager.

                  Filter Folder
                  Allows you to specify filters for displaying tree list objects.




10-4 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                               Oracle Tablespace Manager Main Window



             Expand One Level
             Expands the selected container in the Oracle Tablespace Manager tree list by one
             level of detail.

             Collapse Branch
             Hides the level(s) of detail below the selected container in the Oracle Tablespace
             Manager tree list.

             Collapse All
             Hides all levels of detail below the database container in the Oracle Tablespace
             Manager tree list.

             Toolbar
             Shows/hides the toolbar.

             Status Bar
             Shows/hides the status bar.

Tools Menu
             The Tools menu includes the following menu items:

             Analyze Objects
             Starts the Tablespace Analyzer tool. This tool submits a job which either computes
             or estimates statistics for selected tables, indexes, and clusters.

             Deallocate Unused Space
             Starts the Tablespace Deallocation tool. This tool submits a job which defragments
             schema objects using storage parameter defaults and basic analysis options. You
             can specify when the job will run.

             Reorganize Objects
             Starts the Tablespace Reorganization tool. This tool submits a job which
             defragments schema objects and lets you set storage parameters, analysis options,
             and scheduling options. You can specify when the job will run.

             Coalesce Free Extents
             Joins adjacent free extents in the database.

Help Menu
             The Help menu includes the following menu items:




                                                            Using Oracle Tablespace Manager       10-5
Obtaining an Overview of Tablespace Storage



                   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 Tablespace Manager
                   Displays version information for this release of Oracle Tablespace Manager.


Obtaining an Overview of Tablespace Storage
                   To obtain an overview of the tablespaces in the database instance, single-click on
                   the Tablespaces container in the left pane. The right pane of the main window
                   displays a multi-column list displaying storage information for each tablespace of
                   the database instance. This list includes the following information:

                   Name
                   Tablespace name.

                   Datafiles
                   Number of datafiles in the tablespace.

                   Total Blocks
                   Total number of blocks in the tablespace.

                   Free Blocks
                   Number of free blocks in the tablespace.

                   % Free
                   Percentage of total number of blocks in the tablespace that are free blocks. A
                   horizontal bar in the background of this field graphically represents the percentage
                   of free blocks in the tablespace.

                   # Adj. Free Extents
                   Number of adjacent free extents in the tablespace.


Obtaining an Overview of Datafiles in a Tablespace
                   To obtain an overview of the datafiles in a given tablespace, from the Oracle
                   Tablespace Manager tree list, single-click on the Datafiles container of the




10-6 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                       Monitoring Extents and Segments of a Tablespace



           tablespace of interest to you. The right pane of the main window displays a
           multi-column list including information for each datafile of this particular
           tablespace. This table includes a subset of the fields described in the previous
           section, as follows:

           Name
           Datafile name, including its directory path.

           Total Blocks
           Total number of blocks in the datafile.

           Free Blocks
           Number of free blocks in the datafile.

           % Free
           Percentage of total number of blocks in the datafile that are free blocks. A
           horizontal bar in the background of this field graphically represents the percentage
           of free blocks in the datafile.


Monitoring Extents and Segments of a Tablespace
           To monitor the segments of a given tablespace, in the tree list, single-click on the
           tablespace of interest. Figure 10–2 shows an example of the Segments and Extents
           Information page that displays.




                                                           Using Oracle Tablespace Manager       10-7
Monitoring Extents and Segments of a Tablespace



                   Figure 10–2 Example of the Oracle Tablespace Manager Segments Page




Segments and Extents Information Property Sheet
                   The Segments and Extents Information property sheet includes the following
                   elements:

                   Selected Extent
                   Lists the information associated with the extent of the segment selected in the
                   Segment Multi-column list. Extent information includes the following elements:

                   # Blocks
                   Number of blocks in the extent.

                   Block ID
                   ID of the first block in the extent.




10-8 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                            Monitoring Extents and Segments of a Tablespace



Extent ID
ID of the extent.

Datafile
Full name and path of the datafile; the physical location of the extent.

Segment Multi-column List
Lists the segments of the tablespace (or datafile) selected in the tree list. When the
Segments page first displays, the first segment in the list is automatically selected.
If no segments have been allocated for the selected tablespace, the multi-column
list is blank.
Click on a segment listed in this list to graphically highlight how the extents in the
selected tablespace (or datafile) are allocated for this segment. Click on the desired
column header to sort the list accordingly. The multi-column list consists of the
following columns.

Segment Name
Name of the segment name. Segments are sorted alphabetically by segment name.

Owner
Name of the segment owner. Segments are first sorted alphabetically by segment
owner, then alphabetically by segment name.

Type
Segment type.Options include: table, index, cluster, table partition, index partition,
rollback, cache, LOB (large object) segment, and LOB index. Segments are sorted
alphabetically by segment type, then alphabetically by segment name.

# Extents
Number of extents within the segment. Segments ar sorted first by the number of
extents in the segment, then alphabetically by segment name (default).

# Blocks
Number of blocks in the extent. Segments are sorted according to the number of
blocks in the extent (lowest to highest).

Allocation Graphic
Graphically displays space allocation for the selected tablespace (or datafile),
showing how space for its segments and extents have been allocated.
White areas denote free space. Colored areas denote used space, as follows:
s   Yellow indicates extents allocated to the segment that is selected in the left pane.




                                                 Using Oracle Tablespace Manager      10-9
Monitoring Extents and Segments of a Tablespace



                   s    Yellow outlined with a dashed line indicates the extent with the highest block
                        ID.
                   s    Cyan (light blue) indicates extents that are allocated to other (nonselected)
                        segments in the tablespace (or datafile).
                   s    Grey indicates Header blocks.
                   Black vertical lines separate extents in the space allocation graphic. If you see large
                   black sections, it is because there are so many extents in the segment that the colors
                   representing the extents themselves are not visible. To minimize this problem,
                   maximize the size of the Oracle Tablespace Manager main window.
                   Extent Legend
                   Clicking on the property sheet button Extent Legend displays a quick reference
                   pop-up defining significance of the different colors used in the Allocation Graphic.
                   s    Allocated Extent
                        Allocated extents for all segments not selected in the tree list are displayed in
                        cyan (light blue).
                   s    Selected Segment
                        Allocated extents of the selected segment in the tree list are displayed in yellow.
                   s    Free Space
                        Extents available in the tablespace that have not been assigned to any segment
                        are displayed in white. These extents are available for allocation next time a
                        segment needs to be extended.
                   s    Header Block
                        First blocks of a file are used as a header and are grey.
                   To view the segment related to an extent, click on an entry in the Segment
                   multi-column list. All of the extents comprising the segment associated with the
                   extent you just clicked are then highlighted in yellow, and the associated segment is
                   then automatically selected in the Left pane. In this case, a dashed line outlines the
                   extent you selected in the Right pane.

                   Segment Information
                    If you select more than one segment from the Segments multi-column list, the
                   Multiple Object Selection Statistics page appears. This page is only available when
                   you select more than one segment in the segment list. It shows the owner of the
                   selected segments if they belong to the same owner or <Multiple Owners> if they




10-10 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                 Reorganizing Objects in a Tablespace



           belong to more than one owner. A table similar to the following is displayed that
           contains information about the segments.
           .

           Table 10–1 Multiple Segments Information
           Segment Type             Selected        Blocks           Kilobytes
           Tables
           Indexes
           Clusters
           Table Partitions (for
           Oracle8 databases)
           Index Partitions (for
           Oracle8 databases)
           Rollbacks
           Cache
           LOB Segments (Large
           Objects)
           LOB Indexes (Large
           Objects
           Total


Reorganizing Objects in a Tablespace
           To reorganize objects in a tablespace, select the desired segment(s) from the
           Segments multi-column list on the Segments and Extents Information page.

           IMPORTANT: Because Oracle Tablespace Manager uses the Oracle Enterprise
           Manager job subsystem to perform table segment defragmentation, the Oracle
           Enterprise Manager Console must be running in order to complete the
           defragmentation process.
           Select the Reorganize Objects option from the Tools menu. This option is disabled if
           the type of any of the selected segments is anything other than Table or if any of the
           segment owners is SYS.
           The Tablespace Reorganizer wizard prompts you as shown in the following
           sections.




                                                         Using Oracle Tablespace Manager       10-11
Reorganizing Objects in a Tablespace



                    Defining Jobname and Type of Organization

                    Jobname
                    Use any character for the jobname except apostrophe and ampersand. For ease of
                    use, keep the jobname short and simple.

                    Type of Organization
                    You can choose either to reorganize data by defragmenting tables and clusters or to
                    deallocate unused space at the end of segments. If you choose to deallocate unused
                    space, you can specify a default size to keep, in either kilobytes or megabytes.
                    For more information about reorganizing data, see “Selecting Objects to
                    Reorganize”

                    Selecting Objects to Reorganize
                    From the tree list on the Object Selection page, click on the checkbox for each object
                    that you want to reorganize. The selection status of each object is denoted as
                    follows:
                    s   A check mark in a white box indicates that the object and its children will be
                        reorganized.
                    s   A check mark in a grey box indicates that the object itself is not selected for
                        reorganization but one or more of its children is.
                    s   A check mark in a grey box next to either a folder or schema indicates that
                        some, but not all of its object are selected for reorganization.
                    To specify parameters, select an object and move on to the next page of the wizard.
                    You can then customize the following parameters to suit your needs:

                    Selected Objects with Properties Page
                    This page allows you to set specific options on the objects you already selected in
                    the previous step.

                    Tablespace
                    Name of the tablespace containing the segment you want to defragment.

                    Extent Parameters
                    s   Compress
                        Click this button (default) if you want to defragment the selected segment by
                        compressing it into a single extent.




10-12 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                       Reorganizing Objects in a Tablespace



s   Initial Size
    Size of the first extent of the table segment. Click either the K (Kilobytes) or M
    (Megabytes) button to specify the size of the measurement.
s   Next Size
    Size of the next extent to be allocated to the segment. The default value is the
    size of five data blocks. The smallest permissible value is the size of one data
    block. Click either the K (kilobytes) or M (megabytes) button to specify the size
    of the measurement.
s   Increase Size By
    Percent by which each extent grows (after the second extent) compared to the
    previous extent.
s   Minimum Number
    Total number of extents allocated when a segment is created. The default value
    is 1. You can enter a value of 1 or greater.
s   Maximum
    Total number of extents, including the first, that Oracle can allocate for the
    segment. You can enter a value of 1 or greater. The default value varies,
    depending on the database block size.
s   Unlimited
    Enabling this option disables the user-specified Maximum field and instead
    allows the maximum number of extents the system permits. For example, on
    many systems, the number is approximately 2.1 billion (2,147,483,645). The
    exact value varies depending on the platform. However, it is not recommended
    having a segment which uses unlimited extents. Rogue transactions containing
    inserts, updates, or deletes that continue for a long time will continue to create
    new extents until a disk is full.

Space Usage
s   % Free
    Percentage of space in each of the data blocks of the segment that is reserved
    for future updates to the segment. You can enter a value from 0 to 99.
s   % Used
    Minimum percentage of used space that an Oracle database maintains for each
    data block of the table segment. A block becomes a candidate for row insertions




                                               Using Oracle Tablespace Manager       10-13
Reorganizing Objects in a Tablespace



                        when its used space falls below the % Used value. You can enter a value from 1
                        to 99. The default value is 40.

                    Free Lists
                    s   Lists
                        Number of free lists for each of the free list groups for the table, cluster, or
                        index. You can enter a value of 1 or more. The default value is 0.
                    s   Groups
                        Number of groups of free lists for a table, cluster, or index. You can enter a
                        value of 1 or more. The default value is 1.

                    Number of Transactions
                    s   Initial
                        Initial number of transaction entries allocated within each data block allocated
                        to the table segment. You can enter a value from 1 to 255. The default is 1 for
                        tables. For clusters and indexes, the minimum and default value is 2.
                    s   Maximum
                        Maximum number of concurrent transactions that can update a data block
                        allocated to the segment. You can enter a value from 1 to 255.

                    Load Default
                        Click on this button to assign the default storage parameters to the selected
                        object(s).

                    Defining Tablespace Reorganization Options
                    Use the Other Options page to specify the general options.

                    Retain temporary export dump files
                    Enable this option to save export and temporary files after the defragmentation job
                    has executed. You can keep the constraint and object files as an audit trail for
                    auditing purposes or reuse the export file to copy the data or save as a backup.




10-14 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                       Reorganizing Objects in a Tablespace



Default Directory
You can enter the directory path on the server in which the export file and
temporary files (object and constraints files) are to be written. This directory path
cannot contain any environment variables.

        Attention: If you do not specify a directory path, these files will
        be written to the directory in which the reorganization job script is
        run. Be sure the Oracle intelligent agent has permission to write to
        the directory for these files or the reorganization job will fail.


Defining Scheduling Options
The Scheduling Options page allows you to schedule the execution of the
reorganization.

Execute
Select the frequency with which you want the job executed. The choices are:
s   Immediately
    Schedules the job as soon as you click the Submit button on the Summary page.
    The job executes only one time.
s   Once
    Schedules the job to occur once at the date and time you choose.
s   Interval
    Allows you to schedule a specific time interval between job executions. The
    interval can be a combination of hours and minutes, or number of days. Select
    the value you want to change in the Time field and click on the scroll buttons.
    You can also type in a new value.
s   On Day of Week
    Allows you to schedule the job on one or multiple days (Sunday, Monday, etc.)
    of the week. Click on the days of the week in the Date field to select the days on
    which you want the job to occur.
s   On Day of Month
    Allows you to schedule the job on one or multiple days (1-31) of the month.
    Click on the dates of the month in the Date field to select the dates on which
    you want the job to occur.




                                               Using Oracle Tablespace Manager       10-15
Reorganizing Objects in a Tablespace



                    Start Execution
                    Choose the first date and time that you want the job executed. This is the starting
                    time for any job scheduled on an interval.
                    s   Date
                        Select the month, day, or year in the Date field and click on the scroll buttons to
                        change the value. You can also type in new values.
                    s   Time
                        Select the hour, minute, or AM/PM in the Time field and click on the scroll
                        buttons to change the value. You can also type in new values.

                    End Execution
                    Choose the last date and time that you want the job executed. This option does not
                    apply if you chose the Immediately or Once execution options.
                    s   Date
                        Select the month, day, or year in the Date field and click on the scroll buttons to
                        change the value. You can also type in new values.
                    s   Time
                        Select the hour, minute, or AM/PM in the Time field and click on the scroll
                        buttons to change the value. You can also type in new values.

                    Time Zone
                    Select the time zone from the pull-down list. The choices are:
                    s   Agent
                        The agent schedules the job execution at each destination based on the actual
                        system time of each agent. Jobs are not necessarily run simultaneously.
                    s   Console
                        The agent schedules the job execution simultaneously on all destinations based
                        on the system time of the console.
                    s   GMT
                        The agent schedules the job execution simultaneously on all destinations based
                        on Greenwich mean time.




10-16 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                          Deallocating Unused Space



           Viewing the Tablespace Reorganization Summary Page
           The Tablespace Organizer Summary page summarizes all the information you
           entered while using the Tablespace Organizer tool.
           The summarized properties are:
           s   General
           s   Objects to Reorganize
           s   Options
           s   Scheduling
           If, while reviewing the Summary page, you find options you want to change, click
           the Back button to make the necessary changes.
           Click the Cancel button at any time to close the Tablespace Reorganization wizard
           without reorganizing the selected segment(s).
           Once you are satisfied with the information, click the Finish button to start the
           reorganization process.
           Click the Help button to display Help for the Tablespace Reorganization wizard.


Deallocating Unused Space
           To deallocate unused space within the tablespace, select the desired segment(s) in
           the Segments multi-column list located on the Segments and Extents Information
           property sheet.
           Start the Tablespace Deallocation wizard by selecting the Deallocate Unused Space
           option in the Tools menu. The wizard guides you through the space deallocation
           job definition process, job scheduling, and finally provides a job summary so that
           you can check the job settings before submission to the Oracle Enterprise Manager
           job system.Defining Job Name and Scheduling Choices.

           Default Options
           You specify the default value for the amount of unused space to be reserved in each
           segment. Space can be specified in units of megabytes or kilobytes.

           Selecting Objects to Deallocate Space
           From the tree list on the Object Selection page, click on the checkbox for each object
           that you want to reorganize. The selection status of each object is denoted as
           follows:




                                                          Using Oracle Tablespace Manager      10-17
Deallocating Unused Space



                   s   A check mark in a white box indicates that the object and its children will be
                       reorganized.
                   s   A check mark in a grey box indicates that the object itself is not selected for
                       reorganization but one or more of its children is.
                   s   A check mark in a grey box next to either a folder or schema indicates that
                       some, but not all of its object are selected for reorganization.
                   To specify parameters, select an object and move on to the next page of the wizard.
                   You can then customize the following parameters to suit your needs:

                   Selected Objects with Properties
                   After specifying default value for the reserved amount of unused space, you can
                   also set objects to reclaim unused space, you can also specify the reserved amount
                   of unused space per segment. This setting overrides the default setting specified
                   earlier.

                   Setting Job and Schedule Options
                   After setting the deallocation job parameters, you can now schedule the job for
                   execution by the Oracle Enterprise Manager job system.

                   Jobname
                   You can accept the default or use any character for the jobname except apostrophe
                   and ampersand. For ease of use, keep the jobname short and simple.

                   Schedule Immediately
                   Select this option to start the job now. When you click the Finish button, the
                   Summary page displays, as described in the following section.

                   Schedule at a Later Time
                   Select this option to run the job at a later time. When you click the Next button,
                   additional fields display where you define scheduling options. Refer to “Defining
                   Scheduling Options” on page 10-15 for details.

                   Viewing the Tablespace Deallocation Wizard Summary Page
                   The Defragment Wizard Summary page summarizes all the information you
                   entered.
                   The summarized properties are:
                   s   General




10-18 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                             Analyzing a Tablespace



           s   Objects to Deallocate Storage
           s   Scheduling
           If, while reviewing the Summary page, you find options you want to change, click
           the Back button to make the necessary changes.
           Click the Cancel button at any time to close the Tablespace Deallocation wizard
           without defragmenting any of the selected segment(s).
           Click the Finish button to accept these options and start the defragmentation job.
           The Segments and Extents Information page of the Oracle Tablespace Manager
           main window displays.
           Check the Console job subsystem to make sure the defragmentation job has
           completed execution.
           To see the results of the defragmentation, choose Refresh from the View menu,
           reselect the tablespace (or datafile) of interest, display the Segments page, and then
           select the defragmented segment from the segments list.
           Click the Cancel button at any time to close the Tablespace Deallocation wizard
           without defragmenting the selected segment(s).


Analyzing a Tablespace
           The Tablespace Analyzer tool submits a job that analyzes various statistics and
           validates structure for selected tables, clusters, indexes, and partitions.

           Defining Default Options
           On this page you define the jobname and default analysis options.

           Default Options
           The default options you can choose are as follows:
           s   Compute Statistics
               Computes exact statistics about the analyzed object and stores them in the data
               dictionary.
           s   Delete Statistics
               Deletes any statistics about the analyzed object that are currently stored in the
               data dictionary.
           s   Estimate Statistics




                                                          Using Oracle Tablespace Manager    10-19
Analyzing a Tablespace



                         Estimates statistics about the analyzed object and stores them in the data
                         dictionary. You can specify a number of rows or a percentage of rows to use in
                         the calculation.
                   s     Validate Structure
                         Verifies the integrity of the structure of an index, table, or cluster. Enable the
                         Cascade option to validate the structure of all indexes on the cluster’s tables,
                         including the cluster index.
                   s     List Migrated and Chained Rows
                         Lists rows that have moved from one data block to another (migrated) or rows
                         that are contained in more than one block (chained). Migrated and chained
                         rows can cause excessive I/O, so you may want to identify them in order to
                         eliminate them.

                   Selecting Objects to Analyze
                   From the tree list on the Object Selection page, select the objects you want to
                   analyze. The selection status of each object is denoted as follows:
                   s     A check mark in a white box indicates that the object and its children will be
                         analyzed.
                   s     A check mark in a grey box indicates that the object itself is not selected for
                         analysis but one or more of its children is.
                   s     A check mark in a grey box next to either a folder or schema indicates that
                         some, but not all of its object are selected for analysis.
                   To choose the various analysis options, select an object and click the right mouse
                   button. The choices you make will override the default values.

                   Selected Objects with Properties
                   After selecting objects to analyze, you can also perform additional analyses on
                   specific objects, beyond the default options set earlier. The same analysis options
                   available for the default options definition are also available at the object level. See
                   “Defining Default Options” on page 19.

                   Defining Scheduling Options
                   The Scheduling Options page allows you to schedule the execution of the analysis.
                   This page is the same as the one used for the Tablespace Organizer. Refer to
                   “Defining Scheduling Options” on page 10-15 for details.




10-20 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                            Joining Adjacent Free Extents in the Database



            Viewing the Tablespace Analyzer Summary Page
            The Tablespace Analyzer Summary page summarizes all the information you
            entered while using the Tablespace Analyzer tool.
            The summarized properties are:
            s    General
            s    Objects to Analyze
            s    Scheduling
            If, while reviewing the Summary page, you find options you want to change, click
            the Back button to make the necessary changes.
            Click the Cancel button at any time to close the Tablespace Analyzer without
            analyzing the selected segment(s).
            Once you are satisfied with the information, click the Finish button to start the
            analysis process.
            Click the Help button to display Help for the Tablespace Analyzer tool.


Joining Adjacent Free Extents in the Database
            If you administer an active database, you may want to join adjacent free blocks in
            the database on a frequent basis. (If you administer an active Release 7.3 database,
            you will rarely, if ever, need to use this Oracle Tablespace Manager feature, because
            adjacent free blocks are automatically joined on a regular basis.)

                     Attention: The Coalesce Free Extents menu item is enabled only
                     when one of the following conditions exists: 1) the release of the
                     Oracle database is 7.2 or greater; and 2) the tablespace that has
                     been selected in the Oracle Tablespace Manager tree list contains
                     adjacent free blocks, as shown in the Segments page graphic dis-
                     play.


            To use Oracle Tablespace Manager to join adjacent free blocks in the database, take
            the following steps:
            1.   In the Oracle Tablespace Manager tree list, click on the tablespace or datafile of
                 interest.
            2.   When the Segments and Extents Information page displays, select the segment
                 for which you want to join adjacent free blocks.




                                                            Using Oracle Tablespace Manager       10-21
Joining Adjacent Free Extents in the Database



                    3.   Choose Coalesce Free Extents from the Tools menu.
                    Each group of adjacent free blocks in the space allocation graphic of the Segments
                    page should now appear as a single free block.




10-22 Oracle Enterprise Manager Performance Monitoring User’s Guide
                                                                                  Index
Symbols                                      Area chart type, 2-10
                                             AUDSID field, 9-10
# Adj. Free Extents field, 10-6              Automatic button
# Datafiles field
                                                Options Refresh, 9-18
   of tablespaces table, 10-6                Automatic menu item, 6-2
# Users Active chart, 3-7                    axes
# Users Logged On chart, 3-7
                                                customizing for a chart, 4-3
# Users Running chart, 3-7                      specifying values for, 4-3
# Users Waiting chart, 3-6, 3-7                 transposing for a chart, 5-5
% Free field
   of datafiles table, 10-7
   of tablespaces table, 10-6                B
                                             Bar chart type, 2-11
Numerics                                     Buffer Cache Hit % chart, 3-2
                                             Buffer Cache Hit chart, 3-7
3D charts                                    Buffer Gets Rate chart, 3-3
  changing depth of, 4-2
  rotating, 4-2
3D View Properties property sheet, 4-2       C
                                             Change Database Connection
A                                               of Oracle Performance Manager, 2-6
                                             Change Database Connection menu item
About Oracle Lock Manager menu item, 8-5        of Oracle Lock Manager, 8-4
About Oracle Performance Manager menu           of Oracle TopSessions, 9-6
    item, 2-9, 10-6
                                             Change Repository Connection
About Oracle TopSessions menu item, 9-7         of Oracle Performance Manager, 2-6
ACTION field, 9-11                           chart definitions
ACTION_HASH field, 9-11
                                                saving and reusing, 7-1
advanced events                              chart legend
  monitoring event conditions, 1-2              clicking the right mouse button on a, 4-4
advanced scheduling options
                                                customizing, 4-4
  in Collection Wizard, 10-15, 10-20                properties other than location, 4-5
ALL_TAB_COLUMNS                              chart type
  significance for Oracle TopSessions, 9-1
                                                Area chart type, 2-10




                                                                                        Index-1
   Bar chart type, 2-11                           Table Access chart, 3-10
   Cube chart type, 2-11                          Table Scan Rows Rate chart, 3-10
   Horizontal Bar chart type, 2-10                Tablespace chart, 3-10
   Line chart type, 2-10                          Tablespace Free Space chart, 3-11
   Pie chart type, 2-10                           Throughput Rate chart, 3-11
   specifying for a new chart, 5-5                transposing axes for, 5-5
   Table chart type, 2-11                         types of, 2-10
chart windows                                     working with data, 7-1
   displaying charts in, 6-1                   Charts menu
charts                                            Performance Manager, 2-8
   , 3-3, 3-6                                  Circuit chart, 3-3
   Buffer Cache Hit % chart, 3-2               CLIENT_INFO field, 9-12
   Circuit chart, 3-3                          Close menu item, 6-2
   complex type, 5-4                           Collapse All menu item
   customizing display elements of, 4-1           of Oracle Tablespace Manager, 10-5
   Data Dict Cache Hit %, 3-3                  Collapse Branch menu item
   defining new, 5-1                              of Oracle Tablespace Manager, 10-5
   Dispatcher chart, 3-3                       COMMAND field, 9-10
   File I/O Rate Details chart, 3-4               of Oracle TopSessions main display, 9-8
   Latch chart, 3-4                            complex charts
   Library Cache Details chart, 3-4               specifying, 5-4
   Library Cache Hit % chart, 3-5              Contention menu item, 2-6
   Lock chart, 3-5                             Contents menu item
   Memory Allocated chart, 3-5                    of Oracle Lock Manager, 8-5
   Memory Sort Hit % chart, 3-5                   of Oracle Performance Manager, 2-9
   modifying the basic definition of, 5-10        of Oracle Tablespace Manager, 10-6
   Network Bytes Rate chart, 3-5                  of Oracle TopSessions, 9-7
   No. of Users Logged On chart, 3-6           Count page
   No. of Users Waiting For Locks chart, 3-7      of the Options dialog box, 9-19
   Overview chart, 3-7                         Cube chart type, 2-11
   Parse Ratio chart, 3-7                      Current button
   Process chart, 3-8                             of Options Sort page, 9-17
   Queue chart, 3-8                            Cursors page, 9-13
   Read Consistency Hit % chart, 3-8           customizing
   Redo Allocation Hit % chart, 3-8               chart legends, 4-4
   Redo Statistics Rate chart, 3-8                display elements of charts, 4-1
   Rollback Nowait Hit % chart, 3-9
   Session chart, 3-9
                                               D
   Shared Server chart, 3-9
   sharing chart and window definitions, 7-2   data
   simple type, 5-3                               working with in Oracle Performance
   specifying functions for, 5-4                      Manager, 7-1
   specifying operations for, 5-4              Data Dict Cache Hit % chart, 3-3
   SQL Area chart, 3-9                         Data Dict Cache Hit chart, 3-7
   System Stats chart, 3-10                    Database_Instance menu item, 2-7




Index-2
DBA_DATA_FILES table, 3-10                            Expand One Level menu item
DBA_FREE_SPACE table, 3-11                               of Oracle Tablespace Manager, 10-5
DBA_OBJECTS                                           Extents field
   significance for Oracle Lock Manager, 8-1             of Segments display, 10-9
DBA_TABLESPACES table, 3-11
DBA_TS_QUOTAS table, 3-11
                                                      F
decimals
   overriding default for display in a chart, 4-3     File I/O Rate chart, 3-3, 3-7
Delete Recorded Data menu item, 2-9                   File I/O Rate Details chart, 3-4
Delta button                                          File menu
   of Options Sort page, 9-18                             of Oracle Lock Manager, 8-4
Details menu item, 9-6, 9-8                               of Oracle Performance Manager, 2-4
dialog boxes                                              of Oracle Tablespace Manager, 10-4
   Login Information, 1-4                                 of Oracle TopSessions, 9-5
Dimensions menu item, 4-1                             Filter Folder menu item
Dispatcher chart, 3-3                                     of Oracle Tablespace Manager, 10-4
Display All Sessions button                           FIXED_TABLE_SEQUENCE field, 9-12
   of Options Count page, 9-19                        free blocks
display area                                              joining adjacent, 10-21
   of a chart window, 6-2                             Free Blocks field
Display menu                                              of datafiles table, 10-7
   of Oracle Performance Manager, 2-6                     of tablespaces table, 10-6
Display Options page                                  Free List Hit % chart, 3-4
   of New Chart dialog box, 5-3                       function
Display Top N Sessions button                             specifying for a chart, 5-4
   of Options Count page, 9-19
Display Value group                                   G
   Options Sort, 9-17
drilldown                                             General page, 9-9
   specifying for a new chart, 5-2                      of Session Details display,   9-9
Drilldown menu item, 4-1
                                                      H
E                                                     Help menu
event conditions                                         of Oracle Lock Manager, 8-5
   monitoring with Oracle Advanced Events,      1-2      of Oracle Performance Manager, 2-9
example                                                  of Oracle Tablespace Manager, 10-5
   of defining a new chart, 5-6                          of Oracle TopSessions, 9-7
Exit menu item                                        historical data
   of Oracle Lock Manager, 8-4                           , 7-7
   of Oracle Performance Manager, 2-6                    collecting for display in charts, 7-3
   of Oracle Tablespace Manager, 10-4                    displaying in charts, 7-5
   of Oracle TopSessions, 9-6                         Horizontal Bar chart type, 2-10
exiting
   from Oracle TopSessions, 9-19




                                                                                                 Index-3
I                                           Manual button
                                              of Options Refresh page, 9-18
I/O menu item, 2-7                          Memory Allocated chart, 3-5, 3-7
                                            Memory menu item, 2-7
K                                           Memory Sort Hit % chart, 3-5, 3-7
                                            menu bar
Kill menu item, 9-8
                                              of a chart window, 6-1
   of Oracle TopSessions, 9-6
                                              of Oracle TopSessions, 9-5
Kill Session menu item
                                              of Tablespace Manager, 10-4
   of Oracle Lock Manager, 8-5
                                              Oracle Lock Manager, 8-4
                                            menu items
L                                             About Oracle Lock Manager, 8-5
labels                                        About Oracle Performance Manager menu
   specifying in a new chart, 5-3                  item, 2-9, 10-6
LAST_CALL_ET field, 9-12                      About Oracle TopSessions menu item, 9-7
Latch chart, 3-4                              Automatic menu item, 6-2
legend                                        Change Database Connection, 10-4
   customizing for a chart, 4-4                  of Oracle Lock Manager, 8-4
Library Cache Details chart, 3-4                 of Oracle Performance Manager, 2-6
Library Cache Hit % chart, 3-5, 3-7              of Oracle TopSessions, 9-6
Line chart type, 2-10                         Change Repository Connection
Load Charts menu item                            of Oracle Performance Manager, 2-6
   of Oracle Performance Manager, 2-5         Close menu item, 6-2
Lock chart, 3-5                               Coalesce menu item, 10-5
Lock Type field, 9-15                            of Oracle Enterprise Manager, 10-5
   of Session Details Locks page, 8-6         Collapse All menu item
Lock Types list box, 9-14                        of Oracle Tablespace Manager, 10-5
locks                                         Collapse Branch menu item
   obtaining an overview of, 8-5                 of Oracle Tablespace Manager, 10-5
Locks menu, 8-5                               Contention menu item, 2-6
Locks page, 9-14                              Contents menu item
LOCKWAIT field, 9-10                             of Oracle Lock Manager, 8-5
logarithmic scale                                of Oracle Performance Manager, 2-9
   selecting for a chart, 4-4                    of Oracle Tablespace Manager, 10-6
LOGON_TIME field, 9-12                           or Oracle TopSessions, 9-7
                                              Database_Instance menu item, 2-7
                                              Defragmentation Wizard menu item, 10-5
M                                             Delete Recorded Data menu item, 2-9
MACHINE field, 9-11                           Details menu item, 9-6, 9-8
  of Oracle TopSessions main display, 9-8     Dimensions menu item, 4-1
main window                                   Drilldown menu item, 4-1
  of Oracle Lock Manager, 8-2                 Exit menu item
  of Oracle Performance Manager, 2-3             of Oracle Lock Manager, 8-4
  of Oracle Tablespace Manager, 10-1             of Oracle Performance Manager, 2-6
  of Oracle TopSessions, 9-3                     of Oracle Tablespace Manager, 10-4




Index-4
    of Oracle TopSessions, 9-6                of Oracle Tablespace Manager, 10-5
Expand One Level menu item                 Toolbar menu item
    of Oracle Tablespace Manager, 10-5        of Oracle Lock Manager, 8-5
Filter Folder menu item                       of Oracle Tablespace Manager, 10-5
    of Oracle Tablespace Manager, 10-4        of Oracle TopSessions, 9-6
I/O menu item, 2-7                         Type menu item, 4-2
Kill menu item, 9-8                        Update Now menu item, 6-3
    of Oracle TopSessions, 9-6             User Defined menu item, 2-7
Load Charts menu item                      Using Help menu item
    of Oracle Performance Manager, 2-5        of Oracle Lock Manager, 8-5
Load menu items, 2-7                          of Oracle Performance Manager, 2-9
Memory menu item, 2-7                         of Oracle Tablespace Manager, 10-6
Modify SQL Chart menu item, 2-8               of Oracle TopSessions, 9-7
Modify Window menu item, 2-8             menu itmes
New SQL Chart menu item, 2-8               Defragmentation Wizard menu item
New Window menu item, 2-8                     of Oracle Tablespace Manager, 10-5
Options                                    Tablespace Analyzer menu item
    Oracle TopSessions, 9-7                   of Oracle Tablespace Manager, 10-5
Overview menu item, 2-7                  menus
Playback menu item, 2-9                    File menu
Print menu item                               of Oracle Lock Manager, 8-4
    of Oracle Performance Manager, 6-2        of Oracle Performance Manager, 2-4
Print Setup menu item                         of Oracle Tablespace Manager, 10-4
    of Oracle Performance Manager, 2-6        of Oracle TopSessions, 9-5
Properties menu item, 4-1                  Help menu
Refresh Data menu item, 10-4                  of Oracle Lock Manager, 8-5
Refresh menu item                             of Oracle TopSessions, 9-7
    of Oracle Tablespace Manager, 10-4     Locks menu, 8-5
    of Oracle TopSessions, 9-6             Sessions menu, 9-6
Save Charts menu item                      Tools menu, 10-5
    of Oracle Performance Manager, 2-5     View menu
Scale menu item, 4-2                          of Oracle Lock Manager, 8-4
Search for Help On menu item                  of Oracle Tablespace Manager, 10-4
    of Oracle Lock Manager, 8-5               of Oracle TopSessions, 9-6
    of Oracle Performance Manager, 2-9   Mode Held field, 9-15
    of Oracle Tablespace Manager, 10-6   Mode Requested field, 9-15
    of Oracle TopSessions, 9-7           Modify Chart property sheet, 5-10, 6-4
Start Recording menu item, 2-9           Modify SQL Chart menu item, 2-8
Status Bar menu item                     Modify Window menu item, 2-8
    of Oracle Lock Manager, 8-5          MODULE field, 9-11
    of Oracle Tablespace Manager, 10-5   MODULE_HASH field, 9-11
    of Oracle TopSessions, 9-6           mouse button
Stop Recording menu item, 2-9              using the right, 2-10
Tablespace Analyzer menu item, 10-5
Tablespace Organizer menu item, 10-5




                                                                                   Index-5
N                                               chart window toolbar, 6-2
                                                Charts menu, 2-8
Name field                                      customizing display elements of charts, 4-1
   of datafiles table, 10-7
                                                displaying charts in chart windows, 6-1
   of tablespaces table, 10-6                   main window, 2-3
Network Bytes Rate chart, 3-5                   menus, 2-4
network environment
                                                overview of features, 1-1
   monitoring event conditions, 1-2             Record menu, 2-8
Network I/O Rate chart, 3-6                     Refresh menu, 6-2
New Chart property sheet, 5-1
                                                starting, 2-2
   Display Options page, 5-3                    status bar of the chart window, 6-2
   SQL Statement page, 5-2                      title bar, 2-4
New SQL Chart menu item, 2-8
                                                using predefined charts, 3-1
New Window menu item, 2-8                       using the right mouse button, 2-10
No. of Users Active, 3-6                        Window menu, 6-2
No. of Users Logged On chart, 3-6
                                                working with data, 7-1
No. of Users Running chart, 3-6                 working with historical data, 7-3
No. of Users Waiting For Locks chart,   3-7   Oracle Tablespace Manager
                                                File menu, 10-4
O                                               Help menu
                                                      , 10-5
Object Name field, 9-15
                                                main window, 10-1
Object Owner field, 9-15
                                                obtaining an overview of datafiles, 10-6
Object Type field, 9-15
                                                obtaining an overview of tablespaces, 10-6
ObjectID field, 9-15
                                                overview of features, 1-3
operations
                                                starting, 10-1
  specifying for a chart, 5-4
                                                status bar, 10-3
Options dialog box, 9-16, 9-18
                                                toolbar, 10-3
  Count Page, 9-19
                                                Tools menu, 10-5
  Sort page, 9-17
                                                View menu, 10-4
Oracle Advanced Events
                                              Oracle TopSessions
  monitoring event conditions, 1-2
                                                , 9-6, 9-7
Oracle Lock Manager
                                                exiting, 9-19
  , 8-4, 8-5
                                                main window, 9-3
  main window, 8-2
                                                menus, 9-5
  menus, 8-4
                                                overview of features, 1-3
  required views to run, 8-1
                                                required views to run, 9-1
  starting, 8-2
                                                Session Details display, 9-9
  status bar, 8-4
                                                Sessions menu, 9-6
  title bar, 8-3
                                                starting, 9-2
  toolbar, 8-3
                                                status bar, 9-5
Oracle Performance Manager
                                                title bar, 9-4
  chart types of, 2-10
                                                toolbar, 9-4
  chart window display area, 6-2
                                              OSUSER field, 9-10
  chart window title bar, 6-1
                                                of Oracle TopSessions main display, 9-7




Index-6
Overview chart, 3-7                               SQL Area chart, 3-9
Overview menu item, 2-7                           System I/O Rate chart, 3-10
Owner field                                       Table Scan Rows Rate chart, 3-10
  of Segments display, 10-9                       tables of, 3-1
                                                  Tablespace chart, 3-10
                                                  Tablespace Freespace chart, 3-11
P
                                                  Throughput Rate chart, 3-11
PADDR field, 9-10                              predefined events
Parse Ratio chart, 3-7                            for monitoring event conditions, 1-2
Pie chart type, 2-10                           preface
PLAN_TABLE                                        Send Us Your Comments, ix
   significance for Oracle TopSessions, 9-2    PREV_HASH_VALUE field, 9-11
Playback menu item, 2-9                        PREV_SQL_ADDR field, 9-11
predefined charts                              Print menu item
   , 3-4, 3-7, 3-9, 3-10                          of Oracle Performance Manager, 6-2
   Buffer Cache Hit % chart, 3-2               Print Setup menu item
   Buffer Gets Rate chart, 3-3                    of Oracle Performance Manager, 2-6
   Circuit chart, 3-3                          Process chart, 3-8
   Data Dict Cache Hit %, 3-3                  PROCESS field, 9-10
   default features, 3-1                       PROGRAM field, 9-11
   Dispatcher, 3-3                                of Oracle TopSessions main display, 9-8
   File I/O Rate chart, 3-3                    Properties menu item, 4-1
   Free List Hit % chart, 3-4                  property sheets
   Latch chart, 3-4                               Modify Chart property sheet, 5-10
   Library Cache Hit % chart, 3-5                 Modify Charts property sheet, 6-4
   Lock chart, 3-5                                New Chart property sheet, 5-1
   Memory Sort Hit % chart, 3-5                   Options property sheet, 9-16
   Network Bytes Rate chart, 3-5                  Segments and Extents, 10-8
   Network I/O Rate chart, 3-6
   No. of Users Active chart, 3-6
   No. of Users Logged On, 3-6                 Q
   No. of Users Running chart, 3-6             Queue chart, 3-8
   No. of Users Waiting chart, 3-6
   No. of Users Waiting For Locks chart, 3-7
                                               R
   Overview chart, 3-7
   overview of, 3-2                            Read Consistency Hit % chart, 3-8
   Process chart, 3-8                          Record menu, 2-8
   Queue chart, 3-8                            Redo Allocation Hit % chart, 3-8
   Read Consistency Hit % chart, 3-8           Redo Statistics Rate chart, 3-8
   Redo Allocation Hit % chart, 3-8            Refresh Data menu item, 10-4
   Redo Statistics Rate chart, 3-8             Refresh Interval list boxes
   Rollback Nowait Hit % chart, 3-9               of Options Refresh page, 9-18
   scaling of, 3-2                             Refresh menu
   Session chart, 3-9                             of a chart window, 6-2
   Sort Rows Rate chart, 3-9                   Refresh menu item




                                                                                            Index-7
   of Oracle Lock Manager, 8-5                      SERVER field, 9-10
   of Oracle Tablespace Manager, 10-4               session activity
   of Oracle TopSessions, 9-6                          obtaining an overview of, 9-7
refresh mode                                        Session chart, 3-9
   changing from Oracle TopSessions toolbar, 9-5    Session Details display, 9-9, 9-14
Refresh page, 9-18                                     , 9-16
refresh time                                           Cursors page, 9-13, 9-14
   for the Session Details display, 9-16               for multiple sessions simultaneously, 9-16
   Oracle TopSessions main display, 9-18               General page, 9-9
right mouse button                                     General page of, 9-9
   using in Oracle TopSessions, 9-8                    Locks page of, 9-14
Rollback Nowait Hit % chart, 3-7, 3-9                  Statistics page, 9-12
ROW_WAIT_BLOCK# field, 9-12                            Statistics page of, 9-12
ROW_WAIT_FILE# field, 9-12                          Session ID field, 9-14
ROW_WAIT_OBJ# field, 9-12                           sessions
ROW_WAIT_ROW# field, 9-12                              viewing details about, 9-9
                                                    shadows
                                                       changing for 3D charts, 4-2
S
                                                    Shared Server chart, 3-9
SADDR field, 9-9                                    Show Cursors list box
Save Charts menu item                                  of Session Details Cursor page, 9-13
   of Oracle Performance Manager, 2-5               Show Explain Plan button
scale                                                  of Session Details Cursors page, 9-13
   overriding default for a chart, 4-3              SID field, 9-9
   selecting linear, 4-4                               of Oracle TopSessions main display, 9-7
   selecting logarithmic, 4-4                       simple chart
Scale and Numbers property sheet, 4-3                  example with drilldown and transposed
Scale menu item, 4-2                                         axes, 5-6
scaling                                                specifying, 5-3
   of predefined charts, 3-2                        Size field
SCHEMA# field, 9-10                                    of Segments display, 10-9
SCHEMANAME field, 9-10                              Sort page, 9-17
Search for Help On menu item                        Sort Rows Rate chart, 3-9
   of Oracle Lock Manager, 8-5                      sort statistic field
   of Oracle Performance Manager, 2-9                  of Oracle TopSessions main display, 9-8
   of Oracle Tablespace Manager, 10-6               Sort Statistic list box
   of Oracle TopSessions, 9-7                          Options Sort page, 9-17
Secondary Y axis button                             SQL Area chart, 3-9
   of the Scale and Numbers property sheet,   4-3   SQL Statement page
Segment Multi-column list, 10-9                        of New Chart property sheet, 5-2
segments                                            SQL_ADDRESS field, 9-11
   monitoring in a tablespace, 10-7                 SQL_HASH_VALUE field, 9-11
Send Us Your Comments                               Start Recording menu item, 2-9
   boilerplate, ix                                  starting
SERIAL# field, 9-10                                    from the Console, 1-4




Index-8
   Oracle Performance Manager, 2-2            Throughput Rate chart, 3-11
   Oracle Tablespace Manager, 10-1            title bar
   Oracle TopSessions, 9-2                        of a chart window, 6-1
   performance monitoring applications, 1-4       of Oracle Lock Manager, 8-3
   standalone mode, 1-4                           of Oracle Tablespace Manager, 10-2
Statistic Filter list box                         of Oracle TopSessions, 9-4
   Options Sort, 9-17                         toolbar
Statistics page, 9-12                             of a chart window, 6-2
   of Session Details display, 9-12               of Oracle Lock Manager, 8-3
status                                            of Oracle TopSessions, 9-4
   of session, 9-8                            Toolbar menu item
status bar                                        of Oracle Lock Manager, 8-5
   of a chart window, 6-2                         of Oracle Tablespace Manager, 10-5
   of Oracle Lock Manager, 8-4                    of Oracle TopSessions, 9-6
   of Oracle TopSessions, 9-5                 Total Blocks field
Status Bar menu item                              of datafiles table, 10-7
   of Oracle Lock Manager, 8-5                    of tablespaces table, 10-6
   of Oracle Tablespace Manager, 10-5         TYPE field, 9-11
   of Oracle TopSessions, 9-6                 Type field
STATUS field, 9-10                                of Segments display, 10-9
   of Oracle TopSessions main display, 9-8    Type menu item, 4-2
Statusbar menu item                           Typical Perfomance Monitoring Scenario,   1-5
   of Oracle Lock Manager, 8-5
Stop Recording menu item, 2-9
                                              U
SYS.DBA_OBJECTS
   significance for Oracle TopSessions, 9-1   Update Now menu item, 6-3
System I/O Rate chart, 3-7, 3-10              User Defined menu item, 2-7
System Stats chart, 3-10                      User Name field, 9-14
                                              USER# field, 9-10
                                              USERNAME field, 9-10
T                                               of Oracle TopSessions main display, 9-7
Table Access chart, 3-10                      Using Help menu item
Table chart type, 2-11                          of Oracle Lock Manager, 8-5
Table Scan Rows Rate chart,    3-10             of Oracle Performance Manager, 2-9
tables                                          of Oracle Tablespace Manager, 10-6
   use in predefined charts,   3-1              of Oracle TopSessions, 9-7
Tablespace chart, 3-10
Tablespace Free Space chart,   3-11
                                              V
tablespaces
   monitoring segments of,     10-7           V$ACCESS view, 3-10
   obtaining an overview of,    10-6          V$CIRCUIT view, 3-3
TADDR field, 9-10                             V$DBFILE view, 3-4
TERMINAL field, 9-11                          V$DISPATCHER view, 3-3
The, 9-6                                      V$FILESTAT view, 3-4
Throughput chart, 3-7                         V$LATCH view, 3-4, 3-8




                                                                                          Index-9
V$LATCHHOLDER view, 3-4                                      components of, 6-1
V$LATCHNAME view, 3-4                                        defining a new, 6-3
V$LIBRARYCACHE view, 3-5                                     modifying existing, 6-4
V$LICENSE view, 3-6                                          saving and reusing definitions of,   7-1
V$LOCK
   significance for Oracle Lock Manager, 8-1
                                                         X
V$LOCK view, 3-5, 9-14
   significance for Oracle TopSessions, 9-1              X Axis button
V$OPEN_CURSOR                                               of the Scale and Numbers property sheet,    4-3
   significance for Oracle TopSessions, 9-1
V$PROCESS view, 3-8, 3-9                                 Y
V$QUEUE view, 3-8
V$ROLLNAME                                               Y axis
   significance for Oracle TopSessions, 9-2                 customizing for a chart,   4-3
V$ROLLNAME view, 9-14
V$ROLLSTAT view, 3-9
V$ROWCACHE view, 3-3
V$SESSION
   significance for Oracle Lock Manager, 8-1
V$SESSION view, 3-3, 3-5, 3-6, 3-7, 3-8, 3-9, 9-7, 9-9
   significance for Oracle TopSessions, 9-1
V$SESSION_WAIT view, 3-6
V$SESSSTAT view, 3-5
V$SESSTAT
   significance for Oracle TopSessions, 9-1
V$SESSTAT view, 9-12
V$SHARED_SERVER view, 3-3, 3-9
V$SQLAREA view, 3-10
V$SQLTEXT view
   significance for Oracle TopSessions, 9-1
V$STATNAME view, 3-5, 9-17
   significance for Oracle TopSessions, 9-1
V$SYSSTAT view, 3-2, 3-3, 3-4, 3-8, 3-9, 3-10, 3-11
V$SYSTEM_EVENT view, 3-6
V$WAITSTAT view, 3-4
View menu
   of Oracle Lock Manager, 8-4
   of Oracle Tablespace Manager, 10-4
   of Oracle TopSessions, 9-6

W
Window menu
  of a chart window,    6-2
windows




Index-10

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:11/30/2012
language:
pages:142