SQL Tuning Advisor

This practice is based on http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php#sql_tuning_advisor.

SQL> conn / as sysdba
SQL> DECLARE
2 l_sql VARCHAR2(500);
3 l_sql_tune_task_id VARCHAR2(100);
4 BEGIN
5 l_sql := 'SELECT e.*, d.* ' ||
6 'FROM hr.employees e JOIN hr.departments d ON e.department_id = d.department_id ' ||
7 'WHERE NVL(employee_id, ''0'') = :empno';
8 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
9 sql_text => l_sql,
10 bind_list => sql_binds(anydata.ConvertNumber(100)),
11 scope => DBMS_SQLTUNE.scope_comprehensive,
12 time_limit => 60,
13 task_name => 'emp_dept_tuning_task',
14 description => 'Tuning task for an EMP to DEPT join query.');
15 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
16 END;
17 /

SQL> EXEC DBMS_STATS.delete_table_stats('HR', 'EMPLOYEES');

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');

In Enterprise Manager, click Advisor Central, you will see your sql tuning task completed. Click that name. In SQL Tuning result page, you can click View Recommendations button to view all of recommendations based on the query.