Oracle GoldenGate Parallel Integrated Replicat – DML rearrangement in transactions

While preparing my presentation for POUG 2018 conference I have found some different behavior in Parallel Intgrated Replicat. This is a new Replicat type available from version 12.3. According to Oracle internal tests Parallel Replicat can be up to 5 times faster than Integrated Replicat in the same test case. I have done a number of tests with various options but one thing struck me. Every time the Replicat rearranges the order of DML operations. And there is no way to turn it off.

The Parallel Replicat comes in 2 forms:

• Parallel Integrated Replicat
• Parallel Nonintegrated Replicat

The issue I am describing here is observed only in the first one. Actually it is observed only in this kind of Replicat. Not in any other Replicat type.

TL;DR:

And later comes the full description.

1. Environment

As usual, first the environment for testing:

a) OGG 12.3.0.1.4, Linux x86_64

b) Oracle Database 12.2 + RU 12.2.0.1.180717, Linux x86_64

At the time of writing this post these are the latest versions. Since Parallel Integrated Replicat does not support Oracle 12.1 database – I have not tested this configuration with Oracle 12.1.

2. Configuration

If anybody wants to reproduce this issue –  this is the full configuration that I have tested:

2.1. Oracle Database instance – O122A

Server configuration is:

ALTER SYSTEM SET MEMORY_TARGET=3G SCOPE=SPFILE;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM SET STREAMS_POOL_SIZE=1G SCOPE=BOTH;

Table space and users configuration:

CREATE TABLESPACE ADAM DATAFILE SIZE 10M AUTOEXTEND ON NEXT 10M BLOCKSIZE 8192;
CREATE TABLESPACE ANNA DATAFILE SIZE 10M AUTOEXTEND ON NEXT 10M BLOCKSIZE 8192;
CREATE TABLESPACE GGADMIN DATAFILE SIZE 10M AUTOEXTEND ON NEXT 10M BLOCKSIZE 8192;

CREATE USER ADAM IDENTIFIED BY xxx DEFAULT TABLESPACE "ADAM" TEMPORARY TABLESPACE "TEMP";
ALTER USER ADAM QUOTA UNLIMITED ON ADAM;
GRANT CONNECT TO ADAM;
GRANT RESOURCE TO ADAM;

CREATE USER ANNA IDENTIFIED BY xxx DEFAULT TABLESPACE "ANNA" TEMPORARY TABLESPACE "TEMP";
ALTER USER ANNA QUOTA UNLIMITED ON ANNA;
GRANT CONNECT TO ANNA;
GRANT RESOURCE TO ANNA;

CREATE USER GGADMIN IDENTIFIED BY xxx DEFAULT TABLESPACE "GGADMIN" TEMPORARY TABLESPACE "TEMP";
ALTER USER GGADMIN QUOTA UNLIMITED ON GGADMIN;
GRANT CONNECT TO GGADMIN;
GRANT RESOURCE TO GGADMIN;
GRANT SELECT ANY dictionary TO GGADMIN;
ALTER SYSTEM SET enable_goldengate_replication = true scope = both;
exec dbms_goldengate_auth.grant_admin_privilege('GGADMIN');

Schemas:

CREATE TABLE adam.TAB0(
  pk0 INT NOT NULL
);
ALTER TABLE adam.TAB0 ADD CONSTRAINT TAB0pk PRIMARY KEY(pk0);

CREATE TABLE adam.TAB1(
  pk1 INT NOT NULL,
  val INT NOT NULL
);
ALTER TABLE adam.TAB1 ADD CONSTRAINT TAB1pk PRIMARY KEY(pk1);

CREATE TABLE anna.TAB0(
  pk0 INT NOT NULL
);
ALTER TABLE anna.TAB0 ADD CONSTRAINT TAB0pk PRIMARY KEY(pk0);
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.TAB0 TO GGADMIN;

CREATE TABLE anna.TAB1(
  pk1 INT NOT NULL,
  val INT NOT NULL
);
ALTER TABLE anna.TAB1 ADD CONSTRAINT TAB1pk PRIMARY KEY(pk1);
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.TAB1 TO GGADMIN;

2.2. OGG configuration:

Parameter files:

cat dirprm/mgr.prm

PORT 5659
DYNAMICPORTLIST 5650-5658

cat GLOBALS

GGSCHEMA GGADMIN
CHECKPOINTTABLE GGADMIN.CKPTAB

cat dirprm/extr.prm

EXTRACT extr
USERID ggadmin@O122A, PASSWORD xxx
EXTTRAIL dirdat/ea
TABLE adam.TAB0;
TABLE adam.TAB1;

cat dirprm/repl.prm

