Apr
28
Written by:
Richard To
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 words “SQL Tuning”, you will find that there are thousands of articles that tell you how to write better SQL and how to read the execution plan. They recommend what is good SQL syntax, or ask you to update the tables/indexes statistics and create new indexes. Yes, all of these are workable solutions for specific problems, or these are good only for simple problems. But when it comes to a complex SQL statement with many tables and join situations, actually, most of these recommendations are not applicable, since most complex SQL statements are already well written by experienced programmers with a tidy syntax structure. Normally all necessary indexes are built and all the tables’ statistics are updated. Most likely, you won’t find any human introduced errors within these SQL. In reality, the root cause of the performance problems for complex SQL statements is the database internal SQL optimizer. Two major limitations may cause the database SQL optimizer not to find the best way to process your complex SQL:
- Plan Space is relative small for complex SQL statements
For a simple SQL statement, the number of ways (execution plans) to process the SQL statement will be small. But for a complex SQL statement, theoretically, the database SQL optimizer can generate a multitude of execution plans internally to process the SQL statement. The number of execution plans that can be generated by the database SQL optimizer is called the plan space. Due to the time limitation of the database, real time parsing of your SQL statement and the limited syntax rewrite ability, the database SQL optimizer cannot consider all possible execution plans. For simple SQL statements, having a small plan space is acceptable since not many execution plans can be generated. But for complex SQL statements, the number of actual execution plans that will be generated by the database SQL Optimizer will be relatively small compared to the potentially huge number of execution plans that could be generated. So, the likelihood that the database SQL optimizer can select the best query plan will significantly decrease for complex SQL.
- Cost estimation is not accurate
In my last blog “10 common misconceptions in SQL Tuning #1”, I mentioned that we cannot determine whether a SQL statement will or will not have good performance based on the database SQL optimizer cost estimation. It is a very common misconception that people always think that 100% correct database statistics will result in 100% query plan cardinality estimation and 100% correct cardinality estimation will result in 100% cost estimation. Some people cannot even distinguish the difference between cardinality estimation and cost estimation. There are many reasons that contribute to why the database SQL optimizer cannot produce a 100% accurate cost estimation. So, within the limited plan space that is generated by the database SQL optimizer that I described above, plus inaccurate cost estimation, it is really hard to guarantee that the database SQL optimizer will provide a good performing execution plan for every SQL statement.
I am not saying that a simple SQL statement does not need to be tuned, because sometimes the cost estimations are exceptionally bad under certain conditions that make the database SQL optimizer select a poorly performing plan. No matter how good looking your SQL syntax structure is, you may need to restructure your SQL with Oracle optimization hints or rewrite the syntax. My goal is not to teach you which syntax is better. That really is meaningless, since every database has its own unique data distribution and statistics. The same SQL syntax in various individual databases and different database versions will have different behavior. If you can read the execution plan, you may know what is causing the performance problem and make corresponding syntax changes and use hints to improve your SQL. But the goal of restructuring is not to write a better-looking SQL. The objective is to fool or guide the database SQL optimizer to select a better plan. I have discussed those tactics previously in my blog (please refer to the following links). So, don’t be surprised, next time, if you find an ugly SQL statement generated by our Quest SQL Optimizer that is performing a hundred times faster than your SQL statement!