First of all I'd like to point out this article presents my personal opinions on hints, which may not be proved accurately. I strived to present arguments supporting my point of view, but they are still my personal options and you may not agree with them.

Positive aspects of hints

Let's look at the positive side of hints first. The hints may be used to provide the database with additional informationto, allowing it to choose a better execution plan. Hints documented in Oracle may be classified as follows:

• hints determining optimization goals, i.e. if the total throughput (ALL_ROWS) or rather a faster response (FIRST_ROWS) is desirable
• hints prescribing access path to a given table, i.e. if the table should be read sequentially (FULL) or if  an index should be used and how (INDEX, NO_INDEX, INDEX_ASC, INDEX_DESC, INDEX_FFS, ...)
• hints prescribing order (LEADING, ORDERED) and method of joining tables (USE_NL, USE_MERGE, USE_HASH, ...)
• hints describing parallel execution of queries (PARALLEL, PARALLEL_INDEX, ...)

Thanks to all those hints you may "slip" additional information to the database about the purpose of the query, how to parallelize it etc. It's important to understand a database uses just estimates of the statistics when planning an execution of a query, and that it may not choose the best plan - in case of stale statistics or statistics that are not precise enough the chosen plan may be actually quite bad. And hints are one of the options how to fight this issue.

Sure, you may argue that the database should collect all necessary statistics (who else should do that, anyway), and then choose the best plan using those collected statistics, but in practice it does not work like that. It's almost impossible to collect all thinkable statistics (about tables, all column combinations, etc.) as it would consume too large part of the performance.

What's more, the database simply can't get some of the information related to the "outer world" and the database has to either guess or rely on guesses made by administrators. An example of such information are data about hardware and operating system. E.g. evaluating a "price" of random vs. sequential reads from a drive may be quite a challenge, as each system behaves a little bit differently. There are several ways to connect the drives (EIDE, SATA, SAS, SCSI, FC, ...), they may be either traditional or SSD, the may use different rpm values (from 5400rpm to 15000rpm). There are different RAID levels (0, 1, 10, 01, 5, ...), different hardware and software RAID solutions, there are many producers of controllers and each of them has several product lines with various cache sizes, ... Simply said, the basic features are quite easy to guess, but for good planning a precise estimate is necessary.

If an administrator or a database itself chooses a really bad estimate, it's usually soon noticed as the database performs poorly. But if the estimate is neither precise enough nor "way off", the result may be ironically much worse, as the database will choose the best execution plan in most cases. If the differences between actual execution plan costs are large, or if the execution plans are not very different, then small errors in estimates will not cause choice of a very bad execution plan.

In some cases (especially in case of complex queries where the inaccuracies may accumulate) a bad execution plan may be chosen, and the performance of the query is then poor. You may ask the administrator to modify the settings (which is not something he'd like to do on the production system, especially if there are other databases and no one else complains), or use a hint to poke the database into the right direction.

Which leads us to the second possible usage of hints - it's unquestionable that each complex software system contains bugs or does behave unexpectedly in some cased, and Oracle is not an exception. And hints are one of the options how to solve these issues. I have described an example of such unexpected behaviour in this SQL puzzler and it's solution using a materialize hint.

Negative aspects of hints

There are negative effects of hints too - I'd like to name at least the following two.

The first problem is that by using hints, you're hard-coding information about the data and its statistical features, or maybe rather a developer's idea of such features, into the application (as the SQL queries are part of the sources). The problem with this is that the developer usually works with information available at the development phase (e.g. assumes that a particular table is small and will remain small in the future), but this may change quite unexpectedly in the production phase (e.g. the small table may grow quite fast). But the information hard-coded into the SQL queries can't reflect these dynamic changes, which usually results in serious problems (with performance).

This especially applies to hints prescribing access paths to a table, order and method of joining the tables, etc. But, to a certain extent, this applies to hints determining optimization goals (ALL_ROWS and FIRST_ROWS) too - that is you may encapsulate a query into a method of a DAO object, but what will prevent the developers from using it in a way that does not fit the chosen optimization goal?

Another serious problem with hints is that they may constitute "a development brake" at the application or database side. What do I mean by that?

Let's say you are developing a database application, and you've just found out a part of the application is poorly designed (e.g. the decomposition was not done properly), which causes serious performance problems. From a long-term perspective the only proper solution would be a refactoring, but let's say another development finds out a hint may be used as a temporary solution. Well, it's not perfect, but it is working, more or less. Will you ever do the refactoring? Most probably not, since it is working and there is always something more important.

With the database it works quite similarly - maybe not really serious bugs, but in case of some unexpected side-effects it works quite the same way. Is there a problem? Yes, there is. Is there a suitable workaround? Yes, we can use a hint. Shall we solve it, then? No ...

Links

• http://www.dba-oracle.com/art_hints_views.htm - problems with links and views
• Using optimizer hints - Oracle 11g (10g)
• Oracle SQL Hints Tuning (Mike Ault)
• The undocumented Oracle SQL materialize Hint (Don Burleson)
• Improving performance with Result Cache in Oracle 11g (Thiru)