Automatic SQL Tuning

1. Enable Automated Maintenace Tasks & Accept SQL Profiles
sqlplus / as sysdba
SQL> exec dbms_auto_task_admin.enable();
SQL> exec dbms_sqltune.set_auto_tuning_task_parameter( 'ACCEPT_SQL_PROFILES', 'TRUE');
SQL> exit;
2. Drops any existing profiles on queries executed by the SCOTT user
./astsetup.sh
3. Execute a workload
sqlplus scott/tiger
SQL> @scott_workload.sql
SQL> exit;
4. Force the opening of next maintenance window right now
./ast_run.sh
5. Execute the scott_workload.sh script again. the execution time for scott_workload.sh is much faster than the original execution. This is probably due to the fact that Automatic SQL Tuning implemented a profile for the statement automatically.
sqlplus scott/tiger
SQL> @scott_workload.sql
SQL> exit;
6. Create of an AWR snapshot
sqlplus scott/tiger
SQL> exec dbms_workload_repository.create_snapshot;
SQL> exit;
7. Check if SQL Profile was automatically implemented. In Enterprise Manager, navigate to Server > Automated Maintenance Tasks (Oracle Scheduler) > Automatic SQL Tuning. On the Automatic SQL Tuning summary page, view the tuning results. Look at the graphs on the Automatic SQL Tuning Result Summary page. (If you do not see any graphs, return to step 5, execute the work load twice, then continue with step 6 and 7.)

8. Click View Report to see a detailed SQL-level report, Note the green check mark meaning that the profile was implemented.

9. Click the View Recommendations

10. Click the SQL text to go to the SQL Details page for this SQL. On the SQL Details - Tuning History page note the link to SYS_AUTO_SQL_TUNING_TASK that is there to show that the SQL was tuned by this tuning task. Click Plan Control

Note that a profile was created automatically for this SQL. The type of AUTO means it was automatically created.

Download astsetup.sh, scott_workload.sql, ast_run.sh scritps:

https://docs.google.com/open?id=0B9WNSq6Zy_K7cE9XMng2V0tkNDg