Sql Tuning Advisor

Sql Tuning Advisor (STA) is a new feature introduced in Oracle 10g. This automates the entire SQL tuning process.

Three ways to utilize STA:
Enterprise Manager Grid Control or Database Control
DBMS_SQLTUNE package
sqltrpt.sql script

1. STA Through EM
The user must have been granted the SELECT_CATALOG_ROLE role.
STA interface can be found through Performance Page > Advisor Central (Related Links) > SQL Tuning Advisor.
Through 'Top Activity' or 'Historical SQL (AWR)', you can choose Hot SQL that you want to tune. And then you can create tune sets and schedule sql tuning.

2. DBMS_SQLTUNE package
To use the APIs the user must have been granted the DBA role and the ADVISOR privilege.
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1) Create a SQL tuning task
2) Execute a SQL tuning task
Example can be found in metalink Doc 262687.1.

3. sqltrpt.sql script
Starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for usage of SQL Tuning Advisor from the command line.