No Intra-partition Parallelism (PDML) When Bitmap Indexes Are Present; May Result in Serial Load (Doc ID 1251546.1)

Last updated on FEBRUARY 01, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

Symptoms

It is a fairly-well-known restriction that Parallel DML (PDML) cannot occur on non-partitioned tables that have a bitmap index.   This is not a bug per <Bug 8234341> : PARALLEL INSERT RUNS IN SERIAL WHEN BITMAP INDEX PRESENT.

A lesser-known restriction on PDML is that only one slave is assigned to each non-composite partition if there is a bitmap index present on the partitioned table.   Should a load insert into only one partition, the effect will be a serial load.  This also is not a bug, but rather the optimized distribution when there is a bitmap index present on a partitioned table.

Compare the tkprof of a load into a partitioned table without a bitmap index to the tkprof of a load into a partitioned table with a bitmap index:

WITHOUT BITMAP INDEX

INSERT /*+ APPEND comment_aug19 */ INTO TARGET (DT) SELECT DT FROM SOURCE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.03 45.00 0 19 32 132206848
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 45.01 0 20 32 132206848

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 325

Rows Row Source Operation
------- ---------------------------------------------------
9 PX COORDINATOR (cr=19 pr=0 pw=0 time=1123500 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)
0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)
0 TABLE ACCESS FULL SOURCE (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)



Elapsed times include waiting on following events: (0-sec events removed)
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message from client 2 16.49 22.76
PX Deq: Parse Reply 9 0.00 0.01
name-service call wait 1 0.09 0.09
PX Deq: Execute Reply 159 2.00 44.84
********************************************************************************



WITH BITMAP INDEX


INSERT /*+ APPEND comment_aug19_withbitmap */ INTO TARGET (DT) SELECT DT FROM
SOURCE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 1 0 0
Execute 1 0.06 488.18 0 19 2 132206848
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.07 488.19 0 20 2 132206848

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 325

Rows Row Source Operation
------- ---------------------------------------------------
1 PX COORDINATOR (cr=19 pr=0 pw=0 time=0 us)
0 PX SEND QC (RANDOM) :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)
0 INDEX MAINTENANCE TARGET (cr=0 pr=0 pw=0 time=0 us)(object id 0)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)
0 PX SEND RANGE :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)
0 LOAD AS SELECT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)
0 PX SEND PARTITION (KEY) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)
0 TABLE ACCESS FULL SOURCE (cr=0 pr=0 pw=0 time=0 us cost=18423 size=1253776239 card=139308471)


Elapsed times include waiting on following events: (0 sec waits removed)
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
os thread startup 18 0.21 2.09
PX Deq Credit: send blkd 29 0.00 0.01
PX Deq: Parse Reply 17 0.01 0.02
name-service call wait 2 0.06 0.06
PX Deq: Execute Reply 489 2.00 485.84
PX Deq: Signal ACK RSG 8 0.01 0.01
PX Deq: Signal ACK RSG 8 0.01 0.01
SQL*Net message from client 1 104.25 104.25


Output from v$pq_tqstat for the load with the bitmap index shows multiple producer slaves, but only 1 consumer slave.

DFO_NUMBER TQ_ID SERVER_TYP NUM_ROWS
---------- ---------- ---------- ----------
1 0 Producer 2032006
1 0 Producer 2015501
1 0 Producer 2042916
1 0 Producer 2008075
1 0 Producer 2003318
1 0 Producer 2011721
1 0 Producer 2017011
1 0 Producer 2021239
1 0 Producer 2141269
1 0 Consumer 18293056 <---

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