Oracle 19c Hint Usage reporting
One reason why we try to avoid hints in our queries is that it is very difficult to use correctly. No error is raised when there’s an incorrect syntax or when the hint cannot be used semantically. 19c dbms_xplan has an important enhancement as it can report hint usage, at least for optimizer hints.
By default, DBMS_XPLAN in the default TYPICAL format will report only invalid hints:
SQL> select /*+ FULL(DUAL) INDEX(BLABLABLA) BLABLABLA(DUAL) */ * from dual;
DUMMY
-----
XSQL> select * from dbms_xplan.display_cursor(format=>'-cost');
PLAN_TABLE_OUTPUT
SQL_ID 3ps01tc9mxuhd, child number 0
-------------------------------------
select /*+ FULL(DUAL) INDEX(BLABLABLA) BLABLABLA(DUAL) */ * from dualPlan hash value: 272002086-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 00:00:01 |
-------------------------------------------------------------Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (N - Unresolved (1), E - Syntax error (1))
--------------------------------------------------------------------1 - SEL$1
N - INDEX(BLABLABLA)
E - BLABLABLA
This tells me that for the line Id=1 of the plan, the query block SEL$1 has two hints unused. One because of syntax Error (E) because BLABLABLA is not a hint. The other, INDEX(), is a valid syntax but mentions an alias that is not in the query and then the error is unresolved (N)
We can choose to show also the hints that were correctly used:
SQL> select * from dbms_xplan.display_cursor('3ps01tc9mxuhd',format=>'+HINT_REPORT');
PLAN_TABLE_OUTPUT
SQL_ID 3ps01tc9mxuhd, child number 0
-------------------------------------
select /*+ FULL(DUAL) INDEX(BLABLABLA) BLABLABLA(DUAL) */ * from dualPlan hash value: 272002086--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)|
--------------------------------------------------------------------Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------1 - SEL$1
N - INDEX(BLABLABLA)
E - BLABLABLA1 - SEL$1 / DUAL@SEL$1
- FULL(DUAL)
The FULL(DUAL) is correct and was used. Of course, this hint was not useful because there’s no other access path to DUAL, but that cannot be reported because basically the FULL() hint only tell the optimizer to ignore other access paths. So it was used even if it was not useful.
The DBMS_XPLAN formats are:
- +HINT_REPORT_USED to show used hints
- +HINT_REPORT_UNUSED to show unresolved and syntax errors
this format flag is included in TYPICAL, the default format - +HINT_REPORT combines both of them and is the default with ALL
As an example, the following formats are the same
format=>'ALL -HINT_REPORT_UNUSED'
format=>'BASIC +HINT_REPORT_USED'OTHER_XML
This displayed by all DBMS_XPLAN display functions is available in OTHER_XML from the PLAN_TABLE, V$SQL_PLAN, AWR, STS, SPM,…
SQL> select extract(xmltype(other_xml),'//hint_usage') from v$sql_plan where other_xml like '%hint_usage%' and sql_id='3ps01tc9mxuhd';
EXTRACT(XMLTYPE(OTHER_XML),'//HINT_USAGE')
------------------------------------------
<hint_usage><q><n><![CDATA[SEL$1]]></n><h o="EM" st="PE"><x><![CDATA[BLABLABLA]]></x></h><t><f><![CDATA["DUAL"@"SEL$1"]]></f><h o="EM"><x><![CDATA[FULL(DUAL)]]></x></h></t><t st="UR"><h o="EM"><x><![CDATA[INDEX(BLABLABLA)]]></x></h></t></q></hint_usage>DBMS_XPLAN is the best way to format it as this xml format is not documented. It seems that:
- ‘<n>’ is the query block name (hint scope can statement ‘<s>’, query block ‘<n>’, or alias ‘<f>’)
- ‘@st’ is PE for parsing syntax error (‘E’ in dbms_xplan note)
- ‘@st’ is UR for unresolved (‘N’ in dbms_xplan note)
- ‘@st’ is ‘NU’ or ‘EU’ for unused (‘U’ in dbms_xplan note)
- ‘<x>’ is the hint text
- we might get a reason for unused ones in ‘<r>’
Pre-19c
Before this feature, there was some information in the CBO trace, but very limited. Here is what I have for my statement:
Dumping Hints
=============
atom_hint=(@=0x7fcd2d8aa460 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("BLABLABLA") )
atom_hint=(@=0x7fcd2d8ac008 err=0 resol=1 used=1 token=448 org=1 lvl=3 txt=FULL ("DUAL") )Examples with reason
Here is an example with statement-level hints FIRST_ROWS/ALL_ROWS:
SQL> explain plan for select /*+ first_rows(1) all_rows */ * from SCOTT.DEPT;Explained.SQL> select * from dbms_xplan.display(format=>'basic +hint_report');
PLAN_TABLE_OUTPUT
Plan hash value: 3383998547----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| DEPT |
----------------------------------Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
--------------------------------------------------------------------0 - STATEMENT
U - all_rows / hint conflicts with another in sibling query block
U - first_rows(1) / hint conflicts with another in sibling query block
The message is clear: conflicting hints are all ignored
SQL> explain plan for select /*+ first_rows(1) */ * from (
2 select /*+ all_rows */ * from SCOTT.DEPT
3 ) ;Explained.SQL> select * from dbms_xplan.display(format=>'basic +alias +hint_report');
[...]Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
--------------------------------------------------------------------0 - STATEMENT
U - all_rows / hint overridden by another in parent query block
- first_rows(1)
Here the conflicting statement level hints are in different query blocks, and the report tells me that only the parent one was used
It is recommended to name the query blocks for complex queries. If some name conflicts (in the query or in underlying views) this will be reported:
SQL> explain plan for select /*+ qb_name(one) */ * from (
2 select /*+ qb_name(one) */ * from SCOTT.DEPT
3 ) ;
[...]
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------1 - SEL$F5BB74E1 / DEPT@SEL$2Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
--------------------------------------------------------------------0 - SEL$2
U - qb_name(one) / same QB_NAME hints for different query blocks1 - SEL$F5BB74E1
U - qb_name(one) / same QB_NAME hints for different query blocks
the consequence is that both were ignored.
You get also an error when the name is too long (more than 20 characters):
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
--------------------------------------------------------------------1 - SEL$1
U - qb_name(X12345678901234567890) / hinted query block name is too long
With duplicate hints, one is ignored:
SQL> explain plan for select /*+ full(DEPT) full(DEPT) */ * from SCOTT.DEPT;
[...]
1 - SEL$1 / DEPT@SEL$1
U - full(DEPT) / duplicate hint
- full(DEPT)When I allow no possible join method, all my join hinting are ignored:
SQL> explain plan for select /*+ leading(DEPT) no_use_nl(EMP) no_use_hash(EMP) no_use_merge(EMP) */ * from SCOTT.DEPT join SCOTT.EMP using(DEPTno);
[...]
1 - SEL$58A6D7F6
- leading(DEPT)3 - SEL$58A6D7F6 / EMP@SEL$1
U - no_use_hash(EMP) / all join methods are excluded by hints
U - no_use_merge(EMP) / all join methods are excluded by hints
U - no_use_nl(EMP) / all join methods are excluded by hints
If I mention an index name that does not exist, I see the reason:
SQL> explain plan for select /*+ index(DEPT SYS_AI_M4J1C) */ * from SCOTT.DEPT;
[...]
1 - SEL$1 / DEPT@SEL$1
U - index(DEPT SYS_AI_M4J1C) / index specified in the hint doesn't existThis means that the table/alias DEPT exists but not the index mentioned.
Here is one where I use FULL() for an IOT:
SQL> explain plan for select /*+ full(IOT) */ count(*) from IOT where id=42;
Table created.SQL> explain plan for select /*+ full(IOT) */ count(*) from IOT where id=42;Explained.SQL> select * from dbms_xplan.display(format=>'basic +hint_report');
PLAN_TABLE_OUTPUT
Plan hash value: 3425135035---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX FAST FULL SCAN| SYS_IOT_TOP_73014 |
---------------------------------------------------Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
--------------------------------------------------------------------2 - SEL$1 / IOT@SEL$1
U - full(IOT) / FULL hint is same as INDEX_FFS for IOT
This is not really unused because without a hint the optimizer would have chosen an INDEX UNIQUE SCAN. But the reason explains clearly that INDEX_FFS was substituted as it is the IOT equivalent of FULL
When ignore_optim_embedded_hints hint is present, other hints are ignored:
SQL> explain plan for select /*+ index(DEPT) ignore_optim_embedded_hints */ * from SCOTT.DEPT;[...]Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
--------------------------------------------------------------------0 - STATEMENT
- ignore_optim_embedded_hints1 - SEL$1 / DEPT@SEL$1
U - index(DEPT) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Exactly the same reason is displayed when it is set at session level:
SQL> alter session set optimizer_ignore_hints=true;Session altered.SQL>
SQL> explain plan for select /*+ index(DEPT) */ * from SCOTT.DEPT;[...]Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
--------------------------------------------------------------------1 - SEL$1 / DEPT@SEL$1
U - index(DEPT) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
There is a similar parameter for parallel hints:
SQL> alter session set optimizer_ignore_parallel_hints=true;Session altered.SQL>
SQL> explain plan for select /*+ parallel(DEPT) */ * from SCOTT.DEPT;[...]Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
--------------------------------------------------------------------1 - SEL$1 / DEPT@SEL$1
U - parallel(DEPT) / because of _optimizer_ignore_parallel_hints
Looking at the binaries (I don’t think it is exposed as a V$), here some the possible reasons:
The documentation has many other examples explained:
