What's New in Oracle Performance?

This section describes new performance features of Oracle Database 10g Release 1 (10.1) and provides pointers to additional information. The features and enhancements described in this section comprise the overall effort to optimize server performance.

For a summary of all new features for Oracle Database 10g Release 1 (10.1), see Oracle Database New Features.

Oracle Database 10g Release 1 (10.1) New and Updated Features for Performance Tuning

The new and updated performance features in 10g Release 1 (10.1) include the following:

• Automatic Performance Diagnostic and Tuning Features

  These features include Automatic Statistics Collection, Automatic Database Diagnostic Monitoring, and Automatic SQL Tuning. The Automatic Workload Repository collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.The Automatic Database Diagnostic Monitor (ADDM) reduces the amount of effort that is required to diagnose and tune Oracle systems. The SQL Tuning Advisor feature allows a quick and efficient technique for optimizing SQL statements. See "Introduction to Performance Tuning Features and Tools" for a brief summary of the new performance features and tools.

• Application End to End Tracing

  Application End to End Tracing identifies the source of an excessive workload, such as a high load SQL statement, by client identifier, service, module, or action. This feature simplifies the debugging of performance problems in multitier environments. See "End to End Application Tracing".

• trcsess Utility

  The trcsess command-line utility consolidates trace information from selected trace files based on specified criteria. See "Using the trcsess Utility".

• Automatic Optimizer Statistics Collection

  This feature automates the collection of optimizer statistics for objects. Objects with stale or no statistics are automatically analyzed, so administrators no longer need to keep track of what does and what does not need to be analyzed, nor to perform analysis by hand. See "Automatic Statistics Gathering".

• Automatic Shared Memory Management

  Automatic Shared Memory Management simplifies the configuration of System Global Area (SGA) memory-related parameters through self-tuning algorithms. It simplifies database configuration, ensures most efficient utilization of available memory and improves performance. See "Automatic Shared Memory Management".

• Rule-based Optimization (RBO) Obsolescence

  RBO as a functionality is no longer supported. RBO still exists in Oracle 10g Release 1, but is an unsupported feature. No code changes have been made to RBO and no bug fixes are provided. Oracle supports only the query optimizer, and all applications running on Oracle Database 10g Release 1 (10.1) should use that optimizer. Please review the following Oracle Metalink desupport notice (189702.1) for RBO:

  http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_
  database_id=NOT&p_id=189702.1
  
  

  You can also access desupport notice 189702.1 and related notices by searching for "desupport of RBO" at:

  http://metalink.oracle.com
  
  

  Notice 189702.1 provides details about the desupport of RBO and the migration of applications based on RBO to query optimization.

  Some consequences of the desupport of RBO are:

  • CHOOSE and RULE are no longer supported as OPTIMIZER_MODE initialization parameter values and a warning is displayed in the alert log if the value is set to RULE or CHOOSE. The functionalities of those parameter values still exist but will be removed in a future release. See "OPTIMIZER_MODE Initialization Parameter" for information optimizer mode parameters.
  • ALL_ROWS is the default value for the OPTIMIZER_MODE initialization parameter.
  • The CHOOSE and RULE optimizer hints are no longer supported. The functionalities of those hints still exist but will be removed in a future release.
  • Existing applications that previously relied on rule-based optimization (RBO) need to be moved to query optimization.

    See Also: Oracle Database Upgrade Guide Oracle Metalink desupport notice for RBO "Moving from RBO to the Query Optimizer"

• Dynamic Sampling

  The default setting for the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is now 2. See "Estimating Statistics with Dynamic Sampling" for information about when and how to use dynamic sampling.

• CPU Costing
  • The default cost model for the optimizer is now CPU+I/O and the cost unit is time. See "Understanding the Query Optimizer".
• New Optimizer Hints
  • "SPREAD_MIN_ANALYSIS" specifies analysis options for spreadsheets.
  • "USE_NL_WITH_INDEX" specifies a nested loops join.
  • "QB_NAME" specifies a name for a query block.
  • "NO_QUERY_TRANSFORMATION" causes the optimizer to skip all query transformations.
  • The "NO_USE_NL", "NO_USE_MERGE", "NO_USE_HASH", "NO_INDEX_FFS", "NO_INDEX_SS", and "NO_STAR_TRANSFORMATION" hints cause the optimizer to exclude various operations from the execution plan.
  • The "INDEX_SS", "INDEX_SS_ASC", and "INDEX_SS_DESC" hints cause the optimizer to use index skip scan operations in the execution plan.
• Updated Optimizer Hints
  • Hints that use a table or index argument in their syntax have been updated to use an expanded table or index specification. See "Specifying Global Table Hints" and "Specifying Complex Index Hints".
  • Some hints can use an optional query block argument. See "Specifying a Query Block in a Hint"
• Renamed Optimizer Hints

  The "NO_PARALLEL", "NO_PARALLEL_INDEX", and "NO_REWRITE" hints have been renamed. The NOPARALLEL, NOPARALLEL_INDEX, and NOREWRITE hints have been deprecated and should not be used.

• Additional Deprecated Optimizer Hints

  The AND_EQUAL, HASH_AJ, MERGE_AJ, NL_AJ, HASH_SJ, MERGE_SJ, NL_SJ, EXPAND_GSET_TO_UNION, ORDERED_PREDICATES, ROWID, and STAR hints have been deprecated and should not be used.

• Wait Model Improvements

  New and updated dynamic performance views are available. Existing V$EVENT_NAME, V$SESSION, and V$SESSION_WAIT views were modified. New V$ACTIVE_SESSION_HISTORY, V$SESS_TIME_MODEL, V$SYS_TIME_MODEL, V$SYSTEM_WAIT_CLASS, V$SESSION_WAIT_CLASS, V$EVENT_HISTOGRAM, V$FILE_HISTOGRAM, and V$TEMP_HISTOGRAM were added.

  See Also: Oracle Database Reference for information about dynamic performance views "Active Session History (ASH)" "Dynamic Performance Views Containing Wait Event Statistics"

• SAMPLE Clause Enhancements

  The sample clause can now be present in complex select statements. See "Sample Table Scans".

• Hash Partitioned Global Indexes

  New hash method can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. See "Using Partitioned Indexes for Performance".

• Oracle Trace Obsolescence

  Oracle Trace as a functionality is no longer available. For the tracing of database activity, use SQL Trace or TKPROF instead. The chapter on Oracle Trace has been removed from this book. See Chapter 20, "Using Application Tracing Tools".