Improving GoldenGate Replicat Data Throughput

Asynchronous Commits

Replicat performance can be further improved by altering the way Oracle GoldenGate commits the transaction on the target database. By default, Oracle will wait for a commit to succeed before allowing the session to continue. However, this synchronous behaviour can cause unnecessary delays when the workload is high.

To alleviate this bottleneck, you can configure your Replicat processes to commit asynchronously at session level by including the following SQLEXEC statement in each parameter file:

SQLEXEC “alter session set commit_wait = ‘NOWAIT’”;

Note that the specification of the NOWAIT allows a small window of vulnerability. These are:

·         If the database instance crashes, causing the database to lose redo that was buffered but not yet written to the online redo logs.

·         A file I/O problem prevents log writer from writing buffered redo to disk.

Don’t be alarmed, in both cases; GoldenGate will automatically "replay" the uncommitted transactions which would be driven by the information stored in the Checkpoint table, following database instance crash recovery.


New Redo Log OCI API

A new option of the TRANLOGOPTIONS parameter is available in Oracle GoldenGate version 11.1.1, called DBLOGREADER. This alleviates the need to access ASM directly to read the redo logs. Now, via an OCI API, GoldenGate reads the redo and archived logs from the DB server, increasing Extract performance over the former PL/SQL API. There is therefore no need to specify the TRANLOGOPTIONS ASMUSER option when specifying DBLOGREADER.

Throughputs of over 75GB per hour are achievable with the new ASM API.


DBFS Enhancements

When configuring DBFS it is highly recommended to adopt the following options:

·         Create a separate database instance running in NOARCHIVELOG mode to support the DBFS

·         Create a DBFS tablespace in the dedicated Database, configured with NOLOGGING

·         Create a SecureFile LOB segment defined with NOCACHE NOLOGGING

·         Create the file system with the PARTITION option

·         Mount the DBFS through /etc/fstab, using the following options:

o   rw,user,direct_io,allow_other,wallet,noauto,max_threads=64

You may be concerned by having NOLOGGING set on your DBFS tablespace for recovery reasons. Rest assured, because GoldenGate can “pick up” from where it left off following a database crash, due to its check-pointing mechanism. GoldenGate will only checkpoint its Replicat process once it has committed data to the target. If you have had to conduct a point-in-time database recovery on your target, GoldenGate can replay the transactions by altering the Replicat process using the associated BEGIN option.



Did you find the article useful?

Please provide your feedback by voting now.

If you have a comment or question, please complete and submit the form below.