Richard To's Blog

A Question from a user of SQL Optimizer 7.5

Richard To — Tue, 16 Feb 2010 19:25:00 GMT

Richard discusses a question from a user who tried our new SQL Optimizer 7.5.

SQL Best Practice is SQL Tuning? -10 Common Misconceptions in SQL Tuning #5

Richard To — Wed, 18 Nov 2009 17:55:00 GMT

This blog is the fifth in a series about misconceptions surrounding SQL tuning that are quite common.

Around 15 years ago, when I was a DBA both for Oracle and Sybase, a lot of SQL came from my colleagues which I needed to tune. Initially, I enjoyed the satisfaction of tuning a SQL statement that ran in a few minutes down to a few seconds. But once SQL tuning became my daily job, it wasn’t fun anymore. I started to search for any tool in the market which could help, but I found that most of the SQL tuning tools only displayed the execution plan. Some provided best practices or recommendations, but these kind of tools actually did not help me to solve numerous SQL tuning problems, since most of the time the problematic SQL syntax was okay and all the indexes were properly built. The major problem for SQL tuning is how to help the database optimizer choose a better execution plan. To be frank, Oracle and Sybase do a good job of picking a good execution plan most of time. I think there will not be more than 5% of the SQL that needs our attention, normally most human mistakes normally can be eliminated during unit test or stress test. For example, a Cartesian join SQL which is missing the basic relationship between two tables in a production database is a very odd case which I have never seen in my life in last 20 years working experience with hundreds of programmers. But it is interesting that this case is always used as a demo case in many SQL tuning products. I don’t know what company would let this kind of SQL statement be executed in a production database without even one unit test on their programs, and then think that it needs to pay hundreds of dollars for a SQL tuning tool to solve this problem. I think anyone in the company who knows SQL coding could fix this problem within a minute!

It is common that a lot of best practices in today’s SQL tuning tools available in the market are built for demonstration only. There are not many cases where these so-called best practices can be applied in a real life situation.

Some Best Practices Discussion

I will discuss some common best practices that I learn from SQL tuning products, market experts, or Oracle manuals.

Best Practices Fallacy - Use UNION ALL instead of UNION

It is quite common to see tuning tools keep recommending to users to use UNION ALL instead of UNION as a best practice. Once those tools detect a UNION key word in a SQL statement, it will give you advice to change the UNION to the UNION ALL operation. I have often wondered if those tool designers really understand the risk of giving this recommendation. Do they really understand their target users’ knowledge? They are two different, although similar, operations and naturally these two operations have different meanings. The result will be equal only in a certain data set. UNION eliminates all duplicated records from a SELECT list. But UNION ALL returns all records without the elimination process, so it runs faster than UNION. If their users are knowledgeable enough and know without a doubt that UNION ALL is the same as UNION in their environment, they may not need these kinds of tools to help them to change UNION to UNION ALL. But the problem is that those users who don’t have enough knowledge to distinguish the difference between these two operations and trust that the recommendation is good in all circumstances, the result may be disastrous, since the problem sometimes will not be discovered in a development database with a small data set during unit test, but it will definitely create problems in their production database. You can imagine what a nightmare it could cause for an online trading system with a million transactions per day.

Best Practices Fallacy - Rewriting the SQL in PL/SQL code

Rewriting the PL/SQL code instead of tuning a SQL statement is one of the best practices that I am always against, unless you really understand what you are doing and know that the database SQL optimizer cannot achieve your performance expectation. In no other case do I think that a query can only be improved by rewriting the PL/SQL, and that it cannot be improved by normal SQL tuning techniques such as Oracle optimizer hints application, new indexes, a materialized view or SQL syntax rewrite. It is a misconception with people who have some sort of experience with rewriting their problematic SQL with PL/SQL code and have gained some performance improvement. The point is that what they are doing is just creating a new “data process flow” or “execution plan” to only process their SQL, when actually, most of time, they can achieve the performance improvement by using normal SQL tuning techniques. Furthermore, the PL/SQL code will introduce extra PL/SQL process and looping overheads to the database engine. Let’s use the following simple join SQL statement for discussion:

Original SQL

SELECT  *
  FROM employee,department
 WHERE emp_dept=dpt_id
   AND dpt_id<‘ACC’

If you place the above SQL into PL/SQL, probably you will start with an Open Cursor for Department with a filter dpt_id<‘ACC’ and then loop the following SQL until the end of the Department cursor.

SQL used in PL/SQL

SELECT *
 FROM employee
 WHERE emp_dept= var_dpt_id

Basically, this PL/SQL code is a typical Nested Loop operation with a driving path from Department to Employee. If Oracle failed to use a Nested Loop for the original SQL and a Nested Loop is a better plan in your environment, you will gain performance improvement for this PL/SQL, but actually, the same improvement can be more easily achieved by adding the USE_NL hint to the original SQL. I wonder how many PL/SQL programmers can use PL/SQL code to achieve Hash Join or Sort Merge Join; if these Hash or Sort Merge Join is better for the SQL. Why not let Oracle to do that for you; it is the reason we pay for the RDBMS.

Factoring Out Subqueries with Temporary Tables or WITH Clause

It is quite common that people will tell you to factor out subqueries with a Temp table or a WITH clause like the following SQL:

Original SQL

SELECT emp_id 
 FROM employee, 
       department 
 WHERE emp_id IN (SELECT emp_id 
                    FROM emp_small 
                   WHERE emp_dept < 'D') 
   AND dpt_manager IN (SELECT emp_id 
                         FROM emp_small 
                        WHERE emp_dept < 'D') 
   AND dpt_id = emp_dept

We can extract these two subqueries and transform them to a WITH clause like the following:

Best Practice SQL

WITH temp_emp AS (SELECT emp_id empid 
                    FROM emp_small 
                   WHERE emp_dept < 'D') 
SELECT emp_id 
 FROM employee, 
       department 
 WHERE emp_id IN (SELECT empid 
                    FROM temp_emp) 
   AND dpt_manager IN (SELECT empid 
                         FROM temp_emp) 
   AND dpt_id = emp_dept

Most people may think that this SQL looks very nice and should be encouraged as a best practice to any programmer and should not use the Original SQL syntax anymore, based on Original SQL syntax, you may interpret that the subquery (SELECT emp_id empid FROM emp_small WHERE emp_dept < 'D') should not be processed twice, so the second “Best Practice SQL” should run faster than the “Original SQL”. But the fact is that the “Original SQL” is almost 50 times faster than the “Best Practice SQL” since using a Temp table or WITH clause may save you time by not executing the factored subquery multiple times. But in reality, the Temp table or WITH clause introduces a temp table or an internal temp table (please refer to the plan in the following screen shot) into the execution plan, and this prevents the database SQL optimizer from merging those subqueries into the main query and limits the number of plans the SQL optimizer can generate during optimization. I am not saying you should or should not use this best practice, all that I am saying is that this example is used to show you that every environment is unique, sometimes this Best Practice is good, but sometimes it is bad. There is no common best practice that always works to improve performance.

Actually, SQL tuning is not using “SQL best practices” that follow generalized rules. It is the techniques that improve the SQL speed in a given hardware environment and a unique database configuration with a specific data distribution. Sometimes the best performance results may violate all the best practices that you have heard about, since your environment is always unique from everyone else’s.

The true best practice is “Don’t adapt those so-called best practices blindly!”

Highlights of SQL Optimizer 7.5

Richard To — Fri, 02 Oct 2009 11:59:00 GMT

Version 7.5 of Quest SQL Optimizer for Oracle will be released in October. The major effort for this new release was in the optimizer engine and the new UI for the optimization, index generation and execution functions. The following is a screen shot of the new UI. It looks tidy and easier to use compared to the old version. All the statistics names now use the Oracle standard naming, so, users can easy understand each statistics by going through the Oracle manual without guessing which statistic in Quest SQL Optimizer corresponds to which Oracle statistic.

A little control and help panel in the middle of the screen is designed to help new users to understand what is going on during SQL optimization and what step to take during or after SQL optimization and rewrite.

The new optimization workflow

We made two changes to the workflow of the SQL optimization process that will significantly improve the speed at which you can find the best SQL alternative. In the previous versions, if the user stopped the rewrite process, no SQL alternatives were available. It was an all or nothing process. You were not provided with the alternatives that were created before you stopped the optimization process. We have spent a lot of effort rewriting our engine to allow SQL alternatives (rewrites) to be returned during the rewrite process. So now if you stop the optimization process in the middle of generating the alternatives, you will have some alternatives to review and test.

Also in the previous versions, the SQL optimization process which rewrites the syntax of the SQL statement must finish before the batch test run can be executed.

In version 7.5, we have also added a new function called “Optimize” which allows you to generate alternatives and test run these alternatives at the same time. You can review any of the newly created better SQL alternatives and stop the optimization process any time without waiting for entire process being finished.

The New Batch Run Workflow

In previous versions, the default test run method is designed to test run the original SQL twice for reducing the first data caching time and then test run all alternatives once only This method plays it safe to give original SQL performance advantage over other SQL alternatives in timing of SQL parsing and new indexes caching. For SQL with a long run time, this method of execution is acceptable, but for SQL statements with a short run time, the overhead for parsing the SQL alternatives may be significant compare to the original SQL statement, so, I recommended test running all SQL statements twice if your original SQL run time is short.

In SQL Optimizer version 7.5, we have automated this method in the Batch Run function and Optimize function for SQL which have a run time less than 5 seconds. All SQL alternatives now will be executed twice automatically and have a fair comparison with the original SQL. This new execution method is used when you select Run all SQL twice if original SQL run faster than 5 seconds option. We have made this the default setting for the Batch Run.

Some Changes in the Optimization Engine

Since the test run and rewrite functions can now be executed at the same time, the time to discover the first alternative with better performance is significantly reduced. Therefore, the Intelligence Level’s granularity can be enlarged for more drastic control over the searching depth. A higher intelligence level now does not mean you have to wait for a long time to get all SQL alternatives generated before you to test run. Now it means that you can check more alternative rewrites while you may have some better alternatives executing. You can stop the process any time you feel comfortable with the current findings.

The Intelligence Levels changed from 1-10 to 1-5. The previous level 2 it now 1, 4 is 2, 6 is 3, 8 is 4 and 10 is 5.

