As we know Golden Gate is very powerful when it comes to data replication between homogeneous and heterogeneous environment. I wrote many articles on Golden Gate process group architectures, Golden Gate configuration, Configuring change capture, configuring change delivery and many more.
You can see all my articles on Golden Gate here
Today we will see optional but powerful options we can use with Golden Gate extract and replication process that boost performance, helps to transfer data securely between production and replication server, compress your data and saves your bandwidth and many more.
Let us start with very powerful option BatchSQL.
We know that Golden Gate works on SQL apply process. So each statement fired on production will be saved in the trail file and replicated to the replication server.
This is typical behaviour of GoldenGate. But, in BatchSQL – all the I, U, D operations [insert, update, delete] on the same table are grouped together in an array rather than an individual statement and apply them at a gee-up rate.
Each statement type is prepared once, cached and executed many times with different variables.
Referential integrity is maintained while doing this grouping. A BatchSQL option can be used with your initial load extract and your routine extract process. The configuration is done at the replicat level.
Syntax :
BatchSQL [BatchErrorMode|NoBatchErrorMode] [BatchesPerQueue <n>] [BatchTransOps<n>] [BytesPerQueue <n>] [OpsPerBatch <n>] [OpsPerQueue <n>]
Let us understand each option in brief.
BatchErrorMode and NoBatchErrorMode
This options set the response to replicat process when an error occurs while using BatchSQL.
BatchErrorMode: If this option specified, replication attempts to resolve errors without reverting to normal mode.HandleCollisions is required to prevent from exiting on an error.
NoBatchErrorMode: Replicat immediately aborts the transaction on an error , temporarily disables BatchSQL and retries in normal mode.
Note: NoBatchErrorMode is default.
BatchesPerQueue : It sets max number of batches per queue before flushing all batches. A queue in terms of batchsql , is a thread of memory containing captured operations which are waiting to be batched. There is one buffer queue by default. Default 50.
BatchTransOps: It controls the size of a batch. Default 1000.
BytesPerQueue: It sets max number of bytes to hold in a queue before flushing batch. Default 20MB
OpsPerBatch: Max number of rows that can be prepared for one batch before flushing.Default 1200
OpsPerQueue: Max number of row operations for all bacthes before flushing. Default 1200
Trace : Enables tracing.
Benefits we can get with BatchSQL : In smaller row changes , we can get higher performance upto 400 to 500 percent. But at 5000 bytes of change per row performance performance alleviate.
Performance benefits may vary depending on the environment of a mix of operations .
Compression : Optional benefit is gives by Oracle Golden Gate , it provides compression when transferring data over TCP/IP . Decompression is automatically performed by the server collector process at replicat server. We can set minimum block size , means upto which you want to compress your block . Default is 1000 bytes means if no option specified Golden Gate will compress block to 1000 bytes.
Golden Gate uses zlib compression method for compression.
Syntax :
RmtHost repserver , managerport 7809 , Compress , CompressThreshold 900
In the above example compression is enabled and compression will be 900 bytes for a single block.
Compression in Exadata Box : We can use compression in Exadata Box same we are doing in normal servers. But when it comes to EHCC [Exadata Hybrid Columnar Compression] extract process must be integrated capture mode and compatibility parameter must be set 11.2 or higher.
To ascertain successful delivery of insert operations with Exadata EHCC , use InsertAppend parameter with replicat parameters . This will cause replicat use and append hint for insert operations so that they remain compressed. Without this hing record will be inserted uncompressed.
If you want to be updated with all our articles send us an Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
