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

Advantages of Using Oracle Expert

Oracle Expert provides many advantages. Oracle Expert:

• helps 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 may be applied

Oracle Expert also serves as:

• an educational tool that explains 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.
• 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 and eliminating resource bottlenecks
• 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 that you have a poorly tuned database environment, you may not be able 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?

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:

• 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

  Oracle Expert can optimize a newly created database using additional information supplied about the workload, physical memory, and expected transaction volumes.

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.

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.