Oracle® Database 10g Insider Solutions

SQL Profiles

We mentioned SQL profiles in Chapter 13 when discussing the SQL Tuning Advisor. SQL profiles come into play when the Optimizer operates in the Automatic Tuning mode. In this mode, the Optimizer collects and uses auxiliary information to both mitigate estimation errors as well as build an SQL profile. When available, the Optimizer also uses the SQL execution plan history stored in AWR's WRH$_SQL_PLAN table. The SQL profile also changes the Optimizer settings as required—for example, it may change the OPTIMIZER_MODE setting from ALL_ROWS to FIRST_ROWS to see if this will generate a more efficient plan. SQL profiling is invoked via the SQL Tuning Advisor information, and this is then presented as a recommendation to accept the profile. ...