New Features in Oracle Database 10g Release 2

Oracle Database 10g Release 2 provides a number of enhancements to the Optimizer. Notable among them is the capability to transport SQL profiles between databases. This allows you to generate a SQL profile and SQL tuning sets in the production environment and transport them to the development and user acceptance database, just as you would transfer object statistics. SQL profiles can now be matched to SQL text with literals after they have been normalized into bind variables (somewhat similar to the effects of the CURSOR_SHARING initialization parameter). A new view, V_$SQL_STATS, can display, among other interesting tidbits, a LAST_ACTIVE_TIME column. SQL statistics are also updated once every five seconds for a SQL that is still in progress.

As well, it is evident from the increase in the number of hidden parameters (1,047 in Release 2 versus the 911 in Release 1) that a number of these new parameters will continue to influence the Optimizer, making it even better.