1
Introduction

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
• What Is Database Tuning?
• What Are the Types of Performance Tuning?
• Ways to Use Oracle Expert
• Work In Progress Dialog Box
• Sample Tuning Session

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.
• an automation tool that assists the DBA by automating routine database maintenance and tuning tasks

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.

• operating system-specific parameters

  These parameters affect performance and are specific to certain hardware platforms.

• Oracle Parallel Server parameters

  These parameters influence the performance and configuration of a parallel server environment.

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.

Work In Progress Dialog Box

Oracle Expert provides a Work In Progress dialog box, as shown in Figure 1-1. Click the toggle button to display the Work In Progress dialog box. By default, the Work In Progress dialog box is displayed in the middle of the Oracle Expert main window. You can move the box to another location by dragging it.

You can toggle the Work In Progress dialog box on and off using the Work In Progress icon in the toolbar or by choosing View=>Work In Progress on the menu bar. You can also dismiss the Work In Progress dialog box by using the Close button in the upper right-hand corner of the Work In Progress dialog box.

Figure 1-1 Work in Progress Dialog Box

This dialog box provides information about the progress of Oracle Expert operations. The components of the Work In Progress dialog box from top to bottom are:

Name of Operation

The operation being performed displays above the Text Box. If no operation is in progress, this area is blank.

Working...

This word displays in the top-right corner whenever Oracle Expert is performing an operation.

Completed

This word displays in the top-right corner whenever Oracle Expert completes an operation.

Clear Button

Removes all text from the Work In Progress text box.

Text Box

Provides history of Oracle Expert tasks performed during the session. Once you close Oracle Expert, this information is removed.

Show Messages

This toggle box allows you to toggle on or off the display of messages in the text box.

Progress Bar

Displays at the bottom of the Work In Progress dialog box to show how far a lengthy operation has progressed. The progress bar disappears once the operation is completed. Note that not all operations have a progress bar.

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.

---