SQL & PL/SQL

Reasons not to use hints - data is changed, the optimizer can choose the better plan.
Reasons to use hints - data is changed, the optimizer can choose worse plan.

Well, it is supposed that the optimizer, using the statistics for the tables, will provide the best solution. And, if the data distribution changes over time, then the optimizer may do better than in case you have a hint in the code, which may work well for a while, and later on may become detrimental.
Yet, in order to have the optimizer do the right thing, you must have statistics on tables computed often enough as not to become stale.
Although, I've encountered situations when using no hints the optimizer did not do great, while a couple of hints helped very much. If the business rules stay the same and the data distribution stays the same, then hints may help even when you happen to have some stale statistics.
There are also some other things to take into consideration. Like: is it a database used mainly for OLTP or for OLAP? How do you have the cursor sharing parameter?

Hi Peter,
i think, it depends. If YOU know what YOU are doing and how your application works and especially how your data looks like, then there is nothing against the hint. But can you judge all this for tomorrow and for next week ?
I think the advice not to use hints is aimed at people who just try some hint and see that it currently helps. And that can be the completely wrong way in the next moment.
We run SAP, and you can discover a few hints here and there in the source text, across all database systems.
I would say that the hint is not forbidden per se, but sometimes it shows unforeseen results.
regards
Kay

Thanks for all the answers, it makes sense. We do have to be careful and think for the future too.
In this particular case I can be 100% sure that this table will only grow and there will be never ever a case when the number of records would justify using a nested loop, regardless of future releases, data changes etc.
In this case using the hint has sped up the query from 45 mins - 1 hour to <1 minute.

Did you check that statistics were not stale? If statistcs are stale then run dbms_stats.gather_table_stats for the table(s) used in your query that hppen to have stale statistics. If a table is partitioned and has stale statistics only for some partitions, then gather statistics only for those tables. After having made sure that there are no stale statistics for the tables in your query, try running the query without any hint and see how that performs. It may happen that the first time you get poor performance, but the next N times you get good performance. If the performance is poor, then you may try some hints and see if those help.
Then, do you have indexes? An index might help in some situations. Does that query have bind variables? I found out that some times in queries in PL/SQL it's better not to have bind variables, but rather run dynamic SQL because the optimizer may then see if it's worth using an index or performing a full table scan based on the actual value used for filtering on an indexed column.

Yes, the table stats are fresh and also on the index that is being used. Didn't help.
I tried the query without any hint, it still has bad estimates. There are no bind variables and the query only uses tables, no views and there are no composite parts of the join.
This is the query (table and column names are masked):

select /*+ use_hash(ckm acc ca) */
  ckm.tab_c_key,
  count(distinct ca.tab_a_id) as cnt_tab_a_id,
  count(distinct acc.tab_b_id) as cnt_tab_b_id
from tab_c_key_master ckm
join tab_bs acc
  on acc.tab_c_key = ckm.tab_c_key
join tab_a ca
  on ca.tab_b_id = acc.tab_b_id
  and ca.Status in ('NLF','A')
where ckm.tab_c_KEY in (select tab_c_key from tab_d where tab_c_key is not null)
group by ckm.tab_c_key;
tab_a

tab_a has 10M+ rows, the Optimizer thinks there will be 3 in this query. When I added the use_hash hint, the estimate has gone back to 10M.

I have read in many places that "hints are not advised" (just like you have), but in my opinion that's the wrong way to put it.
Better: "hints should be the last thing you try, not the first." (Other than, as BluShadow explained already, using hints to help you diagnose problems; after you have the right diagnostic, try to cure the problems, rather than just applying a band-aid, which is what a hint really is.)
Apart from that, there are several situations where hints are in fact the best tool, and some such situations that can be described in advance.
One is a case where you know something about your data, while the optimizer doesn't (there is no way to communicate it). Things beyond "statistics" and "histograms". Often such additional information is relevant when the query can take advantage of that knowledge. You know the data, you know what your query does, you know how the query should take advantage of the specific details of your data, but there's no way for the optimizer to do that for you. Then you use a hint. (Warning: while such situations do exist, very often programmers think that's their case when in fact it isn't.)
Another situation is where you already know what choice the optimizer makes, and you simply know that the choice is wrong. The only way to force the optimizer out of its choice is with a hint. Here is an example:
In version 10 Oracle implemented hash aggregation. In older versions it sorted in order to group - which is significantly slower on large data. However, if a query has both GROUP BY (list of expressions) and ORDER BY (same list of expressions), the optimizer, even in current versions, will always choose "sort group by" (presumably on the basis of needing to order by those same expressions in the end anyway). This is just idiotic; when you process 30 million rows, group them, get 10,000 groups, then you must order the groups, it is much faster to "hash group by" (on the 30 million rows) and then order 10,000 result rows from scratch, than to order all 30 million input rows. Yet there is no way to force the optimizer to use the correct approach on its own - you do need to hint "hash aggregation" explicitly.

