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.
