'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += '
'; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 4 - Managing with Oracle Enterprise Manager (OEM)

Using Oracle8

Chapter 4

Managing with Oracle Enterprise Manager (OEM)

Introducing OEM Components

The Oracle Enterprise Manager combines a graphical console, agent processes, and common services to provide an integrated and comprehensive systems management platform for managing Oracle databases on the network. You can perform the following tasks from Enterprise Manager:

Don't install OEM 1.2.2 and OEM 1.5.0 in Oracle 8.0.3 home
OEM v1.2.2 isn't compatible with Oracle Server 7.3.3 and 8.0.x. OEM v1.5.0 shouldn't be installed in an Oracle Server 8.0.3 home directory. OEM's latest version (1.5.5), however, works fine for Oracle 7.3.3 and 8.0.x.

Table 4.1 describes OEM's database application tools that allow you to perform the primary database administration tasks; Figure 4.1 shows these components.

Figure 4.1 : OEM comprises various components that can be used for specific tasks.

Backup ManagerLock Manager
Data ManagerTopSessions
Instance ManagerPerformance Manager
Schema ManagerOracle Trace
Security ManagerNavigator window
SQL WorksheetJob Scheduling window
Storage ManagerEvent Management window

Table 4.1  OEM components and their functions
OEM Component
Instance ManagerManage instances, INIT.ORA file initialization parameters, and sessions
TableSpace ManagerManage fragmentation and free space in tablespaces
Storage ManagerManage tablespaces, data files, and rollback segments
Security ManagerManage users, roles, privileges, and profiles
Schema ManagerManage schema objects such as tables, indexes, views, clusters, synonyms, and sequences
Server ManagerPerform line-mode database operations from the client
Software ManagerManage the software distribution process
Backup ManagerPerform database backups and create backup scripts
Data ManagerPerform export/import and data loads

Using the Instance Manager,

The Enterprise Manager environment consists of the following major components:

The basic OEM functionality is available to you with Oracle Server; however, you can install several optional management packs: Change Management Pack, Diagnostic Pack, and Tuning Pack.

OEM Console

The console user interface contains a set of windows that provide various views of the system. There's only one console per client machine. Table 4.2 describes the various components of the console.

Table 4.2  Components of the console
Navigator windowA tree view of all the objects in the system and their relationships
Map windowAllows customization of the system views
Job windowUser interface to the Job Scheduling system
Event Management windowUser interface to the event management system

Common Services

The following services are common to various OEM components (see Table 4.3 for details on how these components interact):

Multiple repositories can exist within the same database
You can use one repository, or you can switch between multiple repositories stored in the same database.

Reactive management is provided by the job and event systems
You can use the Job and Event systems together to provide a reactive management system. This is achieved by allowing certain jobs to get executed when the specified events occur.

Table 4.3  Communication between OEM components
Communication Path
Console and communication daemonThe console sends job and event requests to the communication daemon, and the status of these jobs and events are sent back to the console. Authentication requests of users logging in to the console are sent to the daemon. The daemon sends information to update the tree of nodes and services in the Navigator.
Communication daemon and Common Services Job and event requests are handed to the Job or Event Management systems. The Common Services passes job and event status back to the communication daemon. Service Discovery information is passed from the Common Services to the daemon.
Communication daemon and intelligent agent Agents communicate with the daemon to report results and status messages for jobs and events from the remote nodes.
Common Services and RepositoryThe Event Management and Job Management systems write event and job information, respectively, to the Repository.

Figure 4.2 represents the communication path between different components of the Enterprise Manager in terms of the jobs, events, or any other requests logged in to the console.

Figure 4.2 : Interaction between the various OEM components is well-defined.

Intelligent Agents

Intelligent agents are intelligent processes running on remote nodes. Each agent resides on the same node as the service it supports and can support all the services on that node. Intelligent agents perform the following functions:

Use an intelligent agent to manage an older Oracle release
Each intelligent agent is compatible with the database with which it's released and prior database releases. When used to manage an older release of the database, the intelligent agent must be installed in an ORACLE_HOME directory current with the agent release. Older releases of the intelligent agent aren't compatible with newer releases of the database.

