Oct
2
Written by:
Richard To
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 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.