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!
Mark Hoxey 