DBMS_XPLAN hint report

After a few years working with SQL Server I’ve recently been doing some performance improvements on an Oracle system running 19c. After clearing out the Oracle cobwebs in my mind I set about tuning some queries and started to look at execution plans. It wasn’t long before I noticed additional information in the output of dbms_xplan that I hadn’t seen before; the hint report section.

Previously, one of the problems with applying hints was that the database was silent on whether a hint was being applied or not. It may be that the hint is not correctly specified or it may not be a valid option for the query. Now the hint report section of the explain plan provides information on hints and why they are not being applied.

Let’s take a quick look at what dbms_xplan shows us, using an Oracle XE 21c database. We’ll start with a simple table with no data:

create table t
    (id   number(6) not null);

exec dbms_stats.gather_table_stats ('','t')

Next we’ll generate the execution plan for a query that specifies an index access for the table, which is invalid as the table has no indexes:

explain plan
for
select /*+ index(t) */
       *
from   t;

select t.*
from   table(dbms_xplan.display()) t;

We get the following output from dbms_xplan:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------

   1 -  SEL$1 / "T"@"SEL$1"
         U -  index(t)

The bottom section of the output shows that there is one hint that is being ignored by the database.

Specifying a table reference that doesn’t exist generates an “unresolved” warning:

explain plan
for
select /*+ index(x) */
       *
from   t;

select t.*
from   table(dbms_xplan.display()) t;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------

   1 -  SEL$1
         N -  index(x)

If we try something that’s completely invalid, which might happen if our hint contains a typo, then we get a syntax error:

explain plan
for
select /*+ no_such_hint(t) */
       *
from   t;

select t.*
from   table(dbms_xplan.display()) t;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  no_such_hint

I’m not sure the specific version of Oracle the hint report appeared in the dbms_xplan listing. I don’t recall it in the versions of 12c I worked with a few years ago but it’s present in 19c. It’s a very useful addition!

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 )

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