Richard To's Blog

A New Intelligent Test Run Function is under Research

Richard To — Fri, 13 Jun 2008 11:20:00 GMT

A problem in the area of SQL optimization that has been around for a long time is the inaccuracy of the cost estimation of a SQL statement. A lot of people have the wrong conception that inaccurate cost estimations generated for a SQL statement by the database optimizer are caused by outdated statistics; whereas in fact, outdated or incorrect statistics is only one of many reasons that cause the database to generate an inaccurate SQL cost estimation. This means that even if you have 100% accurate statistics on your tables and indexes, you will not necessarily have 100% accurate cost estimation for your SQL statements.

Most of the poorly performing SQL statements found in production databases are caused by the wrong SQL cost estimation. For a more detailed discussion on this topic, see my previous blog Why SQL Tuning?.

In Quest SQL Optimizer, we use an Artificial Intelligence technique to generate syntactically different, but semantically equivalent SQL rewrites. This provides several alterative SQL statements for the original SQL. But, since we cannot rely on the database SQL cost estimation to identify the best SQL alternative, we still have to test run those alternatives in a production database or similar environment to pick out the best alternative SQL statement. A close look at the testing results of several different SQL statements will show you that the best alternative SQL statement is not normally the one with the lowest cost in most of cases. This is why Quest SQL Optimizer provides a test run function to help users to pick out the best alternative SQL statement. But a physical test run for all alternative SQL statements is time consuming, so some users may select only those SQL with relatively lower cost to test run without going through all the alternatives. This approach can save time, but it may miss some potentially better alternatives whose estimated costs have been wrongly estimated high.

It has been on my mind for many years to try to find a way to quickly identify the best SQL alterative from all the alternatives generated by Quest SQL Optimizer. There is no easy solution to this problem. Since each database environment is unique and the cost estimation is not reliable, I knew that what was needed was an algorithm which could save users time when test running the alternatives while not missing any potentially good alternatives. Recently, I have been involved in a research project that uses an AI cluster technique to improve the traditional Fuzzy Self-learning algorithm. One of the benefits that have come out of this research is that I found a better way to automatically select which SQL alternatives to test. It is to use an AI cluster technique that we have developed which provides a simple and useful solution to this long standing problem in our Quest SQL Optimizer

Let me illustrate this using the following SQL statement:

select * from employee
where emp_dept in (select dpt_id from department
                   where dpt_name LIKE 'D%')

I put this SQL statement into Quest SQL Optimizer so that it could be rewritten by our optimization process using an AI engine. At the intelligence level 4, in less than 10 seconds, 15 alternatives were generated:

Let’s have a closer look at the cost of all the SQL alternatives and the cost distribution chart for the SQL statement and the 15 alternative SQL statements generated by Quest SQL Optimizer for Oracle:

As you can see in the chart, the cost distributions of all SQL alternatives are grouped into multiple regions. SQL statements with similar cost normally have similar execution plans which results in similar performance.

Executing the original and alternative SQL statements produced the following run times.

In reviewing the run times, you can look at the Plan Cost and see that the Total Elapsed Time and Total CPU time for each of the alternatives with a similar cost are very close. Sometimes, because of how long it takes to execute a SQL statement, testing all of the alternatives is not practical. So, to save time when testing the alternatives, instead of testing only the SQL statements with the lowest cost, it is best to test one SQL statement from each group of SQL with a similar cost.

For this example, Alt #12 is the fastest SQL statement even though the estimated cost from Oracle is among the highest. The fastest SQL statement would have been missed if only the SQL statements with the lowest cost were selected for testing.

The behavior we see in this example, that SQL statements with similar cost have similar run time, is common across the broad whether the syntax of the SQL statement is simple or very complex.

A new Algorithm for select SQL to test

It is an easy task for a person to look at a group of SQL statements sorted by the cost value and pick one SQL statement from each group of SQL statements that have a similar cost. But it is not so easy to develop an algorithm that can correctly do the same thing, which is to start with a fixed number of points (in this case the Plan Cost values) that have a wide range with an indeterminate granularity of numbers and then group them so that one can be selected. In the field of Artificial Intelligence, this issue would be classified as a clustering problem.

