Forum Stats

  • 3,875,873 Users
  • 2,266,977 Discussions
  • 7,912,362 Comments

Discussions

Why is using hints not advised?

I keep hearing that using hints is a rabbit hole and "using hints is a dangerous path to go down".

But. An example from a production environment where it's mission critical that a particular query should perform: we have a query that always uses hash join and it should - but from time to time it can happen (let's not talk about the reasons, there can be many that our team has no effect on) that cardinality estimates become way off and the CBO decides to use nested loops which in this case considerably degrades performance.

What's wrong with using a USE_HASH hint to ensure that this query will always use hash join as it should? My understanding is that at worst it will change nothing, at best it will revert the plan to the right one.

Thanks!

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 42,589 Red Diamond
    Answer ✓

    Generally speaking, using optimizer hints for production code is not advised.

    You're last words "at best it will revert the plan to the right one" indicate why. In a database where transactions are taking place, records being added, updated and possibly deleted all the time, the "right one [plan]" is best decided by the CBO in the database as it has the best information about how the data is stored and knows it's own internal ways of accessing that data. By using an optimizer hint, you are saying "I know best how to optimize this query above anything the CBO can do now, or in the future (including when patches and database versions change), regardless of how the data changes over time"... but really? do you know best? can you predict how the optimizer is going to change or be improved in the future and your hint will still be better than that?

    And that's the reason it's not advised.

    Those hints are a great way to actually help identify where the causes of performance issues are in your queries or table/database design so that you can work to improve those and not actually need the hints in production. The fact that your cardinality estimates sometimes go way off.... why is that? You're using hints to circumvent that issue rather than investigate and resolve the issue.

    Of course there are always exceptions or limitations to what we can do, hence why it's advisable not to use such optimizer hints in production, but if you've no other option... well... you have to. However you should always be aware of where those hints are used and be ready to remove them or re-visit those in the future following patches and upgrades, otherwise your hints could actually be detrimental to performance in the long run.

«1

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,589 Red Diamond
    Answer ✓

    Generally speaking, using optimizer hints for production code is not advised.

    You're last words "at best it will revert the plan to the right one" indicate why. In a database where transactions are taking place, records being added, updated and possibly deleted all the time, the "right one [plan]" is best decided by the CBO in the database as it has the best information about how the data is stored and knows it's own internal ways of accessing that data. By using an optimizer hint, you are saying "I know best how to optimize this query above anything the CBO can do now, or in the future (including when patches and database versions change), regardless of how the data changes over time"... but really? do you know best? can you predict how the optimizer is going to change or be improved in the future and your hint will still be better than that?

    And that's the reason it's not advised.

    Those hints are a great way to actually help identify where the causes of performance issues are in your queries or table/database design so that you can work to improve those and not actually need the hints in production. The fact that your cardinality estimates sometimes go way off.... why is that? You're using hints to circumvent that issue rather than investigate and resolve the issue.

    Of course there are always exceptions or limitations to what we can do, hence why it's advisable not to use such optimizer hints in production, but if you've no other option... well... you have to. However you should always be aware of where those hints are used and be ready to remove them or re-visit those in the future following patches and upgrades, otherwise your hints could actually be detrimental to performance in the long run.

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    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.

    Billy VerreynneKayKBEDE
  • BEDE
    BEDE Oracle Developer Member Posts: 2,478 Gold Trophy

    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?

  • KayK
    KayK Member Posts: 1,740 Bronze Crown

    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

    BEDEJonathan Lewis
  • Peter_Elekes-Oracle
    Peter_Elekes-Oracle Member Posts: 9 Employee

    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.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,478 Gold Trophy

    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.

  • Peter_Elekes-Oracle
    Peter_Elekes-Oracle Member Posts: 9 Employee

    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.

  • mathguy
    mathguy Member Posts: 10,918 Black Diamond

    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.

    Peter_Elekes-OracleJonathan Lewis
  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    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.

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    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.