Invalid Hints are Silently Ignored? An Invalid USE_HASH Hint Transforms a Sort-Merge Join into a Nested Loops Join

30 09 2011

September 30, 2011

How many times have you heard or read that the Oracle Database query optimizer may freely ignore hints if it so chooses?  How many times have you heard or read that the query optimizer silently ignores hints that are invalid?  I was recently reminded of both of these concepts while reading the book “Oracle Database 11g Performance Tuning Recipes“.

Page 494 of the book states:

“Tip: Hints with incorrect or improper syntax are ignored by the optimizer.”

Page 496 of the book states:

“Note: Hints influence the optimizer, but the optimizer may still choose to ignore any hints specified in the query.”

Hints with incorrect syntax may cause the optimizer to consider the hints as invalid, but the changed outcome is potentially much worse (or better) than the hint being simply ignored, as demonstrated in this blog article.  As described in another blog article, hints are directives and must be obeyed unless…

I thought that I would craft an interesting, yet simple test case to see if invalid hints are silently ignored.  Here is the test case script:

SET AUTOTRACE OFF

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;

CREATE TABLE T1 AS
SELECT
  ROWNUM RN
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

CREATE TABLE T2 AS
SELECT
  ROWNUM RN
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL,TABNAME=>'T1')
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL,TABNAME=>'T2')

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'USE_HASH_10053';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;

SELECT /*+ USE_HASH(T1 T2) */
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;

SET AUTOTRACE OFF

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

The following shows the output that I received with Oracle Database 11.2.0.2:

SQL> SELECT
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;

Execution Plan
----------------------------------------------------------
Plan hash value: 412793182

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   1 |  MERGE JOIN         |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   2 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))
       filter(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))

SQL> SELECT /*+ USE_HASH(T1 T2) */
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;

Execution Plan
----------------------------------------------------------
Plan hash value: 1967407726

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  4950 | 29700 |   144   (5)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |  4950 | 29700 |   144   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |    50 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."RN">"T2"."RN")

So, the USE_HASH hint which is invalid because a hash join cannot be used when the join condition is an inequality, causes the optimizer to switch from a sort-merge join to a nested loops join.  Does this mean that invalid hints are potentially NOT silently ignored?


Actions

Information

4 responses

30 09 2011
Nigel Thomas

Arguably, the USE_HASH hint is not invalid – just inapplicable. An invalid hint would be something like /*+ GO_FASTER */. But your test suggests it may be misnamed – perhaps it should have been NO_USE_SORT_MERGE? That’s the trouble with the CBO – what looks like a binary choice in 1990 becomes much more complex by 2011.

30 09 2011
Charles Hooper

Nigel, that is an interesting perspective.

Here is a little more fun (explained in the links at the top of this article). The undocumented FAST hint :-) :

SELECT /*+ FAST=TRUE USE_HASH(T1 T2) */
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;
SQL> SELECT /*+ FAST=TRUE USE_HASH(T1 T2) */
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 412793182
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   1 |  MERGE JOIN         |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   2 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))
       filter(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))

Trying again, just to make certain that the syntax of the undocumented hint is correct:

SELECT /*+ FAST(TRUE) USE_HASH(T1 T2) */
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;
 
SQL> SELECT /*+ FAST(TRUE) USE_HASH(T1 T2) */
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 412793182
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   1 |  MERGE JOIN         |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   2 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))
       filter(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))
30 09 2011
Charles Hooper

Just to be complete, reversing the order of the hints, placing the undocumented FAST hint last:

SELECT /*+ USE_HASH(T1 T2) FAST(TRUE) */
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;
SQL> SELECT /*+ USE_HASH(T1 T2) FAST(TRUE) */
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1967407726
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  4950 | 29700 |   144   (5)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |  4950 | 29700 |   144   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |    50 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."RN">"T2"."RN")

To the casual observer, the order of the hints potentially matters. However, for those who read the articles at the start of this blog article, it is quite clear why the execution plan reverted back from the sort-merge join (well, almost… why was that 10053 trace enabled?).

30 09 2011
Charles Hooper

Here is another fun experiment. Page 502 of the book (that was mentioned at the beginning of this article) showed how to use the OPTIMIZER_FEATURES_ENABLE hint:

SELECT /*+ optimizer_features_enable('10.2') */ *
FROM EMP JOIN DEPT USING(DEPTNO);

That hint does not look right. Why?

SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2';
ERROR:
ORA-00096: invalid value 10.2 for parameter optimizer_features_enable, must be
from among 11.2.0.2.1, 11.2.0.2, 11.2.0.1, 11.1.0.7, 11.1.0.6, 10.2.0.5,
10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1, 10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0,
9.2.0.8, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7,
8.0.6, 8.0.5, 8.0.4, 8.0.3, 8.0.0

OK, so changing the parameter directly to 10.2 does not work. What happens when it is attempted in a hint? Here is a test script to see what happens:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'USE_HASH2_10053';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2') USE_HASH(T1 T2) */
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
 
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'USE_HASH3_10053';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
 
SELECT /*+ USE_HASH(T1 T2) OPTIMIZER_FEATURES_ENABLE('10.2') */
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
 
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'USE_HASH4_10053';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
 
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.4') USE_HASH(T1 T2) */
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

The results follow:

SQL> SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2') USE_HASH(T1 T2) */
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 412793182

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   1 |  MERGE JOIN         |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   2 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))
       filter(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))

Note that in the above, the error in the OPTIMIZER_FEATURES_ENABLE hint caused the optimizer to apparently not see the USE_HASH hint.

So, what happens when we reverse the order of the hints?:

SQL> SELECT /*+ USE_HASH(T1 T2) OPTIMIZER_FEATURES_ENABLE('10.2') */
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1967407726
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  4950 | 29700 |   144   (5)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |  4950 | 29700 |   144   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |    50 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."RN">"T2"."RN")

Note in the above that the optimizer saw the USE_HASH hint (even though a nested loops join was the result).

Now for the final part of the output, a properly specified OPTIMIZER_FEATURES_ENABLE:

SQL> SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.4') USE_HASH(T1 T2) */
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1967407726
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   500 |  3000 |   144   (5)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |   500 |  3000 |   144   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     5 |    15 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."RN">"T2"."RN")

Note that in the above, the optimizer saw the USE_HASH hint (even though a nested loops join was the result).

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 )

Google+ photo

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

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: