The Oracle Tuning Pack® is a set of applications integrated with Enterprise Manager® that provides automated database analysis and tuning.  The Tuning Pack identifies opportunities to increase database performance by tuning database instance settings, indexes, SQL and space usage.  Proactive database tuning with the Tuning Pack can increase system responsivness and reduce the cost of database maintenance. 

• Instance performance  -- The Tuning Pack augments the self-tuning capabilities of the Oracle instance, focusing key database initialization parameters involved in SGA, sorting, I/O, Parallel Query, and Oracle® Parallel Server performance, operating system-specific parameters, and resource contention problems.
• SQL Tuning -- The Tuning Pack identifies and tunes high impact SQL
• Space Management -- The Tuning Pack identifies and eliminates space usage problems. 
• Optimal Data Access -- The Tuning Pack evaluates the database's indexes and recommends new indexing strategies.

Locate and Explain SQL Performance Bottlenecks
Inefficient SQL statements are a major contributor to database performance problems.  Yet too often they go unchecked due to the complexities of SQL tuning.  The Oracle Tuning Pack provides the database administrator and developer with the solution; graphical tools for identifying, diagnosing and tuning high impact SQL. 

The TopSQL feature is a powerful, flexible way to locate high impact SQL.  It searches the Oracle SQL cache using criteria easily defined through the TopSQL options dialog.  For example, TopSQL can be used to search for SQL accessing a specific table, being run by a certain user or SQL that exceeds certain performance boundries such as high disk reads. 

After high impact SQL statements are identified, the process of breaking down and examining the SQL operations begins with the explain plan.  The explain plan provides a step-by-step explanation of a SQL statement's operations.  Explain plans can be confusing to interpret because the hierarchical order of operations listed in the plan are not always the order of execution.  The Oracle Tuning Pack clarifies the order and type of SQL operations through the Graphical Explain Plan component.  The Graphical Explain Plan allows the user to easily visualize how the SQL statement is retrieving and processing data from the database. 

The Graphical Explain Plan presents SQL operations in an intuitive visual diplay, highlighting the relationships between database objects and SQL operations.  Useful information such as the number of rows and cost of operations are provided directly within the graphical plan. 

The user can walk through the plan and immediately understand how the statement will execute and what step each operation will perform.  The Graphical Explain Plan is integrated with other Enterprise Manager diagnostic tools and can be invoked automatically when a SQL statement is identified as the source of the performance problem.

Automatically Rewrite SQL Statements
The Oracle Tuning Pack's SQL Analyze application can automatically check SQL statements for basic SQL design violations.  These are problems such as the inadvertent disabling of an index by the use of certain operations, or using unnecessary filtering and sorting operations. 

The SQL Tuning Wizard finds problems and  predicts performance gains.

If SQL Analyze finds a SQL design violation, it will alert the user and provide an estimate of the projected performance improvement based on the reduction in optimizer cost of the rewritten SQL query.  SQL Analyze will then generate alternative SQL that corrects the problem and improves the performance.  The rewritten SQL statement can be saved and copied to the source application.

SQL Analyze can be used to closely examine a SQL statement and capture SQL performance metrics such as elapsed time, memory and I/O usage.  It provides the ability to easily compare the performance and explain plans of different SQL variations and Oracle optimizer modes. 

SQL explain plans can be more easily reviewed and documented using the SQL Explain Plan Report.  This feature generates an HTML-based report which presents the explain plan with links to detailed descriptions of the steps involved in the plan.  The user can walk through the plan or simply review the sequence of steps descriptions in order to understand the SQL operations.

SQL Analyze is integrated with the Oracle Diagnostics Pack Performance Manager application.  This allows SQL Analyze to be launched in the context of a performance problem identified from the Performance Manager�s graphical real-time monitoring charts.  A performance problem detected at the database instance level can be tracked down through Performance Manager to the session and SQL level, from which point SQL Analyze be used to perform a detailed tuning analysis of the costly SQL statement.

Improve Response Time by Tuning Indexes
Index tuning can be complex and hazardous.  Deciding what to index and evaluating the tradeoffs between faster queries and increased transaction overhead takes a lot of analysis and quess work.  The Tuning Pack removes the guess work and automates the analysis of index requirements.   It provides several levels of index tuning aimed at meeting the range of index tuning opportunities, from proactive planning to performance problem solving.

The Oracle Expert application provides comprehensive index tuning focused on one or more tables at a time.  Expert collects a SQL workload profile for the target tables, and evaluates and ranks the SQL access requirements of the workload.  It also considers update activity of the table and produces an optimized indexing strategy the balances the tradeoffs of queries and table volatility.  Expert will recommend adding new indexes, modifying existing indexes and dropping unnecessary indexes.

The Index Tuning Wizard provides quick access to  index tuning that is focused on high impact SQL statements.  The Wizard will identify the top high impact SQL statements and the tables used by that portion of the workload.  It provides focused index tuning to meet the requirements of improving table access for these top queries.   Similarly, SQL Analyze provides index tuning focused on improving the performance a specific SQL statement.  It will identify the optimal index requirements for a high impact SQL statement.

Test Your Index Plans Before Construction
In the past, the only way to determine if an index would improve performance was to build the new index.  The Tuning Pack provides a new feature, the Virtual Index Wizard, which allows indexes to be tested with the Oracle Optimizer before they are built.  The Virtual Index Wizard is part of the the SQL Analyze application.  It walks the user through the process of selecting a candidate index to test against a specific SQL statement.  If the optimizer selects the new index for use by the query, the Wizard reports the change in "cost" of the query.  This lets the user iteratively test index plans and select the best index design before constructing the index.

