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
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 |