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