Avoid compound hints for better Hint Reporting in 19c
Even if the syntax accepts it, it is not a good idea to write a hint like:
/*+ 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.SQL> create table demo1 as select 1 id from dual;
Table DEMO1 created.SQL> create table demo2 as select 1 id from dual;
Table DEMO2 created.SQL> explain plan for
select /*+ USE_HASH(demo1 demo2) */ *
from demo1 join demo2 using(id);Explained.SQL> select * from dbms_xplan.display(format=>'basic +rows +hint_report');PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 3212315601
--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | HASH JOIN | | 1 |
| 2 | TABLE ACCESS FULL| DEMO1 | 1 |
| 3 | TABLE ACCESS FULL| DEMO2 | 1 |
--------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object
Alias):Total hints for statement: 2 (U - Unused (1))
--------------------------------------------------------------------2 - SEL$58A6D7F6 / DEMO1@SEL$1
U - USE_HASH(demo1 demo2)
3 - SEL$58A6D7F6 / DEMO2@SEL$1
- USE_HASH(demo1 demo2)
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%';CAST(EXTRACT(XMLTYPE(OTHER_XML),'//HINT_USAGE/Q/T/H')ASVARCHAR2(4000
--------------------------------------------------------------------
<h o="EM"><x><![CDATA[USE_HASH(demo1 demo2)]]></x></h>
<h o="EM" st="NU"><x><![CDATA[USE_HASH(demo1 demo2)]]></x></h>
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);Explained.SQL> select * from dbms_xplan.display(format=>'basic +rows +hint_report');PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 3212315601
--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | HASH JOIN | | 1 |
| 2 | TABLE ACCESS FULL| DEMO1 | 1 |
| 3 | TABLE ACCESS FULL| DEMO2 | 1 |
--------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
--------------------------------------------------------------------
2 - SEL$58A6D7F6 / DEMO1@SEL$1
U - USE_HASH(demo1)
3 - SEL$58A6D7F6 / DEMO2@SEL$1
- USE_HASH(demo2)
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%';CAST(EXTRACT(XMLTYPE(OTHER_XML),'//HINT_USAGE/Q/T/H')ASVARCHAR2(4000))
--------------------------------------------------------------------
<h o="EM"><x><![CDATA[USE_HASH(demo2)]]></x></h>
<h o="EM" st="NU"><x><![CDATA[USE_HASH(demo1)]]></x></h>
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);Explained.SQL> select * from dbms_xplan.display(format=>'basic +rows +hint_report');PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 61862555
-----------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | HASH JOIN | | 1 |
| 2 | MERGE JOIN CARTESIAN| | 1 |
| 3 | TABLE ACCESS FULL | DEMO0 | 1 |
| 4 | BUFFER SORT | | 1 |
| 5 | TABLE ACCESS FULL | DEMO1 | 1 |
| 6 | TABLE ACCESS FULL | DEMO2 | 1 |
-----------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
--------------------------------------------------------------------
3 - SEL$9E43CB6E / DEMO0@SEL$1
U - USE_HASH(demo0)
5 - SEL$9E43CB6E / DEMO1@SEL$1
U - USE_HASH(demo1)
6 - SEL$9E43CB6E / DEMO2@SEL$2
- USE_HASH(demo2)SQL> select cast(extract(xmltype(other_xml),'//hint_usage/q/t/h') as varchar2(4000)) from plan_table where other_xml like '%hint_usage%';CAST(EXTRACT(XMLTYPE(OTHER_XML),'//HINT_USAGE/Q/T/H')ASVARCHAR2(4000))
--------------------------------------------------------------------
<h o="EM"><x><![CDATA[USE_HASH(demo2)]]></x></h>
<h o="EM" st="EU"><x><![CDATA[USE_HASH(demo1)]]></x></h>
<h o="EM" st="NU"><x><![CDATA[USE_HASH(demo0)]]></x></h>
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:
