Automatic SQL Tuning - Oracle 11g

Although a DBA could manually tune poorly performing SQL statements, the large number of distinct SQL statements in any real-life production database and the expertise it requires to write good SQL code, design fast data access, and work with explain plans and trace output makes manual tuning quite a daunting task, besides being extremely time consuming.

Oracle Database 10g gave you two excellent performance tuning tools, the SQL Tuning Advisor and the Automatic Database Diagnostic Monitor (ADDM),to facil itate tuning SQL queries.As candidates for tuning,ADDM picks high-load SQL statements that provide the biggest bang for the buck in the form of improved performance.You must look at the ADDM reports and then run the SQL Tuning Advisor to receive recommendations for improving performance.The SQL Tuning Advisor makes recommendations to improve problem SQL queries,due either to a poorly designed query or to a query that wasn’t making use of optimal statistics.The SQL Tuning Advisor’s recommendations might include gathering statistics, adding indexes, creating a SQL profile, or modi fying the SQL statements.

Even when you got recommendations from the SQL Tuning Advisor that saved you the rigors of performing a manual SQL tuning, in Oracle Database 10g you were still in charge of evaluating and then actually implementing therecommen dations made by the SQL Tuning Advisor.In Oracle Database 11g, Oracle goes much further in automating the entire SQL tuning process, as we explain in the next section.

Automation of SQL Tuning in Oracle Database 11g

In Oracle Data base 11g,the new Automatic SQL Tuning Advisor task runs nightly by default. As is the case with the other two automated management tasks—the Optimizer Statistics Gathering and Automatic Segment Advisor tasks—the Automatic SQL Tuning Advisor task is enabled by default when you create a database or migrate an Oracle 10g database to the 11g release.The new automatic task, named the Automatic SQL Tuning Advisor task(sys_ auto_ sql_ tuning_ task), runs automatically every night, as part of the automatic maintenance task framework, looking for ways to speed up the perfor mance of high-load SQL statements by improving their execution plans.

Automation of SQL Tuning in Oracle Database 11g

The Automatic SQL Tuning Advisor task does the following things:

  • Identifies inefficient SQL statements
  • Runs the SQL Tuning Advisor to get recommendations for improving the problem statements
  • Tunes problem statements by implementing the SQL profile
    recommendations made by the SQL Tuning Advisor

In the following sections,we’ll cover how the new automatic SQL tuning feature works in Oracle Database 11g.

Identifying Candidates for SQL Tuning

The Automatic SQL Tuning Advisor job runs every night during the maintenance window of the automated maintenance tasks feature.You don’t need to provide any SQL tuning sets for the job, because the tuning task selects the workload based on its analysis of database performance.The Automatic SQL Tuning Advisor task depends on the AWR for the selection of candidates for further analysis. Automatic SQL tuning pulls SQL from the AWR that was at the top of the list of SQL queries that performed poorly during four different time periods, which it calls buckets. These four buckets are based on weekly, daily, hourly, or top SQL in a single SQL execution based on response time. Automatic SQL tuning combines the top queries from the four buckets into one by assigning weights.

The database considers the following types of queries ineligible for automatic SQL tuning, although you can still tune them (except ad hoc SQL queries) manually using the SQL Tuning Advisor:

  • Parallel queries
  • Ad hoc SQL
  • Any SQL statement that wasn’t executed more than once during the past week
  • DDL statements
  • Queries that are still at the top of the poorly performing query list, even after profiling
  • Recursive SQL statements

Running the Automatic SQL Tuning Advisor

The first step in automatic SQL tuning is to find the bad SQL statements to tune.Once the Automatic SQL Tuning Advisor task generates a list of candidate SQL statements,the advisor orders the candidate SQL list in the order of importance.The SQL Tuning Advisor then tunes each of the candidate SQL statements in the order of importance and recommends SQL profiles to improve theperformance of the SQL statements.

Implementing the Recommendations

If the SQL Tuning Advisor finds an existing SQL profile,it checks the optimizer statistics to ensure they do exist and that they are up-to-date.If not,it lets the gather_stats_task job know about it so it can collect the necessary statistics.

The automatic SQL tuning process may make other recommendations besides the adoption of a SQL profile,such as creating new indexes,refreshing statistics,or even restructuring the SQL state ments.However,you must review and manually implement these other recommendationsyourself,as in Oracle Database 10g.The data base implements only the SQL profiles automatically.

