Avoid compound hints for better Hint Reporting in 19c

Franck Pachot
Apr 26, 2019 · 4 min read

Even if the syntax accepts it, it is not a good idea to write a hint like:

Image for post
Image for post

/*+ USE_NL(A B) */ with multiple aliases (‘tablespec’) even if it is documented.

One reason is that it is misleading. How many people think that this tells the optimizer to use a Nested Loop between A and B? That’s wrong. This hint just declares that Nested Loop should be used if possible when joining from any table to A, and for joining from any table to B.

Actually, this is a syntax shortcut for: /*+ USE_NL(A) USE_NL(B) */

The other reason is that the 19c Hint Reporting will not tell you wich join was possible or not. Here is an example:

SQL> create table demo0 as select 1 x from dual;
Table DEMO0 created.

Here, my hint was used to join to DEMO2 but not to join to DEMO1 because the optimizer didn’t choose a plan with DEMO1 as the inner table. This is reported by the 19c dbms_xplan: I have two lines, one for the used hint and one for the Unused one. But both mention the same hint because I used a compound syntax.

I have more detail in the PLAN_TABLE.OTHER_XML, with the state ‘NU’ but still mentioning the full hint with the two alias names:

SQL> select cast(extract(xmltype(other_xml),'//hint_usage/q/t/h') as varchar2(4000)) from plan_table where other_xml like '%hint_usage%';

Now, running the same query with the two USE_HASH hints, one for each alias:

SQL> explain plan for
select /*+ USE_HASH(demo1) USE_HASH(demo2) */ *
from demo1 join demo2 using(id);

Everything is clear now: the hint on DEMO1 was not used but the hint on DEMO2 was used.

SQL> select cast(extract(xmltype(other_xml),'//hint_usage/q/t/h') as varchar2(4000)) from plan_table where other_xml like '%hint_usage%';

Having the detail for each join hinted is also useful to get the reason. For example, if I hint with an inexistent alias, like /*+ USE_HASH(demo1) USE_HASH(demoX) */, I have two different reasons — ‘Unused’ and ‘uNresolved’:

Total hints for statement: 2 (U - Unused (1), N - Unresolved (1))

1 - SEL$58A6D7F6
N - USE_HASH(demoX)

2 - SEL$58A6D7F6 / DEMO1@SEL$1
U - USE_HASH(demo1)

With one compound hint, I will not know which ’N’ and which is ‘U’

I’ll show this with the two internal states of ‘Unused’ by adding an additional cartesian join with DEMO0:

SQL> explain plan for
select /*+ USE_HASH(demo0) USE_HASH(demo1) USE_HASH(demo2) */ *
from demo0 cross join demo1 join demo2 using(id);

The USE_HASH(demo0) is impossible because, as above, it is the first outer table. It has the internal unused state of ‘NU’. The USE_HASH(demo1) is a possible join but not used because the join type (cartesian) is incompatible with the hash join. The unusable state for it is ‘EU’. If you have any guess about those names (‘NU’/’EU’), or any comment, don’t forget my Twitter:

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store