GoldenGate

Today at a client I had a old abended replicat process that I decided to give life to. So I solved the problem and start it.

The environment is using GoldenGate 11.1.1.1.2 against Oracle 10.2.0.4 database with ASM, on AIX. The replicat process replicates all operations of a 700+ tables schema. There were other replicats running at the same time writing to other schemas.

Then I decided to see the performance of GoldenGate. With the query:

GGSCI> stats r_xxxx TOTALSONLY * REPORTRATE sec

You get the detailed statistics of operations per second since the start of the process, since the beginning of the day and last hour for all tables replicated by that process. For instance:

*** Hourly statistics since 2012-04-16 13:00:00 ***
        Total inserts/second:                     1397.76
        Total updates/second:                     1307.46
        Total deletes/second:                      991.50
        Total discards/second:                       0.00
        Total operations/second:                  3696.71

So here we can see it is doing a bit more than 3500 operations per second, divided quite evenly between inserts, updates and deletes.

As usually the GoldenGate is used for realtime replication and there are no big operations, the client does not use performance related parameters. But this time I decided to play with them.

After adding both: BATCHSQL and INSERTAPPEND to the parameter file of the replicat process, the results were the following (after more than 10 minutes running) and performance is still increasing:

*** Hourly statistics since 2012-04-16 13:43:17 ***
        Total inserts/second:                     2174.92
        Total updates/second:                     2540.20
        Total deletes/second:                     1636.16
        Total discards/second:                       0.00
        Total operations/second:                  6351.28

We see the performance increased by 90% !

I got interested to see if the BATCHSQL parameter only by itself could make the difference. So I removed the INSERTAPPEND parameter (which only influences the inserts anyway). Here are the results after more than 10 minutes.

*** Hourly statistics since 2012-04-16 14:00:00 ***
        Total inserts/second:                     2402.21
        Total updates/second:                     2185.24
        Total deletes/second:                     1742.43
        Total discards/second:                       0.00
        Total operations/second:                  6329.88

Yep, seems the system of my client in certain situations benefits mostly of the BATCHSQL parameter.

For those who don’t know, “in BATCHSQL mode, Replicat organizes similar SQL statements into batches within a memory queue, and then it applies each batch in one database operation. A batch contains SQL statements that affect the same table, operation type (insert, update, or delete), and column list.” (in GoldenGate Reference Guide).