The SQL Tuning Advisor Links

At a high-level, the SQL Tuning Advisor samples historical SQL as captured in SQL Tuning Sets (STS) from the dba_hist_sqlstat and dba_hist_sql_plan views.  The SQL Tuning Advisor can be launched from many places within Enterprise Manager:

§         For high-load SQL statements identified by ADDM, SQL Tuning Advisor can be launched from the ADDM Finding Details screen.

§         When selecting from the Top SQL statements, Advisor can be launched from the Top SQL Page.

§         When the STS is the input for tuning, Advisor can be launched from the SQL Tuning Sets page.

The last input set for the SQL Tuning Advisor is user-input statements or the SQL Tuning Set.  This could include untested SQL statements, or a set of SQL statements currently under development. For tuning a set of SQL statements, a SQL Tuning Set (STS) has to be constructed and stored, and fed to the SQL Tuning Advisor

The OEM display screen for the SQL Tuning Advisor has the menu options shown in Figure 19.54.

While each of these is explained in detail later in this chapter, it is important to understand that this is the main anchor screen for the SQL Tuning advisory functions.  For more sophisticated DBA’s, Oracle provides the dbms_sqltune DBMS package as an interface to this powerful analytical tool.

This series of screens are arranged in a hierarchy of screens, showing each of the sub-screens from the links screen shown in Figure 19.55.

In addition to using Enterprise Manager, the SQL Tuning Advisor is obtained by using the PL/SQL package dbms_sqltune. This is a new package introduced in 10g, and it has comprehensive procedures that help to conduct the full SQL Advisor session. Some of the useful procedures include:

§         create_tuning_task - This procedure creates a tuning task for a given SQL statement or for a cached cursor.

§         execute_tuning_task - This procedure executes the tuning task and generates the tuning data.

§         report_tuning_task - This procedure generates a complete report of the results of a task.

§         report_plans - This procedure shows the SQL plans.

Most Oracle professionals find the Enterprise Manager interface far easier to use than the dbms_sqltune package.  Therefore, a closer look at these SQL Tuning functions is in order.  The OEM SQL Tuning Advisor execution plan display is shown in Figure 19.56 below. From the top-level of the SQL Tuning advisor screen, hyperlinks to the following OEM areas are shown:

Top SQL: This OEM screen displays the most resource intensive SQL for any time period that is specified.  As the time-period changes, the top SQL is displayed along with the percentage of total elapsed time used by the statement, the CPU time consumed, the total wait time for the SQL and the average elapsed time per execution.

SQL Tuning Sets: SQL tuning sets are encapsulations of SQL statements.  They contain the source for the SQL statement; the host variables used during historical execution, performance metrics and allow a complete environment for testing the efficiency of a single SQL statement over time.

Snapshots: SQL Snapshots are taken every hour and can be manipulated manually with the new dbms_workload_repository package.

Preserved Snapshot Sets: A snapshot set, also known as a baseline, is a collection of multiple AWR snapshots.  The preserved snapshot sets are then used to compare workload performance over specific periods.

Drilling down into the top SQL areas shows how it captures the most resource-intensive SQL allowing the DBA to explore the exact conditions leading to any Oracle performance bottleneck.

SEE CODE DEPOT FOR FULL SCRIPTS