Richard To's Blog

Optimizing SQL Part 4 – Optimizing SQL statements with a VIEW

Richard To — Fri, 21 Nov 2008 13:54:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It covers optimizing SQL statements that use VIEWs.

In early versions of the Oracle database, a VIEW was handled like a temporary table that stores the temporary data from the VIEW’s SELECT statement. Then the temporary table was joined with the main portion of the SQL statement, so the tuning of a SQL statement that contained a VIEW could be separated into the VIEW’s SQL statement and main SQL statement. In more recent versions of Oracle, the SQL optimizer in Oracle can merge these two SQL statements together before it does further SQL optimization, so, it is now more complicated for you to tune a VIEW SQL statement by hand than before. Quest SQL Optimizer has a “View to Inline View transformation” technique to solve this problem.

The optimization process in Quest SQL Optimizer includes this technique of rewriting the VIEW’s SQL statement when it transforms the syntax of the original SQL statement. So it will automatically do all the work for you.

For example, with this simple SQL statement

SELECT * FROM VIEW_DEPT
WHERE DPT_AVG_SALARY > 40000

that uses the following VIEW.

CREATE OR REPLACE VIEW VIEW_DEPT
    (DPT_ID,
     DPT_NAME,
     DPT_MANAGER,
     DPT_AVG_SALARY)
AS
SELECT "DPT_ID", 
       "DPT_NAME", 
       "DPT_MANAGER", 
       "DPT_AVG_SALARY" 
  FROM DEPARTMENT 
 WHERE DPT_ID IN (SELECT EMP_DEPT 
                    FROM EMPLOYEE 
                   WHERE EMP_ID > 50)

When the VIEW’s SELECT statement is inserted into the original SQL statement, the SQL statement looks like this:

SELECT *
 FROM (SELECT "DPT_ID", 
               "DPT_NAME", 
               "DPT_MANAGER", 
               "DPT_AVG_SALARY" 
          FROM DEPARTMENT 
            WHERE DPT_ID IN (SELECT EMP_DEPT 
                               FROM EMPLOYEE 
                              WHERE EMP_ID > 50))
 WHERE DPT_AVG_SALARY > 40000

The original SQL statement and the SQL statement with the VIEW inserted will both be rewritten by the Quest SQL Optimizer to generate all the possible SQL statements which produce the same results as the original SQL statement. Some of the SQL alternatives will not include the VIEW’s SQL and others will have the VIEW’s SQL rewritten.

An example of one of the SQL alternatives is:

SELECT * 
  FROM (SELECT DPT_ID,
               DPT_NAME,
               DPT_MANAGER,
               DPT_AVG_SALARY
          FROM DEPARTMENT 
         WHERE EXISTS (SELECT 'X' 
                         FROM EMPLOYEE 
                        WHERE EMP_ID > 50 
                          AND EMP_DEPT = DEPARTMENT.DPT_ID))
 WHERE DPT_AVG_SALARY > 40000

You can control whether the VIEW’s SQL is rewritten along with the original SQL statement with the Transform view to inline view setting in the Options. Quest SQL Optimizer will also transform a VIEW that is being used by another VIEW. You control how many levels (VIEWs within VIEWs) are included when the original SQL is rewritten by specifying the Transformation levels setting.

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

Optimizing SQL Part 3– Generating more SQL alternatives for simple SQL statements

Richard To — Thu, 06 Nov 2008 16:10:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It covers how to generate more SQL alternatives.

When you optimize a simple SQL statement, Quest SQL Optimizer may only generate a few SQL alternatives using the default settings in the Optimizer options. It is very likely that many more alternatives can be generated, but they did not get generated because of the way the options were set.

The options settings in Quest SQL Optimizer for Oracle enable you to select which Oracle optimization hints are applied, include or exclude a few of the syntax transformation rules that are used, select how many driving paths will be tried, and increase or decrease the number of SQL alternatives generated.

We’ll take a look at how you can do this using the Optimizer options. These options apply to the optimization process in both the Tuning Lab and the Batch Optimizer.

To open the Options window, click Options .
Select Tuning Lab | Optimizer | Intelligence.
By default, the “intelligence level” of the optimization process is set to Predefined Settings with level 4. Change the intelligence level to 10.
Level 10 selects all of the Optimization options, all of the Oracle optimization hints, and sets the quotas for the number of SQL statements that will be generated very high.

Applying Level 10 to your original SQL statement maximizes the possibility of finding a better alternative for you.

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

Optimizing SQL Part 2 – Generating fewer alternative SQL statements

Richard To — Fri, 24 Oct 2008 12:53:00 GMT

Written by Rene Woody

This blog is a continuation of a series (view last blog on the Optimization Process) about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It covers how to limit the number of SQL alternatives generated by the optimization process.

