What's New in Oracle Database Performance Tuning Guide?

This section describes new performance tuning features of Oracle Database 11g Release 2 (11.2) and provides pointers to additional information. The features and enhancements described in this section comprise the overall effort to optimize database performance.

For a summary of all new features for Oracle Database 11g Release 2 (11.2), see Oracle Database New Features Guide.

The new and updated performance tuning features in Oracle Database 11g Release 2 (11.2) include:

• New Automatic Workload Repository (AWR) views

  AWR supports several new historical views, including DBA_HIST_DB_CACHE_ADVICE and DBA_HIST_IOSTAT_DETAIL.

  For more information, see "Using Automatic Workload Repository Views".

• New Automatic Workload Repository reports

  New AWR reports and AWR Compare Periods reports have been added for Oracle Real Application Clusters (Oracle RAC).

  For more information, see "Generating Automatic Workload Repository Reports" and "Generating Automatic Workload Repository Compare Periods Reports".

• Table annotation support for the client result cache

  The client result cache supports table annotations.

  For more information, see "Using Result Cache Table Annotations".

• Enhancement to the RESULT_CACHE annotation for PL/SQL functions

  In Oracle Database 11g Release 1 (11.1), PL/SQL functions that performed queries referencing annotated tables required the RELIES_ON clause. This clause has been deprecated and is no longer required.

• Hints specifying parallelism at the statement level

  The scope of the parallel hints has been extended to include the statement level.

  For more information, see "Hints for Parallel Execution".

• In-Memory Parallel Execution

  When using parallel query, you can configure the database to use the database buffer cache instead of performing direct reads into the PGA for a SQL statement. This configuration may be appropriate when the database servers have a large amount of memory.

  For more information, see "Using the Buffer Cache Effectively".

• Hints for online application upgrades

  The online application upgrade hints suggest how to handle conflicting INSERT and UPDATE operations when performing an online application upgrade using edition-based redefinition. For more information, see "Hints for Online Application Upgrade".

• SQL Tuning Advisor enhancements

  This release includes the following enhancements to SQL Tuning Advisor:

  • While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding. See "Alternative Plan Analysis".

  • You can transport a SQL tuning set to any database created in Oracle Database 10g (Release 2) or later. This technique is useful when using SQL Performance Analyzer to tune regressions on a test database. See "Transporting a SQL Tuning Set".

  • Sometimes SQL Tuning Advisor may recommend accepting a profile that uses the Automatic Degree of Parallelism (Auto DOP) feature. A parallel query profile is only recommended when the original plan is serial and when parallel execution can significantly reduce the elapsed time for a long-running query. See "SQL Profile Recommendations".

• Migrating stored outlines to SQL plan baselines

  Oracle Database enables you to safely migrate from stored outlines to SQL plan baselines. After the migration, you can maintain the same plan stability you had using stored outlines while being able to utilize the more advanced features provided by the SQL Plan Management framework. For more information, see "Migrating Stored Outlines to SQL Plan Baselines".