Automating the SQL Advisors

Oracle Database 10g comes bundled with a number of advisors, a few of which you have already seen in previous chapters. This chapter discusses the SQL advisorsnamely, the SQL Tuning Advisor and the SQL Access Advisor. We have combined the coverage of both these advisors into one chapter because they are somewhat related and operate exclusively on SQL statements.

Before launching into the details, it is wise to have some background information about these advisors as well as about SQL tuning in general so that you can keep everything in perspective.

The SQL Tuning Advisor

The SQL Tuning Advisor is actually a front-end interface to the deeper Automatic SQL Tuning capability of the query optimizer. The main objective of the SQL Tuning Advisor (STA in its short form) is to automate the entire process of tuning SQL. Along with the SQL Access Advisor, it tries to automate one of the hardest and most complex tasks of a performance analystthat of changing the SQL or the environment in which it works so that the SQL statement runs more efficiently.

The query optimizer actually operates in two modes: normal and tuning. In normal mode, the optimizer parses and executes a SQL without spending too much time generating the execution plan, which may hence be suboptimal. In tuning mode, the optimizer performs additional analysis to check whether the execution plan previously produced under the normal mode can be further improved. In this mode, however, the query optimizer does not execute the SQL, but similarly to ADDM produces a series of recommendations along with their rationale and the expected benefit for producing a significantly superior plan. When called under tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer and the tuning performed therein is called Automatic SQL Tuning. The SQL Tuning Advisor simply exposes these recommendations and provides an interface to perform such tuning. All this is again possible via the use of an advisor framework in which advisor specific tasks are created and submitted to the advisors for processing.

Difference Between EXPLAIN PLAN and Automatic SQL Tuning

You are likely aware of the EXPLAIN PLAN statement in current and previous versions of Oracle. Although both the EXPLAIN PLAN command and automatic tuning determine the execution plan and do not actually execute the SQL, the similarity stops there. EXPLAIN PLAN works in normal mode and simply exposes the plan that the query optimizer would take if the SQL was executed at that point of time. The automatic tuning mode, however, does much more than just generate an execution plan, as you will see. Be aware that the execution plan shown by EXPLAIN PLAN may not always be the way it would execute. These plans are influenced by the environment in which they operate, which may not be the same as the actual execution environment. The actual execution plan can be seen only via the V$SQL_PLAN view.

Automatic SQL Tuning

We mentioned the two modes in which the query optimizer executes. Let's now look in detail at the tuning mode, because this is the mode that is involved with the SQL Tuning Advisor. This kernel code component is also known as the Automatic Tuning Optimizer, and is an integral part of the query optimizer. This integration provides several advantages:

• Because the query optimizer is ultimately responsible for execution plans and, hence, the SQL performance, it is expedient to extend the optimizer itself to perform more than just the run-time parsing and execution-plan generation. Oracle kernel code relating to the optimizer, internal rules, and the prior, deep knowledge about the optimizer can thus be reused when creating this extension.

• Enhancements to the query optimizer that are an ongoing exercise are passed on to the Automatic Tuning Optimizer component.

• This component can access the past execution statistics of a SQL statement and thus can customize the optimizer settings for that statement. This is critical because mistakes of the past can be used to avoid choosing a wrong path.

• In addition to the regular object statistics used by the query optimizer, the Automatic Tuning Optimizer collects auxiliary information that can be used in conjunction with the former.

In fact, you can consider the Automatic Tuning Optimizer as an extension and improvement to the runtime query optimizer that is available on call when required. This way, the overhead associated with in-depth analysis is not imposed on the normal, everyday SQL statements during parsing, but is reserved for invocation on high-load SQLs only, aiding efficient use of computing resources.

In normal mode, during the parse phase, the query optimizer generates an execution plan for a given SQL statement within a short amount of time, usually less than a second and at worst no more than few seconds. Because of this self-imposed stringent requirement, the optimizer performs a limited plan search by using built-in heuristics to pare down the optimization phase of parsing. As well, there is no investigation and verification of objects and their statistics during the plan-generation process. On the other hand, the Automatic Tuning Optimizer is typically given much more time, usually in minutes, to perform the necessary investigation and verification steps as part of the tuning process. Thus, the Automatic Tuning Optimizer has a much higher probability of generating a well-tuned plan. Because it is given sufficient time, the Automatic Tuning Optimizer uses dynamic sampling and partial execution techniques (that is, execution of fragments of a SQL statement) to verify its own estimates of cost, selectivity, and cardinality. It also uses the past execution history of the SQL statement to determine optimal settings when such history is available. For example, if it determines that a small number of rows will be returned by the SQL, then it may switch the OPTIMIZER_MODE from the default ALL_ROWS (which works toward higher throughput) to FIRST_ROWS (which works toward higher initial response).

Parameters Controlling the Optimizer Permutations

