Oracle Enterprise Manager Oracle Expert User's Guide

Library

Product

Contents

Index

Oracle Expert Methodology

Steps in Oracle Expert Methodology
Setting the Scope of the Tuning Session
Collecting the Data
Viewing and Editing the Collected Data
Analyzing the Data
Reviewing the Oracle Expert Recommendations
Implementing the Recommendations
Inputs and Outputs

Oracle Expert performs tuning by following a designed methodology.

The topics in this chapter include:

Steps in Oracle Expert Methodology

The Oracle Expert methodology includes the following steps (see Figure 2-1).

Figure 2-1: Implementation of Oracle Expert Methodology

1.: setting the scope of the tuning session
2.: collecting the data
3.: viewing and editing the collected data
4.: analyzing the data and generating recommendations
5.: reviewing the Oracle Expert recommendations
6.: generating scripts for implementing the recommendations

Setting the Scope of the Tuning Session

When you set the scope of a new tuning session, you are telling Oracle Expert exactly what you want to tune. Your choices can be one or more of the following:

instance tuning

: Use this option to tune instance parameters. These parameters include: SGA, I/O, parallel query, and sort parameters.

application tuning

: Use this option to evaluate specific applications and SQL statements. Application tuning consists of SQL tuning and access methods tuning.

structure tuning

: Use this option to optimize access to specific tables in a database. Structure tuning consists of sizing and placement.

Collecting the Data

To give you the best performance recommendations, Oracle Expert collects the following classes of data:

database (database name and version, database users, tablespaces, and public synonyms)
instances (parameters and statistics)
schemas (tables, constraints, indexes, clusters, views, and synonyms)
environment (logical device and system information)
workload (can include data collected by Oracle Trace, or the SQL cache)

Viewing and Editing the Collected Data

Once you have collected the various pieces of tuning data, you have the opportunity to view and edit that data. The data appears organized as follows:

database
system
logical device (disks)
workload application (business unit, transaction, and request)

You can also edit the rules and attributes associated with the previously listed data.

Analyzing the Data

Once you have collected and edited the data as needed, you can have Oracle Expert perform the analysis to generate tuning recommendations.

During the analysis, Oracle Expert evaluates the collected data in conjunction with all its rules and provides the best performance recommendations possible.

Reviewing the Oracle Expert Recommendations

Once Oracle Expert has analyzed the data, you have the option of reviewing the recommendations and deciding which to accept.

For example, say that Oracle Expert recommends that you increase the shared_pool_size parameter from 300,000 to 500,000. If you choose not to use this recommendation, analyze the data again. Oracle Expert keeps track of the recommendations you have accepted and takes into account interdependencies among the collected data before generating new recommendations.

Implementing the Recommendations

When you are ready to implement the Oracle Expert recommendations, you can use Oracle Expert to create parameter files and implementation scripts. These files and scripts allow you to implement the Oracle Expert recommendations at your convenience. These files and scripts minimize the risk of introducing any new problems during implementation, and they reduce the level of expertise required to implement the recommendations.

Inputs and Outputs

While following this methodology, Oracle Expert takes collected input, processes this input through various rules and algorithms, and creates tuning recommendations, tuning scripts, and reports. (See Figure 2-2.)

Figure 2-2: Overview of Oracle Expert

Tuning Inputs

Oracle Expert uses the following input data to generate effective tuning recommendations:

Database class

: The Database class data contains database-wide attributes of the database, such as the database name and version, installed options, users, tablespaces, and public synonyms.

Instance class

: The Instance class data refers to the instance parameters and instance statistics information Oracle Expert collects from the database's V$ tables for the instance you are tuning.

Schema class

: The Schema class data refers to the tables, indexes, clusters, views, and constraints information collected by Oracle Expert.

Environment class

: The Environment class data refers to the physical hardware resources available to the database; for example, logical device data and system data (which includes memory and CPU data).

Workload class

: Workload class data refers to the nature, frequency, and importance of transactions that access the database. This SQL activity against the database is collected either by Oracle Trace or through a SQL cache collection.

rules

: Rules are pieces of knowledge used by Oracle Expert to analyze collected data for a tuning session. By changing rule values, you influence the tuning recommendations made by Oracle Expert.

control parameters

: Control parameters provide Oracle Expert with a higher level of guidance about how the database is used. This is data that cannot be collected directly from the database. For example, the Oracle Server has specific features that are designed to optimize performance in a DSS (decision support system) environment. If you set Workload class to DSS, Oracle Expert knows that these features are applicable.

Generated Output

With inputs such as database, instance, schema, environment, workload, and rules, Oracle Expert generates the output needed for solving database performance problems. Oracle Expert generates the following:

reports

Oracle Expert generates the Analysis and Session Data reports.

Analysis report

: Describes the tuning recommendations made by Oracle Expert. This report provides a detailed explanation of what Oracle Expert evaluated, how Oracle Expert interpreted the collected data and why, and any risks involved in implementing the recommendations.

Session Data report

: Provides summary database information. It also provides detailed information about instances, database users, tablespaces, schemas, environment, workload, and rules.

implementation files

In addition to the reports, Oracle Expert generates files to help you implement its recommendations. These files include:

implementation script (.TXT file)

: Contains SQL for implementing structure and/or index recommendations. Some recommendations, such as table relocation to a new tablespace, are described textually and performed by the DBA.

instance parameter file subsets (.ORA file)

: Contains instance parameter values that Oracle Expert recommends for improving the performance of a particular instance. Oracle Expert generates these subsets, which you can merge into the existing INIT.ORA file for the instance.

Prev Next

Library

Product

Contents

Index