An interesting aspect here is that the database doesn’t automaticallyimplement all SQL profiles that the SQL Tuning Advisor generates during the tuning process.It implements only those SQL profile recommendations that will improve performance by at least threefold.To be more precise, here are the requirements a SQL profile must satisfy before the database automaticallyimplements it:

  • There should be a threefold improvement in the sum of the CPU time and IO time.
  • Neither CPU time nor IO time must deteriorate because of the new SQL profile.

The reason for the previous set of requirements is to ensure that anycontention caused by the new SQL profile doesn’t make performance actually worse than running the query without the SQL profile.The automatic SQL tuning process tests SQL statements with and without the SQL profile before making the recommendation to accept the new profile.

The automatic SQL tuning process doesn’t modify the offending SQLstatement in any way,and you can reverse its actions any time by removing the new profiles that it implemented.

Managing Automatic SQL Tuning

Managing the automatic SQL tuning feature involves configuring the automatic SQL tuning feature on and off, and second, managing the scheduling of the task during the maintenance window.You also need to configure theparameters of the Automatic SQL Tuning Advisor task.These task parameters include actions such as setting time limits on thetuning tasks, turning SQL profile implementation on and off, and even disabling the testing of new SQL profiles, if the time to implement the changes is a major concern.

Like most components of the advisor framework,you can tune automatic SQL tuning through Enterprise Manager Database Control (or Grid Control) orthrough Oracle-supplied PL/SQL packages.Next we’ll cover how you canmanage automatic SQL tuning through Enterprise Manager Database Control.

Using Database Control

The easiest way to manage most components of the automatic SQLmanagement framework is by using Database Control.Follow these steps to manage automatic SQL tasks through Database Control:

  1. Click the Server tab on the home page of Database Control.
  2. On the Server page, click the Automated Maintenance Tasks link in the Oracle Scheduler section.
  3. On the Automated Maintenance Tasks page, click the Automatic SQLTuning link.
  4. You’ll now be on the Automatic SQL Tuning Result Summary page.To turn automatic SQL tuning on and off, go to the Configure Automatic SQL Tuning page by clicking the Configure button in the Task Status section of this page. If you want, you can enable the feature only for some days of the week.
  5. Click the View Report link in the Task Activity Summary section to view the Automatic SQL Tuning Result Details page,shown in Figure.Here, you can view all recommendations made by the SQL Tuning Advisor,and you can choose to implement some or all of the recommendations from this page. At the bottom of the same page, you can view a histogram showing the pre-change response time and another showing the tuning benefit.

The Automatic SQL Tuning Result Details page

The Automatic SQL Tuning Result Details page

Using the DBMS_AUTO_TASK_ADMIN Package

Use the DBMS_AUTO_TASK_ADMIN package to enable and disable theAutomatic SQL Tuning Advisor task and to control various aspects of executing the SQL Tuning Advisor task. Here’s a summary of the main procedures youcan use to control the execution of the Automatic SQL Tuning Advisor task:

  • enable: Lets you enable a previously disabled operation,client, target type or target to be enabled again.
  • disable: Lets you prevent executing requests from a specific client or operation.
  • get_client_attributes: Returns select client attribute values.
  • get_p1_resoruces: Returns the percentage of resources allocated to each automatic maintenance task’s High Priority Consumer group.
  • set_p1_resources: Sets the resource allocation for each High PriorityConsumer group used by the automatic maintenance task clients.
  • override_priority: Lets you override task priorities.

SQL Access Advisor Enhancements

Oracle introduced the SQL Access Advisor in the Oracle Database 10g release to help you determine efficient access structures such as indexes, materialized views, and materialized view logs.The SQL Access Advisor either takes the actual database workload (SQL cache) or derives a hypothetical workload or a SQL tuning set as the basis for making recommendations for better execution of SQL statements.You can run the advisor in limited mode or in comprehensive mode, as was the case with this advisor in Oracle Database 10g.

In Oracle Database 10g,the SQL Access Advisor provided recommendations regarding indexes, materialized views, and materialized view logs.New in Oracle Database 11g, the advisor will also recommend partitions fortables, indexes, and materialized views.The partition enhancements are part of the Partition Advisor, which is inte grated into the SQL Access Advisor.

The SQL Access Advisor now shows you the expected gains of implementing its recommendations.In addition, the advisor also reports intermediate results by breaking up the workload into parts.These intermediate results are called publish points.

