Invalid Hint in 10g Can Cause Other Hints To Be Ignored

Invalid Hint in 10g Can Cause Other Hints To Be Ignored, Including Parallel Hints (Doc ID 826893.1)

 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2
This problem can occur on any platform.

Symptoms

After upgading a database from 9i to 10g, an insert statement was taking many more hours to complete under 10g than it had in 9i.
After obtaining explain plans from both 9i and 10g, it was found that without modification, the same insert statement ran in with parallel DML in 9i, but serially in 10g:

insert /*+ nologging parallel(fid2,2) */ 
-- comment 1
into fid2 (select * from fid1);

The 9i execution plan showed the following:

------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| 
Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |    11 |   319 |     3   (0)| 
|   1 |  PX COORDINATOR          |          |       |       |            |    
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |    11 |   319 |     3   (0)| 
|   3 |    LOAD AS SELECT        | FID2     |       |       |            |               |
|   4 |     BUFFER SORT          |          |       |       |            |    
|   5 |      PX RECEIVE          |          |    11 |   319 |     3   (0)|
|   6 |       PX SEND ROUND-ROBIN| :TQ10000 |    11 |   319 |     3   (0)|  
|   7 |        TABLE ACCESS FULL | FID1     |    11 |   319 |     3   (0)| 
------------------------------------------------------------------------------
------------------------------------

The execution plan for the same insert statement showed the following in 10g:

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |      |    11 |   319 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| FID1 |    11 |   319 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Changes

Upgrade from 9i to 10g (patchset level is insignificant to this issue).

Cause

Bug 8432870 - NOLOGGING HINT IN 10G CAUSES HINTS AFTER IT TO BE IGNORED was filed for this issue and determined it as "not a bug."   Hint parsing was revisited in 10gR1.  

1. When an invalid hint is a valid SQL keyword (such as NOLOGGING, LOGGING, SELECT, FROM, WHERE, etc.) it is ignored,  but causes the rest of the hints within the /*+   */ delimiters to also be ignored; the invalid hint in this case is recognized as a valid, but misplaced, language construct.
2. When an invalid hint is not a valid SQL keyword, it is ignored, but does not affect any of the other hints within the /*+   */ delimiters.

Solution

As this is not a bug, but rather intended behavior in 10gR1 and higher versions, the only solution is to remove the invalid hint that is also a valid SQL keyword.   In general, it is best practice to remove all invalid hints.