Another new feature selects which SQL alternatives to execute first in the Batch Run. I have found that SQL statements with similar plan cost usual have very similar performance. This new function selects one SQL alternative to execute from each group of SQL alternatives whose Oracle plan cost is determined to be within the same cost group. The SQL alternatives that are selected from the plan cost groups are executed before the rest of the SQL alternatives. This is done to see if the best performing SQL can be found more quickly. In the Options settings, the Execution order for SQL option enables you to use this Intelligence order or you can select Plan cost to execute the SQL alternatives in order of the lowest plan cost to the highest cost. By executing one SQL from each group first, you increase the likelihood that you will find a faster alternative quickly.

Due to time limitation, there are not many changes in transformation rules, but you will still feel some improvements in Oracle11g, since we have modified some rules to cater to the Oracle 11g SQL optimizer new behavior. You are invited to test the difference. Your feedback is always welcome.

Executing SQL In Tuning Lab – Part 10 – Performance Improvement Expectations

Richard To — Fri, 21 Aug 2009 14:10:00 GMT

Written by Rene Woody

This blog is a continuation of a series about test running the SQL statements in the Tuning Lab in Quest SQL Optimizer for Oracle to find the best performing SQL statement in your database environment. It covers the expectation that Quest SQL Optimizer will always be able to improve the performance of your SQL statements.

Occasionally, even when you have increased the intelligent level up to 10, you may find that none of the alternative SQL statements that Quest SQL Optimizer generated improves the performance of your original SQL statement and you may wonder why. There are many reasons you cannot improve your SQL statement. For example, take a simple SQL statement; it may not have many alternative execution plans that can be explored, so you cannot improve something that is already the best. For complex SQL statements, it is normal that a lot of alternative execution plans can be generated by our SQL Optimizer. But if you still cannot find a better alternative from all those SQL alternatives, it could be that your original SQL statement is already using the best execution plan to retrieve the specific data you are looking for.

In this case, what Quest SQL Optimizer has done for you is to confirm that you have the best execution plan to obtain a specific result set. Many DBAs and developers know the techniques of writing SQL statements and tuning them for good performance and have spent many hours on this process. If you have already written the best alternative, the optimization process in Quest SQL Optimizer confirms that you have the best performing SQL. Therefore you will know that you do not need to spend any more time trying to figure out if there is a better way to rewrite it.

If you still need performance improvement for a SQL statement, you may want to look into using the index generation process in the Tuning Lab to see if there are indexes that that would produce other execution plans for your original SQL statement. Don’t forget to re-optimize your SQL statement again, once you have created the new index, because sometimes you will have even better performance based on the index.

One thing you should remember is that a new index for a given SQL statement may affect other SQL statements that are accessing the same table, so, you should use our Index Impact Analyzer to check those mission critical SQL statements that are accessing the new index table.

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

A SQL that returns records faster is not necessarily better-10 Common Misconceptions in SQL Tuning #4

Richard To — Fri, 14 Aug 2009 13:52:00 GMT

This blog is the fourth in a series about misconceptions surrounding SQL tuning that are quite common. It covers the misconception that the goal of SQL tuning is only to make a SQL return all the records faster.

I still remember around 20 years ago, I was using SQL*Plus to tune my SQL statements. At that time, it was my impression that a good SQL rewrite was one that normally would return the records faster. No SQL tuning tools were available in the market at that time, so SQL*Plus was the only product I could use to tune my SQL statements. Sometimes I had to use my watch to keep track of the speed of each rewritten SQL. When I was working with SQL statements that returned many records, I had no patience to wait for all the records to be completely returned and I normally stopped the SQL execution once the first few records were returned. It saved me a lot of time so that I could test multiple rewrites in a short time. This testing approach was good, since at that time Oracle provided only the Nested Loop join. The Nested Loop was the lowest cost join method for the machines in that age. I still remember my first project was using an Oracle database (I forgot the version number) running on a Digital machine with 48M RAM. A Sort Merge join would need too much memory for this kind of machine.

Today, we normally have multiple gigabytes of memory in our Oracle servers. Oracle now provides more join methods such as Sort Merge and Hash Join to fully utilize our machine’s memory and CPU. We should not base our decision whether a SQL is a better alternative solely on the time it takes the SQL statement to execute. We should take into account how the SQL statement is used in the application. Let me use a simple SQL to explain:

select * from employee,
              department
 where emp_dept=dpt_id

Basically for this kind of simple SQL statements, the Oracle internal SQL optimizer can perform any join method from the basic Nested Loop, Sort Merge, or Hash Join. You can use the Oracle optimization hints to influence Oracle to use any execution plan you want. For a Nested Loop join, you use the USE_NL hint or FIRST_ROWS. You may be wonder why the FIRST_ROWS hint will result in a Nested Loop join. Hash Join has to build a hash table and Sort Merge has to sort a table before they can return any records at all, so when you tell Oracle to process a SQL statement to give you the first record as fast possible, Oracle will prefer a Nested Loop in spite of its relatively higher cost. For an online query that needs to display only the first one or two pages of records from a cursor, the Nested Loop join is definitely a good choice since it provides the first records faster than the other two join methods. For example; if you have a query in a Datawindow in PowerBuider or TQuery in Delphi and the end users will not scroll down to see more than one or two pages, you can use the FIRST_ROWS hint in your SQL statement to speed up the response time for the user and save the user from having to wait for almost the entire SQL to be processed with Sort Merge or Hash Join. For example, if a SQL returns 1000 records, a Nested Loop join can quickly give you 20 records in a SQL cursor and display them on the screen. The user may only want to review the first 20 records and then close the cursor immediately, This process only spent around 20/1000=2% of the total workload it would take to retrieve the entire 1000 records for this SQL. If a Sort Merge is used when you want the first 20 records to be displayed on screen, Oracle has to finish the Sort operation first and then return the records in the Merge stage, so, most of the heavy sort operation is already done before it can return any records. Therefore you will not save much workload by breaking the cursor fetching after 20 records when using a Sort Merge join.

Here is the SQL statement with the FIRST_ROWS hint and the resulting execution plan:

select /*+First_Rows */ * from employee,
                               department
 where emp_dept=dpt_id

If you are not quite sure how the SQL statement you are tuning is going to be use in your application, it is best to use the default Oracle behavior which is good in most cases. Of course, you can use our Quest SQL Optimizer to find a better rewrite with has both a good response time (retrieving the first few records) and a good elapsed time (retrieving all records).

Let’s get back to today’s topic. You can see that a SQL rewrite which has a fast response time is normally generated by a Nested Loop join operation. The Nested Loop join is good only in certain circumstances such as for a small result set and when the driving path is correct (that is when the smaller table or result-set is looping a large table or result-set using a unique index search). Other than that, the Sort Merge or Hash Join will be a safe choice. It is common in modern relational databases that the cost of the Nested Loop is relatively higher than the Sort Merge or Hash Join, since the driving path will not affect the performance too much in Sort Merge or Hash Join. But a wrong driving path in Nested Loop join may be a disaster in your application, therefore, database vendors prefer not to take this risk in designing their SQL optimizer and give the Nested Loop join a higher cost.

The SQL statement that I have used to illustrate this point is a simple SQL. For complex SQL, there is no single rule which is always true for you to follow in order to pick out the best join method to use. It is better that you try the different methods and see which one produces the best results in your specific database environment and application. Quest SQL Optimizer makes this search for performance a simple process by rewriting the SQL syntax for you and testing the SQL alternatives.

Executing SQL In Tuning Lab – Part 9 – Why Run Times Vary

Richard To — Fri, 07 Aug 2009 14:16:00 GMT

This blog is a continuation of a series about test running the SQL statements in the Tuning Lab in Quest SQL Optimizer for Oracle to find the best performing SQL statement in your database environment. It covers why the run time of a SQL statement may vary from one execution to the next.

When you execute a SQL statement several times in Quest SQL Optimizer, you may notice that the run time will vary from one execution to the next. This adds an additional challenge to picking out the best SQL statement from a group of alternative statements.

The run time varies because each time the SQL statement is executed it is sharing the database and CPU processing with other jobs running on the computer. It is especially true for the first time you execute your SQL statement, since some data may be cached into memory. So it is normal that the first execution will take longer than the following executions. When you execute a SQL statement in the Quest SQL Optimizer, the run time (Total Elapsed Time) is the “clock time” from the moment that the SQL statement starts executing on the CPU to the moment it is finished. So since the execution of the SQL statement is sharing the CPU with other processes, the “clock time” is likely to vary from one execution to the next depending on how much sharing of the CPU occurred while the SQL statement was executing.

There is no overall solution to this challenge. To perform the run time testing of the SQL statements when there is less of a workload on the system is a good practice.

For SQL statements that run in sub-second times, it is recommended to run each SQL alternative several times using the Multiple-Execute option: Tuning Lab | Execution | Execution Method | Number of times to execute each scenario. See the previous blog Executing SQL In Tuning Lab – Part 3 – Equal Comparison for Run Times: Minimizing the Effect of Other Activities on the CPU for more information.

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

Executing SQL In Tuning Lab – Part 8 – Criteria for Best SQL

Richard To — Fri, 24 Jul 2009 14:57:00 GMT

When selecting the best performing SQL statement for your application, how should disk I/O be weighed in comparison to the run time when you are selecting the best SQL alternative to replace your original SQL statement?

Executing SQL In Tuning Lab – Part 7 – Testing SQL with Variables

Richard To — Fri, 10 Jul 2009 13:33:00 GMT

A SQL statement with a variable that is provided at run time poses an extra challenge when trying to determine which alternative SQL statement is going to give the best overall performance. Each time the SQL statement is executed, a different variable could be supplied.

Executing SQL In Tuning Lab – Part 6 – Testing in a Development Environment

Richard To — Fri, 26 Jun 2009 14:05:00 GMT

This blog covers testing the SQL alternatives in a development environment when it is not possible to do extensive testing in the production environment.

No single SQL syntax is always the best - 10 Common Misconceptions in SQL Tuning#3

Richard To — Tue, 16 Jun 2009 14:26:00 GMT

Written by Richard To

This blog is the third in a series about misconceptions surrounding SQL tuning that are quite common. It covers the misconception that there is always some SQL syntax that is the best across different databases and hardware environments.

A lot of so-called SQL experts on the internet give you advice on how to write a better SQL or how to tune your SQL statements. I agree that most of them are skillful SQL coders, but it doesn’t mean that the advice that they are providing is always correct. Most of their experience was gained from a limited testing environment. These so-called (or maybe self-proclaimed) experts think that their SQL syntax or syntax transformation which is better in their testing environment will be applicable across the board to all environments. But they are seldom fully aware of the dynamics of today’s cost-based SQL optimizers. The same SQL syntax may not get the same execution plan in different database environments and therefore the performance will vary.