When you are optimizing a complicated SQL statement, Quest SQL Optimizer is capable of generating hundreds to even thousands of SQL alternatives. While theoretically this may be nice, it is not practical to review, select, and test hundreds of SQL statements, especially if it is a long running SQL statement.

The options settings in Quest SQL Optimizer for Oracle enable you to control the maximum number of SQL alternatives generated, select which Oracle optimization hints are applied, exclude a few of the transformation rules that are used, and limit the maximum number of SQL statements generated.

We’ll take a look at how you can do this using the Optimizer options. These options apply to the optimization process in both the Tuning Lab and the Batch Optimizer.

To open the Options window, click Options .
Select Tuning Lab | Optimizer | Intelligence.
By default, the “intelligence level” of the optimization process is set to Predefined Settings with level 4 selected. In order to customize the number of alternative statements that are generated and other options, set the intelligence level to Custom Settings.
Select Tuning Lab | Optimizer | Optimization.
If the Transform view to inline view or Transform query to inline view apply to your SQL statement, fewer SQL alternatives will be generated if you unselect these options.
1. The Transform view to inline view option inserts the SELECT statement from each VIEW used in the SQL statement and then transforms the syntax of the original SQL statement and the SQL statements from the VIEWs.
2. The Transform query to inline view option transforms the subquery to an inline view (a subquery used as a table in a FROM clause).
Select Tuning Lab | Optimizer | Hints.
The Oracle optimization hints are displayed on six pages. Select the hints you want to use for optimizing your SQL statements. The fewer hints selected the fewer alternative SQL statements will be generated by the SQL Optimizer.
Select Tuning Lab | Optimizer | Quota.
Set the quotas to reduce the number of SQL statements generated during the optimization process.
1. The Syntax transformation quota limits the number of SQL alternatives generated during the SQL syntax transformation phase of the optimization process. What this means is that the SQL Optimizer will stop finding syntactically different SQL statements after the quota has been reached.
  
  Note: Although the SQL statements are syntactically different, they still produce the same result.
2. The Table join permutation quota limits the number of ways the order of the tables is rearranged. This table order rearrangement is actually one of the many rules that comprise the syntax transformation rules. The Table join permutation quota is used to control how many SQL syntax transformations are created using only this one rule.
3. The Hints quota is the number of SQL alternatives that are generated by applying the Oracle optimization hints to the original SQL statement and the SQL alternatives that were created by transforming the SQL syntax.

By changing the options above, fewer or more SQL alternatives can be generated for your 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.

Optimizing SQL Part 1 – The Optimization Process

Richard To — Fri, 10 Oct 2008 13:48:00 GMT

Written by Rene Woody

This blog is the beginning of a series that will cover the optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle.

When writing a SQL statement, it is easy to be satisfied when you have figured out how to get the correct results. This is especially true when you are writing a complicated SQL statement. But in order to have good database performance, you need to find the best execution plan that retrieves the desired results.

Quest SQL Optimizer has a unique optimization process for finding the best performing SQL statements for each unique database environment. It generates SQL alternatives for a given SQL statement in order to find alternative execution plans for that SQL statement.

SQL Syntax Transformation

The optimization process first transforms the syntax of a SQL statement to generate alternative SQL statements through a proprietary and unique artificial intelligence algorithm. It applies SQL syntax transformation rules to rewrite a SQL statement in a multitude of different ways that still produces the same result. The knowledge base in the optimization engine has more than 60 SQL transformation rules, which can theoretically generate up to 60! alternatives. (60! = 8.3209871127413901442763411832e+81)

This optimization process is a recursive process that rewrites a SQL statement until there are no more possible alternatives. Then it will rewrite each of the alternatives. It will stop when it can no longer generate any more SQL alternatives or when it has reached a user-defined quota for the maximum number of SQL alternatives it should generate while transforming the syntax.

Database Optimization Techniques

After it has finished rewriting the SQL statement using the SQL syntax transformation rules, it will generate more SQL statements by applying techniques that are unique to the database platform. For Oracle, it applies the Oracle optimization hints. For SQL Server, it applies hints. For DB2 LUW, it applies SQL Options. And for Sybase, it applies forces, optimization goals and optimization criteria. For all database platforms, it applies these techniques to original SQL statement and the SQL alternatives. You can control which specific items are applied to the SQL statements through the settings in the Optimization options.

Looking for unique execution plans

After the optimization process has finished generating the SQL alternatives, it will retrieve the execution plan for each alternative SQL. It compares the execution plans so that it can eliminate the SQL alternatives that have duplicate execution plans.

The final result of this optimization process is one SQL alternative for each unique execution plan.

Using Quest SQL Optimizer, you can be sure that you will have tried most of possible alternative execution plans for your original SQL statement, which can be mathematically proved to generate the same results. The next step is then to test the alternatives to findthe one that performs the best in your database environment.

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