An agent is required for all or some functionality of these components: Service Discovery; Job Control System; Event Management System; Backup Manager; Software Manager; Data Manager's Export, Import, and Load applications; Oracle Events; and Trace.

Application Programming Interface (API)

The APIs available with Enterprise Manager enable third-party applications-for example, applications that can analyze the data collected through Oracle Expert-to integrate the console with the Common Services. Third-party applications written in C++ that use OLE technology work very well with these APIs. Applications can be integrated at the console, service, or agent level; however, this integration depends on the third-party applications.

Installing and Configuring OEM

Several issues are involved in the installation and configuration of Enterprise Manager that you should address for the components to work together. Some issues include setting up the client, the server, and the Repository. Configuration involves setting preferred credentials and setting up security, among other things.

Not available for UNIX
OEM is available only for Windows NT and Windows 95. However, the intelligent agent can run on UNIX or Windows NT.

Minimum Requirements

You need the following minimum hardware resources to install and use the OEM components:

Installing documentation is optional
The OEM documentation can take a lot of space. If you don't have enough disk space, you can run it from the CD-ROM when needed.

The following minimum software resources are needed:

Compatibility Issues

Table 4.4 lists the components of Oracle Enterprise Manager version 1.5.0 and their compatibility with specific releases of Oracle Server.

Table 4.4  Compatibility matrix for OEM 1.5.0

Oracle Server 7.2
Oracle Server 7.3
Oracle Server 8.0.3
Oracle Server 8.0.4
See /1/
Service Discovery
Job Control System
Event Management System
Database Applications
Backup Manager
Instance Manager
Schema Manager
Security Manager
Storage Manager
SQL Worksheet
Software Manager
See /2/
Utility Applications
Data Manager/Export
Data Manager/Import
Data Manager/Load
Performance Pack
Lock Manager
Oracle Events
Performance Manager
Tablespace Manager
Top Sessions

OEM 1.5 must be installed in a different home if there is a local 8.0.3 database.
Software Manager can support Oracle Server 7.3.3 agents (Windows NT only) with upgraded OSM job files.

Performing the OEM Installation

Install and configure Enterprise Manager (general steps)

  1. Configure Net8 locally and on the server.
  2. Set up the client.
  3. Set up the server.
  4. Set up the Repository.
  5. Install the OEM software on the client.
  6. Install the intelligent agent on the server. The agent and the database that it services must be installed on the same node.

Configuring Net8

You can use the following tools to generate the different files required for Net8 and Enterprise Manager:

Net8 must be installed before installing OEM
If Net8 isn't installed on the machine, select it from the OEM installer. You also can choose to install Performance Pack at this point. You need to configure Net8 so that it can use the database you want to access with OEM.

You use Network Manager and Topology Generator to generate the TNSNAMES.ORA file, which will contain the information for the sample database. However, you need to edit this file with the Net8 easy configuration utility or a text editor so it will have information of other databases that you will use from OEM.

Installing and Configuring the Intelligent Agent

You can choose to install the intelligent agent as part of the Oracle Server installation, or you can install it later by running the Oracle installer.

The following is required for the agent to function correctly:

Before the agent is started, you must do the following to create a user account with appropriate privileges for the intelligent agent:

Creating a user,
Granting privileges to roles,

Install Oracle Enterprise Manager

  1. Log in to Windows NT as the administrator or a user with permissions equivalent to an administrator.
  2. Change to the \NT_x86\INSTALL directory on the CD-ROM drive.
  3. Double-click ORAINST.EXE or SETUP.EXE to launch the Oracle installer.
  4. Select Oracle Enterprise Manager to install the base product.
    The Installer will search for the TOPOLOGY.ORA and TNSNAMES.ORA files in the ORACLE_HOME\network\admin directory. If TOPOLOGY.ORA isn't found, an error message appears. If TNSNAMES.ORA is found but not TOPOLOGY.ORA, you'll be prompted to create the TOPOLOGY.ORA file by using the Oracle Network Topology Generator. If the TNSNAMES.ORA file isn't found, you can use the Oracle Network Manager to create the file.
  5. Exit the installer after installation is complete.
  6. Log off from Windows NT and then log in again.
  7. If a local Oracle NT database is being accessed, you need to use Control Panel's Services tool to verify that the Oracle Service is started, and then start up the local NT database.