Eliminate Tablespace Inefficiencies
Managing the space usage of your tablespaces means using space more efficiently and reducing disk I/Os for improved database responsiveness.  The Oracle Tuning Pack can be used to monitor and manage tablespace usage.  The Tablespace Map provides "at-a-glance" viewing of tablespace details and space usage.  The size and location of objects is presented in a graphical map view, which helps users easily identify the location of segments and extents.  Users can select a specific segment and display the location of that segment�s extents, highlighting segment fragmentation.   User�s can take advantage of the Map�s "zoom" feature to explore the details of large tablespaces.

The Tablespace Map presents a graphical image of the location and relative size of a tablespace's database segments,  Problem segments are automatically flagged for reorganization by the Reorg Wizard.

The Tablespace Map also includes a Tablespace Analysis report that can be generated directly from the map to check for a variety of problems, such as row chaining, index fragmentation and inefficiently sized objects.   Problem objects are flagged for reorganization by the Reorg Wizard, which can then be launched in the context of the problem objects.

With the Oracle Tuning Pack Reorg Wizard, users can reorganize specific schema objects or reorganize an entire tablespace. During the reorganization process, users have the option of modifying the storage attributes of segments, such as the number and size of extents and data-block percent free/used parameters.  If a row chaining problem was detected by the Tablespace Map then the user has the option of rebuilding the entire object or just repairing the chained rows.  Reorganizations can be performed immediately or scheduled through the Oracle Agent.

The Reorg Wizard uses the online rebuild capabilities of the Oracle server.  If an object can be reorganized online, such as indexes and certain Oracle 9i tables, then the Reorg Wizard allows the user to choose between the online or off-line technique. 

Take Advantage of New Oracle Performance Features
The Tuning Pack identifies opportunities to Oracle performance features such as bitmapped indexes.  It will also take advantage of new server features where possible.  For example, Oracle9i will capture and save the runtime explain plan for SQL cursors open in the Oracle SGA.  The Graphical Explain Plan and SQL Analyze applications will use the runtime plan if it exists as the explain plan presented for the SQL statement being tuned. 

The Tuning Pack also enables you to take advantage of certain new Oracle performance features.  The new Tablespace Migration Wizard will automatically convert a tablespace from the traditional dictionary-managed format to the preferred "locally-managed" format, also known as the bitmapped tablespace.  Tablespaces that are organized in the locally-managed format will automatically control their own growth and space usage, eliminating the need for administrator control and dramatically reducing the need for space reorganization. 

The Outline Management application helps you take advantage of the plan stability feature introduced in Oracle8i.  Plan stability allows you to stabilize the performance of a SQL statement by creating a stored outline of the an execution plan for the SQL.  The Outline Managment application can be used to create, edit and administer stored outlines, for enhanced SQL performance stability.

Produce Consultant-Quality Tuning Reports
The Tuning Pack provides the details behind its tuning recommendations in the form of comprehensive tuning analysis reports.  Tuning recommendations are backed-up by a complete listing of the data and rules logic used in the analysis.  The reports are generated in HTML format for easy navigation and publication for review by others.  Some of the reports provided by the Tuning Pack include:

• Oracle Expert Analysis - a comprehensive review of the efficiency of your instance settings and other key performance considerations.
• Index Wizard Analysis - recommendations for tuning the performance of the top SQL statements in your workload by modifying or adding new indexes.
• Tablespace Analysis - identifies and describes segment problems, and provides advise for correcting and avoiding space usage problems.
• Explain Plan Report - a valuable presentation and explanation of the Oracle explain plan with links between operations and descriptions; very useful for navigating large explain plans.

The Tuning Pack is integrated with the Oracle Diagnostics Pack and Oracle Enterprise Manager, providing a complete solution for database performance management.

 

KEY FEATURES
Comprehensive Automated Database Tuning  Hundreds of built-in database tuning rules  Complete analysis of the top twenty instance parameters that effect performance Identifies database segments in need of repair or reorganization Evaluates and rewrites SQL statements to eliminate performance bottlenecks Recommends changes to database index strategies to improve query performance and eliminates unnecessary indexes NEW! Administers and edits stored SQL outlines for SQL plan stability Reorganizes database objects for high performance, including online reorganization of Oracle 9i tables NEW!	Graphical Tuning Analysis and Reports NEW! Provides an intuitive graphical presentation of the Oracle SQL explain plan for easy plan navigation with built-in descriptions and operation details NEW! Virtual Index Wizard allows indexes to be tested by the Oracle Optimizer prior to construction  NEW! Detailed HTML-based SQL explain plan reports Comprehensive HTML-based tuning reports for instance analysis, index evaluation and space management  Detailed graphical depiction of tablespace space usage and problem segments Reorganization impact analysis reports
RELATED PRODUCTS AND SERVICES The Oracle Tuning Pack is integrated with the following Oracle management applications: Oracle Enterprise Manager -- for easy discovery and management of database, nodes, web servers and other targets. Oracle Intelligent Agent -- for lights out control of database reorganization operations Oracle Diagnostics Pack -- used to pinpoint performance bottlenecks that can be corrected using the Tuning Pack, such as high impact SQL statements and objects that require tuning.	.