A Question from our Quest SQL Optimizer User

Richard To — Thu, 04 Sep 2008 09:50:00 GMT

Recently, we got a question from a customer:

“Why does adding a ||'' to the end of a key value improve performance... in this case significantly, this query runs in 187 milliseconds regularly where without the ||'' it takes minutes to run... This is the only modification in the result set from SQL Optimizer that was made to this code block. I got this code from a developer, so I know there are other methods to improve upon here, but this one change did everything performance wise that I needed to happen and I want to understand what the ||'' does to the query.”

Let me explain the question using a simple SQL like the following:

SQL A

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

Our SQL Optimizer rewrites it like this:

SQL B

select *
 from employee
 where emp_dept in (select dpt_id || ’’
                      from department 
                     where dpt_name like 'D%')

Most SQL tuning books or SQL tuning experts will tell you how to use or enable indexes to improve the performance of a SQL statement. Most of them will only tell you that using indexes is the way to improve SQL speed. Seldom will they tell you how to change or control the execution plan that Oracle will generate for the SQL statement. In the SQL B above, you will find that we added an empty string to the dpt_id column in the select list of the subquery. It looks like this dummy operation, dpt_id || ’’, is stupid and meaningless since it is concatenating nothing to the dpt_id column in the select list.

To explain this dummy operation, let’s have a look at the following SQL statement which is semantically equivalent to SQL A:

SQL C

select *
 from employee
 where exists (select ‘x’
                       from department 
                       where dpt_name like 'D%'
                         and emp_dept=dpt_id)

If we change the SQL syntax in SQL C to the following, do you think that the execution plan of this SQL will remain the same or not?

SQL D

select *
 from employee
 where exists (select ‘x’
                       from department 
                       where dpt_name like 'D%'
                         and emp_dept=dpt_id || ’’)

I think most everyone would expect that the new syntax would generate a different plan since the dpt_id index is disabled by the addition of || ’’. The driving path in SQL C which would do a full table scan on the employee table and an index search of the department table using the dpt_id column can no longer be used (assuming that it was used in the original plan) in SQL D. Therefore when you add || ’’ to a character field or + 0 to a number field, Oracle will try other plans in place of the original plan. Sometimes the new plan will be better in your database environment, and therefore your SQL will be faster.

Now, let’s take a look at both SQL A and SQL C. SQL A uses an IN clause and SQL C has been rewritten to use the EXIST clause. The Oracle internal SQL optimizer like most of commercial databases has a built-in SQL transformation algorithm. When the SQL is executed, the internal optimizer will transformed the SQL syntax to a better syntax which is known to perform better or can be more easily optimized in next stage of plan generation. Therefore, SQL A and SQL C can be treated as the same SQL when you do SQL tuning by rewriting the syntax because the internal SQL optimizer in the database will perform this transformation during its limited optimization process which takes place before the SQL statement is executed. Actually, most database internal optimizers will transform an EXIST or IN statement to a Join SQL statement internally, but to simplify our discussion, I used the EXIST example instead of using a Join SQL.

I would like to emphasize that there is no computer algorithm that can 100% predict which execution plan is the best in your database. Therefore, to test run all alternative SQL which have unique execution plans is the only way to make sure you are using the best SQL alternative, no matter whether you are tuning your SQL statements manually or using our Quest SQL Optimizer.

Batch Optimizer Part 6 - Using Batch Optimization with Rule Based Optimizer

Richard To — Tue, 12 Aug 2008 14:32:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle.

In the Batch Optimizer in Quest SQL Optimizer for Oracle you may notice that the options for selecting which SQL alternatives to execute by the batch process are all based on the Oracle cost estimation. Although it is not a good idea to trust Oracle cost estimation for problematic SQL statements, in order to save time when testing the SQL alternatives, it is an acceptable way to reduce the test run time.

Note: If you really want the best SQL optimization results from the testing, you will not limit the number of alternatives that are tested but will test them all by selecting All alternatives.

When you have the Oracle Optimizer Mode parameter set to Rule Based in your Oracle database, there is no cost estimation in the execution plan of the SQL statements. So, that prompts the question, “Which Auto Select SQL Rewrites for Execution option should be used to limit the number of SQL alternatives that are executed when the Optimizer Mode is set to Rule Based?”

In reviewing the Batch Optimizer option Auto Select SQL Rewrites for Execution, you can see that one option includes a setting for the minimum number of SQL statements to be executed. The % of alternatives with lowest cost option includes the Minimum alternatives executed. This setting takes the first 10 (default setting) alternatives and executes them. When there is no cost estimation, it simply takes the first 10 alternatives that were generated and executes them. Therefore, this is the setting that should be selected when the Optimizer Mode parameter is set to Rule Based and you want to limit the number of SQL alternatives executed.

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

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.