The shared SQL area and AWR provide the capability to identify and tune high-load SQL statements from the current time going as far back as the AWR retention allows, which by default is at least 8 days. To help you estimate the impact of this recommendation, you are provided with a comparison between the actual CPU usage and the projected CPU usage (as if the recommendation were applied). Generate recommendations and implement SQL profiles automatically. The SQL Performance Analyzer, or SPA, is another new feature introduced in release 11 g. SPA, and another feature named Database Replay, fall under what is known as Real Application Testing. Specifically, SQL Tuning Advisor can profile the following types of statement: DML statements (SELECT, INSERT with a SELECT clause, UPDATE, DELETE, and the update or insert operations of MERGE), CREATE TABLE statements (only with the AS SELECT clause).

The database uses the shared SQL area to tune recent SQL statements that have yet to be captured in AWR. This section shows the old and new explain plans used by each SQL statement analyzed by SQL Tuning Advisor. SQL profiling is the verification by the Automatic Tuning Optimizer of its own estimates. Displays information about tasks owned by the current user. Local mode is appropriate for databases in which the performance overhead of SQL Tuning Advisor execution is acceptable. You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week. For each comparison, you can use any previous SQL trial as the pre-change SQL trial and the current SQL trial as the post-change SQL trial. Auto-created indexes don't block ALTER/RENAME commands.

As shown in Figure 24-8, Automatic Tuning Optimizer identifies the following categories of structural problems: A suboptimally performing statement may be using NOT IN instead of NOT EXISTS, or UNION instead of UNION ALL. If you do not use Cloud Control, then you can monitor currently executing SQL tuning tasks by querying the data dictionary and dynamic performance views. The database contains a vast amount of statistics about its own operations. Performance tune the queries. If, after a couple of days, automatic tuning still believes that the index would be beneficial, the process starts again. This section explains how to manage the Automatic SQL Tuning task. If the optimizer generates auxiliary information during statistical analysis or SQL profiling, then the optimizer recommends implementing a SQL profile. If these statistics are stale or missing, then the optimizer can generate suboptimal plans. A classic example of a design mistake is a missing join condition. The recommended user interface for running SQL Tuning Advisor manually is Cloud Control. SQL Tuning Advisor can perform deep mining and analysis of internal information to improve execution plans. SQL Tuning Advisor only recommends an alternative plan if the elapsed time of the original plan is worse than alternative plans. Database Advisor helps you to improve the performance of your databases by providing intelligent tuning recommendations that are based on historical usage. Any known product incompatibilities with SPA Console will be noted.

This report describes in detail the SQL statements that were analyzed, the recommendations generated, and any SQL profiles that were automatically implemented.