I'm speaker at DOAG 2015

Facepalm is comming to DOAG 2015! :)

I'm happy to announce that I'll be presenting "Think simple and spare yourself a facepalm" session at developer track at DOAG 2015. Looking forward to meet you there!

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).

ORA-00600: internal error code [kkmupsViewDestFro_4]

After an upgrade to Oracle 11.2.0.4 (from 11.2.0.3), one of our batch jobs in test environment starting crushing on following error:

SQL Error: ORA-00600: internal error code [kkmupsViewDestFro_4], [0], [8024169], [], [], [], [], [], [], [], [], []
00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
               exceptions. This indicates that a process has encountered an
               exceptional condition.
*Action:   Report as a bug - the first argument is the internal error number

SQL had nothing special in it:

MERGE INTO TABLE1 a USING
   (SELECT d.*
   FROM TABLE2 d,
            TABLE3 t
    WHERE d.cola   = t.cola
    AND d.colb   = t.colb
   ) b ON (a.col1 = b.col1 AND a.col2 = b.col2)
WHEN MATCHED THEN
  UPDATE
  SET ….
  WHERE a.SCN <= b.SCN
WHEN NOT MATCHED THEN
  INSERT
    (
      …
    )
    VALUES
    (
      …
    )

I searched through Oracle My Support and found article ORA-600 [kkmupsViewDestFro_4] During Merge Statement (Doc ID 1181833.1): 

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=534519724153934&id=1181833.1&_adf.ctrl-state=b2we448s9_695

One of the solution was to upgrade to Oracle 11.2.0.2 … Hmmmm … well, that didn’t work out :)

So I tried next one:

alter session set "_optimizer_join_elimination_enabled"=false;

Nope … and next one:

alter session set "_fix_control"="7679164:OFF";

Nope … and next one … recode the MERGE statement, for example add ROWNUM pseudo column:

MERGE INTO TABLE1 a USING
   (SELECT d.*, ROWNUM
   FROM TABLE2 d,
            TABLE3 t
    WHERE d.cola   = t.cola
    AND d.colb   = t.colb
   ) b ON (a.col1 = b.col1 AND a.col2 = b.col2)
WHEN MATCHED THEN
  UPDATE
  SET ….
  WHERE a.SCN <= b.SCN
WHEN NOT MATCHED THEN
  INSERT
    (
      …
    )
    VALUES
    (
      …
    )

And voilà … it’s working!

I still don’t understand why we got the error in 1st place … should have been fixed in 11.2.0.2