What's New in Oracle Performance?

This section describes new performance tuning features of Oracle Database 11g Release 1 (11.1) 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 1 (11.1), see Oracle Database New Features Guide.

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

• Active session history (ASH) enhancements

  ASH statistics are enhanced to provide row-level activity information for each SQL statement that is captured. This information can be used to identify which part of the SQL execution contributed most significantly to the SQL elapsed time.

  For more information, see Chapter 5, "Automatic Performance Statistics".

• Automatic Database Diagnostic Monitor (ADDM) enhancements

  ADDM is enhanced to perform analysis:

  • On database clusters (Global ADDM)

  • On various levels of granularity, such as database cluster, database instance, or specific targets

  • Over any specified time period (not tied to a pair of snapshots)

  For more information, see Chapter 6, "Automatic Performance Diagnostics".

• Automated SQL tuning

  You can automate the scheduling of SQL Tuning Advisor tasks to run during maintenance windows using Automated Maintenance Task. For more information, see Chapter 17, "Automatic SQL Tuning".

• AWR baselines

  A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. There are several types of available baselines in Oracle Database: fixed baselines, moving window baseline, and baseline templates. For more information, see Chapter 5, "Automatic Performance Statistics".

• Database Replay

  You can capture a database workload on a production system and replay it on a test system to ensure that system changes, such as database upgrades, will yield desired results. For more information, see Chapter 22, "Database Replay".

• Enhanced I/O statistics

  I/O statistics are collected for all I/O calls made by Oracle Database in the following dimensions: consumer group, database file, and database function. For more information, see Chapter 10, "Instance Tuning Using Performance Views".

• Enhanced optimizer statistics maintenance

  The collection and publication of statistics is now decoupled. You can collect optimizer statistics, store them as pending statistics, test them, and then publish them only if the statistics are valid. For more information, see Chapter 13, "Managing Optimizer Statistics".

• Extended statistics

  The optimizer now gathers information on a group of columns in a table (MultiColumn statistics) to analyze column dependencies in column groups. It also gathers information about expressions in columns. For more information, see Chapter 13, "Managing Optimizer Statistics".

• I/O calibration

  The I/O calibration feature of Oracle Database enables you to assess the performance of the storage subsystem, and determine whether I/O performance problems are caused by the database or the storage subsystem. For more information, see Chapter 8, "I/O Configuration and Design".

• Query result caches

  You can store results of frequently run SQL queries in server-side and client-side result caches. This ensures a faster response time when these queries are subsequently executed. For more information, see Chapter 7, "Memory Configuration and Use".

• Real-time SQL monitoring

  Real-time SQL monitoring enables you to monitor the execution of long running SQL statements as they are being executed. Both cursor-level statistics (such as CPU time and I/O time) and execution plan statistics (such as cardinality of intermediate results, memory and temporary space used by each operator in the plan) are updated in near real-time while the statement executes. These statistics are exposed by two new fixed views, V$SQL_MONITOR and V$SQL_PLAN_MONITOR. For more information, see Chapter 10, "Instance Tuning Using Performance Views".

• SQL Performance Analyzer

  The SQL Performance Analyzer enables you to forecast the impact of system changes on SQL performance by testing these changes using a SQL workload on a test system. For more information, see Chapter 23, "SQL Performance Analyzer".

• SQL plan baselines

  Changes to the execution plan for a SQL statement can cause severe performance degradation. Oracle Database can capture, select, and evolve SQL plan baselines, preventing the optimizer from using a new plan until it has been verified to be more efficient than the existing plan. This feature replaces the plan stability feature in previous versions of Oracle Database. For more information, see Chapter 15, "Using SQL Plan Management".

• SQL test case builder

  The SQL test case builder provides the ability to build a reproducible test case by gathering and packaging the necessary information related to an SQL incident so that it can be reproduced on another system. For more information, see Chapter 16, "SQL Tuning Overview".