User and Repository Setup

Before Oracle Enterprise Manager is used, you must create a set of base tables that contain environment information for the managed databases-this is the Repository. You create the necessary tables in the Repository by using the SMPCRE.SQL and XPOCR.SQL scripts found in the $ORACLE_HOME/ rdbms/admin directory.

An Oracle user must be created with appropriate permissions to access the Repository before the scripts are run. For each user that needs to access the console, a separate Repository must be created and setup scripts must be run.

Console and repository compatibility
The Repository must be compatible with the version of the Oracle Enterprise Manager. If the Repository version is older or newer than the console version, you must install a more recent compatible version of Enterprise Manager.

Set up the user and Repository

  1. Create a new user with Server Manager:
SVRMGR> create user sysman identified by sysman

  1. Grant this user the same privileges as SYSTEM:
SVRMGR>grant dba to sysman

  1. Connect as the new user:
SVRMGR> connect sysman/sysman@testdb

  1. Execute SMPCRE.SQL and XPOCR.SQL to build the tables and views required by the console and Expert:

The user sysman can now log in to the Oracle Enterprise Manager.

Starting the Intelligent Agent and the Listener

For Enterprise Manager to connect and work successfully, the intelligent agent and the listener must be started on the server. For the client to communicate with the server, the communication daemon must be running.

Starting the daemon
The communication daemon is started and shut down automatically when Enterprise Manager is started.

The following shows how to run the agent and the listener:

Start the agentc: > net start oracleagent
Shut down the agentc: > net stop oracleagent
View agent's statusc: > net start
Start the listener on UNIX$ lsnrctl start testdblsnr
Shut down the listener on UNIX$ lsnrctl stop testdblsnr

Start/stop the listener in Windows NT
Use the Control Panel's Services tool to start and stop the listener in Windows NT.

Testing the Configuration

Test the configuration

  1. Shut down the listener and the agent.
$ lsnrctl stop testlsnr
$ lsnrctl dbsnmp_stop

  1. Start up and log in to Enterprise Manager. You should get the message ORA-12224: TNS: no listener occurs, and login is not possible. Enterprise Manager requires that the listener run on the server at all times.

Logging in to OEM doesn't require the agent to be running
Log in is possible without the agent running, because the agent is required only for jobs and events submitted or returned by the remote database or the console.

  1. Start up the listener:
$ lsnrctl start testlsnr

  1. Double-click the Oracle Enterprise Manager icon from Program Manager and log into the database as sysman connected as sysdba (see Figure 4.3).

Figure 4.3 : Log in to OEM by providing the information requested.

The username to use for connecting to the database (for example, sysman)
The password for the username
The Net8 service name for the database to which you're connecting
Connect as Normal, SYSOPER, or SYSDBA

Setting Up Preferred Credentials

You set up preferred credentials to avoid retying the service name, service type, and username for each database, listener, or node that the user intends to access. The Preferred Credentials page of the User Preferences dialog box shows the list of databases, listeners, and nodes in the network (see Figure 4.4).

Figure 4.4: You can set preferred credentials from the console.

Set the preferred credentials

  1. From the console's File menu, choose Preferences.
  2. Select any service from the list of entries in the dialog box and populate the Username, Password, Confirm, and Role text boxes.

Setting Up Security

The following operations on a remote instance require that security be set up for Enterprise Manager users:

Set up remote security

  1. Create a password file by connecting to the oracle user account, changing to the ORACLE_HOME/dbs directory, and then using the orapwd utility in UNIX:
$ orapwd file=orapwtestdb password=testpass entries=10
    In this example, the SID is assumed to be testdb.

  1. Grant appropriate roles:
SVRMGR> grant sysdba to sysman
SVRMGR> grant sysoper to sysman

  1. Edit the INIT.ORA file to add the following entry:

  1. Shut down the instance.

At this point, the database instance can be shut down from Enterprise Manager, but local security needs to be set up on Windows NT clients to start up the database from OEM.

Set up local security

  1. Download the INIT.ORA and CONFIG.ORA files from the server and copy them into the \OEM_directory\dbs directory on the Windows NT client.
  2. On the client, edit the INIT.ORA file by using any text editor like Notepad, and change the "ifile" entry to the directory in which the CONFIG.ORA file is located, with the "ifile" set to the CONFIG.ORA file.
  3. Restart the Enterprise Manager.

Examples of Client Files Required by Enterprise Manager

OEM uses several files on the client side-SQLNET.ORA, LISTENER.ORA, and TOPOLOGY.ORA-in the $ORACLE_HOME/network/admin directory. SQLNET.ORA contains optional parameters that can be used for tracing Net8 connections, whereas LISTENER.ORA is used to provide the address on which the listener listens.



ORACLE_HOME is set to c:\orant
Because the default domain and zone are set to world, the service names in TNSNAMES.ORA should have world tagged to them



The domain world must match the SQLNET.ORA file
Should match the port in the SNMP.ORA file
Should match the port in the LISTENER.ORA file
The database and SID name is test

The port numbers in TNSNAMES.ORA must be unused by any other service and must be valid port numbers as per TCP/IP standards.



Should match agent name in TNSNAMES.ORA
Should match listener name in LISTENER.ORA
Database name should match the one in TNSNAMES.ORA

Examples of Server Files Required by Enterprise Manager

The agent uses several files on the server side-SQLNET.ORA, TNSNAMES.ORA, and LISTENER.ORA-that reside in the $ORACLE_HOME/network/agent directory. SQLNET.ORA contains optional parameters that can be used for tracing Net8 connections, whereas LISTENER.ORA is used to provide the address on which the listener listens.




#Database Addresses



Listener name, domain, host name, and SID are the same in all the other files
Listener name, domain, host name, and SID are the same in all the other files
Must match the port in TNSNAMES.ORA on the client and server machines
Listener name, domain, host name, and SID are the same in all the other files



Listener name, SID, and host name are the same in the other files
Must match exactly with agent address in TNSNAMES.ORA on client machine

Basic Management Tasks with OEM

As an Oracle DBA, you'll be performing several tasks on a daily basis on OEM, such as starting up shutting down the database and managing users.

Starting and Stopping Your Database

After you set up remote and local security, you can start or shut down an Oracle database from the Enterprise Manager console. To start up or shut down a database, you must have the SYSOPER or SYSDBA role.

Start up the database

  1. Start up and log in to the Enterprise Manager.
  2. Double-click the database object to be started.
    If you haven't set up preferred credentials for this host, you'll get an error message. Type the correct username and password each time, or set up preferred credentials.
  3. In the database property sheet that appears, choose the Startup option.
  4. Click the appropriate startup option and specify the location of the INIT.ORA file.
  5. Click the startup button.

Shut down the database

  1. Start up and log in to Enterprise Manager.
  2. Double-click the database object to be stopped.
    If preferred credentials haven't been set up for this host, an error message occurs. Type the correct username and password each time, or set up preferred credentials.
  3. In the database property sheet that appears, choose the Shutdown option.
  4. Click the appropriate shutdown option and then click the shutdown button.

Managing Users and Privileges

You can easily manage users and privileges by using OEM's Security Manager component (see Figure 4.5). You can manage user information for several databases from one centralized location.

Figure 4.5 : Security Manager enables you to manage users, profiles, and roles.

After Security Manager successfully connects to the database, you see a tree structure with three context-sensitive objects. The database name is displayed next to the database container, and the Users, Roles, and Profiles containers branch from the current database container.

