BATCHSQL--GoldenGate Parameter

東北胖子發表於2016-05-22
BATSQLGo

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.

[sql] view plain copy
  1. replicat rep1  
  2. :::  
  3. BATCHSQL  
  4. :::  
  5. MAP user1.*, TARGET user1.*;  
  6. INSERTDELETES  
  7. MAP user1.t1, TARGET user2.t1hist,  
  8. COLMAP (TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),  
  9. BEFORE_AFTER = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"),  
  10. OP_TYPE = @GETENV ("GGHEADER", "OPTYPE"),  
  11. ID = ID);  
  12.   
  13. INSERT INTO "USER1"."T1" ("ID")   
  14. VALUES  
  15.  (:a0)  
  16.   
  17.   
  18. call     count       cpu    elapsed       disk      query    current        rows  
  19. ------- ------  -------- ---------- ---------- ---------- ----------  ----------  
  20. Parse        0      0.00       0.00          0          0          0           0  
  21. Execute     24      0.03       0.05          0        435       2149       13824  <<< 不是一行一行的處理，而是13824/24=576  
  22. Fetch        0      0.00       0.00          0          0          0           0  
  23. ------- ------  -------- ---------- ---------- ---------- ----------  ----------  
  24. total       24      0.03       0.05          0        435       2149       13824  
  25.   
  26.   
  27. INSERT INTO "USER2"."T1HIST" ("TS","BEFORE_AFTER","OP_TYPE","ID")   
  28. VALUES  
  29.  (:a0,:a1,:a2,:a3)  
  30.   
  31.   
  32. call     count       cpu    elapsed       disk      query    current        rows  
  33. ------- ------  -------- ---------- ---------- ---------- ----------  ----------  
  34. Parse        0      0.00       0.00          0          0          0           0  
  35. Execute     24      0.05       0.02          0        327        855       13824  
  36. Fetch        0      0.00       0.00          0          0          0           0  
  37. ------- ------  -------- ---------- ---------- ---------- ----------  ----------  
  38. total       24      0.05       0.02          0        327        855       13824  
  39.   
  40.   
  41. BATCHSQL statistics:  
  42.   
  43.               Batch operations:    221216  
  44.                        Batches:       380  
  45.               Batches executed:       394  
  46.                         Queues:       191  
  47.               Batches in error:         1  
  48.         Normal mode operations:         4  
  49.     Immediate flush operations:         0  
  50.                  PK collisions:        14  
  51.                  UK collisions:         0  
  52.                  FK collisions:         0  
  53.            Thread batch groups:         0  
  54.                        Commits:      1244  
  55.                      Rollbacks:         1  
  56.              Queue flush calls:         7  
  57.   
  58.                  Ops per batch:    582.15  
  59.         Ops per batch executed:    561.46   <<< about 576,因為做了很多操作，可能是一個近似值  
  60.                  Ops per queue:   1158.20  
  61.            Parallel batch rate:       N/A 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/2317695/viewspace-2105019/，如需轉載，請註明出處，否則將追究法律責任。

相關文章