' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //-->

Using Oracle8

Chapter 4

Managing with Oracle Enterprise Manager (OEM)

• Introducing OEM Components
  • OEM Console
  • Common Services
  • Intelligent Agents
  • Application Programming Interface (API)
• Installing and Configuring OEM
  • Minimum Requirements
  • Compatibility Issues
  • Performing the OEM Installation
  • User and Repository Setup
  • Starting the Intelligent Agent and the Listener
  • Testing the Configuration
• Setting Up Preferred Credentials
• Setting Up Security
  • Examples of Client Files Required by Enterprise Manager
  • Examples of Server Files Required by Enterprise Manager
• Basic Management Tasks with OEM
  • Starting and Stopping Your Database
  • Managing Users and Privileges
  • Using OEM's Navigator Menu to Manipulate Users and Privileges
  • Managing Database Storage
• Performing Advanced Management Tasks with the Performance Pack
  • Using Oracle Performance Manager
  • Using Oracle Expert
  • Using Oracle TopSessions

• Install and configure Oracle Enterprise Manager
• Set up the Repository
• Manage users and privileges
• Manage storage
• Tune your database

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:

• Administer, diagnose, and tune multiple databases.
• Schedule jobs such as executing a SQL*Plus script on multiple nodes.
• Monitor objects and events such as database and node failures throughout the network.
• Integrate third-party tools.

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 Manager		Lock Manager
	Data Manager		TopSessions
	Instance Manager		Performance Manager
	Schema Manager		Oracle Trace
	Security Manager		Navigator window
	SQL Worksheet		Job Scheduling window
	Storage Manager		Event Management window

SEE ALSO
Using the Instance Manager,

The Enterprise Manager environment consists of the following major components:

• Oracle Enterprise Manager Console
• Services common to all OEM components, such as the Repository, discovery service, communication daemon, and job scheduling and event management systems
• Intelligent agent
• Integrated applications
• Application programming interfaces (APIs)
• Command Line Interface (CLI)
• Online help system

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.

Common Services

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

• The Repository is a set of tables in your schema, which can be placed in any Oracle database in the system. Information stored in the Repository includes the status of jobs and events, discovery cache, tasks performed, and messages from the notification queue in the communication daemon.
  To set up the Repository and manipulate it, you need to log on with an account that has DBA privileges. When logging in to Enterprise Manager, you're establishing a database connection into your Repository. At any given time, each user is connected to a single Repository. The connection to the Repository must be active during your working sessions. There can be more than one repository in the system. Repository tables can be installed on any Oracle database accessible to the console. A repository can be moved to another Oracle database. The Repository can be started or shut down from the Instance Manager but not from the console.

• The communication daemon is a multithreaded process that manages console communication activities. It's responsible for communicating with agents and nodes for job scheduling and event monitoring, queuing and retrying failed jobs periodically, service discovery, contacting nodes periodically to determine their status, and maintaining a cache of connections to agents on nodes.
• The Job Scheduling System enables you to schedule jobs on remote sites by specifying the task to perform, the start time, and the frequency of execution. The Job System isn't usable if Oracle Intelligent Agent isn't installed and configured.

• The Event Management System monitors events at remote sites, alerts you when a problem is detected, and optionally fixes it. In Windows NT, the application event log contains many of the errors detected by OEM.
• Security Services manages administrative privileges for nodes and services in the system. It manages a list of administrators who are notified when an event occurs.
• Service Discovery maintains an up-to-date view of the nodes and services being managed. The console's Navigator tree is populated with this information.

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:

• Execute jobs or events from the console or third-party applications can be sent to the intelligent agent for execution.
• Cancel jobs or events as directed.
• Run jobs, collecting results and queuing them for the communication daemon.
• Run autonomously without requiring the console or the daemon to be running.
• Autonomously detect and take reactive measures (as specified by the administrator) to fix problems.
• Autonomously perform specified administrative tasks.
• Check and report events to the communication daemon.
• Handle SNMP requests if supported on the agent's platform.

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.

Minimum Requirements

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

• Intel 486 PC or higher
• VGA video (SVGA strongly recommended)
• 32MB RAM
• CD-ROM drive
• Windows 95/NT-compatible network adapter
• 25MB of hard disk space for Oracle Enterprise Manager, Net8, and required Oracle support files
• 4MB of hard disk space for Oracle Enterprise Manager online documentation
• 15MB of hard disk space for OEM's Performance Pack
• Disk space for installing a local Oracle database or intelligent agent for Windows NT

The following minimum software resources are needed:

• Microsoft Windows NT version 3.51 or higher, or Windows 95
• TCP/IP services

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.

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:

• Network Manager 3.1 for Windows to generate Net8 files and TNSNAMES.ORA
• Oracle Topology Generator to generate TOPOLOGY.ORA
• ASCII editor

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:

• Sun SPARCstation
• Solaris version 2.4
• 32MB RAM
• Oracle Server version 7.3 or higher
• SQL*Net version 2 or higher (Net8)

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

• Run the CATSNMP.SQL script (found in the $ORACLE_HOME/rdbms/admin directory) from Server Manager to create a user for the intelligent agent and give that user DBA privileges.

SEE ALSO
Creating a user,
Granting privileges to roles,

• The SNMP.ORA file must be edited with any text editor. This file contains the address of the agent and other listener information, which is read by the agent. It resides in the $ORACLE_HOME/network/admin directory on the database server.

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.

Set up the user and Repository

1. Create a new user with Server Manager:

SVRMGR> create user sysman identified by sysman