You can use Security Manager's User menu to create, edit, or remove existing users on a database.

Manipulate roles and profiles from the menus
Roles and profiles can also be similarly created, edited, and removed by using the Roles and Profiles menus.

Create a user

  1. From the User menu choose Create.
  2. In the Create User property sheet, enter the new user's username in the Name text box (see Figure 4.6).
    Figure 4.6 : You can create users from the property sheet.
  3. In the Authentication section, enter a password and then re-enter to confirm it.
  4. Choose the appropriate default and temporary tablespaces for the user from the Tablespaces section.
  5. Click Create.
  6. Verify the user creation by checking the Users object in Security Manager's tree structure. This verification can also be done by logging in as the new user with the password.

Quick-edit a user

  1. Right-click the username to be modified.
  2. Select Quick Edit from the pop-up menu.
  3. Make the desired changes to the quotas, privileges, and roles.
  4. Click OK.

Remove a user

  1. Select the username to be removed.
  2. From the User menu, choose Remove.
  3. In the confirmation dialog box, click Yes.

The user can also be removed by right-clicking the highlighted username and choosing Remove from the pop-up menu.

The User menu can be used to give privileges to users.

Assign privileges to users

  1. From the User menu choose Add Privileges to Users.
  2. In the Add Privileges to Users dialog box (see Figure 4.7), select the user to which privileges are to be granted. Ctrl+click additional users in the list to select more than one user.
    Figure 4.7 : Privileges can be assigned to users in the Add Privileges to Users dialog box.
  3. Select the Privilege Type (Roles, System, or Object).
  4. Select the privileges to be granted. Ctrl+click additional privileges in the list to select more than one privilege.
  5. Click Apply.

The Security Manager's Profile menu can be used to assign existing profiles to existing users.

Assign profiles to users

  1. From the Profile menu choose Assign Profile to Users.
  2. In the Assign Profile dialog box (see Figure 4.8), select the user or users to whom profiles are to be assigned.
    Figure 4.8 : Users can be assigned profiles in the Assign Profile dialog box.
  3. Select the profile to assign.
  4. Click Apply.
  5. Additional profiles can be assigned by repeating steps 3 and 4. Click OK when all profiles are assigned.

Using OEM's Navigator Menu to Manipulate Users and Privileges

Create a user with the Navigator menu
  1. In OEM's navigator tree, click the + to the left of the Databases folder.
  2. Click the + to the left of the database name.
  3. Click the + to the left of the Users folder.
  4. From the Navigator menu, select Create User.
  5. Enter the User information and click OK.

Copy a user between databases

  1. In OEM's navigator tree, click the + to the left of the Databases folder.
  2. Click the + to the left of the database name.
  3. Click the + to the left of the Users folder.
  4. Select the username to be copied.
  5. Drag and drop the username from one database to the other database folder.

Manage database user properties such as quotas, roles, and privileges

  1. In the navigator tree, click the + to the left of the Database folder.
  2. Click the + to the left of the database name.
  3. Click the + to the left of the Users folder.
  4. From the Navigator menu, select Alter User.
  5. On any of the four tabbed pages (General, Quotas, Privileges, or Default Roles), select the desired types.
  6. Click OK.

Managing Database Storage

You can use Storage Manager (see Figure 4.9) to perform administrative tasks associated with managing database storage, such as managing tablespaces and rollback segments and adding and renaming data files.

Figure 4.9 : You can use Storage Manager to manipulate tablespaces, data files, and rollback segments.

You can use Oracle's Tablespace Manager to monitor and manage database storage. It can be used to display graphically how storage has been allocated for the database segments, to defragment segments, and to coalesce free adjacent blocks.

Monitoring Tablespaces

Tablespace Manager's main window includes a tree list on the left and a drill-down on the right for a detailed view. You use the Tablespace Manager as follows:

Performing Advanced Management Tasks with the Performance Pack

As a DBA, you should frequently monitor your system resources to identify contention. OEM provides various predefined charts that can help you in monitoring the usage of different resources that can contribute to contention. (For additional information on how to identify and reduce various types on contention, see Chapter 21, "Identifying and Reducing Contention.") Three resources need to be carefully monitored:

