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

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2 - Release: 10.1 to 11.2
Information in this document applies to any platform.

Symptoms

***Checked for relevance on 27-Jan-2011***

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms