|Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Part Number A86647-01
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:
The Oracle Tuning Pack enables users to proactively tune their database environment. The applications within the Oracle Tuning Pack can be used to:
In the SQL Tuning area, the following enhancements were added:
In the Space Management area, the following enhancements were added:
In the Plan Stability area, two new applications have been added:
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 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.
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.
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.
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 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 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.
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.
Once you have determined which application you would like to use, you have several options for starting the products in the pack.
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.
From the Tools Menu, choose Tuning Pack and select a Tuning Pack application.
Some of the applications in the Oracle Tuning Pack require the Oracle Management Server (OMS). Review this guide before getting started for more information on each application's requirements.
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.