4
Performance Pack Configuration

This chapter describes how the Oracle Performance Pack is set up.

It covers the following topics:

Topic	See Page
Setting up the Performance Pack Applications	4-2
Setting up TopSessions	4-2
Setting Up Tablespace Manager	4-3
Setting up Oracle Trace	4-4
Setting up Oracle Performance Manager	4-6
Setting up Oracle Expert	4-7

Setting up the Performance Pack Applications

The Performance Pack applications are set up after you have installed the Oracle Performance Pack.

For information on how each Performance Pack application is set up, see the specific section in this chapter.

Note:

The Performance Pack is a separately licensed and purchased product.

Setting up TopSessions

In order to install the features of Oracle TopSessions,

• you need to create additional tables and views on each database you want to connect to from TopSessions.
• certain SELECT privileges must be granted

The smptsixx.sql scripts have been provided to help automate this process.

The xx in the file name identifies the version of the database against which the script should be run. The script for each database version is located in the $ORACLE_HOME\SYSMAN\ADMIN directory..

Version of the Database	Script to Run
Oracle 7.3	smptsi73.sql script
Oracle 8.0	smptsi80.sql script

When smptsixx.sql is run, it also automatically runs the following two scripts:

• catbloxx.sql
• utlxplxx.sql

These two scripts create in the managed database some additional tables, views, and public synonyms that are required by the Oracle Advanced Events.

To set up Oracle TopSessions for a database:

Step	Action	Additional References
1	From the Oracle Enterprise Manager program group, double-click on the SQL Worksheet icon to launch the tool.	For information on starting Oracle Enterprise Manager and tools, see Starting Enterprise Manager Tools on page 2-8.
2	Use the Login Information dialog box to connect to the managed database as SYS.	See the online help or the Oracle Enterprise Manager Administrator's Guide.
3	Grant SELECT ANY TABLE privileges to each administrator account. This step may be omitted if the account has already been granted the same privileges as SYSTEM. Note that when preparing to run the smptsixx.sql script on managed databases, you should log into each database as SYS, as mentioned previously.	Refer to Setting Up the Repository User Account on page 2-2 for an example of how to grant privileges to an account.
4	Run the smptsixx.sql script for the managed database. If the smptsixx.sql script is not run on a managed database, you may see a "Table or View does not exist" message when you try to use Oracle TopSessions.
5	Exit SQL Worksheet.

Setting Up Tablespace Manager

Before you can use Tablespace Manager's Analyze Wizard against a database, the CHAINED_ROWS table must exist in the database. To create the CHAINED_ROWS table:

1. Log into the database as SYS.
2. Run utlchain.sql, which is located in the $ORACLE_HOME/RDBMS/ADMIN directory.

The CHAINED_ROWS table is also used by the Oracle Advanced Events chained rows event.

Setting up Oracle Trace

Oracle Trace relies on correct client/server configuration.

Oracle Trace requires that:

• the Oracle Enterprise Manager intelligent agent for Oracle 7.3.3 or later is installed and running on the nodes that are targets for Oracle Trace collections
• the Oracle Enterprise Manager Console is running, and the nodes you want to use Oracle Trace on are listed in the console Navigator. Oracle Trace uses the Oracle Enterprise Manager job subsystem to perform its tasks.

This section contains the following topics:

Topic	See Page
Using Oracle Trace for Oracle 8 Server Collections	4-4
Using Oracle Trace for Oracle 7.3 Server Collections	4-4
Creating Oracle Trace Repository Tables	4-5
Creating Oracle Trace Formatter Tables	4-5
Other Configuration Information	4-6

Using Oracle Trace for Oracle 8 Server Collections

If you are using Oracle Trace for Oracle8 server collections, you must set the value of the ORACLE_TRACE_ENABLE parameter in your INITsid.ORA file to TRUE.

Using Oracle Trace for Oracle 7.3 Server Collections

If you are using Oracle Trace for Oracle 7.3 server collections, check that the Oracle Trace user account, TRACESVR, and the Oracle Trace stored procedure packages: DBMS_ORACLE_TRACE_AGENT and DBMS_ORACLE_TRACE_USER, exist. If they do not, you must create them by running the otrcsvr.sql script as SYS. The otrcsvr.sql script is located in $ORACLE_HOME/otrace/admin on UNIX systems and in $ORACLE_HOME\otracexx\admin on NT systems.

The otrcsvr.sql script is run automatically during database installation on most platforms. However, if your server platform is NT, you must run this script manually.

Creating Oracle Trace Repository Tables

