From: http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php

Automatic SQL Tuning in Oracle Database 10g

This article the discusses the new features which automate the tuning of SQL statements in Oracle 10g:

• Overview
• SQL Tuning Advisor
• Managing SQL Profiles
• SQL Tuning Sets
• Useful Views

Overview

In its normal mode the query optimizer needs to make decisions about execution plans in a very short time. As a result it may not always be able to obtain enough information to make the best decision. Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further. This process may take several minutes for a single statement so it is intended to be used on high-load resource-intensive statements.