Parallel facepalm

If you are used to parallel execution in Oracle, you’ve definitely read through their documentation on the matter ones or twice (http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#i1006712) … I did. And in that in mind I was bashing my head against this simple query:

UPDATE /*+ parallel(d) */ my_table d SET d.flag = 'A';

Table is partitioned by range with 38 partitions. So there is nice opportunity to run this DML in parallel from read to write. So I’ve set up the environment …

ALTER SESSION ENABLE PARALLEL DML;

Checked execution plan and …

----------------------------------------------- | Id  | Operation             | Name          | ----------------------------------------------- |   0 | UPDATE STATEMENT      |               | |   1 |  UPDATE               | PBVADAVKY_VZP | |   2 |   PX COORDINATOR      |               | |   3 |    PX SEND QC (RANDOM)| :TQ10000      | |   4 |     PX BLOCK ITERATOR |               | |   5 |      TABLE ACCESS FULL| MY_TABLE     | -----------------------------------------------

Ok, how about forcing it.

ALTER SESSION FORCE PARALLEL DML PARALLEL 4;

----------------------------------------------- | Id  | Operation             | Name          | ----------------------------------------------- |   0 | UPDATE STATEMENT      |               | |   1 |  UPDATE               | PBVADAVKY_VZP | |   2 |   PX COORDINATOR      |               | |   3 |    PX SEND QC (RANDOM)| :TQ10000      | |   4 |     PX BLOCK ITERATOR |               | |   5 |      TABLE ACCESS FULL| MY_TABLE     | -----------------------------------------------

Ok …. We’ll …. After 1-2 hours …. Long story short:

SELECT trigger_name, trigger_type from DBA_TRIGGERS WHERE table_name='MY_TABLE';

TRIGGER_NAME                   TRIGGER_TYPE ------------------------------ ---------------- MY_TABLE_UPD              BEFORE EACH ROW MY_TABLE_DEL              BEFORE EACH ROW MY_TABLE_INS              BEFORE EACH ROW

So remember folks, you should remember your data model ... and If you don’t …. have a proper look first (http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#BEICCEJJ).