Golden Gate Performance Tips – For DML Operations

Problem Statement:

Updates and Inserts (or mainly DMLs) in GG are taking long time to execute

 

The top few reasons for DMLs to run long are,

  1. Heavy transactions are being processed at Source
  2. DB performance may be a reason too
  3. Unique/Primary Keys missing

To Overcome the above issues, there are couple of possible solutions, which I’ve implemented and noticed a significant improvement in performance

Possible Solutions

  1. Enable BATCHSQL in Replicat processes
  2. Configure Multiple Replicat processes for a single Trail file

Just a little detailed explanation of BATCHSQL and Multiple Replicat Processes,

BATCHSQL:

  1. By default Replicat operates in Normal mode
  2. Replicat batches together SQL statements that affect the same table
  3. Operation type supported by BATCHSQL is Insert, Update or Delete
  4. In contrast, using the BATCHSQL parameter with a default OPSPERBATCH value of 1200
  5. Oracle recommends regularly checking the process report file and statistics to make sure few transactions are reverting back to normal mode if an exception occurs

Oracle claims that the performance of BATCHSQL is even more if we use Intergrated Replicat feature.

 

Picture1.png

Multiple Replicats:

We could configure multiple replicat processes reading the same Trail files and split the work load. This is a very good performance booster.

multiple_replicats.png

These pictures are taken from Oracle Documentation.

Advertisements
This entry was posted in Oracle Golden Gate. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s