Oracle 19c – Hint Reports with DBMS_XPLAN

With Oracle 19c, we got a feature which was most awaited one.
It tries to answer, Whether hint is getting applied, its valid and getting used or not with DBMS_Xplan package.

Initially, we used to get 10053 trace to understand whether hint is getting applied or not?
Most of the developer don’t have access to enabling trace files or getting it from Trace dump directories.
With new feature of Hint Report within DBMS_XPLAN packages, leverage developer confidence on any hint applied.

For all Demo, we have use https://livesql.oracle.com

select * from v$version;
BANNER
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

lets create table for our demo,

DROP TABLE TAB_TEST_HINT;
CREATE TABLE TAB_TEST_HINT
AS
(
SELECT ROWNUM AS COL1 , LPAD('A',ROWNUM, 'B') AS COL2, RPAD('C',ROWNUM,'D') AS COL3 FROM DUAL CONNECT BY ROWNUM < 101
);

lets start with adding a hint which is not valid.
We will apply INDEX hint with TAB_TEST_HINT not having any index created on it.

EXPLAIN PLAN FOR
SELECT /*+ INDEX(ALIAS1) */ *
FROM TAB_TEST_HINT ALIAS1;

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

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / ALIAS1@SEL$1
U - INDEX(ALIAS1)

It highlight the hint which was detected as part of SQL statement as

“Total hints for statement: 1 “

later marked it as Unused, as we have not created any index on table or HINT it not valid as per SQL.
(U – Unused (1))

Now lets have a hint which is valid and will get applied.
we will consider FULL hint.

EXPLAIN PLAN FOR
SELECT /*+ INDEX(ALIAS1) */ *
FROM TAB_TEST_HINT ALIAS1;

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

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$1 / ALIAS1@SEL$1
- FULL(ALIAS1)

As FULL hint is valid, it is applied and mark as USED. its a great way to confirm applied hint command is accepted.
As hint is never a Request or an option for optimizer to check, if it is valid it is going to get applied.

Now lets try to check a scenario, when hint is incorrect.
we will intentionally passed “FULL2” as hint, which is not a legit hint.

EXPLAIN PLAN FOR
SELECT /*+ FULL2(ALIAS1) */ *
FROM TAB_TEST_HINT ALIAS1;

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

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - FULL2

it is treated as Syntax error, as we don’t have any hint as FULL2.

now, let try to add a hint which is invalid and other one is not used.
/*+ INDEX(ALIAS1) FULL2(ALIAS1) */

INDEX hint can’t be used as Table does not have any indexes and FULL2 is not a legit hint.
EXPLAIN PLAN FOR
SELECT /*+ INDEX(ALIAS1) FULL2(ALIAS1) */ *
FROM TAB_TEST_HINT ALIAS1;

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

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1), E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - FULL2
1 - SEL$1 / ALIAS1@SEL$1
U - INDEX(ALIAS1)

It identified both the hints and mark it as per its category.
Unused (1) for INDEX(ALIAS1) and E – Syntax error (1) for FULL2(ALIAS1).

please note, if initials hint itself is syntactically incorrect it won’t processed remaining hints.

EXPLAIN PLAN FOR
SELECT /*+ FULL2(ALIAS1) INDEX(ALIAS1) */ *
FROM TAB_TEST_HINT ALIAS1;

SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - FULL2

New Feature of Oracle 19c – Hint report, on itself is a great asset for developer to verify and test hints applied on SQL.

Advertisements

About Deepak Mahto

Having 10 Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal, Oracle Performance and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s