10G Automatic Tuning Optimizer

Oracle has integrated a new feature into the optimizer that allows it to provide automatic SQL tuning. The optimizer has been enhanced to perform in-depth tuning of SQL statements that are fed to it by the SQL Tuning Advisor. For more information on how to run the SQL Tuning Advisor, please refer to my blog titled "The SQL Tuning Advisor."

In that blog, I stated that it is the optimizer's job is to create the most high performance access path in the shortest time possible. It's easy to understand how these two objectives can be viewed as "mutually exclusive." In a perfect world, you would give the optimizer as much time as it needs to create the most optimal access path. In the real world, you need to get the statements executing as quickly as possible. That includes optimization.

Since the SQL Tuning Advisor does not have time constraints, the tool can take as much time as necessary to optimize the statement. This allows the optimizer to generate high-quality recommendations. The premise is the more time it spends performing the analysis, the higher the quality of recommendations it will provide. The SQL Tuning Advisor Home Page provides a drop down menu that allows the administrator to "throttle" the amount of time the utility spends optimizing the statement.

In tuning mode, the optimizer is able to perform a higher level of analysis than in normal mode where it is required to generate access paths as quickly as possible. The output the optimizer produces in normal mode is an access path. The output the optimizer produces in tuning mode is a series of recommendations. Those recommendations include information justifying why it generated those recommendations. This prevents the advisor from becoming a "black hole" that pumps out advice without supporting information. The recommendations may include collecting statistics on objects, new index creation, restructuring the SQL statement or the creation of a SQL Profile to create a more optimal access path. When the optimizer is called by the SQL Tuning Advisor, Oracle refers to the optimizer as the Automatic Tuning Optimizer or ATO.

The Four Types of Analysis Performed by the ATO
When the SQL Tuning Advisor activates the ATO, the optimizer performs four types of in-depth analysis:

Statistics Analysis
Craig Mullins, well known DB2 Guru, provides an excellent description of a database optimizer in The DB2 Developer's Guide "the optimizer is equivalent to an expert system. An expert system is a standard set of rules when combined with situational data can return an expert opinion."

The cost based optimizer uses statistics generated by the DBMS_STATS procedure as the situational data when creating its expert opinion on which access path to the data is most optimal. These statistics are stored in the data dictionary and describe the data object's space characteristics, data uniqueness and data distribution.

The cost-based optimizer is only as good as the statistics it uses as input. Statistics collections should be run on a regular basis to ensure that the statistics are current (representative of the data being accessed). The optimizer is then able to create a highly accurate access path that is based on the least cost. If statistics are not available, the optimizer uses an algorithm to calculate the statistics, which often leads to "less than optimal" access paths.

The ATO checks all of the objects accessed by the SQL statement evaluated for missing or stale statistics. The ATO will make recommendations to generate statistics for objects that have statistics that are missing or stale. In addition, the ATO will collect auxiliary information to compensate for substandard statistics.

SQL Profile Generation Analysis
Before we begin discussing how the ATO creates a SQL Profile, a quick comparison between Stored Outlines and SQL Profiles is in order. A common misconception is that a SQL Profile is like a Stored Outline.

A Stored Outline is a set of optimizer hints that are attached to a SQL statement that freezes its access path. When the Stored Outline is enabled, Oracle uses the stored hints to generate the access path. Administrators are able to manually change the access path of a given statement by using the Outline Management Editor in Oracle Enterprise Manager.

Another method to change the access paths is to manually update the hints that are attached to the SQL statement. Since the hints are stored in a table, administrators are able to use SQL DML statements to alter them. I just find the SGT (Sissy GUI Tool) method to be so much easier that I use Oracle Enterprise Manager religiously to change a Stored Outline's access path. For more information on Stored Outlines, please refer to my blog appropriately titled "Optimizer Plan Stability in Oracle9i". Although the title says Oracle9i, the information is still pertinent in 10G.

A SQL Profile is a collection of information gathered by the ATO that is specific to a particular SQL statement. The information is stored in the data dictionary and is used by the optimizer to make more informed, and more intelligent, access path decisions for that statement.

As stated previously, we expect the optimizer to generate access paths as quickly as possible. As a result, the optimizer often makes estimates on cardinality and selectivity to generate the access path. During the SQL Profile analysis phase, the ATO will verify predicate selectivity, cardinality and optimizer settings. The ATO accomplishes this by:

• Dynamically sampling the data being accessed and applying the predicates used in the SQL to the sampled data. If the ATO determines that the difference between its original estimates and the statistics generated by the dynamic sampling is large enough to warrant a correction, it will change its original estimates to more accurately reflect the data being accessed.
  
  
• Decomposing the SQL statement into fragments and executing the fragments to gather access path and performance related information.
  
  
• Evaluating the SQL statement's past execution history. One of the outcomes of this evaluation could be a change in the optimizer mode (first rows vs all rows) for the statement being analyzed

If you take a look at this screenshot from my blog on the SQL Tuning Advisor, you will see the ATO recommending that I accept a SQL Profile.If I accept the tool's recommendation, the optimizer will use the information contained in SQL Profile in addition to the normal database statistics to generate an optimal execution plan.

Unlike its Stored Outline counterpart, a SQL Profile does not freeze a statement's access path. As the data in the objects being accessed changes as a result of normal database operations, the information contained in the SQL Profile may become outdated. This could result in less than optimal access paths being generated. This problem can be corrected by running the SQL Tuning Analyzer again to regenerate fresh information or removing the SQL Profile altogether.

Access Path Analysis
When the SQL Tuning advisor activates the ATO to perform detailed SQL analysis, it also provides advice on indexes. When the SQL Tuning Advisor generates an index recommendation, the output usually includes a recommendation to run the SQL Access Advisor.

We know that an index has the possibility of influencing all SQL statements that access the table that the index is built upon. An index that provides fast access for one query may negatively impact other statements. The scope of analysis the ATO performs does not include a global review of how the new index will affect the entire SQL workload. As a result, the SQL Tuning Analyzer will often recommend that the SQL Access Advisor be run on a representative SQL workload along with the query that is being analyzed. The result is a much more global set of index recommendations that takes all of the queries contained in the representative workload into consideration.

SQL Structure Analysis
The ATO also examines the structure of the statement being analyzed to determine if it can be coded differently to obtain a more optimal access path. During analysis, the ATO compares the structure of the SQL statement against a set of coding best practices. The ATO does not rewrite the query, but will provide a recommendation for an alternative SQL statement structure that it thinks would generate a more optimal access path. The ATO attempts to identify design mistakes such as Cartesian products, predicates that don't match the datatype of indexed columns, UNION vs UNION All etc.

Summary
The SQL Tuning Advisor, SQL Access Advisor and the ATO combine to make a powerful tuning tool that database administrators can use to ensure optimal SQL execution performance. The future of Oracle database tuning will be administrators interpreting and implementing the recommendations generated by the advisors and ADDM. I absolutely and firmly believe that the advisors and ADDM will become more intelligent in their recommendations. As their intelligence increases, the usage of statistics dumps to attempt to improve database performance will decrease.

Thanks for reading,
Chris Foot