Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Release 9.0.1

Part Number A86647-01


Book List



Master Index


Go to previous page Go to next page

Introduction to Oracle Expert

Oracle Expert is a software tool for optimizing the performance of your database environment. Oracle Expert assists with the initial configuration of a database and with the collection and evaluation of performance characteristics of existing databases.

Oracle Expert automates the process of collecting and analyzing performance tuning data and provides expert database tuning recommendations. Additionally, Oracle Expert generates scripts that assist with the implementation of tuning recommendations.

The topics in this chapter include:

Advantages of Using Oracle Expert

Oracle Expert provides many advantages. Oracle Expert:

Oracle Expert also serves as:

What Is Database Tuning?

The database tuning process consists of tasks such as:

Database Tuning Issues

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

Resolving Tuning Issues

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

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?

Tuning an Oracle database can involve tuning the application, the instance, and the space usage in a database.

Whether you are writing new SQL statements or tuning problematic statements in an existing application, application tuning can improve CPU response time, reduce disk I/O, and reduce memory resources. The methodology for tuning SQL involves identifying the statements that consume the most resources and then tuning these statements to use fewer resources. In general, a small number of SQL statements are responsible for most of the activity that occurs in the database. Rather than trying to completely understand an application, focus your tuning efforts on those statements or tables where the benefit of tuning will exceed the cost.

Approaches to SQL statement tuning include: determining which indexes should exist on a table, and identifying existing indexes that should be rebuilt to improve performance. You should also ensure SQL is shared effectively. Ineffective SQL sharing can result in unnecessary reparsing which requires more CPU usage.

Instance tuning can be used to solve a variety of problems such as inefficient memory allocation and I/O problems. Instance tuning involves tuning areas such as the redo log buffer, the shared pool, the buffer cache, and the sort areas. Instance tuning also tunes the log writer (LGWR) and database writer (DBWR) background processes.

Effective space management will improve database availability and reduce performance problems resulting from poor space utilization. When an object such as a table is created, space in the database is allocated for the data. Therefore, correct placement and sizing of these objects is essential.

Through the available tuning scopes, Oracle Expert supports the above tuning areas by checking for the following:

Type of Tuning  Tuning Scope 

Application Tuning 

SQL Reuse Opportunities

Optimal Data Access 

Instance Tuning 

Instance Parameter Optimizations 

Space Management 

Appropriate Space Management 

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:

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, environment, and SQL workload information.


The sample tuning session does not use a real instance. Therefore performing a collection from the sample simply reloads the data from the sample file. 

With this sample tuning session, you can experiment with the View/Edit page, analysis, reviewing recommendations, and generating script 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 $ORACLE_HOME\SYSMAN\EXPERT\SAMPLE directory.

When setting collect options for database, instance, schema, or workload, choose to collect from File, and select XPPSO.XDL file as the source. This file is located in the $ORACLE_HOME\SYSMAN\EXPERT\SAMPLE directory. The XPPSO.XDL file contains the information needed for all the collection classes.

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.


Book List



Master Index