Oracle Enterprise Manager Oracle Expert User's Guide

Library

Product

Contents

Index

Introduction

Advantages of Using Oracle Expert
What Is Database Tuning?
What Are the Types of Performance Tuning?
Ways to Use Oracle Expert
Sample Tuning Session

Oracle Expert is a software tool you use to optimize the performance of your database environment. Oracle Expert assists with the initial configuration of the database and with the collection and evaluation of the performance characteristics of existing databases.

The topics in this chapter include:

Advantages of Using Oracle Expert

Oracle Expert provides many advantages. Oracle Expert:

uses trends to ensure continued performance as the database environment changes over time
provides consistent and complete recommendations
performs interdependency checking during analysis
detects and reports symptoms of poor performance
sifts through vast quantities of data quickly to identify performance problems
identifies situations where advanced performance features of the database can be applied

Oracle Expert also serves as:

an educational tool that explains all of its tuning recommendations through detailed reports
a methodology tool that guides the DBA, analyst, and designer through the process of improving the performance of an Oracle database
a maintenance tool that uses historical trends to inform the DBA of degrading performance and impending bottlenecks
an information tool that collects tuning data from numerous sources. This data not only feeds the tuning process, but can be displayed, edited, and reported on.

What Is Database Tuning?

The database tuning process consists of tasks such as:

balancing the different types of competing resources in the database environment so that the most important applications have priority access to the resources they need to run
identifying resource bottlenecks and eliminating them
optimizing the use of existing resources within the database environment
taking advantage of database features for the types of work performed on the database

Database Tuning Issues

Even though you may realize you have a poorly tuned database environment, you may not have the luxury to resolve the problem, because:

Tuning the database environment may take more time than you can afford to spend on the task.
Management may not want to disrupt business operations.
You may not want to risk introducing further performance degradation.
The complexity of the problem may be beyond the skill level of the person assigned the problem.
The tools may not be available to identify the cause of the problem.

Resolving Tuning Issues

To resolve tuning issues, there are at least two important requirements:

extensive tool expertise
consistency in tuning

A significant amount of a database expert's time is spent collecting and sifting through vast quantities of information. Collecting information for a normal database tuning session requires knowledge of many tools.

Also, the effectiveness of the database tuning effort can vary a great deal depending on the expertise of the person doing the job. To further complicate the issue, with database tuning there often is no exact solution to a specific performance problem.

The tuning recommendations produced by Oracle Expert are both consistent and accurate. Oracle Expert can sift through volumes of tuning information without missing relevant symptoms. It automates many of the repetitive and time-consuming aspects of database tuning, thus reducing the time required to get meaningful performance improvements. Finally, Oracle Expert manages the history of the collected information over time.

What Are the Types of Performance Tuning?

Oracle Expert supports the following types of performance tuning:

instance tuning
application tuning
structure tuning

Instance Tuning

Instance tuning optimizes the instance parameter settings. The instance tuning recommendations are based on the user load on the instance being tuned and the hardware resources available. The instance parameters you can tune are:

SGA (System Global Area) parameters

: These parameters affect the total size of the instance's System Global Area.

I/O (input/output) parameters

: These parameters affect the throughput or distribution of I/O for the instance.

parallel query parameters

: These parameters are specific to the parallel query behavior of the instance. These parameters are relevant only when the parallel query option is installed.

sort parameters

: These parameters influence how the Oracle Server performs sort operations on behalf of the user.

Application Tuning

Application tuning evaluates a specific application or SQL statement. Application tuning consists of:

SQL tuning

: During SQL tuning, Oracle Expert identifies similar statements that prevent SQL statements from being reused in the shared pool because of differences in case and/or spacing.

access methods

: During access methods tuning, Oracle Expert determines the optimal indexes that should exist on a table.

Structure Tuning

Structure tuning optimizes access to a specific table in the database. Structure tuning consists of:

sizing

: Oracle Expert makes recommendations for some of the default storage parameters of a tablespace. These parameters provide default initial allocation and growth rate of segments created within that tablespace.

placement

: Oracle Expert makes recommendations to ensure that separate tablespaces exist for different types of segments and that database users have been assigned appropriate default and temporary tablespaces.

Ways to Use Oracle Expert

Oracle Expert is as flexible as you are. Oracle Expert can help you achieve any and all of the following:

comprehensive tuning

: Deals with the optimization of all aspects of the database environment and maintains the performance of the database over time.

focused tuning

: Delves into the resolution of a known performance problem. This resolution occurs as you choose the appropriate tuning categories and focus on the particular problem.

initial configuration

: By providing Oracle Expert with as much information as possible at the time of configuration, you enable Oracle Expert to generate a database configuration that minimizes the amount of up-front work you must do to configure your database. You can use a similar production database as a guide to your initial database configuration.

Sample Tuning Session

Oracle Expert provides you with a sample tuning session called "Personnel session." Personnel session is a tuning session against a non-existent Personnel database. It contains example data used by Oracle Expert, such as, database, instance, schema, table, column, synonym, and SQL workload information.

With this sample tuning session, you can experiment with the View/Edit page, analysis, reviewing recommendations, and generating implementation files.

To load this sample, choose Help=>Load Sample from the Oracle Expert menu bar. The Personnel tuning session displays in the tree list.

If you want to experiment with collecting data for this tuning session, you must use the XPPSO.XDL file in the ORANT\SYSMAN\EXPERT\SAMPLE directory.

When setting collect options for database, instance, schema, or workload, choose to collect from File, selecting the ORANT\SYSMAN\EXPERT\SAMPLE\XPPSO.XDL file as the source. This file contains the information needed for all the collection classes.

Prev Next

Library

Product

Contents

Index