Oracle 19c Hint Usage reporting

SQL> select /*+ FULL(DUAL) INDEX(BLABLABLA) BLABLABLA(DUAL) */ * from dual;
DUMMY
-----
X
SQL> 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 dual
Plan 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
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 dual
Plan 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 - BLABLABLA
1 - SEL$1 / DUAL@SEL$1
- FULL(DUAL)
  • +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
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>
  • ‘@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
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)
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 blocks
1 - SEL$F5BB74E1
U - qb_name(one) / same QB_NAME hints for different query blocks
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
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)
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
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 exist
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
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_hints
1 - SEL$1 / DEPT@SEL$1
U - index(DEPT) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
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
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

--

--

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

Franck Pachot

502 Followers

Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member