1
Introduction to the Oracle Tuning Pack

The Oracle Tuning Pack contains six applications to help users tune all aspects of their database environment; from identifying and correcting problem SQL statements to adjusting the instance parameters for the database. The Tuning Pack applications are:

Oracle SQL Analyze	Reorg Wizard
Oracle Expert	Tablespace Map
Oracle Index Tuning Wizard	Outline Editor (New in Oracle9i)
Outline Management (New in Oracle9i)

.

The Oracle Tuning Pack enables users to proactively tune their database environment. The applications within the Oracle Tuning Pack can be used to:

• Identify and resolve performance problems
• Identify the cause of a problem that has been reported and get advice on the best way to correct it
• Maintain existing performance
• Avoid performance problems by practicing proper maintenance operations
• Identify potential tuning problems before they occur
• Provide tools and methodologies to establish and maintain database performance

Overview of the Tuning Pack Applications

What's New in Tuning Pack for 9i

In the SQL Tuning area, the following enhancements were added:

• Integrated TopSQL charts from SQL Analyze
• Additional SQL statistics such as cost and byte count per operation
• Optional verification of the index solution by the optimizer
• Virtual Index Wizard provides "What If" capability
• Graphical Statistics display from the statement comparison dialog
• Comprehensive HTML reports

In the Space Management area, the following enhancements were added:

• Tablespace Map now offers an optional tabular view of tablespace metrics for visually impaired users
• Tablespace Map and Reorg Wizard are now browser-enabled
• Reorg Wizard gives you the option of reorganizing for availability or for speed
• Reorg Wizard support for online table movement
• Reorg Wizard has improved object navigation and selection

In the Plan Stability area, two new applications have been added:

• Outline Management allows you to manage stored outlines
• Outline Editor allows you to change an outline's join order, access methods, and other outline properties to optimize its performance.

Oracle SQL Analyze

Oracle SQL Analyze can be used to identify and correct problem SQL statements. SQL Analyze allows you to sort SQL statements by various performance metrics, and acquire detailed tuning information for any SQL statements. Wizards are included which guide the user through a SQL optimization methodology or add hints to existing SQL statements.

Oracle Expert

Oracle Expert automates the database tuning process. Oracle Expert provides a methodology that is used to collect, evaluate, verify, and implement database tuning changes. Tuning areas covered by Oracle Expert include instance parameter tuning, database structure and placement tuning, index tuning, and SQL statement reuse evaluation.

Oracle Index Tuning Wizard

The Index Tuning Wizard provides an easy solution to the Index Tuning problem. It automatically identifies tables which would benefit from index changes, presents its findings for verification, and allows you to implement the index tuning recommendations.

Reorg Wizard

The Reorg Wizard can help you maintain a well performing database by correcting space usage problems. Over time, database performance can be affected by problems such as row chaining and/or migration and index stagnation. The Reorg Wizard can eliminate space problems by improving database space usage. The Reorg Wizard also allows you to change an object's storage settings and location.

Tablespace Map

The Tablespace Map provides a quick, graphical presentation of important tablespace information, such as the physical layout of a tablespace and an analysis of potential tablespace problems. Tablespace Map also provides a list of all of the segments for a selected tablespace and a graphical display of the extents that make up those segments. Tablespace Map allows you to perform a problem analysis on each segment within the tablespace by using the Segment Analysis tool. This will search the tablespace for problems such as excessive row chaining and/or row migration and overextended objects.

Outline Management

Outline Management is an advanced Oracle9i application that allows the user to manage plan stability outlines. The application also allows you to create, drop, and view outlines.

Outline Editor

Outline Editor is an advanced Oracle9i application that allows the user to control the optimizer behavior by modifying the optimizer mode, join order, or index usage without having to change the statement in the application code.

Choosing the Right Application

The Oracle Tuning Pack supports both proactive and reactive tuning. Proactive tuning adjusts the database environment before performance degradation becomes significant enough to be noticed. Reactive tuning occurs when a database performance problem is reported that needs immediate correction.

The first step in using the Oracle Tuning Pack involves determining which resource to tune. If you suspect that inefficient SQL is the cause of the problem, SQL Analyze can be used to identify the most resource intensive SQL statements, and help you to write them more efficiently. If you suspect a problem with your database resources, such as inefficient use of memory or data storage, Oracle Expert or the Reorg Wizard can help optimize resource usage. You can view specific storage characteristics with the Tablespace Map.

Index Tuning is task focused and solves specific tuning problems. The wizard can be used for both proactive and reactive tuning. The Index Tuning wizard will identify tables with inefficient indexes and make recommendations that will improve access to those tables.

Oracle Expert, SQL Analyze, and the Index Tuning wizard share SQL statements collected in the SQL History. The purpose of the SQL History is to gather and provide relevant SQL statements to any Oracle Tuning Pack application that makes tuning recommendations. The SQL History ensures consistency in tuning recommendations between the different Oracle Tuning Pack applications. The SQL History also reduces Tuning Session demand on production database resources, since SQL statements do not need to be collected separately for each application.

Additional details on how to use each Oracle Tuning Pack application can be found in the application-specific sections of this manual.

Starting the Tuning Pack Products

Once you have determined which application you would like to use, you have several options for starting the products in the pack.

Option One

From the Oracle Enterprise Manager console, click on the Tuning Pack tool drawer and select an application icon or from the Oracle Enterprise Manager console, select Tools=>Tuning Pack.

Figure 1-1 Tuning Pack Tool Drawer for Windows NT

Text description of the illustration tp_palet.gif

Option Two

From the Tools Menu, choose Tuning Pack and select a Tuning Pack application.

Using Help

Once you have started using the product, refer to the Online help for detailed information on the dialog boxes, fields, and concepts. For context sensitive help, position the cursor above an object and press F1.