My tuning philosophy
Performance tuning shouldn't be treated as a reactive strategy; instead, it should be a preventive action based on trends detected through analysis by using tools such as the Performance Pack.

The Performance Pack is a value-added component of the Oracle Enterprise Manager. It provides various tools to monitor and tune the performance of your database. It's important to understand that taking a point-in-time snapshot of the system doesn't do performance tuning, but it's a way to take into consideration the system performance over a period of time.

You can perform three different types of tuning by using the Performance Pack components (see Table 4.5).

Table 4.5  Types of tuning available through the Performance Pack
Tuning Type
Routine TuningUsed to identify and solve potential problems before they occur
Focused TuningUsed to resolve known performance problems
What-If TuningUsed to determine what would happen if a particular configuration change is made

The Performance Pack provides several tools (see Table 4.6) to capture, store, and analyze information so you can improve overall performance.

Table 4.6  Performance Pack components and their functions
Performance ManagerDisplays tuning statistics on contention, database instance, I/O, load, and memory within predefined or customized charts
Oracle ExpertCollects and analyzes performance-tuning data on predefined rules, generates tuning recommendations, and provides scripts that help with the implementation of tuning recommendations
Oracle TraceCollects performance data based on events and generates data for the Oracle Expert
Oracle TopSessions MonitorDisplays the top 10 sessions based on any specified sort criteria
Tablespace ViewerDisplays the free space left on each data file
Oracle Lock ManagerDisplays the blocked and waiting sessions
Oracle Advanced EventsMonitors the specified conditions in the databases, nodes, and networks

To start the performance-monitoring applications from the OEM console, use the Performance Pack launch palette or the Performance Pack option on the Tools menu.

Using Oracle Performance Manager

Performance Manager is a tool for monitoring database performance in real-time. It provides a number of predefined charts for displaying various statistics in different formats, including tables, line charts, bar charts, cube charts, and pie charts (see Figure 4.11).

Figure 4.11: Read consistency hit ratio is one type of information that can be charted.

Performance Manager's Display menu includes items for seven different categories of predefined charts. Table 4.7 describes these categories and the set of charts that focus on displaying information of that category.

Table 4.7  Charts used to identify contention
Charts Included in This Category
ContentionCircuit, Dispatcher, Free List Hit %, Latch, Lock, Queue, Redo Allocation Hit %, Rollback NoWait Hit %, and Shared Server
Database_InstanceProcess, Session, System Statistics, Table Access, Tablespace, Tablespace Free Space, #Users Active, #Users Waiting for Locks, and #Users Running
I/OFile I/O Rate, File I/O Rate Details, Network I/O Rate, and System I/O Rate
LoadBuffer Gets Rate, Network Bytes Rate, Redo Statistics Rate, Sort Rows Rate, Table Scan Rows Rate, and Throughput Rate
MemoryBuffer Cache Hit %, Data Dictionary Cache Hit %, Library Cache Hit %, Library Cache Details, SQL Area, Memory Allocated, Memory Sort Hit %, Parse Ratio, and Read Consistency Hit %
Overview#Users Active, #Users Logged On, #Users Running, #Users Waiting, Buffer Cache Hit, Data Dictionary Cache Hit, File I/O Rate, Rollback NoWait Hit %, System I/O Rate, and Throughput Rate
User-DefinedCharts created by the user

By default, information in the predefined charts is presented in the following manner:

The overview charts are a set of 12 predefined charts that give a good overall picture of the system (see Table 4.8).

