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?
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.
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 🙂 :
Trying again, just to make certain that the syntax of the undocumented hint is correct:
Just to be complete, reversing the order of the hints, placing the undocumented FAST hint last:
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?).
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:
That hint does not look right. Why?
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:
The results follow:
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?:
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:
Note that in the above, the optimizer saw the USE_HASH hint (even though a nested loops join was the result).