Partitioning Recommendations

In addition to the normal recommendations to create or drop indexes,materialized views,and materialized view logs,you’ll now also get recommendations to partition existing tables and indexes.The advisor may also recommend adding a partitioned index on a table or a materialized view or adding a new partitioned materialized view.In some case, the advisor may recommend replacing the current table partitioning with a more efficient partitioning scheme.

The SQL Access Advisor may recommend range, interval, list, hash, range-hash, and range-list partitioning schemes for base tables and indexes.It mayrecomm end local, range, and hash partitioning schemas for partitioningindexes.

To implement its partition recommendations,the SQL Access Advisor generates an implementation script,which may invoke an external package such as the DBMS_REDEFINITION package, and partitions the base table online.If the table that the advisor is going to partition has any bitmap indexes defined on it, the DBMS_REDEFINITION package may fail.In this case, you must drop the bitmap indexes before the advisor implements its partitioning recommendations and create the indexes after the table partitioning is completed

Here are some key things to note about the partition recommendations:

  • If the partitioning recommendation is one of several other recommen dationssuch as creating indexes beside the partitioning recommendation, you can’t implement the other recommendations only and decide not to part ition the table or index.This is because the other recommendations are contin gent upon the implementation of the partitioning recommendations.
  • The SQL Access Advisor recommends only single-column interval and hash partitioning, with interval being the default.Hash partitioning is designed to take advantage of partition-wise joins.
  • The advisor recommends partitions only for tables that have at least 10,000 rows.
  • The partitioning recommendations may take a considerable amount of time to complete, if the table is large.
  • Partitioning recommendations are limited to workloads that contain predic ates or join table columns of type NUMBER or DATE only.
  • You’ll need additional storage if the DBMS_REDEFINITION package isinvoked, since that package creates a temporary copy of the table during the online reorganization of a table.

Accessing Intermediate Results

As you are probably aware, the SQL Access Advisor takes a considerableamount of time to execute, especially when you run it in comprehensivemode.In Oracle Database 11g, you can access the intermediate results while the advisor is still executing.This gives you the opportunity to cut short an advisor run if the intermediate results aren’t appealing to you. Similarly, if the intermediate results show evidence that the recommendations will lead to a significant benefit, you can cut short the advisor run by interrupting itmidstream.

The SQL Access Advisor bases its partitioning recommendations on its analysis of a significant proportion of the workload.Thus, if you interrupt an advisor task early in the game, the advisor may not include any base table partitioning recommendations in the intermediate results.If you decide to interrupt, wait for a substantial portion of the workload to complete so you can get the partitioning recommendations as well.

To accept the intermediate results, you must formally interrupt the advisor task.The advisor will then stop executing the task and mark it as interrupted. Following this, you can either ask the advisor to complete the recommendation process or choose to accept the recommendations and generate scripts for implementing them.

New DBMS_ADVISOR Procedures

Two new procedures in the DBMS_ADVISOR package have a bearing on the SQL Access Advisor.These are the add_sts_ref and copy_sqlwkld_to_sts procedures.

ADD_STS_REF Procedure

You can use the add_sts_ref procedure to link a SQL Access Advisor task to the SQL tuning set.The advisor task can use this link to access the SQL tuning set for analysis purposes. Here’s the structure of the add_sts_ref procedure:

Here’s an example showing how to use the add_sts_ref procedure to link a task (task1) toa SQL tuning set (sts_1):

SQL> exec dbms_advisor.add_sts_ref ("task1','hr', 'sts_1');

Once you create a link between the advisor task and the SQL Tuning Advisor task,the STS can’t be altered or deleted, thus providing a stable view of the workload for the advisor.


The copy_sqlwkld_to_sts procedure transfers the contents of a SQL workload object to an STS.The copy_sqlwkld_to_sts procedure has the following attributes:

The workload_name attribute refers to the source SQL workload,and thests_name attribute points to the target STS into which you’ll be importing the SQL Workload object.

The import_mode attribute can take the value append, new, or replace and determines the handling of the data in the target STS upon the import of the SQL Workload object.Here’s an example showing how to use thecopy_sqlwkload_to_sts procedure:

SQL>exec dbms_advisor.copy_sqlwkld_to_sts ('workload_1', 'sts_1','new');

Oracle will create a new STS if the STS you specify doesn’t exist.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics