BATCHSQL--GoldenGate Parameter

来源：互联网发布：window安装mac双系统编辑：程序博客网时间：2024/05/22 02:18

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.*;INSERTDELETESMAP 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           0Execute     24      0.03       0.05          0        435       2149       13824  <<< 不是一行一行的处理，而是13824/24=576Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total       24      0.03       0.05          0        435       2149       13824INSERT 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           0Execute     24      0.05       0.02          0        327        855       13824Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total       24      0.05       0.02          0        327        855       13824BATCHSQL 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

0 0