TIP 102#: GoldenGate replicat performance

One of the nice feature of GoldenGate is BATCHSQL which enables GoldenGate replicat to organize similar SQL statements into array and apply them at once instead of normal mode to apply SQL statement one by one. Oracle document says that around 5K bytes of data change per row, BATCHSQL loses its benefits.

The following shows a sample run of BATCHSQL with INSERTAPPEND and how it saves resources and improves the performance.

Scenario 1 : Small table with massive changes

create table repuser.tbl1 (id number, name varchar2(30));
alter table repuser.tbl1 add constraint uc1 unique (id);

Initial load :

SQL> delete from repuser.tbl1;

0 rows deleted.

SQL> insert into repuser.tbl1 select object_id*-1,object_name from all_objects where object_id is not null;

56135 rows created.

SQL> c/-1/-100000
1* insert into repuser.tbl1 select object_id*-100000,object_name from all_objects where object_id is not null
SQL> /

56135 rows created.

SQL> c/-100000/1
1* insert into repuser.tbl1 select object_id*1,object_name from all_objects where object_id is not null
SQL> /

56135 rows created.

SQL> commit;

Commit complete. 

To find out the impact of BATCHSQL in terms of performance, The changes are tested on Oracle 11203 using GG 11201 and the changes are shipped to target with Oracle 11203 using GG 11201. The following shows different setting for replicat and how each setting improves the performance

Replicat setting 1 : Default setting - No BATCHSQL, No INSERTAPPEND

GGSCI (localhost.localdomain) 33> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     RENA_IN     00:22:24      00:00:02    

 It takes more than 22 minutes to apply changes (224450 deletes and 224450 inserts)

The following trace shows that 224450 times delete and 224450 times insert is run and it took 1261.54+27.72=1289.26 seconds to complete executions of these two statements (Overheads and other internal executions are ignored).

DELETE FROM "REPUSER"."TBL1"
WHERE
"ID" = :b0 AND ROWNUM = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 224540   1261.54    1296.81       2979  567004335     238878      224540
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   224541   1261.54    1296.81       2979  567004335     238878      224540

INSERT INTO "REPUSER"."TBL1" ("ID","NAME")
VALUES
(:a0,:a1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 224540     26.91      27.72          0        787     237105      224540
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   224541     26.91      27.72          0        787     237105      224540

Replicat setting 2 : BATCHSQL, INSERTAPPEND

GGSCI (localhost.localdomain) 23> !
info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     RENA_IN     00:07:04      00:00:00    

The same changes only took more than 7 mins to be completed. It is more than 3 times faster setting 1.

The following shows that only 450 times delete and 450 times insert are run and it took only 452.10+0.71=452.81 seconds to complete the executions of two statements.
Also it shows that GoldenGate runs insert in DIRECT PATH with adding hints.

DELETE FROM "REPUSER"."TBL1"
WHERE
"ID" = :b0 AND ROWNUM = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    450    440.00     452.10         12  139050526     238818      224540
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      451    440.00     452.10         12  139050526     238818      224540

INSERT /*+ APPEND APPEND_VALUES */ INTO "REPUSER"."TBL1" ("ID","NAME")
VALUES
(:a0,:a1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    450      0.60       0.71          0        922       5263      224540
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      451      0.60       0.71          0        922       5263      224540

Replicat setting 3 : BATCHSQL, No INSERTAPPEND

GGSCI (localhost.localdomain) 23> !
info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     RENA_IN     00:07:54      00:00:00    

It took around 8 minutes to apply the same change, Timing is very close to BATCHSQL/INSERTAPPEND but almost 3 times faster than traditional GoldenGate.

Trace also shows that 450 executions of delete and 450 executions of inserts for the same amount of the change.Elapsed time is 518.87+0.48=519.35 seconds.

DELETE FROM "REPUSER"."TBL1"
WHERE
"ID" = :b0 AND ROWNUM = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    450    509.74     518.87          0  168600692     238673      224540
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      451    509.74     518.87          0  168600692     238673      224540


INSERT INTO "REPUSER"."TBL1" ("ID","NAME")
VALUES
(:a0,:a1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    450      0.46       0.48          0       2312      11070      224540
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      451      0.46       0.48          0       2312      11070      224540

So far, BATCHSQL and INSERTAPPEND sounds promising for massive changes in small table. In the next post, I will try to test this feature with wider table to see how this feature performs for changes around 5KB per row.