Most time and resources spent during the parsing of SQL is during the preparation of the execution plan. When an SQL is complex and has a large number of table joins, it is possible for the optimizer to consume a lot of CPU cycles, data dictionary I/O, and shared pool space computing the estimated cost of access for many thousands of permutations. Until the last release of Oracle 9i R2, the Cost Based Optimizer was restricted to calculating a specific number of such permutations using the OPTIMIZER_MAX_PERMUTATIONS and the related but hidden OPTIMIZER_SEARCH_LIMIT initialization parameters. The original default value of 80,000 in Oracle 8i for OPTIMIZER_MAX_PERMUTATIONS was scaled down to a reasonable value of 2000 in Oracle 9i R2. Ultimately, both these parameters are now hidden in Oracle Database 10g. The default value of 2000, however, has been retained, and it is assumed that this is the restricting factor for the normal mode of the optimizer. For more details, read MetaLink Notes #66030.1 and #62284.1.

When invoked, the Automatic Tuning Optimizer performs four types of analyses during the plan-generation phase:

1.	Statistics analysis. This is the most basic of checks, something that a performance analyst would do when tuning a poorly performing SQL statement. The Automatic Tuning Optimizer checks each object involved in the query for missing or stale statistics, and makes recommendation to gather relevant statistics. This may be a moot point because optimizer statistics are normally automatically collected and refreshed. You need to keep in mind that this problem may be encountered only when automatic optimizer statistics collection has been turned off or tables or indexes have been created and populated, and the statistics have not yet collected because of the schedule. The Automatic Tuning Optimizer also collects auxiliary information to supply missing statistics or correct stale statistics in case recommendations are not implemented.
2.	SQL profiling. The Automatic Tuning Optimizer uses the auxiliary information collected in step 1 to mitigate estimation errors as well as build an entity referred to as an SQL profile. This SQL profile enables the query optimizer to generate a well-tuned plan when executing later in the normal mode, as it forms a template for the better plan. This is in some ways similar to the use of Outlines to force SQL to behave in a certain way. SQL profiling allows ill-written SQL to execute efficiently without having to change the original code, and is thus ideal for use in tuning third-party applications. We will look at SQL profiles in detail in the next chapter.
3.	Access path analysis. This is another one of the basic steps that a performance analyst would normally perform when tuning an SQL statement. The Automatic Tuning Optimizer explores whether a new index can be used to significantly improve access to each table in the query and, when appropriate, makes recommendations to create such indexes. At this point in time, such information can prompt the SQL Tuning Advisor layer that overlays the Automatic Tuning Optimizer to invoke another advisor, namely the SQL Access Advisor.
4.	SQL structure analysis. This is the final step that a performance analyst would normally perform during SQL tuning. In this phase, the Automatic Tuning Optimizer identifies SQL statements that are prone to generate poor plans, and makes relevant suggestions to restructure them. This restructuring can involve syntactic as well as semantic changes to the SQL code.

As you may have noticed, these steps are similar in both sequence and nature to those used during manual SQL tuning. Automatic SQL tuning just performs this for you on demand, tirelessly, automatically, and free of charge! The SQL Tuning Advisor makes this wealth of tuning information available in an understandable and usable form.

The SQL Tuning Advisor is available for access both via OEM as well as via the advisor and SQL tuning API, described later in this chapter.

Inputs to the SQL Tuning Advisor

The SQL Tuning Advisor takes inputs from multiple sources. The SQL Tuning Advisor can deal with more than one SQL statement at a time, so you can submit a composite SQL workload for analysis. As you saw in previous chapters, high-load SQL statements identified by the ADDM form the main input for the SQL Tuning Advisor, which now plays the role of the Specialist in the Patient-General Practitioner-Specialist story. Equally well, these statements could have also been identified by the AWR or even by a manual process. The manual process could include the testing of a set of SQL statements that are yet to be deployed, tuning individual performance as well as a collection of SQL statements. This collection is now turned into a persistent tuning object called the SQL tuning set (STS), which is described in detail later in this chapter.

The SQL Access Advisor

The SQL Access Advisor works alongside the SQL Tuning Advisor and could be called by the former when appropriate. It is a tuning tool that provides advice specifically on materialized views, indexes, and materialized view logs. Given a specified workload in the form of a SQL tuning set or even individual SQL statements, the SQL Access Advisor recommends the creation 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 SQL Access Advisor considers the tradeoffs between space usage and query performance and recommends the most cost-effective configuration of new and existing materialized views and indexes. The SQL Access Advisor is actually based on and builds upon the Oracle 9i Summary Advisor.

The SQL Access Advisor interfaces to generate SQL when requested. You can get to this tool both via the OEM as well as via the advisor and SQL tuning API.

Materialized Views (MV) and MV Logs

A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views. Note that the term snapshot was used for materialized views in Oracle Version 7.

On the other hand, a materialized view log is a shadow table that stores a record for every change made to its master or source table. Essentially, it is used to perform refreshes of the corresponding materialized views. For more details, refer to the Oracle Database 10g Administrator's Guide and the Oracle Database 10g Advanced Replication manual.

Inputs to the SQL Access Advisor

The SQL Access Advisor can use the following inputs in order to operate on and produce recommendations:

• Current contents of the SQL cache

• Current object in a specified schema

• User-defined tables

• Additional SQL statements in a workload

• SQL tuning set

The steps to use the SQL Access Advisor are as follows:

1.	Create a task and optionally define parameters.
2.	Create or specify a workload using any of the aforementioned input methods listed. Optionally define workload parameters.
3.	Generate recommendations using either the OEM or the APIs.
4.	Review and implement the recommendations as appropriate.

As with all other advisors, the option is left to the performance analyst or DBA to implement the recommendations.