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 to hardcode all their SQL statements with Hints and mess up the overall database performance when the data volume dramatically changes or something changes the database environment such as a database upgrade. To be honest, there are not many experts who really know how to use all these optimization Hints in various situations (unless you use Quest SQL Optimizer). So, the argument “Don’t use Hints unless it is your last resort” is obvious.
When you should use Hints?
I think it is easy to make the decision not to do anything. But to make up your mind to do something can be difficult
The following are some guidelines for using Hints which makes the decision of when to use optimization Hints more straight forward.
Uncompromising performance requirement
Some applications require uncompromising performance improvement. Some suboptimal ways to tune your applications may be available, but if the application of Hints to specific SQL statements proves to be the best method that outperforms all other methods, you should use Hints without hesitation. Actually, Hints sometimes provide unique performance advantage over other methods. For example, if the database statistics are up-to-date and you have a poor performing SQL statement, this means that the Oracle optimizer is not able to find the best execution plan for your SQL statement, so the Oracle optimizer is the root cause and what you have to do is guide the Oracle Optimizer to find the best execution plan by adding Hints to your SQL without adding unnecessary overhead to your database. Adding a Hint to a poor performing SQL, can improved the SQL performance without the need of adding new indexes.
Don’t want to create more indexes
We are use to add more indexes to database to improve SQL performance. But adding more indexes means that you are introducing more overhead to your database. Furthermore a new index may improve performance for one of your problematic SQL, but it may introduce a new optimization problem for other existing SQL statements that you may not know until after you have created the index. I had an experience tuning an application that was running in a department of the Hong Kong Government. They had an online query with very poor performance. After review of the SQL statement, I decided to add a new index to improve this SQL statement instead of rewriting the SQL. Since it was easier to create a new index then to change the source code, go through the version control, QA, and deployment procedures. Everything was going fine during the daytime, but I got a call the next morning. They reported that an end of day process was not able to finish until 4:00 am that morning. It surprised me that the new index had introduced such a negative influence to other SQL statements. So, I dropped this index and went back to changing the syntax of the SQL statement in the source code.
In my next blog I will discuss other hints including using Hints for Mission Critical Systems.