The next generation of test running the SQL statements in Quest SQL Optimizer will have an Intelligent Test Run function. This function will be based on an algorithm, developed in the Fuzzy Self-learning project, which will simplify the clustering process (the process of grouping the SQL statements with similar costs) so that Quest SQL Optimizer will be able to select one SQL statement from a group of SQL with similar cost.

The algorithm is too technical and complex to describe here, but for sure, you will enjoy a faster and better SQL optimization product soon.

Batch Optimizer Part 5 – Viewing the SQL alternatives and execution run time statistics

Richard To — Fri, 23 May 2008 12:07:00 GMT

Written by Rene Woody

After a SQL statement has been optimized in the Batch Optimizer in Quest SQL Optimizer for Oracle, you can view the text of the SQL alternatives and the execution plans in the Tuning Lab module. If the original SQL statement and alternatives have been executed, you can view the run time statistics as well.

1. In the SQL in Selected Job window in the Batch Optimizer, click the SQL statement to highlight it.

2. Click Open in Tuning Lab .

Functions Available in the Tuning Lab

Since the Batch Optimizer limits the number of SQL alternatives that are executed automatically, in the Tuning Lab you can execute the alternatives that were not executed in the Batch Optimizer to see if you can find a faster alternative.

When you are viewing the details of a SQL statement that was optimized in the Batch Optimizer, in addition to the Execute function, you may also use the Test for Scalability, Store Session, Reports, Retrieve Results, and Alter Session Parameters functions.

Select the Best SQL Alternative

In the Tuning Lab as you view the SQL alternatives and the run time statistics from the execution of the SQL statements, you can change the alternative that is the “best” alternative based on your own review of all of the run time statistics.

In the Tuning Lab | SQL Details Layout | Scenario Explorer window, right-click the SQL statement and select Mark as Best in Batch Optimizer.

This changes the SQL alternative that is selected as the “Best Alternative” in the Batch Optimizer. When you generate the optimized script, this “Best Alternative” will be used to replace the original SQL statement.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

Batch Optimizer Part 4 – Options Settings for the Batch Optimizer

Richard To — Fri, 09 May 2008 13:53:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings for determining the criteria for selecting the best alternative, for terminating longer running SQL, and eliminating the effect of caching on the comparison run times.

Selecting the Best Alternative

The best alternative is selected based on the setting selected in the Best alternative selected based on lowest option. This is either the fastest Total elapsed time or the fastest First row elapsed time. The Total elapsed time is how long it takes to retrieve or process all the records. The First row elapsed time is how long it takes to retrieve or process the first record.

Termination Criteria

In testing of the SQL alternatives, the goal is to find a SQL statement that runs faster than the original. So as soon as it is determined that a SQL alternative is not the best alternative, it should be terminated to minimize the time it takes for testing.

The Terminate execution of SQL alternative if it runs longer than option provides three settings for determining when to terminate the execution of a SQL statement.

Run time of fastest SQL is the default setting. With this option, the original SQL statement is executed first and the time from that statement is used as the termination time for the next SQL statement. When a SQL statement runs faster than this termination time, the faster time is used as the new termination time. So you are always using the fastest run time as the termination time for the next SQL statement.

Run time of original SQL terminates a SQL alternative if it runs longer than the original SQL statement.

User defined time lets you set your own termination time. It first retrieves the run time of the original SQL statement. When the SQL alternatives are executed, they are terminated if they reach the specified time.

Accounting for Caching

Caching the data, the indexes, and the SQL statement can affect the comparison times if one SQL statement has to do some caching and others do not. Therefore, options are available so that you can select the one that provides you with the most accurate comparison of the run times in your environment.

The Retrieving run time executing optionhas these settings:

Original SQL twice using second run time and all others once

The first time you access data from a table, the data is cached into memory. This process takes a few moments. The next time you access that data, it is already in memory. Therefore, the first SQL statement executed will have the additional time it takes to cache the data included in the run time. Whereas, the SQL statements that follow do not have the additional time included in their run time. So to have a comparable test, the first SQL is run twice and the time from the second run is compared to the time from the other statements which are run once. In this case, all SQL statements are executed with the data cached.

