Re: Oracle Hint Behavior

On Jan 30, 9:28 am, "Dereck L. Dietz" <diet...@xxxxxxxxxxxxx> wrote:
Oracle 10.2.0.3.0
Windows 2003 Server

What is the behavior of hints in Oracle 10g?  I knew that prior to 10g
Oracle would treat them as only "suggestions" but I thought in 10g they
would be mostly followed.

We have a query which is doing a full table scan even with hints being used
to try to get it to use an index.  I can see why it would choose a full
table scan considering the percentage of the table being returned but I'd
like to be able to explain why the hint is being ignored now.

Thanks.

Hints are directives to the optimizer and if valid will be followed;
however, there are operations on the SQL such as query transformation
that can render what is syntaxically a valid hint invalid. There are
also optimizer decisions that are made prior to the hint being
considered that can render the hint invalid such as the choice to hash
join.

Multiple hints are often necessary to try to force a specific plan and
you may need to disable the pushing of predicates or sub-queries
(hints available) to get the plan you want to test.

To get help for your specific SQL you should post the SQL and the
explain plan. Otherwise all anyone can provide is general cases.

HTH -- Mark D Powell --




.

Relevant Pages

  • Re: Hints in Practice
    ... and do not conform to any standard. ... Thus you don't use array processing, don't use exception handling, don't use PL/SQL packages, and heaven-forbid don't use any type of index other ... then embrace Oracle. ... If they are confident about the optimizer, why are hints necessary? ...
    (comp.databases.oracle.server)
  • Re: Hints in Practice
    ... Are there any other Oracle features do what RAC does? ... hints if you want to force Oracle's CBO to use a specific plan. ... Those cases would be when the optimizer is not doing its job. ...
    (comp.databases.oracle.server)
  • Re: Help with sql query speed. Explain plan included
    ... Oracle 10.2.0.3 running on Windows Vista. ... 10.2.0.3, at least on this platform, seems to ignore ... Speaking of hints you just reminded me that in the current issue of ...
    (comp.databases.oracle.server)
  • Re: obvious bugs with 10.2.0.2 and aix5L
    ... consultants might be over looking that could cause high cpu usage with ... Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - ... erp doesn't have any hints that I have ever seen in the sql that is ... I have seen some recursive sql that have hints but that is ...
    (comp.databases.oracle.server)
  • Re: Hints in Practice
    ... The analogy holds. ... Are there any other Oracle features do what RAC does? ... hints if you want to force Oracle's CBO to use a specific plan. ... Those cases would be when the optimizer is not doing its job. ...
    (comp.databases.oracle.server)