79696

About the SQL Tuning Advisor

The SQL Tuning Advisor examines a givcn SQL statement or a set of SQL statements and provides rccommendations to improve efficicncy. It can make various types of rccommendations. such as crcating a SQL profile (a collcction of information that enables the query optimizer to create an optimal execution plan for a SQL statement), restructuring SQL statements, and refreshing optimizer statistics. SQL Tuning Advisor also enables you to pick an altcmative cxecution plan (stored in AWR) from the past and use it with the SQL statement, and can also recommend degree of parał lei ism profiles. EM Exprcss enables you to accept and implement many of these rccommendations with just a few mouse clicks.

You use the SQL Tuning Advisor to tune a single SQL statement or multiple SQL statements. Typically. you run the SQL Tuning Advisor in response to an ADDM performance finding that recommends its use. You can also run it periodically on the most resource-intensive SQL statements, and on a SQL workload.

When tuning multiple SQL statements, the SQL Tuning Advisor does not recognize interdependencies between the SQL statements. It solves SQL performance problems by identifying problems with individual SQL statements, such as a poorly performing optimizer plan orthe mistaken use of certain SQL structures.

You can run the SQL Tuning Advisor against the following sources:

■Activity—The most resource-intensive SQL statements executed during the last hour that appear on the Activity tab of the Performance Hub that might have caused recent performance problems.

■Historical SQL—A SQL statement from the last day, week, or month that appear on the Activity tab of the Performance Hub when one of the historical settings is sclcctcd in the Select Tinie Period field. Use this option for proactivc tuning of SQL statements.

■Historical SQL from ADDM—A rcsource-intcnsivc SQL statement from an ADDM task that you discover when analyzing a task on the ADDM tab of the Performance Hub.

■SQL statement in SQL Tuning Advisor—A rcsource-intcnsive SQL statement that appears as a tuning task in SQL Tuning Advisor.

■SQL tuning sets (STS)—A set of SQL statements you provide. An STS can be created from SQL statements captured by AWR snapshots or from a SQL workload.