Yet there is no way to force the optimizer to use the correct approach on its own - you do need to hint "hash aggregation" explicitly.
The reason to fix the plan is not the answer to the question. The plan can be fixed without explicit hint in code.

The documentation says why not to use hints.
Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.
Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.
Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.
I prefer to use hints directly in the code because when i modify the query, i see it, test it and change hints too. SQL Profile or SQL plan baseline may be silently ignored.

tab_a has 10M+ rows, the Optimizer thinks there will be 3 in this query. When I added the use_hash hint, the estimate has gone back to 10M.
I got to the question you asked about the monitor screen for this query before I got to this question on hints so I've already pointed out that the optimizer does NOT think there will be 3 rows in the table, it thinks that when it executes a nested loop with this table as the inner (second) table then each index-driven probe will return 3 rows.
If the stats about the OTHER tables were better then the optimizer might get a better plan, though there may be some other difficulties relating to index definitions, correlation between columns in different tables, non-standard parameter settings, etc. that make the choice of plan fragile.

The main reason for a general advisory against hints is that most people don't know they do and don't know how to use them safely -- even in fairly trivial cases.

Regards
Jonathan Lewis

@user-h3j7u
The reason to fix the plan is not the answer to the question. The plan can be fixed without explicit hint in code.
The example that @mathguy gave was correct. You can't get Oracle to use hash aggregation in the example he gave without hinting. If you try to rewrite the code to avoid use hash aggregation in a way that doesn't need an explicit use_hash_aggregation() hint you will have to layer the code to sort an aggregate inline view (or factored subquery) - but if you try that you'll find that you have to use the no_merge() hint to stop Oracle merging the inline view upwards and falling back to hash aggregation. It's a defect in the optmizer that's been there since 10g.
Regards
Jonathan Lewis

The example that mathguy gave was correct. You can't get Oracle to use hash aggregation in the example he gave without hinting.
I can use the hash aggregation without explicitly hinting!

create table t1 as select level n, mod(level-1, 10) m from dual connect by level<=100;
-- method one
explain plan for select m, count(*) from t1 group by m order by m+0;
select * from dbms_xplan.display();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2808104874
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    10 |    30 |     5  (40)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |    10 |    30 |     5  (40)| 00:00:01 |
|   2 |   HASH GROUP BY     |      |    10 |    30 |     5  (40)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

-- method two
... DBMS_SPM ...

explain plan for select m, count(*) from t1 group by m order by m;
select * from dbms_xplan.display();

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2808104874
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    10 |    30 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |    10 |    30 |     4  (25)| 00:00:01 |
|   2 |   HASH GROUP BY     |      |    10 |    30 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - SQL plan baseline "SQL_PLAN_4n7tu1u7pxdz3bfe4f9fc" used for this statement

I can use the hash aggregation without explicitly hinting!

First, try the same with a varchar2 column for grouping and ordering and see what happens. If you concatenate null, you will find that the optimizer doesn't use hash aggregation; instead, it figures out correctly that concatenating null doesn't change the expression, so it eliminates it from the query before taking other steps (such as generating plans and evaluating them). And, also, this is not one of the few cases where Oracle treats empty string different from null; if you concatenate empty string, you get the same behavior (no hash aggregation).
Then you may think of other silly things - for example, concatenate a character (then make sure it's prepended, not added at the end - so that null is still coming last); but if some of your strings are already at the max length, that will break the whole thing. Or, worse, none of your strings are at max length now, but that will change in the future. [EDIT: Actually the whole thing doesn't work anyway - null won't come last with this method regardless - but that doesn't change the overall philosophy of what I was saying.]
This is exactly the kind of reason I object very strongly to "modifying the query" for the purpose of getting a desired execution plan. That's backwards. If the query is perfectly fine, but the plan generated by the optimizer isn't, then "changing the query" will be the absolute last thing I will consider - certainly only after concluding that optimizer hints aren't enough.
You may feel that changing the expression m to m+0 like you did is harmless. I respectfully but strongly disagree. That solution seems much worse to me than the use of a hint.

Very cute. I'll have to let you claim victory with the first one. But I will point out that there are already some cases where Oracle transforms the +0 out of existence and some people might say that a hack like that shouldn't go into production anyway (for exactly the "one day the RU/RUR will come" reason).
Using a baseline is just hinting, of course, just an off-code way of doing it and significantly less likely to fail. That's why I promoted the "if you can hint it, baseline it" mantra, because once you've got lucky once the full set of hints from the outline information will almost certainly reproduce the plan. (Now changed to "if you can hint it patch it" because SQL Patches don't carry the penalty that baselines do.)
Regards
Jonathan Lewis