Understanding Automatic SQL Tuning in Oracle10g

SQL tuning is a critical facet of database performance tuning and requires high level of expertise as it is a time consuming and inherently complicated task. The new Automatic SQL tuning feature of Oracle10g has been implemented as an enhancement of the Oracle Query Optimizer and is also an integral part of the Oracle’s framework for self managing Databases. It helps to relieve the workload of DBA for the challenging task of tuning that requires in depth knowledge in query optimization, SQL design and also an intimate knowledge of the database structures.

The Automatic SQL Tuning component of Oracle10g comprises of the Automatic Tuning Optimizer and the SQL Tuning Advisor. The Automatic SQL Tuning is based on an extension of the Oracle query optimizer called the Automatic Tuning Optimizer. In the normal mode the query optimizer needs to make decisions based on execution plans in a short time. As a result it may not have sufficient information to make the best possible decision.

Oracle10g allows the optimizer to perform in tuning mode where it has a larger time budget to perform necessary investigation and analysis, so that it has adequate information to recommend practices on tuning. The Automatic Tuning Optimizer performs additional tasks such as SQL Profiling, verification of data statistics and What-If tuning analysis.

The tuning output which is a set of recommendations is present to the user via the SQL Tuning Advisor. The SQL tuning advisor offers an automated, powerful interface for the performance of SQL tuning by accepting SQL statements and passing it to the Automatic Tuning Optimizer and also by relaying the tuning advices which is a result of SQL profiling and verification performed by the Automatic Tuning Optimizer to the user. The advice consists of one or more recommendations along with the rationale and an estimate of the corresponding benefit.

THE SQL Profile is a result of Statistics Analysis, Estimates Analysis, and Parameter Settings Analysis. The Statistical Analysis verifies that there are no missing statistics and what is present is not outdated. If it is either missing or stale then auxiliary information is generated to compensate or recommendations to gather statistics for the objects is given as output.

Estimate analysis lies in the ability to derive the size of intermediate result when deciding the join algorithm. Parameter Settings Analysis makes use of the past execution history of a SQL statement to conclude the correct optimizer settings. It constitutes a customized parameter setting for SQL statements. When the SQL Profile is created, it is used in addition with the existing statistics by the Oracle Query Optimizer to produce well tuned plans.

The Automatic Tuning Optimizer determines the candidate indexes that could be a potential factor in improving performance. The effectiveness of the candidate indexes are evaluated based on the statistics of its relevant tables. Based on the cost of the plan the Automatic Tuning Optimizer recommends the usage of these indexes. It may also recommend the use of SQL Access Advisor which is a workload based server side tuning solution.

The Oracle Query Optimizer performs extensive query transformations based on cost-based selection or heuristics while preserving the semantics of the original query. The SQL statements that cause poor performance due to poor syntax based constructs or semantic based constructs or Design issues are detected by What-if Analysis and appropriate recommendations are given for changing. Accompanying each recommendation is also a rationale given in terms of cost improvement. This can guide the application developers in writing well formed SQL.

The scope of SQL tuning extends beyond tuning a single SQL statement. The focus is on making the database self managing by providing end to end solution to the tuning challenges faced by application developers including handling of High-load SQL representing subset of SQL statements that are resource and time consuming.

The SQL tuning lifecycle that is performed in Oracle10g has four key components such as AWR (Automatic Workload Repository), ADDM (Automatic Database Diagnostic Monitor), STS (SQL Tuning Set) and STB (SQL Tuning Base).
This is generally intended to be used only on high load resource demanding statements.

The three phases of the Oracle10g self managing loop are Observe, Diagnose and Resolve. The Observe phase is automatic and continuous. The chief component is the AWR which is a repository of performance and system data. The Diagnose phase refer to the analysis of the various parts of the database system referring the data in AWR.

THE ADDM is a central database performance diagnostic engine that runs automatically and identifies causes of problems and also provides solutions to resolve. The SQL Tuning Set features SQL statements with their execution context and statistics. The Resolve phase is when the recommendations are implemented or applied to the database by the database itself or on manual initiation. The SQL Tuning Base stores and manages all the tuning actions related to specific SQL statements.

The Automatic SQL tuning can scale over a large number of queries that change over time depending on the application workload. It comprehensively explores all the different ways to tune an SQL statement and provides recommendation. The SQL Tuning Advisor offers a powerful tuning interface and SQL Access Advisor recommends and advices which indexes or materialized views to create or retain or drop.

The superiority of this new technology can be understood by comparing the results of Automatic SQL Tuning to manual tuning on simulation of an actual customer workload. An understanding of the self tuning infrastructure and capabilities can help the DBA’s and Application Developers to go along way in reducing the daunting challenges and tedious repetitive tasks involved in SQL tuning.

| An Introduction to Oracle’s JD Edwards EnterpriseOne Tools | Avoiding-sql-injection-in-pl-sql-oracle1og | Developing PL/SQL Web Applications in Oracle10g: An Overview | Identifying Rows by Address in Oracle 10g | Introducing Oracle OLAP option to Oracle Database 11g | Some Exciting New Features of Oracle11g | The Benefits of Partition for Improved Performance in Oracle Database 11g | Understanding Automatic SQL Tuning in Oracle10g | Using XMLTextReader Class for Better Access to XML |