All SQL twice using the second run time

For fast running SQL statements, executing all SQL statements twice and using only the second run time enables you to eliminate two additional caching factors that can affect the accuracy of the comparison results: caching the SQL statement and caching the indexes. If some SQL statements have been recently executed, then the SQL information for those statements is likely to be resident in the cache and the statements may execute faster because the SQL statement does not need to be cached. Also, if some of the SQL statements use different indexes, one index may be resident in the cache and the other may not be in the cache. So running all the SQL statements twice ensures that each index is cached.

All SQL once

For long running SQL statements, there is no need to run any statement twice since the effect from caching is diminished over time.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

Batch Optimizer Part 3 – Options Settings for the Batch Optimizer

Richard To — Fri, 25 Apr 2008 14:44:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings that determine which SQL statements will be automatically executed by the Batch Optimizer process and the method used for their execution.

Executing Options

When the optimization process is finished, the execution of the original SQL statement and the selected alternatives is automatically started if the Automatically execute SQL rewrites after optimization setting is selected.

The specific type of SQL statements which are executed automatically is determined by the SQL types selected under the Execute only these SQL statement types: SELECT, INSERT, UPDATE, or DELETE. By default, only the SELECT SQL statements are executed automatically.

Note: INSERT, UPDATE, and DELETE SQL statements are run in a transaction that is rolled back after each execution so that the data in the database is not changed by the execution process.

The Execute only these SQL alternatives setting also determines which SQL alternatives are executed. All of the options under this setting are based on the Oracle cost estimation for each SQL alternative in comparison to the Oracle cost of the original SQL statement. By default, only the ten SQL statements with the lowest Oracle cost are executed.

When executing the SQL statements in the Batch Optimizer, the default execution method is Run on server. This means that all SQL statements are executed on the server and do not return the data from the SELECT statements to the client. Executing the SQL statements with this method provides you with the time it takes the SQL statement to execute on the Server. It does not cause extra network traffic by sending the data to the client. When using this method, your logon account must have the privilege to use the SYS.DBMS_SQL package to execute SQL statements on the server.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

Batch Optimizer Part 2 – Options Settings for the Batch Optimizer

Richard To — Fri, 11 Apr 2008 06:05:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. The Batch Optimizer process is fully automated with the default settings in the Batch Optimizer options. If you would like, you can have any one of the three steps (searching for SQL, optimizing SQL, and testing SQL alternatives) pause and wait for you to continue the process. The option settings for the Batch Optimizer in Quest SQL Optimizer for Oracle enable you to control the types of SQL statements that are optimized and which of the SQL alternatives are executed.

This blog covers the options settings for the first two steps in the Batch Optimizer process, the searching for SQL statements and the optimization of the SQL statements. The other Batch Optimizer option settings will be covered in upcoming blogs.

Searching Options

The Batch Optimizer automatically starts searching for SQL statements in a job as soon as the job is added to the batch queue. By clearing the Automatically start extracting SQL when job is added setting, you can have the job wait until you manually start.

The jobs are searched using the settings in the SQL Scanner settings. The specific settings for the SQL Scanner will be covered in another topic.

Optimizing Options

When the searching process is finished, the optimization process automatically starts to optimize the SQL statements one-by-one. The SQL to automatically optimize setting determines the specific SQL statements which are optimized using the SQL classifications (Simple, Complex, and Problematic). For example, with the default setting of Problematic, only those SQL statements that are classified as Problematic during the searching process will automatically be optimized. If none of the SQL classifications are selected, then you must manually select each SQL statement to be optimized.

The SQL statements are optimized using the Optimizer settings. The specific settings for the optimization will be covered in another topic.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

Batch Optimizer Part 1 – How the Batch Optimizer Works

Richard To — Fri, 28 Mar 2008 12:01:00 GMT

Written by Rene Woody