REPLICAT repl
USERID ggadmin@O122A, PASSWORD xxx
MAP adam.TAB0, TARGET anna.TAB0;
MAP adam.TAB1, TARGET anna.TAB1;

Process creation:

START MGR
DBLOGIN USERID GGADMIN@O122A, PASSWORD xxx
ADD EXTRACT extr, INTEGRATED TRANLOG, BEGIN NOW
REGISTER EXTRACT extr DATABASE

ADD EXTTRAIL dirdat/ea, EXTRACT extr
ADD TRANDATA adam.TAB0
ADD TRANDATA adam.TAB1
START EXTRACT extr

ADD CHECKPOINTTABLE
ADD REPLICAT repl, PARALLEL INTEGRATED, EXTTRAIL dirdat/ea, CHECKPOINTTABLE ggadmin.CKPTAB
START REPLICAT repl

To make a brief summary: I replicate 2 tables from O122A instance to O122A instance. They are:

• adam.TAB0 -> anna.TAB0
• adam.TAB1 -> anna.TAB1

3. Test

The test scenario is also very simple:

INSERT INTO adam.TAB1 values (0, 0);
COMMIT;
INSERT INTO ADAM.tab0 VALUES (1);
UPDATE ADAM.tab1 set VAL = 1;
COMMIT;

4. Results

Since the Parallel Replicat uses Streams API and turns off the triggers it is not so easy to show the real order of DML. Let’s try two approaches:

4.1. Logminer dump

I have dumped the contents Redo Log using Logminer and the results are:

7.31.462 insert into "ANNA"."TAB1"("PK1","VAL") values ('0','0');
9.31.492 update "ANNA"."TAB1" set "VAL" = '1' where "VAL" = '0' and ROWID = 'AAAFnlAAFAAAACkAAA';
9.31.492 insert into "ANNA"."TAB0"("PK0") values ('1')

The UPDATE of TAB1 is executed BEFORE the INSERT to TAB0 (!)

4.2. 10046 trace

Let’s run the test again with the 10046 trace to check what is executed by the Replicat processes.

The trace contains:

INSERT /*+ restrict_all_ref_cons */ INTO "ANNA"."TAB1" ("PK1","VAL") VALUES (:1 ,:2 )
UPDATE /*+ restrict_all_ref_cons */ "ANNA"."TAB1" SET "VAL"=:1 WHERE "PK1"=:2 
INSERT /*+ restrict_all_ref_cons */ INTO "ANNA"."TAB0" ("PK0") VALUES (:1 )

I am not mistaken… the UPDATE is really executed BEFORE the INSERT.

5. Further investigation

I have checked all parameters that might influence this behavior with all possible configurations (total 288 test cases).

The parameters I have tested are:

• DBOPTIONS INTEGRATEDPARAMS (COMMIT_SERIALIZATION FULL)
• DBOPTIONS INTEGRATEDPARAMS (COMMIT_SERIALIZATION DEPENDENT_TRANSACTIONS)
• DBOPTIONS INTEGRATEDPARAMS (BATCHSQL_MODE DEPENDENT)
• DBOPTIONS INTEGRATEDPARAMS (BATCHSQL_MODE DEPENDENT_EAGER)
• DBOPTIONS INTEGRATEDPARAMS (BATCHSQL_MODE SEQUENTIAL)
• BATCHSQL
• SPLIT_TRANS_REC xxx
• LOOK_AHEAD_TRANSACTIONS xxx
• APPLY_PARALLELISM 1
• MAP_PARALLELISM 1

Still in every possible test scenario the order of DMLs is changed compared to the order of DML in the source transaction. None of all the options above can change this behavior.

This is the first time I have seen this behavior – it was not present in all other Replicat types (including Parallel Nonintegrated Replicat). First I thought that it might be the BATCHSQL option turned on by default. But this is not the case.

6. Conculsions

I do not know any more details about this behavior of rearranging DMLs by the Parallel Integrated Replicat. I do not know when it appears and in which conditions. They appeared in the first test I have ever made. For me it is very hard to make any kind of analysis if the Replicat behaves like this. I could not find a word about this behavior in the manual. This DML rearrangement is not present in any other Replicat type (Classic, Coordinated, Integrated, Parallel Nonintegrated). It appears only In Parallel Integrated Replicat.

I have created an SR for Oracle (3-18049467561). The only answer I have received is to run some diagnostic scripts. I don’t have enough patience and I think I will close it. The Support team has received the reproduction script to run this test. If anybody has enough willingness they work on their own now.  I suppose that the are Engineering already aware of this issue and it will be corrected in a next version.

Nothing more to say at this moment. If you are interested in more details about Parallel Integrated Replicat – please visit my session at POUG 2018.