When you execute a SQL statement, the database optimizer evaluates different executions plans for the SQL statement and picks the plan that it determines has the lowest cost. You will find that in different database environments, the database SQL optimizer will likely determine that different execution plans will have the lowest cost to process your SQL statement.

Let me illustrate this point with the following diagram:

In this illustration database A selected “Plan 2” to process the “SQL A”, but in database B, “Plan 5” was used. So, the same syntax for “SQL A” may have a different execution plan in different databases.

So if you only tune your SQL statement using some general rule that certain syntax is the best way to write a SQL statement, at times you will end up using a less optimal execution plan.

Now let’s further narrow down this dynamic decision by applying a hint to the SQL. For this example we are using the Oracle optimization hint, USE_MERGE, for a two-table-join SQL statement and we will assume that the database SQL optimizer will actually use this hint and use sort merge to process the two tables. Note: Even though you added a hint to a SQL statement, the database optimizer may not be able to apply that hint in all cases and will use an execution plan that ignores the hint.

Let’s take a look at another diagram to see how the SQL statement performed in different database environments using the Sort Merge Plan:

So, from this illustration you can see that even the same execution plan applied to different databases may result in different performance. In this case, Database C is most suitable for a Sort Merge join for these two tables.

Now let’s take a look at combining these two diagrams into one:

In reality, the cost-based SQL optimizer will use different database statistics to generate an execution plan. But in this diagram, I used human thinking to select a plan (in my case Plan 2) first and then assumed that it would be the best across the board. So, you may see that how dangerous it is for those so-called SQL experts to tell you which plan or SQL syntax is better without telling you under which conditions to use this syntax or plan.

This is why in my Blog; I seldom tell you how to write better SQL statements, although there are some best practices in the market that are good for most environments. But a lot of problematic SQL statements need special tuning which normally steps outside the bounds of those common rules that the so-called experts provide. Surprisingly, sometimes even a badly constructed SQL may have good performance in certain environments. For most developers, it is not problem for them to master learning those best practices. The problem is that those best practices limit developers’ thought process and they fail to take the next and important step to explore the relationship between SQL syntax and execution plan generation, because people are too bias to thinking that it is the syntax of the SQL statement that provides the better performance instead of realizing that it is the database optimizer’s selection of the execution plan that determines the performance of the SQL statement. So, next time you find some tips from a tuning book or the internet which tells you how to write better SQL, please always keep in mind that it is good only for certain environments and cannot be applied as an across the board rule!

Executing SQL In Tuning Lab – Part 5 – Selecting Alternatives to Test

Richard To — Fri, 12 Jun 2009 13:27:00 GMT

In Quest SQL Optimizer when you generate alternative SQL statements for your original SQL statement you may be presented with hundreds of SQL alternatives. The only way to be completely sure that you have the fastest one is to run all the SQL alternatives. But unless the SQL statements run relatively quickly, this is not always practical. So then you are left to determine which SQL statements you should select to test.

Executing SQL In Tuning Lab – Part 4 – Equal Comparison for Run Times: Eliminating the Effect of Network Traffic

Richard To — Fri, 29 May 2009 11:13:00 GMT

When you are executing SQL statements to find which one is the best, you need to take into consideration the factors that can skew the accuracy of the results of the testing. This blog discusses eliminating the effect of network traffic when you are comparing SQL run times.

Executing SQL In Tuning Lab – Part 3 – Equal Comparison for Run Times: Minimizing the Effect of Other Activities on the CPU

Richard To — Fri, 15 May 2009 10:58:00 GMT

When you are executing SQL statements to find which one is the best, you need to take into consideration the factors that can skew the accuracy of the results of the testing. This blog discusses minimizing the effect of other activities on the CPU when you are testing very fast running SQL statements.

Executing SQL In Tuning Lab – Part 2 – Equal Comparison for Run Times: Minimizing the Effect of Caching

Richard To — Fri, 01 May 2009 12:56:00 GMT

When you are executing SQL statements to find which one is the best, you need to take into consideration the factors that can skew the accuracy of the results of the testing. These factors include caching the data, caching the indexes, caching the SQL statement, other activities on the CPU, and network traffic.

10 Common Misconceptions in SQL Tuning #2

Richard To — Tue, 28 Apr 2009 16:50:00 GMT

This blog is the second in a series about common misconceptions surrounding SQL tuning. It covers the misconception that the goal of SQL tuning is to write a better SQL statement. The real goal of SQL tuning is not to create a better SQL statement, but it is to help the database optimizer to make the right decision when it is choosing the execution plan.

If you browse the internet with the key words “SQL Tuning”, you will find that there are thousands of articles that tell you how to write better SQL and how to read the execution plan. They recommend what is good SQL syntax, or ask you to update the tables/indexes statistics and create new indexes. Yes, all of these are workable solutions for specific problems, or these are good only for simple problems. But when it comes to a complex SQL statement with many tables and join situations, actually, most of these recommendations are not applicable, since most complex SQL statements are already well written by experienced programmers with a tidy syntax structure. Normally all necessary indexes are built and all the tables’ statistics are updated. Most likely, you won’t find any human introduced errors within these SQL. In reality, the root cause of the performance problems for complex SQL statements is the database internal SQL optimizer. Two major limitations may cause the database SQL optimizer not to find the best way to process your complex SQL:

Plan Space is relative small for complex SQL statements
For a simple SQL statement, the number of ways (execution plans) to process the SQL statement will be small. But for a complex SQL statement, theoretically, the database SQL optimizer can generate a multitude of execution plans internally to process the SQL statement. The number of execution plans that can be generated by the database SQL optimizer is called the plan space. Due to the time limitation of the database, real time parsing of your SQL statement and the limited syntax rewrite ability, the database SQL optimizer cannot consider all possible execution plans. For simple SQL statements, having a small plan space is acceptable since not many execution plans can be generated. But for complex SQL statements, the number of actual execution plans that will be generated by the database SQL Optimizer will be relatively small compared to the potentially huge number of execution plans that could be generated. So, the likelihood that the database SQL optimizer can select the best query plan will significantly decrease for complex SQL.
Cost estimation is not accurate
In my last blog “10 common misconceptions in SQL Tuning #1”, I mentioned that we cannot determine whether a SQL statement will or will not have good performance based on the database SQL optimizer cost estimation. It is a very common misconception that people always think that 100% correct database statistics will result in 100% query plan cardinality estimation and 100% correct cardinality estimation will result in 100% cost estimation. Some people cannot even distinguish the difference between cardinality estimation and cost estimation. There are many reasons that contribute to why the database SQL optimizer cannot produce a 100% accurate cost estimation. So, within the limited plan space that is generated by the database SQL optimizer that I described above, plus inaccurate cost estimation, it is really hard to guarantee that the database SQL optimizer will provide a good performing execution plan for every SQL statement.

I am not saying that a simple SQL statement does not need to be tuned, because sometimes the cost estimations are exceptionally bad under certain conditions that make the database SQL optimizer select a poorly performing plan. No matter how good looking your SQL syntax structure is, you may need to restructure your SQL with Oracle optimization hints or rewrite the syntax. My goal is not to teach you which syntax is better. That really is meaningless, since every database has its own unique data distribution and statistics. The same SQL syntax in various individual databases and different database versions will have different behavior. If you can read the execution plan, you may know what is causing the performance problem and make corresponding syntax changes and use hints to improve your SQL. But the goal of restructuring is not to write a better-looking SQL. The objective is to fool or guide the database SQL optimizer to select a better plan. I have discussed those tactics previously in my blog (please refer to the following links). So, don’t be surprised, next time, if you find an ugly SQL statement generated by our Quest SQL Optimizer that is performing a hundred times faster than your SQL statement!

Executing SQL in Tuning Lab – Part 1 – Criteria for Terminating SQL

Richard To — Fri, 17 Apr 2009 10:37:00 GMT

Written by Rene Woody

This blog is the first of a series about test running the SQL statements in the Tuning Lab in Quest SQL Optimizer for Oracle to find the best performing SQL statement in your database environment. It covers the options for terminating the SQL alternatives when they are executed in a batch.

The SQL optimization process in the Tuning Lab generates multiple alternative SQL statements that produce the equivalent results to your original SQL statement. You are obviously looking for the SQL alternatives that run faster than your original SQL statement. Most likely, some of the alternative SQL statements will run longer than your original statement and some will run faster.

When testing the performance of the alternatives, you will undoubtedly want to terminate the longer running SQL statements, once you have determined that they do not meet your performance criteria. You can do this using the Option settings in the Tuning Lab | Execution | Execution Criteria | SQL Termination Criteria section.

When a SQL statement is terminated by the criteria that you select, “Query Cancelled. Exceed benchmark” is displayed in the Results Comparison column of the Scenario Explorer window to let you know that the SQL statement exceeded the termination time specified in the options.

In Quest SQL Optimizer 7.4 for Oracle or later,these three options are provided for choosing when to terminate the SQL alternatives.

The Cancel execution by the fastest SQL run time option cancels SQL statements that run longer than the current best run time. With this option, your original SQL statement is run 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 time, the faster time is used as the new termination time for the next SQL statement. So, you are always using the fastest run time that has been found so far as the termination time for the next SQL statement. This option is the default setting.

The Cancel execution at this percentage of the original SQL run time option cancels SQL statements whose total elapsed time is longer than the specified percentage of the total elapsed time for the original SQL statement. It terminates all SQL statements that run past the calculated termination time. The default value is set at 100% so a SQL alternative will be terminated if it runs longer than your original statement using this default value.

The Cancel execution by the user defined time option cancels SQL statements that run longer than a time you specify. The option can be useful in a couple of ways. For example, if you know that your original SQL statement runs in half an hour, you can select to not run the original statement and set the termination time for 30 minutes, thereby saving yourself a half hour of testing time.

Another example for using the user defined time to cancel the SQL statements is if your original statement takes a long time to execute and there are many alternative statements, executing all statements may take considerable time. In that event, consider setting an aggressive user defined termination time. If the original SQL takes 1 hour, try a 5 minute termination time. If you find one that executes that quickly, you will certainly save yourself considerable time in testing. If no alternative statements execute in under 5 minutes, raise the termination time to 10 minutes, etc.

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

SQL Optimizer Part 10 – How to Optimize without Oracle Hints

Richard To — Mon, 13 Apr 2009 10:18: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 explains how to generate SQL alternatives that do not have Oracle optimization hints.

Oracle provides optimization hints that can be added to the syntax of a SQL statements to attempt to influence the execution plan that the database optimizer will use to execute the SQL statement. Quest SQL Optimizer for Oracle uses this technique in its optimization process to generate more unique execution plans for your original SQL statement. For more information on how the SQL alternatives are generated, see this previous blog: Optimizing SQL Part 1 – The Optimization Process.

