BATCHSQL
Valid for Replicat
Usethe BATCHSQL parameter to increase theperformance of Replicat. BATCHSQL causes
Replicatto organize similar SQL statements into arrays and apply them at an accelerated
rate.In its normal mode, Replicat applies one SQL statement at a time.
BATCHSQL is valid for:
● DB2 LUW
● DB2 on z/OS
● Oracle
● NonStop SQL/MX
● PostgreSQL
● SQL Server
● Teradata
HowBATCHSQL works
In BATCHSQLmode, Replicat organizes similar SQL statements into batcheswithin a
memoryqueue, and then it applies each batch in one database operation. A batchcontains
SQLstatements that affect the same table, operation type (insert, update, ordelete), and
columnlist. For example, each of the following is a batch:
● Inserts to table A
● Inserts to table B
● Updates to table A
● Updates to table B
● Deletes from table A
● Deletes from table B
NOTE OracleGoldenGate analyzes foreign-key referential dependencies in the batches
beforeexecuting them. If dependencies exist among statements that are in
differentbatches, more than one SQL statement per batch might be required to
maintain the referential integrity.
- replicat rep1
- :::
- BATCHSQL
- :::
- MAP user1.*, TARGET user1.*;
- INSERTDELETES
- MAP user1.t1, TARGET user2.t1hist,
- COLMAP (TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
- BEFORE_AFTER = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"),
- OP_TYPE = @GETENV ("GGHEADER", "OPTYPE"),
- ID = ID);
- INSERT INTO "USER1"."T1" ("ID")
- VALUES
- (:a0)
- call count cpu elapsed disk query current rows
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- Parse 0 0.00 0.00 0 0 0 0
- Execute 24 0.03 0.05 0 435 2149 13824 <<< 不是一行一行的处理,而是13824/24=576
- Fetch 0 0.00 0.00 0 0 0 0
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- total 24 0.03 0.05 0 435 2149 13824
- INSERT INTO "USER2"."T1HIST" ("TS","BEFORE_AFTER","OP_TYPE","ID")
- VALUES
- (:a0,:a1,:a2,:a3)
- call count cpu elapsed disk query current rows
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- Parse 0 0.00 0.00 0 0 0 0
- Execute 24 0.05 0.02 0 327 855 13824
- Fetch 0 0.00 0.00 0 0 0 0
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- total 24 0.05 0.02 0 327 855 13824
- BATCHSQL statistics:
- Batch operations: 221216
- Batches: 380
- Batches executed: 394
- Queues: 191
- Batches in error: 1
- Normal mode operations: 4
- Immediate flush operations: 0
- PK collisions: 14
- UK collisions: 0
- FK collisions: 0
- Thread batch groups: 0
- Commits: 1244
- Rollbacks: 1
- Queue flush calls: 7
- Ops per batch: 582.15
- Ops per batch executed: 561.46 <<< about 576,因为做了很多操作,可能是一个近似值
- Ops per queue: 1158.20
- Parallel batch rate: N/A