Oracle 19c New Feature - Hint Usage Reporting

Hint Usage Report is a new feature from 19c which helps to determine from the execution plan output which are all the hints are used by the Optimizer and not used by the Optimizer. In releases before Oracle Database 19c, it could be difficult to determine why the optimizer did not use hints. The hint usage report solves this problem. Oracle Database includes a hint usage reporting mechanism from 19c that reports whether hints are used during plan generation or not. In some cases, the report explains why a hint was not used or ignored, for example, a syntax error or conflict between hints. You can generate hint usage reports with the standard DBMS_XPLAN display functions. This is also useful in diagnosing plan reproducibility issues with SPM baselines or SQL profiles to identify which hint(s) are not getting used.

The most common reasons for ignoring hints are as follows:

  • Syntax errors - This happens with the wrong syntax of hints. For Example, /*+ FULLL(a) */. If multiple hints appear in the same hint block, and if one hint has a syntax error, then the optimizer honors all hints before the hint with an error and ignores hints that appear afterward.
  • Unresolved hints - An unresolved hint is invalid for a reason other than a syntax error. For example, a statement specifies INDEX(employees emp_idx), where emp_idx is not a valid index name for table employees.
  • Conflicting hints - The database ignores combinations of conflicting hints, even if these hints are correctly specified. For example, a statement specifies FULL(employees) INDEX(employees), but an index scan and full table scan are mutually exclusive. In most cases, the optimizer ignores both conflicting hints.
  • Hints affected by transformations - A transformation can make some hints invalid. For example, a statement specifies PUSH_PRED(some_view) MERGE(some_view). When some_view merges into its containing query block, the optimizer cannot apply the PUSH_PRED hint because some_view is unavailable after the view merge transformation happened successfully.

Hint tracking is enabled by default. You can access the hint usage report by using the following DBMS_XPLAN functions:

  • DISPLAY
  • DISPLAY_CURSOR
  • DISPLAY_WORKLOAD_REPOSITORY
  • DISPLAY_SQL_PLAN_BASELINE
  • DISPLAY_SQLSET

The above functions generate a report when you specify the value HINT_REPORT in the format parameter. The value TYPICAL displays only the hints that are not used in the final plan, whereas the value ALL displays both used and unused hints. Any of the following formats can be used.

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'TYPICAL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'BASIC +HINT_REPORT'));

Example

In the below, different hints are used in the SQL statement and the hint report shows whether the hints are used or ignored. It also tells the reason for not getting used.


SQL> select /*+ INDEX(e emp_idx) PUSH_PRED(dept) FULL(@sel$2 l) PARALLEL(2) OPT_PARAM('_simple_view_merging','false') */ e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code from employees e,(select /*+ FULL(l) FUL(d) */ d.department_id, d.department_name, l.street_address, l.postal_code from departments d, locations l where d.location_id = l.location_id) dept_locs_v where dept_locs_v.department_id = e.department_id and e.last_name = 'XXXXX';

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));

SQL_ID  cvs4cpka7w3dz, child number 0
-------------------------------------
select /*+ INDEX(e emp_idx) PUSH_PRED(dept) FULL(@sel$2 l) PARALLEL(2)
OPT_PARAM('_simple_view_merging','false') */ e.first_name, e.last_name,
dept_locs_v.street_address, dept_locs_v.postal_code from employees e,
   (select /*+ FULL(l) FUL(d) */ d.department_id, d.department_name,
l.street_address, l.postal_code       from departments d, locations l
    where d.location_id = l.location_id) dept_locs_v where
dept_locs_v.department_id = e.department_id and e.last_name = 'XXXXX'

Plan hash value: 3889688035

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |       |       |     7 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000    |     1 |    61 |     7  (15)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |             |     1 |    61 |     7  (15)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     JOIN FILTER CREATE  | :BF0000     |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  5 |      TABLE ACCESS FULL  | EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     VIEW                |             |    27 |  1161 |     5  (20)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |      HASH JOIN          |             |    27 |  1026 |     5  (20)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       PX BLOCK ITERATOR |             |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  9 |        TABLE ACCESS FULL| LOCATIONS   |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       JOIN FILTER USE   | :BF0000     |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 11 |        TABLE ACCESS FULL| DEPARTMENTS |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   5 - SEL$1 / E@SEL$1
   6 - SEL$2 / DEPT_LOCS_V@SEL$1
   7 - SEL$2
   9 - SEL$2 / L@SEL$2
  11 - SEL$2 / D@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - filter("E"."LAST_NAME"='XXXXX')
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
   9 - access(:Z>=:Z AND :Z<=:Z)
  11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"D"."DEPARTMENT_ID"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   2 - (#keys=0) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   3 - (#keys=1; rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   4 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   5 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   6 - (rowset=256) "DEPT_LOCS_V"."DEPARTMENT_ID"[NUMBER,22], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12]
   7 - (#keys=1; rowset=256) "L"."POSTAL_CODE"[VARCHAR2,12], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "D"."DEPARTMENT_ID"[NUMBER,22]
   8 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
   9 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
  10 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]
  11 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):

Total hints for statement: 7 (U - Unused (2), N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------------------

   0 -  STATEMENT
           -  PARALLEL(2)
           -  opt_param('_simple_view_merging','false')

   1 -  SEL$1
         N -  PUSH_PRED(dept)

   5 -  SEL$1 / E@SEL$1
         U -  INDEX(e emp_idx) / index specified in the hint doesn't exist

   7 -  SEL$2
         E -  FUL

   9 -  SEL$2 / L@SEL$2
         U -  FULL(l) / hint overridden by another in parent query block
           -  FULL(@sel$2 l)

Note
-----
   - Degree of Parallelism is 2 because of hint

97 rows selected.

In the above hint report, Optimizer considers PARALLEL(2), OPT_PARAM(’_simple_view_merging’,‘false’) & FULL(@sel$2 l) hints only. The hint INDEX(e emp_idx) is unused because the specified index does not exist. The hint FULL(l) is unused because a similar hint is given in the parent query block and so the query in the subquery block is overridden. There is a syntax error with FUL hint and so not considered. The hint PUSH_PRED(dept) is unresolved there is no any view with name “dept”.

Related Posts

Latest Posts

  • How to Create Index Partitions To Ranged Partitioned Table
  • How to Move Partition Online in Oracle 12c
  • How to add or modify Printer settings in CentOS/RHEL using GUI
  • What is machine-id in Linux