To learn more about how the hints are applied by Quest SQL Optimizer see this previous blog: Optimizing SQL Part 6 – Using Oracle Optimization Hints when tuning SQL.

Basically, using Oracle hints to tune SQL is an acceptable method that makes your SQL statements to be more stable in the production database. Some people may argue that using hints should be the last resort or even be forbidden for tuning SQL, since hints will limit the flexibility of the Oracle SQL optimizer to handle future data changes or adapt to new optimizer enhancements. But the point is that for a problematic SQL statement that Oracle cannot handle well today, why not fix it today with the best execution plan even if it uses a hint instead of holding to the expectation that the Oracle optimizer may automatically solve it in future. What this means is that you are sacrificing the current performance for the unforeseeable future improvement or maybe even a further performance degradation.

If you do not want any of the Oracle hints applied to the SQL alternatives that are generated for your original SQL statement, you can use an option setting to turn off this feature.

Click .
Select Tuning Lab | Optimizer | Intelligence.
Select Predefined Settings.
In the list, select Do not use Oracle optimization hints.

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

10 Common Misconceptions in SQL Tuning #1

Richard To — Fri, 13 Mar 2009 12:11:00 GMT

This blog is the first in a series about misconceptions surrounding SQL tuning that are quite common. The first one covers the misconception that you can use the estimated cost from the database SQL optimizer to accurately judge the performance of a SQL statement in comparison to its rewrites.

SQL tuning is a very interesting topic and most DBA or developers have at least some experience with tuning SQL. They learn some tuning concepts from different sources such as the internet, tuning books, or their colleagues. There is no single scientific tuning guideline or method in the field. This gives us the impression that SQL tuning is more of an art than scientific knowledge. I have been in this field for almost 20 years and have experience doing SQL tuning on at least 4 database platforms. Basically, I have found that each database internal optimizer’s behavior is different from the others. For example, IBM LUW(Unix) has a very strong internal rewrite that can transform your SQL to a relative standardized internal syntax in which you don’t have any control over the ultimate query plan’s generation. In contrast, the Oracle SQL optimizer is the most syntax sensitive SQL optimizer in the market which allows you to influence this optimizer to select a better plan. Microsoft SQL Server and Sybase came from the same architecture in the beginning. Consequently, their SQL optimizers behave similarly, but now they are getting more diverse from version to version.

I cannot say which database platform’s approach to SQL optimization is better, but what I can tell you is that Oracle is the most aggressive and open database SQL optimizer among all databases. I believe the architects of Oracle are the first few people who admit the limitation of their SQL optimization technology. They do not pretend to provide you with a mighty database internal SQL optimizer Instead they provide you Hints, SQL Outlines, SQL Profiles and even the latest SQL Plan Baselines features to help you to rectify their SQL optimization problems. Actually, this trend is getting popular. Now, every database platform provides at least some features for you to influence or freeze a SQL query plan.

It is no doubt that SQL optimization is still one of the challenges of today’s database research. We are still suffering from bad performance SQL from day to day. Since there are many common misconceptions floating around SQL optimization, I would like to make some clarifications in this corner and hope it will be helpful for you while you are tuning your SQL statements.

First Misconception:

The estimated cost is a good way to judge the performance of a SQL and its rewrites

It is quite a common misconception that people should use the cost to judge a SQL statement’s performance. Statistically, if we are talking about hundreds of SQL statements, this judgment is correct. For example, if we have a pool of two hundred SQL statements that is running on the same database and if we select the set of one hundred SQL statements with highest estimated cost from this pool and compare the execution time of the set with the other set of one hundred SQL statements with lowest cost from the same pool, the result is probably as expected, the set with the lowest cost will be the better performing set. (This explanation is assuming that there is no one SQL statement that dominates the total execution time.) But if you randomly select two SQL statements to compare their cost and execution time, unless the cost is significantly different up to multiple times, it will be hard to tell which SQL will run faster just by the cost estimation. The reason for this is that the cost estimation algorithm of the database SQL optimizer is not as accurate as what we expect it to be. There are many reasons for why it is not accurate, especially for complex SQL statements. The most common errors found in cost estimations are filtering and join cardinality estimation.

Here is an example to demonstrate the problems a database optimizer will encounter.

select emp_name
  from employee, manager
 where emp_mgr_id=mgr_id
   and mgr_name like “% Peter %”

This SQL extracts all employees’ names whose reporting manager’s name is like “% Peter %”. Since there is no information for the database SQL optimizer to estimate how many managers will have a name like “% Peter %” before the execution, the SQL optimizer will fail to make an accurate estimation for this filtering step in the query plan which is at the first stage of the plan. It will create an arbitrary number at this stage. Another problem is the join cardinality estimation. In a very ideal situation where the emp_mgr_id is equally distributed in the employee table, the SQL optimizer can use the emp_mgr_id histogram to roughly estimate the matched records number, but the accuracy will depend on the arbitrary number created in the first stage. In contrast, in a real life situation, the emp_mgr_id distribution may be skewed in the employee table, so, it will only be by luck that the SQL optimizer can provides an accurate cost estimation of the join cardinality.

Let me give you another more mathematical example with the following:

select *
 from A,B
 where A.f1 = B.key

In this example, the database SQL optimizer has no problem in estimating an accurate cardinality of the join result, since both tables are retrieved without filter criteria. Theoretically, the database optimizer shouldn’t have any problem to do a 100% accurate cost estimation, but a correct cardinality estimation doesn’t mean that you will have a 100% accurate cost estimation. Why?

To simplify the discussion, let me assume the SQL optimizer can generate only two query plans for this SQL. The first query plan is a Nested Loop Join that uses table A to index search of table B using B.key. The other plan is a Sort Merge Join, in which table A and table B are sorted and then merged together. Let’s further simplify the discussion by assuming both tables have the same structure and the same number of records N.

The cost of the Nested Loop is ~ N*log2N/2 ~ O(NlogN),

where log2N/2 is the average depth of the a balance B-tree index and assuming that two nodes for each parent node.

The cost of Sort Merge will be in the order of O(NlogN).

So, you will find that both join methods have similar orders of speed. The difference will be the scale and cost of the individual operation. Let’s further simplify the situation and assume that the scale and the operation cost are the equal for both join methods, and see whether we can distinguish which join method will use less resource or run faster. The answer is still “not 100% sure!” Why?

You may be aware that the sort algorithm speed depends on the input data’s natural order. An ordered data will be a benefit to a Merge sort, but it will be a disadvantage to a Quick sort. It is quite common that the data in a database has at least some sort of order that is created by the users’ operation behavior. So, even though I made a lot of assumptions and tried to make everything isolated, I still cannot 100% accurately estimate the cost of a Nested Loop join and a Sort Merge join for this simple SQL statement, of course, I am talking about 100% accurate estimation here. But for the commercial database, we don’t need that rigid of a requirement, maybe 70% accuracy will be good enough, but please remember that this accuracy rate is not a constant; it will get lower in proportion as your SQL complexity gets higher.

Conclusion

The above mentioned examples are only part of the reasons that the database optimizer cannot carry out accurate cost estimation for your SQL statements. There are many other reasons that have not been discussed here. So, next time when you tune SQL statements, you will get the best result if you test run your rewrites or create physical indexes instead of relying on the virtual indexes cost estimation to judge which SQL rewrite or new indexes are better. I put this as the first discussion topic, since it is a very common mistake, and even some senior members in our team have this misconception sometimes!

SQL Optimizer Part 9 – How to Shorten a Long Optimization Time

Richard To — Fri, 13 Feb 2009 14:16: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 sheds some insight on why the SQL optimization process can take hours to run and what you can do to shorten the process.

SQL Statement with Views

When the SQL statement is using Views instead of Tables the optimization process has an option for rewriting the SELECT statement from the View as well as your original SQL statement. Say you are using three Views in your SQL statement. The optimization process would then rewrite four SQL statements (the original and all three views). You can see how this would add a lot of time to the optimization process particularly if any of the views are quite complex.

To have the optimization skip this process

On the Tuning Lab | Optimizer | Intelligence page, select Intelligence Level 4 using the slider.
Select Custom Settings.
On the Tuning Lab |Optimizer | Optimization page, clear Transform view to inline view.

Only Transform SQL Syntax

You can also specify to have the optimization process just transform the syntax of the SQL statement and not apply any of the Oracle optimization hints.

On the Tuning Lab | Optimizer | Intelligence page, select Intelligence Level 4 using the slider.
Select Predefined.
In the list next to Predefined, select Do not use Oracle optimization hints.
Note: You can select any of the other options which only applies a few of the hints. Or you can select the Custom Settings and only apply a few hints that you choose.

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 Quest SQL Optimizer to tune complex SQL - A real life example #2

Richard To — Wed, 04 Feb 2009 18:35:00 GMT

In a previous blog http://www.toadworld.com/Community/Blogs/tabid/67/EntryID/324/Default.aspx I discussed a real life example of “How to use Quest SQL Optimizer to tune complex SQL”. I spent two days installing the database and another two days tuning a complicated SQL statement, which originally the SQL statement executed in 8 hours and was tuned down to 1 minute and 28 seconds using Quest SQL Optimizer. This SQL statement is now almost 320 times faster than the original SQL statement.

But there was another complex SQL statement that was waiting for me to tune, and I had only one day left from the original 5 days I was given to to tune these problem SQL statements. Normally, I am very confident that I could tune any SQL within a reasonable amount of time. The issue that I had with the Case 2 SQL statement was that it took more than 8 hours to execute. Given that I had only one day to find a better alternative, I could not do even one test run for any alternative, nor did I have the luxury to completely execute the original SQL since executing it took longer than the time I had left to find the best alternative. It was my first experience where I had to tune a SQL statement where I did not know the actual run time of the original SQL statement I was trying to improve.

DOWNLOAD PDF: A Real Life Example 2 (1.1 MB)

SQL Optimizer Part 8 - Display Order for SQL Alternatives

Richard To — Fri, 30 Jan 2009 14:31: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 explains the order in which the SQL statements are displayed.

After the optimization process is finished, the SQL alternatives are displayed in the order of their Oracle cost from the smallest cost to the largest. The Oracle cost is found in the execution plan and is generated by Oracle. The Oracle cost provides an estimate of the system resources that will be used by this execution plan to process the SQL statement. The theory is that the SQL statement with the lowest cost should be the best alternative. When actually testing the performance of alternative SQL statements in your database environment, you will find that frequently the SQL statements with the lowest cost are not the best performing SQL, since if Oracle cost estimation was correct for this SQL, you probably would not need to tune this SQL, so the best practice is to execute all the SQL alternatives to find the best one and not just assume that the SQL statement with the lowest estimate is the best.

