When to use hints in oracle query

Question

I have gone through some documentation on the net and using hints is mostly discouraged. I still have doubts about this. Can hints be really useful in production specially when same query is used by hundreds of different customer.

Is hint only useful when we know the number of records that are present in the tables? I am using leading in my query and it gives faster results when the data is very large but the performance is not that great when the records fetched are less.

This answer by David is very good but I would appreciate if someone clarified this in more details.

Answer 1

I couldn't just rephrase that, so I will paste it here (it's a brief explanation as of "When Not To Use Hints", that I had bookmarked):

In summary, don’t use hints when

1. What the hint does is poorly understood, which is of course not limited to the (ab)use of hints;

2. You have not looked at the root cause of bad SQL code and thus not yet tapped into the vast expertise and experience of your DBA in tuning the database;

3. Your statistics are out of date, and you can refresh the statistics more frequently or even fix the statistics to a representative state;

4. You do not intend to check the correctness of the hints in your statements on a regular basis, which means that, when statistics change, the hint may be woefully inadequate;

5. You have no intention of documenting the use of hints anyway.

Source link here.

I can summarize this as: The use of hints is not only a last resort, but also a lack of knowledge on the root cause of the issue. The CBO (Cost Based Optimizer) does an excellent job, if you just ensure some basics for it. Those include:

1. Fresh statistics 1.1. Index statistics 1.2. Table statistics 1.3. Histograms
2. Correct JOIN conditions and INDEX utilization
3. Correct Database settings

This article here is worth reading: Top 10 Reasons for poor Oracle performance

Presented by non other, but Mr. Donald Burleson.

Cheers

Answer 2

In general hints should be used only exceptional, I know following situations where they make sense:

Workaround for Oracle bugs

Example: Once for a SELECT statement I got an error ORA-01795: maximum expression number in list - 1000, although the query did not contain an IN expression at all.

Problem was: The queried table contains more than 1000 (sub-) partitions and Oracle did a transformation of my query. Using the (undocumented) hint NO_EXPAND_TABLE solved the issue.

Datewarehouse application while staging

While staging you can have significant changes on your data where the table/index statistics are not aware about as statistics are gathered only once a week by default. If you know your data structure then hints could be useful as they are faster than running DBMS_STATS.GATHER_TABLE_STATS(...) manually all the time in between your operations. On the other hand you can run DBMS_STATS.GATHER_TABLE_STATS() even for single columns which might be the better approach.

Online Application Upgrade Hints

From Oracle documentation:

The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does not apply for these three hints.

Answer 3

Most hints are a way of communicating our intent to the optimizer. For instance, the leading hint you mention means join tables in this order. Why is this necessary? Often it's because the optimal join order is not obvious, because the query is badly written or the database statistics are inaccurate.

So one use of hints such as leading is to figure out the best execution path, then to figure out why the database doesn't choose that plan without the hint. Does gathering fresh statistics solve the problem? Does rewriting the FROM clause solve the problem? If so, we can remove the hints and deploy the naked SQL.

Some times there are times where we cannot resolve this conundrum, and have to keep the hints in Production. However this should be a rare exception. Oracle have had lots of very clever people working on the Cost-Based Optimizer for many years, so its decisions are usually better than ours.

But there are other hints we would not blink to see in Production. append is often crucial for tuning bulk inserts. driving_site can be vital in tuning distributed queries.

Conversely other hints are almost always abused. Yes parallel, I'm talking about you. Blindly putting /*+ parallel (t23, 16) */ will probably not make your query run sixteen times faster, and not infrequently will result in slower retrieval than a single-threaded execution.

So, in short, there is no universally applicable advice to when we should use hints. The key things are:

1. understand how the database works, and especially how the cost-based optimizer works;
2. understand what each hint does;
3. test hinted queries in a proper tuning environment with Production-equivalent data.

Obviously the best place to start is the Oracle documentation. However, if you feel like spending some money, Jonathan Lewis's book on the Cost-Based Optimizer is the best investment you could make.