This discussion is archived
3 Replies Latest reply: Jul 24, 2011 5:04 AM by user3206995 RSS

Parallel degree of parallelism (DOP) equals the number of partitions or sub

user3206995 Newbie
Currently Being Moderated
hi all


I wanted to parallel update a nonpartitioned table . I thought this is in Oracle 11g possible, however the statement runs sequential:

ALTER SESSION ENABLE PARALLEL DML;

UPDATE /*+ PARALLEL (B, 8) */ test b
SET
b.address_1 = NULL,
b.address_2 = NULL
WHERE address_1 IS NOT NULL
OR address_2 IS NOT NULL

UPDATE STATEMENT Cost = 1418 16 MRows 397 MB--ALL_ROWS
UPDATE of 'test'
PX COORDINATOR
PX SEND (QC (RANDOM)) of 'SYS.:TQ10000' 16 MRows 397 MB [P->S]
PX BLOCK (ITERATOR) 16 MRows 397 MB [PCWC]
TABLE ACCESS (FULL) of 'test' (TABLE) 16 MRows 397 MB [PCWP]{B@UPD$1}--ANALYZED



1.

Is this because of the following:
For tables that do not have the parallel DML itl invariant property (tables created before Oracle Database release 9.2 or tables that were created with the COMPATIBLE initialization parameter set to less than 9.2), the degree of parallelism (DOP) equals the number of partitions or subpartitions. That means that, if the table is not partitioned, the query runs in serial. To see what tables do not have this property, issue the following statement:

SELECT u.name, o.name FROM obj$ o, tab$ t, user$ u
WHERE o.obj# = t.obj# AND o.owner# = u.user#
AND bitand(t.property,536870912) != 536870912;


2.

will recreating the table really help this and does the PCTFREE and INITRANS play a part


3.

If my DOP is 6 does it help to recreate my table with initrans set to a value of 6 and above?

Thanks in advance

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points