Tag Archives: Batch

Using Event Actions to improve batch performance

All replication technologies typically suffer from one flaw, which is the way that the data is replicated. Consider a table that is populated with a million rows as part of a batch process. This may be a bulk insert operation that Oracle completes on the source database as one transaction. However, Oracle will write each change to its redo logs as Logical Change Records (LCRs). GoldenGate will subsequently mine the logs, write the LCRs to a remote trail, convert each one back to DML, and apply them to the target database, one row at a time. The single source transaction becomes one million transactions, which causes a huge performance overhead. To overcome this issue, we can use GoldenGate’s Event Marker Interface to employ Event Actions that:

• Detect the DML statement (INSERT INTO TABLE SELECT ..)
• Ignore the data resulting from the SELECT part of the statement
• Replicate just the DML statement as an Event Record
• Execute just the DML statement on the target database

The solution requires a statement table on both source and target databases to trigger the event. Also, both databases must be perfectly synchronized to avoid data integrity issues.
In the Extract configuration, the DML statement is passed in the replicated data stream by using the EVENTACTIONS parameter combined with IGNORE, TRANS, and INCLUDEEVENT. The source transaction is ignored, except for the DML statement that is written to the trail file:

TABLE PDB1.SRC.STATEMENT, &
 EVENTACTIONS (IGNORE TRANS INCLUDEEVENT);
 TABLE PDB1.SRC.BATCH;

On the target, the Replicat configuration uses a TABLE statement to pass the Event Record to a SQLEXEC statement that executes the DML statement by calling a stored procedure, as shown in the following code:

SOURCEDEFS ./dirdef/statement.def
 TABLE PDB1.SRC.STATEMENT, SQLEXEC(SPNAME execdml, ID sqlid, &
 PARAMS(P1 = sql_statement)), &
 EVENTACTIONS (IGNORE, LOG INFO);
 DISCARDFILE ./dirrpt/rolap01.dsc, PURGE, MEGABYTES 100
 ASSUMETARGETDEFS
 MAP PDB1.SRC.* TARGET PDB2.TGT.*;

Note the reference to a SOURCEDEFS file. This is necessary to explicitly declare the STATEMENT table definition to the Replicat. The procedure call is very simple because its only task is to execute the DML statement that is being passed by the Event Marker Interface.
This is shown in the following code:

CREATE OR REPLACE PROCEDURE ggadmin.execdml (P1 IN VARCHAR2)
 AS
 BEGIN
 execute immediate(P1);
 END;
 /

Now perform a test transaction by inserting the DML statement into the SQL_STATEMENT column of the SRC.STATEMENT table first. Then, execute the same DML statement on the source database, as shown in the following code:

SQL> conn SRC@PDB1
 Enter password:
 Connected.
 
SQL> INSERT INTO STATEMENT (SQL_STATEMENT) VALUES ('INSERT INTO TGT.BATCH SELECT * FROM ALL_OBJECTS');

1 row created.

SQL> INSERT INTO BATCH SELECT * FROM ALL_OBJECTS;

24372 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from batch;

COUNT(*)
 ----------
 24372

The Extract report shows the runtime statistics. The report file can be generated using the GGSCI SEND and VIEW REPORT commands as follows:

GGSCI (db12server01) 2> send extract EOLTP01, report

Sending REPORT request to EXTRACT EOLTP01 ...
 Request processed.

GGSCI (db12server01) 3> view report EOLTP01
 ..
 *****************************************************************
 * ** Run Time Statistics ** *
 *****************************************************************

Report at 2015-06-07 13:20:44 (activity since 2015-06-07 11:56:04)

Output to ./dirdat/lt:

From Table PDB1.SRC.STATEMENT:
 # inserts: 1
 # updates: 0
 # deletes: 0
 # discards: 0
 From Table PDB1.SRC.BATCH:
 # inserts: 0
 # updates: 0
 # deletes: 0
 # discards: 0
 # ignores: 24372

As expected, the 24372 records inserted into the BATCH table have been ignored by GoldenGate; only the DML statement is replicated to the STATEMENT table in the TGT schema on the target database. This is shown in the runtime information from the Replicat report as a successful Event Action and DML execution.

Note that the statistics show only one transaction and the call to the stored procedure:

 *****************************************************************
 * ** Run Time Statistics ** *
 *****************************************************************
 The last record for the last committed transaction is as follows:
 ___________________________________________________________________
 Trail name : ./dirdat/rt000083
 Hdr-Ind : E (x45) Partition : . (x04)
 UndoFlag : . (x00) BeforeAfter: A (x41)
 RecLength : 90 (x005a) IO Time : 2015-06-07 13:10:29.000465
 IOType : 5 (x05) OrigNode : 255 (xff)
 TransInd : . (x03) FormatType : R (x52)
 SyskeyLen : 0 (x00) Incomplete : . (x00)
 AuditRBA : 180 AuditPos : 6877200
 Continued : N (x00) RecCount : 1 (x01)

2015-06-07 13:10:29.000465 Insert Len 90 RBA 2009
 Name: PDB1.SRC.STATEMENT
 ___________________________________________________________________

Reading ./dirdat/rt000083, current RBA 2226, 1 records

Report at 2015-06-07 13:17:01 (activity since 2015-06-07 13:10:42)

From Table PDB1.SRC.STATEMENT:
 # inserts: 0
 # updates: 0
 # deletes: 0
 # discards: 0
 # ignores: 1

Stored procedure sqlid:
 attempts: 1
 successful: 1

From Table PDB1.SRC.STATEMENT to PDB2.TGT.STATEMENT:
 # inserts: 1
 # updates: 0
 # deletes: 0
 # discards: 0

The described method represents a massive performance boost to replicate bulk operations.

Previously, using Event Actions to improve batch performance was supported in classic delivery mode only. I have discovered that adding an explicit COMMIT statement to the stored procedure on the target database enables the mechanism to function correctly with the Integrated Replicat.

CREATE OR REPLACE PROCEDURE ggadmin.execdml (P1 IN VARCHAR2)
 AS
 BEGIN
 execute immediate(P1);
 commit; -- Explicit commit
 END;
 /

[contact-form][contact-field label=’Name’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Website’ type=’url’/][contact-field label=’Comment’ type=’textarea’ required=’1’/][/contact-form]