2. Grant this user the same privileges as SYSTEM:

SVRMGR>grant dba to sysman

3. Connect as the new user:

SVRMGR> connect sysman/sysman@testdb

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

SVRMRG>@smpcre.sql
SVRMGR>@xpocr.sql

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.

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

Testing the Configuration

Test the configuration

1. Shut down the listener and the agent.

$ lsnrctl stop testlsnr
$ lsnrctl dbsnmp_stop

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

3. Start up the listener:

$ lsnrctl start testlsnr

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

• STARTUP
• SHUTDOWN
• ALTER DATABASE
• OPEN and MOUNT
• ALTER DATABASE BACKUP
• ARCHIVE LOG
• RECOVER
• All system privileges with ADMIN OPTION
• CREATE DATABASE
• Time-based recovery

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.

2. Grant appropriate roles:

SVRMGR> grant sysdba to sysman
SVRMGR> grant sysoper to sysman

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

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

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

SQLNET.ORA

===========================================================

===========================================================

	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

TNSNAMES.ORA

===========================================================

===========================================================

	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.

TOPOLOGY.ORA

===========================================================

===========================================================

	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.

SQLNET.ORA

===========================================================
AUTOMATIC_IPC=OFF
trace_level_server=off
TRACE_LEVEL_CLIENT=off
SQLNET.EXPIRE_TIME=0
NAMES.DEFAULT_DOMAIN=world
NAME.DEFAULT_ZONE=world
SQLNET.CRYPTO_SEED="-2089208790-14606653312"
===========================================================

TNSNAMES.ORA

===========================================================
#Database Addresses
test.world=(description=
        (address_list=
            (address=
                (community=tcp.world)
                (protocol=tcp)
                (host=fastmachine)
                (port=1701)
            )
        )
        (connect_data=
                (sid=test)
                (global_name=test.world)
        )
    )
===========================================================

LISTENER.ORA

===========================================================

===========================================================

	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

SNMP.ORA

===========================================================

===========================================================

	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.

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:

• Click the Tablespaces container to display the space usage for each tablespace in the instance.
• Double-click the Tablespaces container for additional information about the tablespaces.
• Click an individual tablespace container to display the Segments page for that tablespace. Clicking an individual segment graphically displays the space utilization for that segment.
• Click a data file container to find the space usage for a data file.
• Click an individual data file to display the Segments page for that data file (see Figure 4.10).
  Figure 4.10: Use Tablespace Manager to manage data files.

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:

• CPU. Every process that executes on the server needs some time slice of the CPU time to complete its task. Some processes need a lot of CPU time, whereas others don't. You should be able to identify your CPU-intensive processes.

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.

• Disk access. Every time a process needs data, it will first look at the buffer cache to see if the data is already brought in. If the data isn't found, the process will access the disk. Disk access is very time-consuming and should be minimized.
• Memory. Insufficient memory can lead to performance degradation. When the system falls short on memory, it will start paging and eventually start swapping physical processes.

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	Description
Routine Tuning	Used to identify and solve potential problems before they occur
Focused Tuning	Used to resolve known performance problems
What-If Tuning	Used 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

Component	Function
Performance Manager	Displays tuning statistics on contention, database instance, I/O, load, and memory within predefined or customized charts
Oracle Expert	Collects and analyzes performance-tuning data on predefined rules, generates tuning recommendations, and provides scripts that help with the implementation of tuning recommendations
Oracle Trace	Collects performance data based on events and generates data for the Oracle Expert
Oracle TopSessions Monitor	Displays the top 10 sessions based on any specified sort criteria
Tablespace Viewer	Displays the free space left on each data file
Oracle Lock Manager	Displays the blocked and waiting sessions
Oracle Advanced Events	Monitors 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

Category	Charts Included in This Category
Contention	Circuit, Dispatcher, Free List Hit %, Latch, Lock, Queue, Redo Allocation Hit %, Rollback NoWait Hit %, and Shared Server
Database_Instance	Process, Session, System Statistics, Table Access, Tablespace, Tablespace Free Space, #Users Active, #Users Waiting for Locks, and #Users Running
I/O	File I/O Rate, File I/O Rate Details, Network I/O Rate, and System I/O Rate
Load	Buffer Gets Rate, Network Bytes Rate, Redo Statistics Rate, Sort Rows Rate, Table Scan Rows Rate, and Throughput Rate
Memory	Buffer 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-Defined	Charts created by the user

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

• Charts showing rates per unit of time are presented as line charts.
• Charts showing ratios are presented as pie charts.
• Charts consisting primarily of text information are presented as tables.
• Charts displaying a large number of instances are presented as tables.

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

Chart	Description
Number of Users Active	Shows the number of users actively using the database instance. Obtains information from the V$SESSION view.
Number of Users Logged On	Shows 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 Running	Shows 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 Waiting	Shows 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 Hit	Shows the Data Dictionary cache hit. Obtains information from V$ROWCACHE.
File I/O Rate	Shows 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 Rate	Shows I/O statistics including buffer gets, block changes, and physical reads per second for the database instance. Obtains information from V$SYSSTAT.
Throughput Rate	Shows 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:

• Instance. It consists of tuning the SGA parameters (which affect the total size of the instance's system global area), I/O parameters (which affect the throughput or distribution of I/O for the instance), parallel query parameters, and sort parameters.
• Application. It consists of tuning SQL and access methods, which involves determining the indexes needed and creating, modifying, and deleting them as needed.
• Structure. It consists of sizing (recommendations for choosing storage parameters) and placement (recommendations on placing data files and partitioning segments).

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.