Oracle Scratchpad

January 20, 2021

Hint Errors

Filed under: Execution plans,Hints,Oracle — Jonathan Lewis @ 11:06 am GMT Jan 20,2021

This is a list of possible explanations of errors that you might see in the Hint Report section of an execution plan. It’s just a list of the strings extracted from a chunk of the 19.3 executable around the area where I found something I knew could be reported, so it may have some errors and omissions – but there are plenty of things there that might give you some idea why (in earlier versions of Oracle) you might have seen Oracle “ignoring” a hint:

internally generated hint is being cleared
hint conflicts with another in sibling query block
hint overridden by another in parent query block
conflicting optimizer mode hints
duplicate hint
all join methods are excluded by hints
index specified in the hint doesn't exist
index specified in hint cannot be parallelized
incorrect number of indexes for AND_EQUAL
partition view set up
FULL hint is same as INDEX_FFS for IOT
access path is not supported for IOT
hint on view cannot be pushed into view
hint is discarded during view merging
duplicate tables in multi-table hint
conditions failed for array vector read
same QB_NAME hints for different query blocks
rejected by IGNORE_OPTIM_EMBEDDED_HINTS
specified number must be positive integer
specified number must be positive number
specified number must be >= 0 and <= 1
hint is only valid for serial SQL
hint is only valid for slave SQL
hint is only valid for dyn. samp. query
hint is only valid for update join ix qry
opt_estimate() without value list
opt_estimate() with conflicting values spec
hint overridden by NO_QUERY_TRANSFORMATION
hinted query block name is too long
hinted bitmap tree wasn't fully resolved
bitmap tree specified was invalid
Result cache feature is not enabled
Hint is valid only for select queries
Hint is not valid for this query block
Hint cannot be honored
Pred reorder hint has semantic error
WITH_PLSQL used in a nested query
ORDER_SUBQ with less than two subqueries
conflicting OPT_PARAM hints
conflicting optimizer_feature_enable hints
because of _optimizer_ignore_parallel_hints
conflicting JSON_LENGTH hints

2 Comments »

  1. Hi Jonathan,

    I’ve just recalled also “E – Syntax error”:

    select/*+ XYZ */ from dual;
    
    Total hints for statement: 1 (E - Syntax error (1))
    ---------------------------------------------------------------------------
    
       1 -  SEL$1
             E -  XYZ
    

    which is element “h” with st=”PE” in v$sql_plan.other_xml `/other_xml/hint_usage/q/h`.

    And we can find all queries with such problems using a query like this:

    select
       sql_id, x.*
    from v$sql_plan p
        ,xmltable(
           '/other_xml/hint_usage/q/h[@st eq "PE"]'
           passing xmltype(p.other_xml)
           columns x xmltype path '.'
        ) x
    where id=1 and other_xml like '%hint_usage%';
    

    other errors: UR – Unresolved, NU – Unused.

    or all hints with errors:

    select
       sql_id, id, x.*
    from v$sql_plan p
        ,xmltable(
           '/other_xml/hint_usage/q/h[@st]'
           passing xmltype(p.other_xml)
           columns x xmltype path '.'
        ) x
    where id=1 and other_xml like '%hint_usage%';
    


    Best regards,
    Sayan Malakshinov

    Comment by Sayan Malakshinov — January 20, 2021 @ 12:09 pm GMT Jan 20,2021 | Reply

    • Sayan,

      Thanks for the comment.

      That type of code could be very useful to run occasionally to get some idea of how many pieces of code had hints that weren’t doing what the developer expected – which could mean SQL that’s doing the right thing “by accident” and will someday do the wrong thing.

      One drawback, of course, is that some people (myself included) to add comment text to (the end of) hints that tags the SQL and would be reported as having a syntax error.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — January 24, 2021 @ 11:23 am GMT Jan 24,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.