All the necessary tables for Oracle Trace are created or updated automatically when any Oracle Enterprise Manager Performance Pack product is started for the first time.

Creating Oracle Trace Formatter Tables

The Oracle Trace formatter tables are required by the Oracle Trace formatting mechanism, which converts and loads an Oracle Trace binary file (collection_name.dat) into Oracle tables for access.

Oracle Trace data collected from Oracle 7.3.3 and later databases can only be stored in databases that are using the latest Oracle Trace formatter tables. Use the vobsh command from a DOS window to create new formatter tables, delete existing formatter tables, or upgrade earlier versions of formatter tables to the latest version for a database. See Manually Creating, Dropping, or Upgrading the Repository on page C-2 for more information on using vobsh.

The formatter table operations and vobsh commands to perform those operations are described below.

These vobsh commands fail if the specified user does not have an account for the specified service. In this case, create an account for the user on the specified service, then execute the vobsh command again.

Creating New Formatter Tables

To create a new formatter tables in a database where no formatter tables currently exist, use the vobsh command:

vobsh -c "user/password@service" -o CREATE -p "EPCFMT"

Note that the formatted data stored in formatter tables can grow very large, so the database in which formatter tables are created should have sufficient space to store large amounts of data.

Dropping Existing Formatter Tables

To drop an existing formatter tables from a table, use the vobsh command:

vobsh -c "user/password@service" -o DROP -p "EPCFMT"

This command drops any version of formatter tables from the specified database. Note that in addition to dropping the existing formatter tables, vobsh also deletes the formatted data stored in the tables.

Upgrading Formatter Tables

To validating (upgrade) older versions of the formatter tables (which creates, drops, or upgrades the formatter tables as needed), use vobsh command:

vobsh -c "user/password@service" -o VALIDATE -p "EPCFMT"

This command determines what version of the formatter tables the database has and allows you to upgrade them to the new version, if necessary.

Other Configuration Information

If you experience any problems running Oracle Trace after completing the configuration instructions in this chapter, refer to the configuration checklist in the Configured Components Checklist section of the Overview chapter of the Oracle Enterprise Manager Oracle Trace User's Guide.

Setting up Oracle Performance Manager

The repository tables required by the Save/Load Charts feature of Oracle Performance Manager are created when the Oracle Enterprise Manager Performance Pack repository is created.

Requirements

The Performance Manager also uses the Oracle Trace formatter tables to store and playback recorded data. These formatter tables must exist in any database in which you want to record and playback data. See Creating Oracle Trace Formatter Tables on page 4-5 for more information on creating Oracle Trace formatter tables.

If you used the recording and playback feature with earlier versions of Oracle Enterprise Manager, validate the formatter tables that were used to record and playback the data. See Creating Oracle Trace Formatter Tables on page 4-5 for instructions on validating formatter tables.

Note:

The Chart FX DLL is required for Oracle Performance Manager. The version that is on the CD-ROM has been enhanced and is different from the Chart FX DLL that is shipped by Software FX. The updated version for Oracle Performance Manager is installed in $ORACLE_HOME\BIN.

Using Performance Manager with Parallel Server

To run Performance Manager in a Parallel Server environment, the following must be true:

• Two instances must be started, otherwise Performance Manager does not treat the server as a Parallel Server. When Performance Manager knows that a Parallel Server is running, it enables the menu items for Parallel Server.
• The following Parallel Server scripts must have been run:
  • ops_ctab.sql
  • ops_dbl.sql
  • ops_gdl.sql
  • ops_mon.sql
  • ops_pack.sql

  These Parallel Server scripts are provided with the Parallel Server installation. These scripts are also available in the $ORACLE_HOME\SYSMAN\ADMIN directory. Running these scripts creates performance monitoring tables and views that Performance Manager requires to fetch and display Parallel Server data in charts.

For more information about these scripts, see Oracle Parallel Server Support for the OEM Console in the Parallel Server documentation set.

Setting up Oracle Expert

When you set up Oracle Expert, review the following sections on creating the repository tables and running the XPVIEW.SQL script.

Creating Oracle Expert Repository Tables

Oracle Expert requires a set of database tables in the Oracle Enterprise Manager repository. These tables store the data associated with each Oracle Expert tuning session.

All the necessary tables are created or updated automatically when any Oracle Enterprise Manager Performance Pack product is started for the first time.

Running XPVIEW.SQL

Before you collect the instance class for a database, run the XPVIEW.SQL script on the database to ensure all instance statistics are collected. After you run the SQL script, Oracle Expert can collect information about the database's shared SQL pool. XPVIEW.SQL only needs to be run once for each database instance.

---