Aug
21
Written by:
Richard To
Friday, August 21, 2009 6:10 AM
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.