Automatic SQL Tuning Features

Manual SQL tuning process poses many challenges to the application developer. So, the SQL tuning process has been automated by the automatic SQL Tuning manageability features.

Below is the feature name and the associated task can be done by them.

1)ADDM
----------
Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database, including high-load SQL statements.

To access ADDM from Home page>Advisor Central>Click ADDM under Advisor or to see previous ADDM findings and Recommendation under Advisor Tasks select ADDM from drop down menu. Also look at various filter options along with it.

2)SQL Tuning Advisor
-------------------------------
SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements.

To access SQL Tuning Advisor from Home page>Advisor Central>Click SQL Tuning Advisor.

3)SQL Tuning Sets
------------------------------
When multiple SQL statements are used as input to ADDM or SQL Tuning Advisor, a SQL Tuning Set (STS) is constructed and stored. The STS includes the set of SQL statements along with their associated execution context and basic execution statistics.



4)SQLAccess Advisor
--------------------------
In addition to the SQL Tuning Advisor, Oracle provides the SQLAccess Advisor that provides advice on materialized views, indexes, and materialized view logs. The SQLAccess Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. In general, as the number of materialized views and indexes and the space allocated to them is increased, query performance improves. The SQLAccess Advisor considers the trade-offs between space usage and query performance and recommends the most cost-effective configuration of new and existing materialized views and indexes.


To use SQL Access Advisor from Home page>Advisor Central>Click SQL Access Advisor.