Parallel Hint Ignored With Large Number Of Columns In Select List (Doc ID 1267953.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.
Reviewed 11-Dec-2014.

Symptoms

The parallel hint ignores with large numbers of columns in select list, for example:

select /*+ parallel ("DUAL",4) */
1,2,3,4,5,6,7,8,9,0,
1,2,3,4,5,6,7,8,9,0,
[...]
[...] <--- Y same row
from dual
where dummy='X'
union
select /*+ parallel ("DUAL",4) */
1,2,3,4,5,6,7,8,9,0,
1,2,3,4,5,6,7,8,9,0,
[...]
[...] <--- Y same row
from dual
where dummy='X'
/

with Y=1000 it works
with Y=2000 it does not use parallelism

Basically, using about 2000 rows, then the parallelism doesn't work.

Using 1000 rows:

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 6 (67)| | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 2 | 4 | 6 (67)| Q1,01 | P->S | QC (RAND) |
| 3 | SORT UNIQUE | | 2 | 4 | 6 (67)| Q1,01 | PCWP | |
| 4 | PX RECEIVE | | | | | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | | | | Q1,00 | P->P | HASH |
| 6 | UNION-ALL | | | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 1 | 2 | 2 (0)| Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 1 | 2 | 2 (0)| Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------


Using 2000 rows:

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 6 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 4 | 6 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

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