We all know that it is important to create SQL statements that perform well in our database environment, but all too often, after creating a SQL statement that retrieves the correct results, the optimizing of the SQL statement is left for another time. Frequently, that time never arrives. Quest SQL Optimizer for Oracle automates the SQL optimization process so that it can be done for you automatically.

This blog is the beginning of a series that will cover the Batch Optimizer module and how to use it to find SQL statements with performance problems and generated better alternatives.

Batch Optimization

The Batch Optimizer in Quest SQL Optimizer for Oracle combines finding the SQL statements in your application source code and database objects, optimizing SQL, and testing SQL alternatives into one simple process. It fully automates the whole process of identifying problematic SQL in your database applications, rewriting the syntax of SQL statements, and executing the original SQL statement along with the alternative SQL statements to find the fastest alternative. Then it creates a script from your original source code in which the poor performing SQL statements are replaced with better SQL alternatives.

The Batch Optimizer begins when you add a “job” to the batch queue. You can do this from within Quest SQL Optimizer or from Toad, SQL Navigator, Spotlight, or Performance Analysis. A job consists of text which is expected to contain one or more SQL statements. A job may be a block of text, a database object, an ASCII or binary file, a job from the SQL Scanner module, a job from the SGA Inspector module, or a Performance Analysis SQL repository.

Finding SQL

The first action of the Batch Optimizer is to search through the text in each job to find the INSERT, UPDATE, DELETE, and SELECT SQL statements. These SQL statements are classified according to characteristics in the execution plan that are likely to cause performance problems and users can specify the classification criteria to suit their only needs through the options.

Optimizing SQL

The second action of the Batch Optimizer is to optimize the SQL statements that were found during the searching process. It exhaustively rewrites the syntax of the SQL statement and applies the Oracle optimization hints. This process produces a list of semantically equivalent and syntactically correct SQL statements that produce the same result set as the original SQL statement.

Since this process is done automatically in a batch process, option settings are provided to determine which SQL statements are optimized. The default settings only optimize the SQL statements that are classified as “problematic.”

Testing SQL alternatives

The third action of the Batch Optimizer is to execute the original SQL statement and the alternative statements to see if any of the SQL alternatives outperform the original SQL statement.

Since the optimization process can provide hundreds of SQL alternatives, option settings are provided to control the number of SQL statements that are automatically executed. By default, only 10 SQL alternatives with the lowest Oracle cost will be executed. This means that there are likely to be many more SQL statements that can be tested if no faster alternative was found by the initial test.

Note: If a SQL statement has a bind variable, the execution process will wait until you enter the value for the variable to execute the SQL statement. If the SQL statement is an INSERT, UPDATE, or DELETE statement, it will require you to confirm the execution before running it when you are using the default settings.

Creating Optimized Script

After the SQL alternatives are executed, if one of the alternatives is faster than the original SQL statement, then an “optimized” script can be created. This script is a copy of the original text that was scanned, with the poor performing SQL statements commented out and the faster SQL alternatives inserted after the comment. You can then take this script and replace the code in the database object or application source code.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

How to use the Quest SQL Optimizer

Richard To — Tue, 18 Mar 2008 13:31:00 GMT

Written by Rene Woody

Quest SQL Optimizer can do an extensive transformation of the syntax of a SQL statement while still maintaining the exact same result set. This exhaustive transformation can produce hundreds of SQL alternatives. So it is necessary to have some insight as how to find a SQL alternative that is better than your original SQL statement without spending too much time. This is especially true when you have a SQL statement with complicated syntax.

During the syntax transformation process, each section of the syntax of a SQL statement is reviewed and appropriate SQL transformation rules are applied to each section. The more complex the syntax of the SQL statement, the more rules there are that can be applied. Applying more rules naturally takes more time and results in many more SQL alternatives to evaluate.

How to optimize for complicated SQL syntax

When optimizing a SQL statement with complex syntax, the first approach can be to lower the number of alternatives that are generated by Quest SQL Optimizer for Oracle by changing the settings in the Optimizer options. You can do this using the Predefined Intelligence Levels or through customizing the individual Optimizer options. If this does not result in finding a better SQL alternative, then the settings can be changed to generate more alternatives.

By lowering the Intelligence Level, fewer optimization hints are applied, fewer Optimization options are selected, and the quotas for the total number of SQL alternatives generated are lowered.

You can also lower the number of SQL alternatives generated by selecting to apply only a specific category of the Oracle optimization hints from the Predefined Settings drop-down list and then select an Intelligence Level.

You can also limit the number of SQL alternatives that are generated through the Custom Settings option. This enables you to select specific Optimization options, Oracle hints, and quotas.

How to optimize for very simple SQL syntax

For a SQL statement with very simple syntax, there are likely to be very few ways to transform the syntax or there may be none at all. But the Oracle optimization hints can be applied to the SQL statement to see if Oracle will generate a different execution plan for the SQL statement. Quest SQL Optimizer for Oracle can apply around 50 Oracle optimization hints to a SQL statement and test to see if the hint results in a unique execution plan. These SQL alternatives can then be executed and their run time statistics can be compared to those of the original SQL statement to find the best alternative.

To apply all the hints to your SQL statement using Quest SQL Optimizer for Oracle,

1. Click Options.

2. Select Tuning Lab | Optimizer | Intelligence.

3. Select Predefined Settings.

4. From the drop-down list, select Use Oracle optimization hints.

5. Move the Intelligence Level slider to 10.

What about a Self-learning SQL Optimizer?

Richard To — Tue, 09 Oct 2007 12:45:00 GMT

There are at least two database vendors are trying to build self-learning SQL optimizers. The idea is to use actual statistics from executed SQL statements to rectify the future cost estimation of the same or similar SQL statements. It seems like a good idea, but, you will find that their existing self-learning SQL optimizer is either turned off by default or built as an individual tuning advisor. Of course, we cannot say that they will not provide a better and fully automatic solution in future. But the fact is that this technology is not mature enough to be turned on automatically today. Furthermore, database SQL optimizers have a lot of problems pending that still need to be solved. They should not just focus on the error of cost estimation without taking care of the small plan space problem.

To be honest, a self-learning database SQL optimizer is still only a dream. Using actual statistics to rectify future cost estimation may solve some problems, but it definitely cannot solve every SQL costing problem and limited plan space problem. Furthermore, new features will cause new problems. To my understanding, what they are doing is like providing a patch to the existing cost estimation problem. It is not a fundamental solution to the database SQL optimizer problem.

I have an idea in mind a few years ago that to build a SQL Tuning Agent with a Query Base Statistics database which offloads the original SQL optimizer from real time optimization. The Agent should be running during non-peaks hours to review all executed SQL statements (or resource intensive SQL). For each SQL statement, the Agent should generate more execution plans than the database SQL optimizer generates since the real-time SQL optimizer cannot spend much execution time during real time optimization. For each execution plan it generated, the statistics can be collected by a test run or partial or fully test run of the query plan. Of course, this Agent based SQL optimizer still faces the same problems a real time SQL Optimizer is encountering today, but the beauty of a SQL Tuning Agent is that it has no response time limitation. Any complex cost estimation, plan space extension or test run algorithm can be built piece-by-piece in the agent. In Oracle 11g, there is a new function called “SQL Plan Management”, I will discuss how it may evolve to this direction later in my blog.

A SQL Performance History from AWR

Richard To — Thu, 13 Sep 2007 22:51:00 GMT

I have been working on SQL Tuning research for more than 10 years, but my focus has changed to time-series forecast technology in the last two years. Recently, Oracle’s AWR and other database vendor’s statistics/metrics repository have drawn my attention. The relatively low cost of keeping performance statistics in databases, combined with today’s powerful CPUs and cheap storage has resulted in more database vendors keeping additional statistics in a time-series manner for future performance tuning or self-management purpose.

Since databases will have a time delay to respond to the new allocation of resources, using real time industrial control systems to automatically tune database resources will not be easy. The walk around is to record the cyclic performance behavior (baseline) of a database and tune it with a dynamic or static configuration based on the particular cyclic behavior, (some domain experts call it the “Signature” or “Baseline” of a database, but I like to call it “Cyclic behavior”). Basically, to analyze or forecast this behavior involves some complicated mathematical time-series forecasting and modeling techniques.

