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
Update August 2021 – New items in 21.3
Hint id larger than number of union groups
ORDER_KEY_VECTOR_USE with less than two IDs
ORDER_SUBQ referenced query block name, which cannot be found
Same table referenced in both lists
Oracle Scratchpad 
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 - XYZwhich 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 |
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 |
[…] Hinting errors (Jan 2021) – there are many reasons why your hints might be wrong; this is a list (probably incomplete) of possible reasons that I extracted from the Oracle executable. […]
Pingback by “Ignoring Hints” Catalogue | Oracle Scratchpad — February 21, 2022 @ 9:44 am GMT Feb 21,2022 |