|
|
Highlights of SQL Optimizer 7.5 |
|
RichardTo
|
Friday, October 02, 2009 3:59 AM
|
|
|
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 na ...
|
 |
|
|
Read More...
|
|
|
|
|
|
|
Executing SQL In Tuning Lab – Part 7 – Testing SQL with Variables |
|
RichardTo
|
Friday, July 10, 2009 5:33 AM
|
|
|
|
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.
|
 |
|
|
Read More...
|
|
|
|
|
Executing SQL In Tuning Lab – Part 5 – Selecting Alternatives to Test |
|
RichardTo
|
Friday, June 12, 2009 5:27 AM
|
|
|
|
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.
|
 |
|
|
Read More...
|
|
|
|
|
|
10 Common Misconceptions in SQL Tuning #2 |
|
RichardTo
|
Tuesday, April 28, 2009 8:50 AM
|
|
|
|
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 ...
|
 |
|
|
Read More...
|
|
|
Executing SQL in Tuning Lab – Part 1 – Criteria for Terminating SQL |
|
RichardTo
|
Friday, April 17, 2009 2:37 AM
|
|
|
|
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.
|
 |
|
|
Read More...
|
|
|
|
10 Common Misconceptions in SQL Tuning #1 |
|
RichardTo
|
Friday, March 13, 2009 4:11 AM
|
|
|
|
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 devel ...
|
 |
|
|
Read More...
|
|
|
|
|
|
|
|
How to use Quest SQL Optimizer to Tune Complex SQL - A Real Life Example #1 |
|
RichardTo
|
Friday, December 19, 2008 12:16 PM
|
|
|
|
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 genera ...
|
 |
|
|
Read More...
|
|
|
|
|
|
|
|
A Question from our Quest SQL Optimizer User |
|
RichardTo
|
Thursday, September 04, 2008 1:50 AM
|
|
|
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 SQ ...
|
 |
|
|
Read More...
|
|
|
|
A New Intelligent Test Run Function is under Research |
|
RichardTo
|
Friday, June 13, 2008 3:20 AM
|
|
|
|
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 o ...
|
 |
|
|
Read More...
|
|
|
|
Batch Optimizer Part 4 – Options Settings for the Batch Optimizer |
|
RichardTo
|
Friday, May 09, 2008 5:53 AM
|
|
|
|
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 ru ...
|
 |
|
|
Read More...
|
|
|
Batch Optimizer Part 3 – Options Settings for the Batch Optimizer |
|
RichardTo
|
Friday, April 25, 2008 6:44 AM
|
|
|
|
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.
|
 |
|
|
Read More...
|
|
|
|
Batch Optimizer Part 1 – How the Batch Optimizer Works |
|
RichardTo
|
Friday, March 28, 2008 4:01 AM
|
|
|
|
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 ...
|
 |
|
|
Read More...
|
|
|
How to use the Quest SQL Optimizer |
|
RichardTo
|
Tuesday, March 18, 2008 5:31 AM
|
|
|
|
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 ...
|
 |
|
|
Read More...
|
|
|
What about a Self-learning SQL Optimizer? |
|
RichardTo
|
Tuesday, October 09, 2007 4:45 AM
|
|
|
|
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 ...
|
 |
|
|
Read More...
|
|
|
A SQL Performance History from AWR |
|
RichardTo
|
Thursday, September 13, 2007 2:51 PM
|
|
|
|
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 wi ...
|
 |
|
|
Read More...
|
|
|
How Quest SQL Optimizer works with Hints |
|
RichardTo
|
Tuesday, August 14, 2007 10:28 AM
|
|
|
|
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 ...
|
 |
|
|
Read More...
|
|
|
|
|
|
Quest Recursive SQL Transformation Technology |
|
RichardTo
|
Wednesday, June 20, 2007 4:58 AM
|
|
|
|
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 t ...
|
 |
|
|
Read More...
|
|
|
From SQL Optimization Hints to Plan Instructions |
|
RichardTo
|
Wednesday, May 02, 2007 7:05 AM
|
|
|
|
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 i ...
|
 |
|
|
Read More...
|
|
|
|
Dummy SQL Transformation Rules? |
|
RichardTo
|
Thursday, March 01, 2007 10:33 AM
|
|
|
|
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.
 |
|
|
Read More...
|
|
|
Transformation Rules Relating to Index Usage |
|
RichardTo
|
Friday, February 09, 2007 8:25 AM
|
|
|
|
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 beg ...
|
 |
|
|
Read More...
|
|
|
How to control many table join |
|
RichardTo
|
Wednesday, January 03, 2007 3:42 PM
|
|
|
|
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.
|
 |
|
|
Read More...
|
|
|
How to Control Two Tables Join Path? |
|
RichardTo
|
Thursday, December 07, 2006 3:06 PM
|
|
|
|
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.
|
 |
|
|
Read More...
|
|