After two years of research, I found that using traditional mathematics is not an easy way to solve database’s time-series forecasting and modeling problems; so I tried to develop a new AI method to solve this problem. The result looks good and most database statistics can be modeled and forecasted properly, but it is not the main topic of today’s discussion. Once the prototype was completed, I tried to see whether I could use this algorithm in our Quest SQL Optimizer product. In order to collect some sample data, I scheduled a SQL statement which was randomly fired each hour in our server for two months.

The following is the SQL and the execution plan
SELECT COUNT (*) FROM EMPLOYEE E, DEPARTMENT D
WHERE E.EMP_DEPT = D.DPT_ID

The size of the “Employee” and “Department” tables was not changed; this QA server was used to test our SQL Tuning products without heavy workload.

I then enabled AWR to collect statistics for this SQL statement for two months. The statistics show that the execution plan remained unchanged for the 2 month period; however, the Elapsed Time and CPU fluctuated quite significantly. I had predicted that the CPU time would have been a steady line for such a simple SQL statement, with the Elapsed Time fluctuating a bit according to the resource availability of the server. I WAS WRONG.

The CPU variation was as high as 30% and the SQL Elapsed Time variation was as high as 200%. This story tells us that to predict a SQL statement's performance is not an easy job, regardless of how good the cost estimation in the execution plan or whether you have actually executed the statement with actual statistics. The significant factor is actually the current system's resources availability, which means that the combination of various resources (uch as CPU, Memory, IO and etc…) at each time will make your SQL runtime differently.

So, don't blame your database optimizer the next time you encounter a poor performing SQL statement - they are already doing a good job.

Elapsed Time

CPU Time

How Quest SQL Optimizer works with Hints

Richard To — Tue, 14 Aug 2007 18:28:00 GMT

Today database vendors are more willing to provide a means for the end user to influence the decision of which execution plan to use for a SQL statement. They provide this because database optimizers cannot guarantee that they the will generate the best execution plan for a given SQL statement. However, when you do apply Optimization Hints you might find that sometimes the specified Hints may not affect the database SQL optimizer’s decision. The reason that the optimizer does not select the execution plan following the Hints direction is that your SQL statement’s syntax prevents the database SQL optimizer from using the Hints.

Let’s take a look at the following SQL statement and execution plans with and without a Hint:

In reviewing the execution plans, this example shows you that the USE_NL hint does not cause the Oracle SQL optimizer to generate a Nest Loop join for this SQL statement. It is actually quite often that the database SQL optimizer will not follow your instruction due to the limitation of the SQL syntax. For complicated SQL statements, the situation is even more complex as we may not be able to tell whether the Hints will be used or how good the result will be if a Hint is applied. That is why Quest SQL Optimizer takes a different approach and does not follow the knowledge oriented SQL tuning approach. The SQL Transformation engine in Quest SQL Optimizer will try most of the possible combinations for rewriting the SQL syntax combined with applying optimization Hints to explore the potential of a database SQL optimizer like the following diagram illustrations:

The first step of this SQL Transformation engine transforms the original SQL statement and produces a group of alternative SQL statements where all the alternative SQL statements still provided the exact same results. Then, Quest SQL Optimizer rewrites each newly created SQL statement to produce another group of alternatives. The engine continues rewriting each alternative until all the SQL statements cannot be rewritten any further or until quotas are reached.

After the SQL Transformation engine has exhausted rewriting the syntax of the SQL statement, the optimization hints are applied to the original SQL statement and each of the SQL alternatives until all selected hints have been applied to all the SQL alternatives or until the quotas are reached.

The SQL Transformation engine releases you from the human trial and error effort and replaces it with a sophisticated computer algorithm. The result is a collection of SQL alternatives which are a combination of rewritten syntax and the application of Hints. What you need to do is to test the performance of each alternative SQL statement to find a better one to replace your original poor performing SQL statement.