Be sure to read part one and part two to learn more about Optimization Hints.

Will Hints limit the SQL’s future optimization flexibility?
Yes, it is a general concern of using Hints on your SQL statements; actually, it is a “give and take” game, if you want to have a stable performance environment, you will lose your chance to upgrade your SQL performance while version upgrade and database environment changes, I will recommend you to comment your original SQL statements within your source code, every time if you upgrade your database, you may want test it with your original SQL statements and see whether Oracle has improved and make your original SQL at least performing like your SQL with hints.

For Quest SQL Optimizer; we use Hints and SQL rewrite to explore most of possible query plans that the optimizer can generate for your SQL statement, by physically test run all alternative SQL statements, you will get the best query plan and corresponding SQL rewrite (may has hints embedded) syntax, which means that you will get the best query plan among all possible query plan that can generate by database optimizer, unless the future data distribution is changed dramatically, such as the ratio of tables’ size are reversed, normally, you don’t need to re-optimize the SQL again.

Explore the potential power of your database optimizer
I have been ask this question “Whether we should use Hints to tune SQL statements” many times, if you use Quest SQL Optimizer (or you are sure you are using the best one) and the number of SQL is small say less than 20(or the number you can manage) SQL, I will tell you to go ahead to do it, since it is the safe way to explore the potential power that a database optimizer can give, why sacrifice from bad performance for something that can be fixed or even may not happen in future, it is like when you are in love with a woman very much and you want to get married with her, I don’t think you will worry about that might be a chance of divorce in future and don’t get married with her.