Oracle 10g New Features: Automatic SQL Tuning Process

SQL tuning is one of the challenging tasks faced by DBAs and developers. It is an interesting and creative, but at the same time, daunting task. Manual tuning of SQL statements requires a high level of expertise and experience to understand and design suitable access paths to yield better response times. It is also a time consuming process. Other challenges include periodic collection of statistics and an ever-changing workload. And in a typical application, there are just too many SQL statements to tune.Oracle Database 10g introduces many useful and easy-to-use tuning tools and methodologies. In this section we will examine these new and improved features.

The new and enhanced features include the following:

- SQL Tuning Advisor

- SQLAccess Advisor

- Automatic Statistics Gathering

- Automatic Workload Repository - AWR

- Automatic Database Diagnostic Monitor - ADDM

SQL Tuning Advisor, newly introduced in 10g, is primarily designed to replace the manual tuning of SQL statements and speed up the overall SQL tuning process. SQL Tuning Advisor studies poorly executing SQL statements and evaluates resource consumption in terms of CPU, I/O, and temporary space. The advisor receives one or more SQL statements as input and provides advice on how to optimize their execution plans, gives the rationale for the advice, the estimated performance benefit, and the actual command to implement the advice. We will cover more details in the following sections.

In addition to SQL Tuning Advisor, Oracle provides the SQLAccess Advisor, which provides expert advice on materialized views, indexes, and materialized view logs. A full detailed account of its utility and usage is covered in Chapter 16, Business Intelligence.

Automatic Workload Repository (AWR) collects, processes, and monitors performance-related statistics. The information aids problem detection and self-tuning. Statistics collected by the AWR are persistent and are stored in the database. Automatic Database Diagnostic Monitor (ADDM) constantly analyzes them. The topics of AWR and ADDM are fully covered in Chapter 9, New Manageability Features.

Automatic Statistics Gathering is another new feature that significantly helps the SQL Tuning process. A more detailed account is available in Chapter 9, New Manageability Features.

SQL Tuning Advisor

SQL Tuning Advisor is like a container for conducting and analyzing many tuning tasks. It calls the optimizer internally and performs the analysis as follows:

- Executes the Stale or Missing statistics analysis and makes a recommendation to collect, if necessary.

- Plans the tuning analysis and creates SQL profile. SQL Profile is a collection of the historical information of prior runs of the SQL statement, comparison details of the actual and estimated cardinality, and predicate selectivity etc. SQL Profile is stored persistently in the data dictionary, and hence does not require any application code changes.

- Performs the access path analysis. The Optimizer recommends new indexes that produce a significantly faster execution path.

- Restructure the SQL statement. Optimizer identifies SQL statements that have bad plans and makes relevant suggestions to restructure them.

The plan analysis mode, which creates the SQL Profiles, is a significant stage where additional information for the query is collected by the optimizer. This analysis is not possible in the normal mode. Such a SQL profile helps generate a better execution plan than the normal optimization. Additional tasks like checking for advanced predicate selectivity, correlation between columns, join skews, and complex predicates such as functions, help in profiling the SQL statement. Once a statement is profiled and stored, it can be used at will.

Using SQL Tuning Advisor Session

This method has many stages. In the first stage, you can create the SQL Advisor task by taking SQL statement input from a variety of sources. They are as follows:

- High Load SQL statements, identified by ADDM. SQL statements that are currently in cursor cache (From the v$sql_plan view).

- SQL statements based on the range of snapshot ids from the Automatic Workload Repository (AWR). By default, AWR maintains data for up to seven days.

- Simple SQL Statement Text. A user can define a custom workload consisting of statements of interest to the user. These may be statements that are either in cursor cache or high-load, to be captured by ADDM or AWR.

- SQL Tuning Set (STS). A SQL Tuning Set is a named set of SQL statements with their associated execution context and basic execution statistics.

STS is mostly used for multi-statement input. STS stores multiple SQL statements, along with their execution information, such as execution context (parsing schema name and bind values) and execution statistics (average elapsed time, and execution count).

SQL Statements from cursor cache, AWR, and custom workload can be filtered and/or ranked before they are input to the SQL Tuning Advisor.

In the second stage, execute the tuning analysis tasks and view the results (or create the SQL profiles). Later, you can implement the suggestions.

Oracle Enterprise manager is a good place to launch and utilize the SQL Tuning Advisor. SQL Tuning Advisor GUI can be invoked at multiple places within the OEM.

- 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 STS becomes the input for tuning, advisor can be launched from the SQL Tuning Sets page.

OEM also allows you to view the tuning analysis results, create the SQL profile, and then implement the suggestions.

In addition to the OEM GUI, SQL Advisor functionality can be 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 - Creates a tuning task for a given SQL statement or for a cached cursor

- execute_tuning_task - Executes the tuning task and generates the tuning data

- report_tuning_task - Generates a complete report of the results of a task

- report_plans - Shows the SQL plans

There are two important initialization parameters that affect the operation of SQL Tuning Advisor. They are as follows:

- optimizer_auto_learn - Enables or disables the auto-learning optimizer feature

- sqltune_category - Selectively enables the SQL tuning base features for specific sessions

---

The above is an excerpt from the bestselling Oracle10g book Oracle Database 10g New Features by Mike Ault, Madhu Tumma and Daniel Liu, published by Rampant TechPress.