The new Quest SQL Optimizer for SQL Server 8.0 has two major enhancements: the improved SQL rewrite engine and the new Plan Control SQL tuning module. Today, I want to discuss some new SQL transformation rules in SQL Optimizer 8.0.

 

How to assess the SQL Server internal SQL optimizer performance?

You may be aware that every SQL statement has to be optimized by the database's internal SQL optimizer before it can be processed. Simply speaking, the database's internal SQL optimizer is designed to find the best way to process your SQL statements. There are two major factors to assess a database's internal SQL optimizer. The first is the accuracy of cost estimation and the second is the size of the plan space.

...

I did not use to pay much attention to the update table in an UPDATE statement. I figured that there was not a lot I could do with an update table unless there were complex search conditions. I preferred to focus on the sub-queries that appear on the update table's filtering and the update SET command.  

Today, I want to introduce a new transformation rule that is not implemented in our engine yet, but it is quite...

Richard discusses why SQL tuning by SQL Plan Baselines is a safe and effective method to improve your SQL performance.

After almost a year of development, I would like to announce the beta release of SQL Optimizer for SQL Server 8.0 is now available here: http://sqloptimizersqlserver.inside.quest.com/index.jspa.

To be honest, I was primarily focused on the Oracle version of SQL Optimizer previously. Consequently, we sometimes received complaints from our SQL Server users saying that our tuning engine had not caught up with the SQL Server upgrade. I am confident that the new version of SQL Optimizer will satisfy most SQL Server users’ expectations. 

...

What is a time-slot based SQL workload?

A SQL workload can come from different sources.  For example, an executing program, a database SQL repository, or a monitoring tool that captures SQL from the database, memory, network or client programs. The way a representative SQL workload is formed will ultimately affect the result of a workload based index advisor or an impact analyzer.  If you are using a monitoring tool...

 

What is a workload based index advisor?

A workload based index advisor uses a specified SQL workload to recommend an optimal indexing configuration. The recommended indexes should be optimal for a specific workload that you are encountering every day. I believe most people know the meaning of workload based index optimization, but few of us really use it as a practice since it is too difficult for a human to...

I have been performing research in the area of index optimization for more than 10 years and I'm pleased to announce that a new workload based index advisor is now available in Quest SQL Optimizer 8.5 for Oracle. The first index advisor algorithm I developed is still available in our SQL Rewrite module.  This advisor was designed to work on a single SQL statement or a small set of SQL and does not consider workload.  It embeds a large amount of human knowledge/rules to narrow down the index candidates in order speed up the search process. Rule/knowledge based index advisors can work well for small numbers of SQL where the index candidate space is relatively small but become ineffective when used on large SQL populations incorporating composite indexes and where index recommendations are based on actual application workload.

I have a dream to build the world's best index advisor that solves for any number of SQL statements, from one to thousands, and auto balances its recommendation based on actual workload. My research in this area was carried out almost 10 years ago and the research paper completed around 6 years ago but due to some reasons we have been unable to implement it - until now. I have not been so excited since I created my first...

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

 

What are Hints?

Most database vendors provide a plan hints feature for users to influence their database SQL optimizer to generate a specific plan for a given SQL statement. Basically, every database vendor provides their own hints feature to correct their database SQL optimizer decisions in special...

Recently I had to prepare a presentation about SQL tuning technology for a university in China. I used to use the Halting Problem to explain the difficulty of building a 100% correct SQL cost estimation algorithm in my presentation (please visit my blog for details: Is SQL Optimization an Unsolvable Problem?...

For Part I, Part II & Part III please visit:

How to Use Quest SQL Optimizer to Tune Data Warehouse SQL – Part I

How to Use Quest SQL Optimizer to Tune Data Warehouse SQL – Part II

...

For Part I, please visit:  How to Use Quest SQL Optimizer to Tune Data Warehouse SQL – Part I

 

A Data Warehouse (DW) is a database system used in an organization to collect data from one or more data sources and is typically used to generate reports or...

A Data Warehouse (DW) is a database system used in an organization to collect data from one or more data sources and is typically used to generate reports or run ad-hoc queries that aggregate data for business analysis purposes. A DW generally consists of an ETL (Extract, Transform, and Load) tool, a database, a reporting tool and other facilitating tools such as a data modeling tool. The nature of a DW is to store large...

In SQL Optimizer for Oracle 8.0, we have selected two simpler rules from our rules library and incorporated them. For a discussion of the first rule, please visit Toad World Blogs - Two New Transformation Rules in SQL Optimizer Version 8 – Part I for details. I...

 

I have been asked many times how to use one source SQL statement and tune it for different sized databases. The problem is that when you have only one program source and you want to deploy it to various sized databases in different companies, it is quite common that the SQL performs well in company A but performs poorly in company B. If you tune the SQL for company B, the new SQL syntax and plan may not be good for...

I haven’t written anything in my blog in the last two months since I’ve been busy developing and testing the new version 8 of Quest SQL Optimizer for Oracle. It has been a long time since I’ve been as deeply involved in the development as I’ve been in last few months. In Chinese, “8” is a lucky number - it means prosperous and good luck.  So we have put a lot of effort to make it happen and hope this new version will bring you prosperity and solve more SQL performance problems than ever before.

We are working on some new SQL transformation rules for upcoming releases of Quest SQL Optimizer for Oracle. For every new transformation rule candidate, we have to do a lot of testing to make sure that there is at least some performance gain in some environments. But the most important task we have to do is to prove that the potential transformation rule is semantically equivalent before and after the SQL transformation.

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

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

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

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

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

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.

...

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

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.

...

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?

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.

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

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

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.

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.

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.

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.

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

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

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.

...

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

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

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

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.

...

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

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

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

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

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

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

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

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

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

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

  

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

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

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

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

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

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

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

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.  

...

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

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

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

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

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

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

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

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

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

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.

In old version of Oracle database, it is easy to control the join path by reordering the tables in table list after the FROM clause, but it is getting difficult to control the join path in today’s cost based SQL optimizer, I am going to introduce method that is applicable in most databases such as Oracle, Sybase, DB2 and SQL Server in the following:

To control a join path, we cannot tell the database SQL optimizer which path is the best one to select. Instead, we add something to the syntax of the SQL statement that causes an increase to the cost of the current join path selected by database SQL optimizer. Let’s take a look at the following example of two table join scenarios.

The Nested Loop join operation is the basic join operation which is supported by most RDBMS, since it requires less memory and less temporary space. Normally, it can provide faster data response time than other join operations. But, the path of a Nested Loop join will significantly affect the speed of the join operation. Let’s use a two table join as an example to understand how this works.

For years, commercial database manufacturers have fought an endless battle to improve the performance of inserting, updating, deleting, and retrieving information stored in the database. Despite their continual efforts and hard work, we have not seen a significant improvement in the performance of most Relational Database Management Systems (RDBMS). Users still suffer from under-performing SQL statements and database experts...