|Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
|PDF · Mobi · ePub|
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 include:
Resource Manager enhancements for parallel statement queuing
You can use Resource Manager to control the order of statements in a parallel statement queue. For example, you can ensure that high-priority statements spend less time in the queue. Also, you can use a directive to prevent one consumer group from monopolizing all of the parallel servers, and to specify the maximum time in seconds that a parallel statement can wait to be launched.
For more information, see "Managing CPU Resources Using Oracle Database Resource Manager" and Oracle Database VLDB and Partitioning Guide.
Resource Manager enhancements for CPU utilization limit
You can use Resource Manager to limit the CPU consumption of a consumer group. This feature restricts the CPU consumption of low-priority sessions and can help provide more consistent performance for the workload in a consumer group.
For more information, see "Managing CPU Resources Using Oracle Database Resource Manager".
New package for Automatic SQL Tuning
DBMS_AUTO_SQLTUNE package is the new interface for managing the Automatic SQL Tuning task. Unlike the SQL Tuning Advisor package
DBMS_SQLTUNE, which requires
DBMS_AUTO_SQLTUNE requires the
For more information, see "Configuring Automatic SQL Tuning".
Oracle Orion I/O Calibration Tool Documentation
Oracle Orion is a tool for predicting the performance of an Oracle database without having to install Oracle or create a database. Unlike other I/O calibration tools, Oracle Orion is expressly designed for simulating Oracle database I/O workloads using the same I/O software stack as Oracle. Orion can also simulate the effect of striping performed by Oracle Automatic Storage Management.
For more information, see "I/O Calibration with the Oracle Orion Calibration Tool".
The new and updated performance tuning features include:
New Automatic Workload Repository (AWR) views
AWR supports several new historical views, including
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 database servers have a large amount of memory. Also, an Oracle Real Applications Cluster (Oracle RAC) database can aggregate the size of the buffer cache of all nodes, thereby caching larger objects and caching more queries.
For more information, see "Using the Buffer Cache Effectively".
Hints for online application upgrades
The online application upgrade hints suggest how to handle conflicting
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".