Table 4.8  Predefined charts
Number of Users ActiveShows the number of users actively using the database instance. Obtains information from the V$SESSION view.
Number of Users Logged OnShows the number of concurrent users logged on to the database instance, regardless of whether any activity is being performed. Obtains information from V$LICENSE.
Number of Users RunningShows the number of concurrent users logged on to the database instance and now running a transaction. Obtains information from V$SESSION_WAIT.
Number of Users WaitingShows the number of users now waiting. Obtains information from V$SESSION_WAIT.
Buffer Cache Hit %Shows the buffer cache hit percentage. Obtains information from V$SYSSTAT.
Data Dictionary Cache HitShows the Data Dictionary cache hit. Obtains information from V$ROWCACHE.
File I/O RateShows the number of physical reads and writes per second for each file of the database instance. Obtains information from V$DBFILE.
Rollback NoWait Hit %Shows the hits and misses for online rollback segments. Obtains information from V$ROLLSTAT.
System I/O RateShows I/O statistics including buffer gets, block changes, and physical reads per second for the database instance. Obtains information from V$SYSSTAT.
Throughput RateShows the number of user calls and transactions per second for the instance. Obtains information from V$SYSSTAT.

Get an overall picture of activity on a database with the Overview chart

  1. In the navigator window, select the ORCL database and then click the Oracle Performance Manager icon.
  2. From the Monitor menu, click Display and then choose Overview.

Monitor disk access, resource contention, and memory utilization

  1. Launch the Oracle Performance Manager in the context of the ORCL database, as explained in step 1 of the previous section.
  2. From the Charts menu choose Define Window.
  3. In the Window Name text box, provide a unique name.
  4. Scroll through the list of available charts, select the chart you want, and click the << button.
  5. Repeat step 4 for all the charts you need, and then click OK.

If the predefined charts don't suit your needs, you can create your own charts and save them for future use.

Creating your own charts

  1. From the Charts menu, choose Define Charts.
  2. Click the New Chart button.
  3. Enter a name for the new chart.
  4. In the SQL Statement text box, enter a statement that will gather the statistics to display in the chart.
  5. Click the Execute button.
  6. Verify the results in the results field.
  7. On the Display Options page, enter the required information for each variable you want to display and click the Add button.
  8. Click the Apply button.
  9. Click OK.
  10. From the File menu choose Save Charts, and save the chart in the Repository.

Recording Data for Playback

You can choose to record data in a chart for analysis at a later time. The collection size varies based on the polling interval, database activity at the time, and the collection interval.

Collect historical data

  1. Display the charts from which you want to collect data.
  2. From the Record menu choose Start Recording.
  3. Provide a unique name in the Data Collection Name dialog box and click OK.
  4. When finished with the data collection, choose Stop Recording from the Record menu.
  5. Provide the database connect string in the Format/Playback Login dialog box.

Playback recorded data

  1. From the Record menu choose Playback.
  2. In the Format/Playback Login dialog box, provide the connect string on the database where the formatted data is saved.
  3. Select the data collection to play back and click OK.

Using Oracle Expert

Oracle Expert is a tool in the Performance Pack that you can use to tune a database. All tuning inputs and recommendations are stored in a tuning repository that allows the review and modification of the data and the rules at a later time. It has a knowledge base of tuning rules, designed through a tight relationship between the Oracle Server, Oracle Trace, and Oracle Expert development teams. It provides an explanation for all the recommendations it makes.

Don't run multiple sessions of Oracle Expert against the same repository
You can run multiple sessions of Oracle Expert against the same repository, but it's not recommended because it can lead to data conflicts between sessions.

You should use Oracle Expert to complement your tuning experience, not as a tool to replace your function as a database performance tuner. You should instead focus on what to do with the findings and suggestions provided by Oracle Expert and enhance the rules used by Oracle Expert in analyzing the performance data.

You can use Oracle Expert to tune the following:

Increase the information analyzed by Oracle Expert
Before performing instance SGA tuning, run XPVIEW.SQL (in $ORACLE_HOME\rdbms\ admin) against the database being tuned to get better recommendations from Oracle Expert. Doing so causes Oracle Expert to collect additional information about the data-base's shared SQL segment.