But no algorithm can accurately guess which SQL statement will be the best performing SQL for your database. The best performing SQL statement is unique to each environment since the performance depends on the design of the database, the indexes, the amount of data, how accurate the table and index statistics are, database parameter settings, and your hardware all of which combined to make a unique environment.

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 7 – Rearranging the Driving Path

Richard To — Fri, 16 Jan 2009 13:49: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 the importance of finding the best “driving path” for retrieving rows from the database.

We’ll use a simple illustration of a Nested Loop operation that retrieves data accessing 2 tables. Table A has 10,000,000 rows and Table B has 1,000.

If a SQL statement is rewritten so that the execution plan does a full table scan on Table A with its 10 million rows and an index scan (assuming a binary index tree with only two nodes with each parent) on Table B, this will result in 34,538,776 operations.

If the SQL statement is rewritten so that the execution plan does the full table scan on Table B which has only one thousand rows and an index scan on Table A, this will result in 8,059 operations. If you write a SQL statement that accesses the larger table first, it is pretty easy to see that you would have a great performance improvement by just changing which table to be used for the full table scan.

One of the SQL transformation rules used to rewrite the syntax of a SQL statement in Quest SQL Optimizer is to rearrange the “driving path” or order that the tables are accessed when retrieving data.

When a SQL statement accesses 2 tables with Nested Loop join, there are 2 ways you can join the tables. If it uses 3 tables, the number of ways you can join the tables is 3! (3*2*1) or 6, which means the optimization process could generate 6 SQL statements. If 6 tables are accessed in a SQL statement, the number of ways to join the tables is 6! which is 720 ways, so, the optimization process could generate 720 SQL statements using this one syntax transformation rule. If the quota for SQL syntax (the Syntax transformation quota) is 100, then you can see that one rule which can generate 720 statements could use up the entire quota. The SQL optimizer engine has more than 60 other syntax transformation rules to apply to the original SQL statement. So this one rule for rearranging the driving path needs to be held in check so that not all the SQL alternatives are generating using only this one rule.

That is where the Table join permutation quota comes into play. It limits the number of attempts that the SQL Optimizer engine will try to find a different driving path.

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 6 – Using Oracle Optimization Hints when tuning SQL

Richard To — Fri, 02 Jan 2009 15:11: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 explains how the Oracle optimization hints are applied to the SQL statements to generate more execution plans.

One technique that you can use to improve the performance of a SQL statement is to add an Oracle optimization hint. Oracle provides “hints” that can be added to the syntax of a SQL statement to attempt to influence the database optimizer to use a particular execution plan to execute the SQL statement.

Quest SQL Optimizer for Oracle uses this technique in its SQL optimization process. After the syntax of a SQL statement is rewritten, Quest SQL Optimizer adds the Oracle optimization hints to the original SQL statement and the SQL alternatives that it has generated. It is seeing if it can find more unique execution plans using the Oracle hints.

This is a straight forward process when the SQL statement has only one SELECT statement. In this case, each hint is applied to that one SELECT statement. But when the SQL statement has multiple SELECTs within it, you can apply the hints to each SELECT in a plethora of combines.

Here is the approach that Quest SQL Optimizer uses to apply the hints for a SQL statement with multiple SELECTs such as this one with three SELECTs:

SELECT *

 FROM EMPLOYEEx,

       DEPARTMENTx DEPARTMENT1,

       GRADEx

 WHERE EMP_GRADE = GRD_ID

   AND EMP_DEPT = DPT_ID

   AND EXISTS (SELECT 'X'

                 FROM DEPARTMENTx DEPARTMENT2

                WHERE DPT_AVG_SALARY IN (SELECT MIN(DPT_AVG_SALARY)

                                           FROM DEPARTMENTx DEPARTMENT3)

                  AND DPT_ID = EMPLOYEEx.EMP_DEPT)

The hints are applied in a manner like this until most of the possible combinations with all the hints is tried or until the quota setting is reached.

The SQL Optimizer tries most of the possible combinations of the first hint, in this case /*+ hint1 */. In this example, there are 7 different transformations using only the first hint.

