To read part one in this series on Optimization Hints you can visit my previous blog.
Use Hints for mission critical systems
For mission critical system, you may not want to take the risk of changing the database's physical structure just to fix a small number of SQL statements’ performance problems. The use of Hints or of rewriting SQL statement is a very safe way to improve the system’s performance without introducing any physical structure changes like new indexes, materialized views, histograms or table partitions, since to change a SQL statement by rewriting the syntax or applying new hints is an isolated event. This approach will normally not introduce any negative impact to other SQL statements.
Use Hints for SQL that access tables with high fluctuation in size
A lot of problems are caused by the SQL that access tables when the data volume rises and falls dramatically from time to time. Some of these problems are:
1. Oracle cannot update its statistics on time.
2. Statistics sampling results are not correct.
3. An old cached plan in memory is not able to correctly handle the table's new size.
4. Optimizer cost estimation algorithm deviates a lot in certain steps of aggregation operations.
5. Histogram granularity is not tiny enough to handle a highly skew distribution.
6. Large plan space is trimmed down for complex SQL statement during internal plan space generation with the result that the best execution plan might be lost.
7. And more…
Actually, these reasons are also applicable to most problematic SQL statements you find in your systems, so, you may want to give the database optimizer a hand in fixing the problem. Hints and SQL rewrite is the cutting-edge weapon for those problem. It not only helps the database optimizer to pick up the right execution plan all the time, but it will not affect the performance of other SQL statements.
Reliable performance requirement
I remember around 15 years ago when I was a Sybase DBA and was working on a hospital system. At that time, Sybase was the first database with cost based SQL optimizer. In the application database, every table was small except the Patients table which had more than 20 millions records. The system was up and running fine for the first month. Unexpectedly, I got a complaint from hospital's operators. The system was hung and they could not get a response to any request. After investigation, I found a frequently executed online query which used to run in less than a second was run away and did not finish after running for an hour. I checked the query plan and compared it with the query plan from the development database. I found that a nested loop join direction was changed from “small tables to index loop Patients table” to “Patients table index loop join small tables”. Obviously, the problem was that the optimizer had wrongly calculated the cost of this query plan and was doing a nested loop on the large Patients table. I had to rewrite the SQL statement to rectify the situation.
So, you may find that a critical SQL statement that is being executed millions times a day cannot afford any performance degradation, no matter how small the change is. A change from 0.1 second to 0.15 second may be a performance disaster. To make sure that the database optimizer will not destroy your career by its unpredictable nature during statistics and environment changes, you may want to play safe and hardcode Hints into your frequently executed and mission critical SQL statements.
In my final entry on Optimization Hints I will address whether Hints limit future optimization flexibility and explore the potential power of your database optimizer.