For Oracle Expert to perform data collection, the target database being tuned should have the following tables: dba_tab_columns, dba_constraints, dba_users, dba_data_files, dba_objects, dba_indexes, dba_segments, dba_ind_columns, dba_tables, dba_rollback_segs, dba_sequences, dba_views, dba_tablespaces, dba_synonyms, dba_ts_quotas, and dba_clusters. Oracle Expert doesn't collect information regarding index-only tables, partitioned tables, partitioned indexes, object types, object tables, and object views.

Use Oracle Expert to gather tuning information (general steps)

  1. Set the scope of the tuning session to tell Oracle Expert what aspects of the database to consider for tuning purposes. Oracle Expert collects the following categories of data: database, instance, schema, environment, and workload.
  2. The collected data is organized in a hierarchical format. You can view and edit the rules and attributes used by Oracle Expert.
  3. Oracle Expert generates tuning recommendations based on the collected and edited data. You can decide to use the recommendations, or ignore them and let Oracle Expert generate a new recommendation.
  4. When you're satisfied with the recommendations, you can let Oracle Expert generate parameter files and scripts to implement the chosen recommendations.

Don't tune the SYS or system schema
Don't use Oracle Expert to tune the SYS or system schema. You should let Oracle tune these items automatically.

Start an Expert Tuning session

  1. From the File menu choose New.
  2. Define the scope of the tuning session.
  3. On the Collect page, specify the amount and type of data to collect.
  4. Click the Collect button to acquire the required data.
  5. On the View/Edit page are the rules used by Expert. You can modify the rules based on your experience.
  6. On the Analyze page, click the Perform Analysis button to begin the data analysis.
  7. Select Review Recommendations to review the recommendations provided by Expert.
  8. If you agree with the recommendations, you can implement them by generating the requisite scripts and parameter files from the Implement page. If you don't agree with the recommendations, you'll have to change one or more rule and re-analyze (without recollecting) the data.

Have enough privileges to perform some functions
If the database management functions are grayed out from the menu bar, it may be because you aren't authorized to perform those functions. Reconnect as SYSOPER or SYSDBA.

The collection classes to use are determined by the selected tuning categories for a tuning session.

Reuse collected data
When tuning multiple categories, the common classes need to be collected only once because Oracle Expert will be able to reuse the data for analysis.

Start Oracle Expert

  1. In the OEM map or navigator window, select a database and then click the Oracle Expert icon in the Performance Pack palette. Or double-click the Expert icon in OEM's Program Manager.
  2. Connect to a tuning repository.
  3. From the File menu choose New to create a new tuning session.
  4. Enter the appropriate data in the dialog box pages.
  5. Click OK.

Permissions to use Oracle Expert
The user running Oracle Expert must have SELECT ANY TABLE privilege for the database in which the repository is stored.

Using Oracle TopSessions

You can use the Oracle TopSessions utility to view the top Oracle sessions based on specified criteria, such as CPU usage and disk activity. Before running TopSessions for Oracle8, run $ORACLE_HOME/sysman/smptsi80.SQL to create all the supporting tables.

Identify Oracle sessions that use the most CPU

  1. In OEM's navigator window, select the ORCL database and then click the Oracle TopSessions icon.
  2. On the Sort page of the Options property sheet (see Figure 4.12), change the Statistics Filter to User and the Sort Statistic to CPU Used by This Session.
    Figure 4.12: You can use the Sort page of the Oracle TopSessions Options dialog box to specify the criteria to use for monitoring sessions.
  3. On the Refresh page of the Options property sheet (see Figure 4.13), select Automatic for the refresh type, set the Refresh Interval to 10, and reset the Minutes and Hours to 0.
    Figure 4.13: The Refresh page of the Options dialog box can be used to change the refresh type and refresh interval.
  4. On the Count page of the Options property sheet (see Figure 4.14), select the Display Top N Sessions button and change the count to 10.
    Figure 4.14: The Count page of the Options dialog box can be used to specify the number of sessions to track.
  5. Click OK to show the results (see Figure 4.15).
    Figure 4.15: Oracle TopSessions shows the results as specified by the resource usage criteria.

© Copyright, Macmillan Computer Publishing. All rights reserved.