Oracle 19c — Generating Reports for Hints Usage (HINT_REPORT)

When multiple hints are used in a statement (especially in a complex query), we may want a way to ensure that Oracle has honored those hints, or generally, to find out which hints were ignored and how many were actually used.

A simple and new feature introduced in Oracle 19c allows us to generate a report while reviewing the execution plan, showing whether the optimizer obeyed the hints provided in the SQL statement. See the following example.

Example: To demonstrate this feature, we first create a table and insert some data into it:

create table mytbl(id number,name varchar2(100),last_name varchar2(100));

Table created.

insert into mytbl values(1,’VAHID’,’USEFZADEH’);

1 row(s) inserted.

Commit;

Statement processed.

We intend to use both the parallel hint and the FULL hint when reading the mytbl table. Then, by reviewing the execution plan, we check whether these hints are actually used.

explain plan for select /*+FULL(a) paralell(10)*/ * from mytbl a;

Statement processed.

SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>’BASIC +HINT_REPORT‘));

In the output of the above command, it is visible that both hints included in the SQL text were used:

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

Total hints for statement: 2

Now, to better understand the specialized terminology in this report, let’s use some other hints.

Suppose we mistakenly typed “paralell” instead of “parallel” in the statement. Additionally, we use index and use_nl hints. What changes will we see in the new report?

explain plan for select /*+FULL(a) use_nl(a) use_nl(mm) index(a) paralell(10)*/ * from mytbl a;

Statement processed.

SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>’BASIC +HINT_REPORT‘));

As shown in the output, due to a syntax error, the paralell hint was not used. Moreover, there is a conflict between the FULL and INDEX hints. Since the statement does not involve any join operations, the use of use_nl(a) is meaningless and ignored. Likewise, use_nl(mm) is fundamentally irrelevant in this context:

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

Total hints for statement: 5 (U – Unused (3), N – Unresolved (1), E – Syntax error (1))

—————————————————————————————

   1 –  SEL$1

         N –  use_nl(mm)

         E –  paralell

   1 –  SEL$1 / A@SEL$1

         U –  FULL(a) / hint conflicts with another in sibling query block

         U –  index(a) / hint conflicts with another in sibling query block

         U –  use_nl(a)©