SELECT /*+ hint1 */ …

   AND EXISTS (SELECT …

                  (SELECT …)

SELECT …

   AND EXISTS (SELECT /*+ hint1 */ …

                  (SELECT …)

SELECT …

   AND EXISTS (SELECT …

                  (SELECT /*+ hint1 */…)

SELECT /*+ hint1 */ …

   AND EXISTS (SELECT /*+ hint1 */…

                  (SELECT …)

SELECT /*+ hint1 */ …

   AND EXISTS (SELECT …

                  (SELECT /*+ hint1 */…)

SELECT …

   AND EXISTS (SELECT /*+ hint1 */ …

                  (SELECT /*+ hint1 */…)

SELECT /*+ hint1 */ …

   AND EXISTS (SELECT /*+ hint1 */ …

                  (SELECT /*+ hint1 */ …)

Then it moves on to the second hint. It applies the first hint to one SELECT and the second hint to another SELECT like this:

SELECT /*+ hint1 */ …

   AND EXISTS (SELECT /*+ hint2 */ …

                  (SELECT …)

So, it tries most of the possible combinations of applying all the selected hints. Since this can be quite a large number of combinations, you can limit the maximum number of SQL alternatives that are created by applying the Oracle optimization hints using the Hints Quota setting.

On the other hand, if you would like the optimization process to apply several of the Oracle optimization hints when there are multiple SELECT statements in one SQL statement you will need to use a large Hints Quota

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 Quest SQL Optimizer to Tune Complex SQL - A Real Life Example #1

Richard To — Fri, 19 Dec 2008 20:16:00 GMT

Recently, I received an email from the presales support team. They asked me to help them tune three SQL statements for a benchmark database provided by a customer. Our presales support said that our SQL Optimizer was not providing any alternatives for two of these three complex SQL statements. I found two of these SQL statements were very complicated and I knew that there should be a lot of alternative SQL generated. Finally, we identified that the reason no SQL alternatives were generated was because the PC was running out of memory and our SQL Optimizer was terminating the optimization process during SQL alternative generation. So, no alternatives were provided after a few hours of SQL optimization. Here I would like to use the first SQL to illustrate how to use Quest SQL Optimizer to tune a complex SQL statement.

The First Workaround

The first workaround I recommended to the presales support was to lower the Intelligence Level down to 3. This setting will narrow down the number of SQL alternatives that are generated, save some memory, and therefore enable SQL Optimizer to provide at least some alternatives to test run. SQL Optimizer successfully generated a lot of alternative when the Intelligence Level was lowered to 3 from the default Intelligence Level of 4. But there was still another problem in tuning this SQL statement that needed to be addressed. The original SQL needed more than 8 hours to finish. Based on this run time, it could take multiple weeks to test run all the alternatives, but in this case, the benchmark report had to be submitted within one week. So, I had to rely on my human intelligence to figure out a way to shorten the testing time. I spent two days copying and installing the 4G database to see whether I could shorten the time of finding the best SQL alternatives.

A requirement of this benchmark test was that all the SQL alternatives would only be generated by Quest SQL Optimizer, but the selecting of which SQL alternatives to test run first could be subjected to the human wisdom of the tester.

The following SQL was created to mimic the original SQL statement for discussion and education only.

The Case SQL statement

SELECT a.apc_cdr,
   a.crfc_no,
        a.crfc_grp_type_cdr,
        c.pdc_cdr,
        e.ccr_grp_cdr,
        long_term,
        CASE long_term WHEN 'Y' THEN d.more_12_wgh_ftr_pc
                       WHEN 'N' THEN d.lss_12_wgh_ftr_pc
                       ELSE NULL END AS wgh_ftr_pc
   FROM tbp108 a,
        (SELECT a.ld_dt,
                a.ld_type_cdr,
                a.apc_cdr,
                a.crfc_no,
                CASE WHEN nbr_month = 12 THEN 'Y'
                     ELSE 'N' END AS long_term
           FROM tbp108 a,
                (SELECT apc_cdr,
                        crfc_no,
                        NVL(osd_am, 0),
                        COUNT(*) AS nbr_month
                   FROM tbp108
                  WHERE ld_type_cdr = 'M'
                    AND ld_dt >= ADD_MONTHS((SELECT MAX(ld_dt)
                                                FROM tbp104
                                               WHERE ld_type_cdr = 'M'
                                                 AND vsn_cdr <> '2'),
                        -11)
                  GROUP BY apc_cdr, crfc_no, NVL(osd_am, 0)
                 HAVING COUNT(*) = 12) b
          WHERE a.ld_dt = (SELECT MAX(ld_dt)
                             FROM tbp104
                            WHERE ld_type_cdr = 'M'
                              AND vsn_cdr <> '2')
            AND a.ld_type_cdr = 'M'
            AND a.apc_cdr = b.apc_cdr (+)
            AND a.crfc_no = b.crfc_no (+)
         UNION
         SELECT a.ld_dt,
                a.ld_type_cdr,
                a.apc_cdr,
                a.crfc_no,
                'N' AS long_term
           FROM tbp108 a
          WHERE a.ld_dt = to_date('02/10/2008', 'dd/mm/yyyy')
            AND a.ld_type_cdr = TRIM('D')
            AND a.cmt_ic = '1'
            AND a.eur_cmt_am - NVL(a.eur_osd_am, 0) > 0
            AND NOT EXISTS (SELECT *
                              FROM tbp108 p
                             WHERE ld_type_cdr = 'M'
                               AND ld_dt = (SELECT MAX(ld_dt)
                                              FROM tbp104
                                             WHERE ld_type_cdr = 'M'
                                               AND vsn_cdr <> '2')
                               AND p.apc_cdr = a.apc_cdr
                               AND p.crfc_no = a.crfc_no)) b,
        tbp109 c,
        tbp072 d,
        tbp069 e
  WHERE a.ld_dt = to_date('02/10/2008', 'dd/mm/yyyy')
    AND a.ld_type_cdr = TRIM('D')
    AND a.eur_cmt_am - NVL(a.eur_osd_am, 0) > 0
    AND a.cmt_ic = '1'
    AND a.apc_cdr = b.apc_cdr
    AND a.crfc_no = b.crfc_no
    AND a.ld_dt = c.ld_dt
    AND a.ld_type_cdr = c.ld_type_cdr
    AND a.apc_cdr = c.apc_cdr
    AND a.crfc_no = c.crfc_no
    AND a.ld_dt = d.ld_dt
    AND a.ld_type_cdr = d.ld_type_cdr
    AND a.rvv_cdr = d.rvv_cdr
    AND c.pdc_cdr = d.pdc_cdr
    AND c.spdc_cdr = d.spdc_cdr
    AND (d.dlt_cdr = 0
          OR d.dlt_cdr IS NULL)
    AND a.ld_dt = e.ld_dt
    AND a.ld_type_cdr = e.ld_type_cdr
    AND a.rvv_cdr = e.rvv_cdr
    AND c.pdc_cdr = e.pdc_cdr
    AND c.spdc_cdr = e.spdc_cdr
    AND (e.dlt_cdr = 0
          OR e.dlt_cdr IS NULL)
  ORDER BY a.apc_cdr, a.crfc_no, e.ccr_grp_cdr

Table Sizes

Table Name	Size	Number of Rows
TBP108	2.36G	1,777,110

Table Name	Size	Number of Rows
TBP104	230 Mb	31

Table Name	Size	Number of Rows
TBP072	230 Mb	3,497

Table Name	Size	Number of Rows
TBP069	230 Mb	5,180

Table Name	Size	Number of Rows
TBP109	525 Mb	3,753,720

After I installed the database, I quickly got the following execution plan. I did a test run for the original SQL in Quest SQL Optimizer and stepped through the query. I aborted the test run of the original SQL after twenty minutes, since I knew that I couldn’t wait 8 hours for it to finish since I only had 3 days to optimize these 3 SQL statements.

Since my machine is faster than the one that presales was originally using and has 2G memory, I decided to optimize the SQL with Intelligence Level set to 5. Quest SQL Optimizer generated 192 alternative SQL after 10 minutes of optimization. Knowing that the original SQL statement had to run at least 8 hours, if I test run all SQL alternatives, in the worst case I would have to spend 192 x 8 hours = 1536 hours = 64 days ~ 9 weeks to test them all. So, instead of using the execution time of the original SQL statement as the time to terminate the execution of each SQL alternative, I decided to use a user-defined time of 10 minutes as the termination time. I was testing to see if any of the SQL alternatives had a run time of less than 10 minutes that I could quickly spot.

Testing 192 alternative SQL using a termination time of 10 minutes, it took around 32 hours to finish. So after a day of test running, I had not had any luck at finding a faster alternative. I decided to stop the test running process. I only had 2 days left, so I went back to review the execution plan again. Suddenly there was a VIEW that caught my eye. This VIEW was in a nested-loop by other tables with return Cardinality=1, shown in the following:

Since a VIEW in the execution plan is working like a temp table, all subsequent query steps will process and contribute records to this VIEW. If it is a driving VIEW that executes once and then processes the other tables that would be fine. But this VIEW was in a nested-loop by other tables and it looked complicated. With the Cardinality = 1, the Oracle optimizer decided to use a nested-loop to loop thought this VIEW, but for a complex VIEW like this, I don’t think Oracle can easily do an accurate cost estimation for this piece of code.

Since the VIEW is actually processing the red (including the green) colored portion of the original SQL (see SQL statement above), I test ran only this portion of the SQL statement and found that it returned 66,614 records. So the problem was obviously that Oracle did an extremely bad estimation of this portion of SQL statements and chose a poor execution plan although all statistics were up-to-date. The performance problem for the original SQL statement was magnified by the Nested Loop join to this VIEW.

The First Solution to Case 1

In order to solve this problem, I optimized the original SQL statement and then I looked for some alternatives that process this VIEW with a Hash Join or Sort Merge. I selected Alt #161 and executed it. It was remarkable that Alt #161 finished within 12 minutes. With my first round of test running using 10 minutes as the termination time, I just missed by 2 minutes finding that this faster alternative Alt #161 is more than 40 times faster than the original SQL statement.

Note: Actually, the requirement for this SQL was to be tuned to less than 5 minutes in a HP UNIX machine with 4 GB RAM 64-bit 2 CPU. Since we don’t have this machine in our lab, I had to use a PC server to carry out the tuning, the original SQL took more than 30 minutes to execute in a HP machine, but in our PC server it took more than 8 hours to finish. So, I believe this solution will run less than 5 minutes in the HP machine.

The Final Solution

Although happy with the results of this tuning process, as a technical guy, I was curious to do more exploring. Since the SQL is very long and complex, I knew that our SQL Optimizer would spend the entire SQL Syntax Transformation quota (at Intelligence Level 5) in the outside layer of the SQL without reaching down to the sub-queries level to transform the syntax of the sub-queries. When a SQL statement is very complex, the way to optimize the inner level of the SQL statement is to take the sub-query from the SQL statement and optimize only the sub-query in Quest SQL Optimizer to find whether there are any better alternative rewrites at the sub-query level. In this case, I optimized and tested the red colored portion of the SQL statement first, since I knew it was the major resource consuming area that I needed to tune and as you can see I found a great improvement to 12 minutes. After reviewing the execution plan and executing the sub-query to find the actual number of records that are returned by the sub-query, I decided to repeat this process with only the green portion of the sub-query to see whether any better alternative could be found for this sub-query. A few seconds later, I had 5 alternatives to test with this sub-query. The second sub-query took around 9 minutes to finish and to my amazement the Alt#5 SQL only took 17 seconds to finish.

It is interesting to note that the Alt #5 disabled all indexes on table TBP108 and the Oracle cost estimation for this alternative is 10 times higher than its corresponding sub-query. But the performance of Alt #5 is 30 times faster than the original. This example illustrates that you should not trust those so called SQL tuning experts who always teach how to use indexes to tune SQL or rely only on the estimated cost to judge SQL performance. It is especially true when you are tuning a SQL statement that is very complex and accesses tables with large data volumes. I am not going to explain here why it is faster to disable the indexes; since it is a really clumsy mathematic explanation that is not suitable for this casual Blog.

So to finish the process of optimizing the original SQL statement, I combined this sub-query from Alt #5 with the tuned SQL statement that was running in 12 minutes and did another test run. The result was amazing! It took only 1 minute and 28 seconds to finish. This process was fun and most satisfying. I spent almost 2 days to tune a SQL from 8 hours down to 1:28 minutes with Quest SQL Optimizer; it is 320 times faster than the original SQL statement. If I had to do it manually, I believe I would have to spend another few weeks.

I will discuss how to tune another complex SQL in my coming blog, it is fun also.

Optimizing SQL Part 5 – Table Join Syntax

Richard To — Fri, 05 Dec 2008 15:23: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 specify the table join syntax to be used when generating SQL alternatives.

When the syntax for SQL statements was originally developed, the conditions of joining tables were specified in the WHERE clause. The Ansi-92 SQL standard introduced specifying table joins with the INNER JOIN, CROSS JOIN and OUTER JOIN with joining conditions in the FROM clause.

One technique for rewriting the syntax of the SQL statement is to try using different JOIN syntax.

Rewrite SQL using the Ansi-92 JOIN syntax

Specify to use the JOIN clause from the Ansi-92 SQL standard when generating the SQL alternatives. During the optimization, the SQL statement is converted to the Ansi-92 SQL standard and then the SQL syntax transformation rules are applied to rewrite the converted SQL statement. Next, the Oracle hints are applied to the original SQL and the transformed SQL. So you may see SQL alternatives that use the join syntax from the original statement SQL, but these SQL alternatives are simply the original SQL with an Oracle hint applied.

The OUTER JOIN is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the OUTER JOIN using the (+) operator. So to avoid producing the wrong result set, the conversion of the OUTER JOIN syntax cannot be applied.

For example:

SELECT DPT_ID
  FROM EMPLOYEE
       INNER JOIN DEPARTMENT
          ON EMP_DEPT = DPT_ID

Rewrite SQL without using the Ansi-92 JOIN syntax

Specify to join tables in the FROM clause without the JOIN syntax or using a comma. The join analysis occurs in the WHERE clause which specifies that the column in one table is compared to a column in another table. During the optimization, the SQL statement is converted from the Ansi-92 SQL standard and then SQL syntax transformation rules are applied to rewrite the converted SQL. Next, the Oracle hints are applied to the original SQL and the transformed SQL. So you may see SQL alternatives that use the JOIN syntax from the original SQL statement, but these SQL alternatives are simply the original SQL with an Oracle hint applied.

For example:

SELECT DPT_ID
  FROM EMPLOYEE,
       DEPARTMENT
 WHERE DPT_ID = EMP_DEPT

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 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.

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.

Let me give you a hint on using database Optimization Hints - Part Three

Richard To — Fri, 27 Jul 2007 15:49:00 GMT

Be sure to read part one and part two to learn more about Optimization Hints.

Will Hints limit the SQL’s future optimization flexibility?
Yes, it is a general concern of using Hints on your SQL statements; actually, it is a “give and take” game, if you want to have a stable performance environment, you will lose your chance to upgrade your SQL performance while version upgrade and database environment changes, I will recommend you to comment your original SQL statements within your source code, every time if you upgrade your database, you may want test it with your original SQL statements and see whether Oracle has improved and make your original SQL at least performing like your SQL with hints.

For Quest SQL Optimizer; we use Hints and SQL rewrite to explore most of possible query plans that the optimizer can generate for your SQL statement, by physically test run all alternative SQL statements, you will get the best query plan and corresponding SQL rewrite (may has hints embedded) syntax, which means that you will get the best query plan among all possible query plan that can generate by database optimizer, unless the future data distribution is changed dramatically, such as the ratio of tables’ size are reversed, normally, you don’t need to re-optimize the SQL again.

Explore the potential power of your database optimizer
I have been ask this question “Whether we should use Hints to tune SQL statements” many times, if you use Quest SQL Optimizer (or you are sure you are using the best one) and the number of SQL is small say less than 20(or the number you can manage) SQL, I will tell you to go ahead to do it, since it is the safe way to explore the potential power that a database optimizer can give, why sacrifice from bad performance for something that can be fixed or even may not happen in future, it is like when you are in love with a woman very much and you want to get married with her, I don’t think you will worry about that might be a chance of divorce in future and don’t get married with her.

Let me give you a hint on using database Optimization Hints - Part Two

Richard To — Fri, 20 Jul 2007 17:03:00 GMT

To read part one in this series on Optimization Hints you can visit my previous blog.

Use Hints for mission critical systems
For mission critical system, you may not want to take the risk of changing the database's physical structure just to fix a small number of SQL statements’ performance problems. The use of Hints or of rewriting SQL statement is a very safe way to improve the system’s performance without introducing any physical structure changes like new indexes, materialized views, histograms or table partitions, since to change a SQL statement by rewriting the syntax or applying new hints is an isolated event. This approach will normally not introduce any negative impact to other SQL statements.

Use Hints for SQL that access tables with high fluctuation in size
A lot of problems are caused by the SQL that access tables when the data volume rises and falls dramatically from time to time. Some of these problems are:

1. Oracle cannot update its statistics on time.
2. Statistics sampling results are not correct.
3. An old cached plan in memory is not able to correctly handle the table's new size.
4. Optimizer cost estimation algorithm deviates a lot in certain steps of aggregation operations.
5. Histogram granularity is not tiny enough to handle a highly skew distribution.
6. Large plan space is trimmed down for complex SQL statement during internal plan space generation with the result that the best execution plan might be lost.
7. And more…

Actually, these reasons are also applicable to most problematic SQL statements you find in your systems, so, you may want to give the database optimizer a hand in fixing the problem. Hints and SQL rewrite is the cutting-edge weapon for those problem. It not only helps the database optimizer to pick up the right execution plan all the time, but it will not affect the performance of other SQL statements.

Reliable performance requirement
I remember around 15 years ago when I was a Sybase DBA and was working on a hospital system. At that time, Sybase was the first database with cost based SQL optimizer. In the application database, every table was small except the Patients table which had more than 20 millions records. The system was up and running fine for the first month. Unexpectedly, I got a complaint from hospital's operators. The system was hung and they could not get a response to any request. After investigation, I found a frequently executed online query which used to run in less than a second was run away and did not finish after running for an hour. I checked the query plan and compared it with the query plan from the development database. I found that a nested loop join direction was changed from “small tables to index loop Patients table” to “Patients table index loop join small tables”. Obviously, the problem was that the optimizer had wrongly calculated the cost of this query plan and was doing a nested loop on the large Patients table. I had to rewrite the SQL statement to rectify the situation.

So, you may find that a critical SQL statement that is being executed millions times a day cannot afford any performance degradation, no matter how small the change is. A change from 0.1 second to 0.15 second may be a performance disaster. To make sure that the database optimizer will not destroy your career by its unpredictable nature during statistics and environment changes, you may want to play safe and hardcode Hints into your frequently executed and mission critical SQL statements.

In my final entry on Optimization Hints I will address whether Hints limit future optimization flexibility and explore the potential power of your database optimizer.

Let me give you a hint on using database Optimization Hints - Part One

Richard To — Fri, 13 Jul 2007 22:12:00 GMT

Should you use Optimization Hints?
The use of Hints in your SQL statements will fix the execution plan so that Oracle Optimizer will not switch to a better (or a worse) execution plan when the database environment changes. Therefore, many people say, we should use Hints only as the last resort and use them carefully. This argument is certainly a safe declaration issued by most of the database vendors. They don’t want people to hardcode all their SQL statements with Hints and mess up the overall database performance when the data volume dramatically changes or something changes the database environment such as a database upgrade. To be honest, there are not many experts who really know how to use all these optimization Hints in various situations (unless you use Quest SQL Optimizer). So, the argument “Don’t use Hints unless it is your last resort” is obvious.

When you should use Hints?
I think it is easy to make the decision not to do anything. But to make up your mind to do something can be difficult

The following are some guidelines for using Hints which makes the decision of when to use optimization Hints more straight forward.

Uncompromising performance requirement
Some applications require uncompromising performance improvement. Some suboptimal ways to tune your applications may be available, but if the application of Hints to specific SQL statements proves to be the best method that outperforms all other methods, you should use Hints without hesitation. Actually, Hints sometimes provide unique performance advantage over other methods. For example, if the database statistics are up-to-date and you have a poor performing SQL statement, this means that the Oracle optimizer is not able to find the best execution plan for your SQL statement, so the Oracle optimizer is the root cause and what you have to do is guide the Oracle Optimizer to find the best execution plan by adding Hints to your SQL without adding unnecessary overhead to your database. Adding a Hint to a poor performing SQL, can improved the SQL performance without the need of adding new indexes.

Don’t want to create more indexes
We are use to add more indexes to database to improve SQL performance. But adding more indexes means that you are introducing more overhead to your database. Furthermore a new index may improve performance for one of your problematic SQL, but it may introduce a new optimization problem for other existing SQL statements that you may not know until after you have created the index. I had an experience tuning an application that was running in a department of the Hong Kong Government. They had an online query with very poor performance. After review of the SQL statement, I decided to add a new index to improve this SQL statement instead of rewriting the SQL. Since it was easier to create a new index then to change the source code, go through the version control, QA, and deployment procedures. Everything was going fine during the daytime, but I got a call the next morning. They reported that an end of day process was not able to finish until 4:00 am that morning. It surprised me that the new index had introduced such a negative influence to other SQL statements. So, I dropped this index and went back to changing the syntax of the SQL statement in the source code.

In my next blog I will discuss other hints including using Hints for Mission Critical Systems.

Quest Recursive SQL Transformation Technology

Richard To — Wed, 20 Jun 2007 12:58:00 GMT

Quest Recursive SQL Transformation technology is an innovative AI technology that simulates human SQL transformation technique. It incorporates a set of transformation rules to transform SQL statements on a section-by-section basis. This replaces the trial and error method used by human to rewrite the syntax of a SQL statement. Each transformation rule in the optimization engine is independent from one another, like a capsule; the rule’s capsule can only be opened when all necessary conditions are satisfied (see figure 6). This guarantees the semantic equivalence of the rewritten SQL statements so that they produce the same results as the original SQL. When a SQL statement is transformed by one rule to produce a new SQL syntax, the new syntax may now satisfy the requirements of another rule, hence transformation action is carried out in a recursive manner (see figure 7).

Figure 6: Recursive Transformation Engine Figure 7: Chain Effect of SQL Transformation(Insert Image)

Let’s take a look at the following SQL statement and use two of the built-in transformation rules to see how this Recursive Transformation works. We will use one rule which transforms the IN condition to an EXISTS condition and than use another rule that does the reverse, changing the EXISTS condition to an IN condition. We will illustrate this with the following SQL statement.

The first two levels of transformation are shown in the left side of figure 8. SQL statements with syntax different from the original can be produced by following a set of transformation rules. You can see that for each rule applied to the SQL statement, the newly transformed SQL will satisfy another rule. And the order in which the rules are processed can result in different SQL alternatives. In this example, the source SQL has gone through two transformation rules executed in a recursive manner. If we do not stop the recursive transformation, the loop will continue infinitely. A total of 4 unique SQL statements (marked by the solid boxes in Figure 8) are generated by the two transformation rules. If each of these SQL statements ends up with a new execution plan, we potentially have three SQL statements that may give us different performance that can be used as a benchmark to the original SQL statement.

Figure 8: A SQL statement being transformed by two recursive transformation rules

Actually, implementing a transformation rule requires a more complicated control than it is shown in this illustration. For example, Quest SQL Optimizer has to check whether a set operator (UNION, MINUS, or INTERSECT) is in a sub-query, whether multiple items are in the SELECT list, and etc…. But the beauty of those rules is that they are self-protected, which means that the transformation and conditional checking are encapsulated into a one rule-capsule to prevent generating incorrect SQL statements (SQL statements that do not produce the same result as the original SQL). In our Recursive Transformation Engine, we have a multitude of transformation rules which can handle very complicated situations. The result of the recursive transformation for a complex SQL statement may exceed what you can imagine. For example, some transformation rules can be applied endlessly to transform a SQL statement to another semantically equivalent statement without limitation, so quotas must be used to control the number of SQL alternatives that are generated.

Today, with our Recursive SQL Transformation Engine, you can explore ultimate potential that a database SQL optimizer can give to speedup your SQL statements.

From SQL Optimization Hints to Plan Instructions

Richard To — Wed, 02 May 2007 15:05:00 GMT

Most database vendors provide optimization hints which enable a user to influence the decision the database SQL optimizer will make when determining which execution plan it will choose. Oracle provides a full set of optimization hints to help users to rectify an individual SQL performance problem, thereby making it the most open of all the database platforms. This approach admits that the database's internal SQL optimizer cannot guarantee every SQL will perform well and therefore it sometimes necessary for the user to intervene for some SQL statements when the database SQL optimizer has failed to generate a good execution plan for them.

You may not be aware that upgrading the database SQL optimizer is a risky undertaking for database vendors. No matter how good a new version of the database SQL optimizer is, it is going to have some negative impact on the performance of at least a few SQL statements. For example, if a new version of the database SQL optimizer can fix 50% of the existing SQL statements' performance problems, but in the meantime, it introduces 5% new performance problems for existing good SQL statements, mathematically, it is 10 times better than the old version. It should be a good idea to the upgrade. But the point is, that most systems are already running on an “adopted” status, which means that users have accepted what they have, they know which functions are running slow and that the person who tunes the database may have already changed the system configuration to address these problems. Sometimes, the users’ daily routines are changed to accommodate those slow SQL processes. For example, they may schedule a meeting for when they are going to launch a batch process or get a cup of coffee while a long running SQL is executing.

If there are any changes after upgrading to a new database version, you are likely to find that even with 50% improvement in SQL performance, this will not stop the users from complaining about the 5% new performance problems. So, this is why database vendors need to provide optimization hints to let the users fix problems at the individual SQL statement level and not in database SQL optimizer global level. This trend is becoming more popular among database vendors. Sybase’s Plan Forces and Microsoft SQL Server’s optimization hints work like Oracle optimization hints. IBM DB2 UDB does not provide any optimization hints, but they do provide optimization classes to adjust the intelligence of execution plan generation without the user's specific control. Unless the database SQL optimizer can guarantee that each execution plan it generates is the best execution plan for each unique SQL and database environment, we prefer the approach that Oracle provides which gives us the opportunity to help the database SQL optimizer choose the best execution plan using the optimization hints.

Something more than optimization hints is needed
Optimization hints are used to guide the database SQL optimizer to pick up a user specified execution plan during SQL optimization. But the problem with this approach to SQL tuning is that you must change your source code to include the optimization hints in your SQL statements. You can see that the effort needed to tune a SQL statement may be substantial. The tasks, from source modification, program compilation, unit testing, QA review to deployment, are time consuming and any mistakes may cause business interruption or an data integrity problem. For the users of packaged software, since the source code cannot be modified, so tuning SQL statements by adding optimization hints is not possible. To resolve this, database vendors have introduced a new feature to allow the user to control how the database SQL optimizer generates the execution plan without the need to change your SQL text. As each database vendor has its own naming practice, I call this type of feature “Plan Instruction Language”. For example, Sybase was the first to introduce the full implementation of this concept with its “Abstract Plan” feature. (Oracle Stored Outlines came out before Sybase's "Abstract Plan", but initially it focused on plan stabilization and was not easy to manage) To instruct the database SQL optimizer to generate a user preferred execution plan for a specific SQL statement, users can modify and save the “Abstract Plan” with the SQL text into the database. Sybase will match the incoming SQL text with the stored SQL text the next time the SQL statement is executed. If it finds a match, the corresponding “Abstract Plan” will be used to generate the execution plan for the SQL statement.

For incoming SQL statements, the SQL text is compared to the stored SQL text, and if a match is found, the saved abstract plan is used to execute the SQL statement..

This new method for SQL tuning reduces the time that a developer needs to tune a SQL statement, since the developer no longer needs to modify, QA, and deploy the source program. What the user has to do is to modify and test the Abstract Plan’s performance and then save it into the production database. Then, the targeted SQL performance will be improved with the new execution plan. Any unexpected performance degradation can be recovered easily by dropping the newly created Abstract Plan. Furthermore, this new SQL tuning feature enables the package users to tune their SQL statements without violating the maintenance contract by privately creating Abstract Plans for slow SQL statements. Package providers could even deploy their program source with different Plan Instructions to fit different database sizes.

This approach is now being commonly adopted by major database vendors. SQL Server 2005 has introduced their "Plan Guides", DB2 Version 9 has its new "Optimizer Profiles and Guidelines" and Oracle 10g has "SQL profiles". “Plan Guides” in SQL Server 2005 provides more powerful SQL optimization instructions that users can manually create for a SQL statement, apart from traditional optimization controls, you can parameterize (share cursor) or un-parameterize a SQL with hardcode literals, furthermore you can force a SQL with bind variable to “optimize for” a specific value (literal) without the need to change your source code as the following example:

Example:
Select * from employee where employee_id <@emp_id
OPTION ( OPTIMIZE FOR (@employee_id = 10) );
The example to force database optimizer to use employee_id index to search EMPLOYEE table, no matter what value it is passed to @employee_id

In coming Oracle11g, there is a new feature called “Plan Management”, according to what is described on the Oracle website, it appears that Oracle Plan Management will work like an internal plan versioning tool. It will verify and review the new execution plan before it is used, which means that the Oracle SQL optimizer will be more careful to use a new plan. The good news is that your SQL will not suddenly slowdown due to environment or statistics change, but it may delay or even forbid the Oracle cost based optimizer to adapt to a changing environment. Anyway, with the limited information available on Oracle 11g, we cannot draw any conclusion at this moment. Let’s wait and see what this new feature will bring!

Is SQL Optimization an Unsolvable Problem?

Richard To — Mon, 02 Apr 2007 20:27:00 GMT

In Computability theory there is a famous decision problem called halting problem which can be informally stated as follows:

Given a description of a program and its initial input, this determines whether the program, when executing the input, will ever halt (complete). The alternative is that it runs forever without halting (stopping).

In 1936, Alan Turing proved that a general algorithm to solve the halting problem for all possible inputs cannot exist. It is said that the halting problem is undecidable over Turing machines.

Perhaps you find that the halting problem is similar to one of RDBMS SQL optimization problems. Actually, there are two major problems that modern RDBMS SQL optimizers encounter today. The first one is the limited size of the plan space (the number of execution plans can be investigated during SQL optimization). Due to the fact that the database SQL optimizer has to do real time optimization, it is impossible for database SQL optimizer to do an exhaustive plan space search (search all possible execution plans internally), otherwise the optimization time will be much longer than the time it takes to execute the SQL statement even with a bad execution plan. The second problem is the accuracy of cost estimation algorithm. After the database SQL optimizer has generated all the internal SQL rewrites and their corresponding execution plans, the database SQL optimizer uses the cost estimation algorithm to choose the theoretical best execution plan, the one with the lowest cost, to execute. The problem is similar to the famous halting problem in Computability theory. Of course, the logic embedded in a SQL statement is much less than that of a program mentioned in the Computability theory. So, we still can use tables, indexes, histograms, assumptions, and other statistics to estimate the cost of an execution plan, but the problem now is no longer to tell whether a SQL/program will halt or not. We are facing a more difficult problem, that is, how long a query(with or without inputs) will run with a specific execution plan? Database vendors have spent a lot of effort in this area, but the fact remains that we still have to tune SQL statements ourselves.

Accurate Cost Estimation Versus Plan Space

The goal of a good database SQL optimizer is not only to provide accurate cost estimation for SQL statements, but it should generate more internal execution steps to compose more execution plans. More internal execution plans means that the database SQL optimizer has a bigger plan space during SQL optimization.

Here is an example to show you the relationship between plan space and cost estimation. Consider how you travel to your office and suppose you only have one route to go to the office. If the only way that you go to the office is jammed due to the weather or traffic conditions, you probably will not be able to get to your office on time. Consequently, most people have multiple routes (plan space) in mind. Every morning, based on the weather and traffic conditions, they will select the best route (cost estimation) to office. The more routes they have in mind, the higher the possibility that they can overcome more complex traffic and weather conditions. The point is, with more possible execution plans, every morning they will spend more time thinking about which path is the best way to go to their office. As the number of routes increases, the chance that they select a non-optimal path gets higher. This problem is similar to what the database SQL optimizer faces. The accuracy of the database SQL optimizer’s cost estimation is opposite to the size of plan space that the database SQL optimizer can generate. The bigger the plan space, the easier it is for the optimizer to select a non-optimal execution plan. That is why you will find Oracle’s SQL statement performance always has room for improvement, since Oracle has a relative larger plan space and it is more sensitive to your SQL syntax.

Dummy SQL Transformation Rules?

Richard To — Thu, 01 Mar 2007 18:33:00 GMT

I have been asked many times that why there are some dummy SQL transformations/rewrites that look meaningless, but it works in certain situations. Let me give you some examples that may help to explain what theory on behind of Quest SQL Optimizer.

The following rules are implemented in the Quest SQL Optimizer for different platforms to deal with individual database SQL optimizer’s behavior. Some rules may look puzzling. In order to understand the theory behind some of these transformations, you may need to have an in-depth understanding of database optimization theory and the design approach to optimization that each database vendor has incorporated into the database SQL optimizer along with the platform specific optimizer functions.

It is a transformation which may look puzzling. The original SQL statement uses a range scan of the employee table with the condition “emp_id > 123456”. For example; we know that IBM DB2 UDB or Microsoft SQL Server have an intelligent algorithm which can preview the value “123456” before the execution plan is generated. Consequently, if “emp_id>123456” returns a small subset of records from employee table, the database SQL optimizer should generate execution plan that uses an index search. In contrast, if the SQL statement returns almost all the records from the table, the database SQL optimizer should generate an execution plan using a full table scan to save the time of retrieving extra index pages. This works fine in most cases. But, there are several factors that can cause the database SQL optimizer to make a mistake. For example;

1) If the statistics are not up-to-date.

2) The data distribution is so skewed that the granularity of the histogram is too big to handle.

3) The costing algorithm fails to take into account the configuration of different machines' I/O thru-put, CPU processing, memory speed, and other system resources.

For Microsoft SQL Server, if you want to rectify the problem, you can use the INDEX hints to force the database SQL optimizer to pick up an index. But for IBM DB2 UDB, it is a little bit more difficult. Let’s look at the following transformations which uses a dummy operation COALESCE(123456,123456) or adds +0 to the literal 123456. The purpose of these dummy operations is to hide the value of 123456, so Microsoft SQL Server or IBM DB2 UDB will not be able to see the value while parsing the SQL statement. Therefore, they will make a rough estimation when they do not know the actual value. Erring on the side of caution, the database SQL optimizer will normally select the execution plan that uses an index search.

In modern RDBMS SQL optimizers, this IN sub-query can normally be transformed to a join SQL statement, which means that the join path can be either from A→B or B→A. For the Nested Loop join case, the path plays an important role in determining the speed of the SQL. For the Hash join or Sort Merge join cases, the join path (the order that the tables are accessed) may not be that significant. But, this transformation rule which adds a GROUP BY clause serves two purposes. The first purpose is that it forces the sub-query to be processed individually. If the original execution plan is a Nested Loop join, after the transformation, the execution plan will normally be changed to a Hash or a Sort Merge join. The other purpose is that the additional GROUP BY function will trim down the result set from B.key (if B.key is not unique) and the duplicate records will be eliminated first. Sometimes this will help to improve the join speed.

Transformation Rules Relating to Index Usage

Richard To — Fri, 09 Feb 2007 16:25:00 GMT

In Quest SQL Optimizer, transformation rules relating to index usage are designed to guide the database SQL optimizer as to how it should use the indexes for a specific SQL statement. Although those rules may familiar to most of SQL developers, but it is still worth to illustrate some commonly used rules for SQL beginners, of course, if you don’t want to do it by yourself or if you don’t have time to check every SQL in your application, you can use Quest SQL Optimizer to do those boring jobs. Why? I still remember around 20 years; I was so exciting that I was getting to know more in tuning SQL statements, the first few hundreds of SQL were interesting to me, after I was promoted to DBA position, then tuning SQL is no longer an interest to me, it become my job! It is ironic when you get pay for your interest; you are no longer interested in it anymore. That’s why I invented a product to automate it job.

The following two SQL statements are quite often used in an online query system in which the user inputs values in a range from :c to :d to retrieve data from a table. If user does not specify the range, the :c and :d values will be null. Due to the complexity of the SQL statement caused by using an OR condition plus some undetermined variables, the database SQL optimizer will usually choose a full table scan to process the SQL statement.

For a SQL statement that has multiple indexes which can be used to search a table, the following transformation can be used to enable any one of the indexes.

To disable the index on the numeric emp_id field, zero was added to the field. This disables the index because zero must be added to emp_id for each row thereby requiring a full table scan or enabling a different index to be used.

The same process is used for the character field of emp_dept where nothing, represented by '' (single quotes with no value), was concatenated to the field. This also disables the index because the concatenate operation must be preformed for each row thereby requiring a full table scan or enabling a different index to be used.

The other technique for disabling the index is to use the COALESCE operation which in the way that it is used in this case does nothing to the value in the field. But because it must be preformed for each row in the table, it disables the index and thereby causes a full table scan or enables a different index to be used.

How to control many table join

Richard To — Wed, 03 Jan 2007 23:42:00 GMT

In my last blog, I use two tables join to illustrate the simple path control, now let’s use a three table join SQL statement to demonstrate a more complicated scenario. Let’s assume that A.key, B.key and C.key are all indexed.