In this last installment we'll examine some of the aspects that may help you to derive better throughput in your OGG processing.
One of the challenges of performance tuning is in deciding which component of Oracle GoldenGate environment needs tuning. In the following section you'll find a number of tips in order to improve OGG processing:
When source and target have identical data structures and character set, add the following to your extract or data pump:
PASSTHRUTo scale up similar DML activity in your Replicat processes when the applier is underperforming, add the following keyword in your replicat parameter file:
BATCHSQLSpeeding up Replicat processing since we don't wait for the commit marker is done as follows:
SQLEXEC "ALTER SESSION SET COMMIT_WRITE = NOWAIT"For improving network throughput when shipping trail files across the network to your target server; please refer to the recipe Implementing design considerations (Simple)for buffer sizing calculations.
RMTHOST hostb MGRPORT 7820 TCPBUFSIZE 10000000We will be implementing parallel data pumps and corresponding parallel Replicats to speed up throughput along the wire.
Sample parameter file for your first pump at source server is as follows:
EXTRACT pp01HR PASSTHRU RMTHOST hostb MGRPORT 7820 RMTTRAIL /u01/app/oracle/goldengate/dirdat/HA DISCARDFILE /u01/app/oracle/gg/dirrpt/pp01hr.dsc, APPEND -- Tables for transport TABLE HR.EMPLOYEE ; TABLE HR.JOBS ;
Sample parameter file for your second pump at source server is as follows:
EXTRACT pp02HR PASSTHRU RMTHOST hostb MGRPORT 7820 RMTTRAIL /u01/app/oracle/goldengate/dirdat/HB DISCARDFILE /u01/app/oracle/gg/dirrpt/pp02hr.dsc, APPEND -- Tables for transport TABLE HR.LOCATION ; TABLE HR.COUNTRY ; TABLE HR.REGION ;
Sample corresponding replicat parameter for the first pump at target server is as follows:
REPLICAT re01hr SETENV (ORACLE_SID="TRG101") SETENV (ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1") SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") USERID ogg PASSWORD ogg DISCARDFILE /u01/app/oracle/goldengate/dirrpt/re01hr.dsc, APPEND DISCARDROLLOVER at 01:00 ReportCount Every 30 Minutes, Rate REPORTROLLOVER at 01:30 DBOPTIONS DEFERREFCONST ASSUMETARGETDEFS MAP HR.EMPLOYEE , TARGET HR.EMPLOYEE ; MAP HR.JOBS , TARGET HR.JOBS ;
Sample corresponding replicat parameter for the second pump at target server is as follows:
REPLICAT re02hr SETENV (ORACLE_SID="TRG101") SETENV (ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1") SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") USERID ogg PASSWORD ogg DISCARDFILE /u01/app/oracle/goldengate/dirrpt/re02hr.dsc, APPEND DISCARDROLLOVER at 01:00 ReportCount Every 30 Minutes, Rate REPORTROLLOVER at 01:30 DBOPTIONS DEFERREFCONST ASSUMETARGETDEFS MAP HR.LOCATION , TARGET HR.LOCATION ; MAP HR.COUNTRY , TARGET HR.COUNTRY ; MAP HR. REGION , TARGET HR.REGION ;
We would now be splitting a single table's workload to enhance throughput of large and heavily accessed tables.
For splitting the
HISTORYtable in 3 ranges at source server perform the following:RMTTRAIL /u01/app/oracle/GG/aa TABLE HR.HISTORY, FILTER (@RANGE(1, 3)) ; RMTTRAIL /u01/app/oracle/GG/ab TABLE HR.HISTORY, FILTER (@RANGE(2, 3)) ; RMTTRAIL /u01/app/oracle/GG/ac TABLE HR.HISTORY, FILTER (@RANGE(3, 3)) ;
Corresponding
HISTORYtable ranges on the target server are as follows:EXTRAIL /u01/app/oracle/GG/aa MAP HR.HISTORY , TARGET HR.HISTORY, FILTER (@RANGE(1,3)); EXTRAIL /u01/app/oracle/GG/ab MAP HR.HISTORY , TARGET HR.HISTORY, FILTER (@RANGE(2,3)); EXTRAIL /u01/app/oracle/GG/ac MAP HR.HISTORY , TARGET HR.HISTORY, FILTER (@RANGE(3,3));
When you use PASSTHRU in your data pump, the benefit is that the extract does not have to lookup table definitions either from the database or from data definition files. The data pump process instead handles reading and sending the local trail files over to the target system.
Use BATCHSQL in your Replicat parameter file to organize similar SQL statements into arrays and to apply them into an accelerated rate.
The commit_rate=nowait command speeds up Replicat processing. The parameter alters the Replicat Oracle session to not wait for commits. Similar to an asynchronous state, however, the transaction is persisted through the redo.
Using the @RANGE function is a powerful way to increase a heavily used table's throughput. It divides the rows of any table across two or more OGG processes. In our example we have split the range in 3; for example, FILTER @RANGE(1,3), FILTER @RANGE(2,3). The @RANGE is safe and scalable to use. It preserves data integrity by ensuring that the same row is always processed by the same process group.
If you like to use a specific column as the range to split on, the syntax is as follows:
MAP HR.HISTORY , TARGET HR.HISTORY, FILTER (@RANGE(1,3, ID)); MAP HR.HISTORY , TARGET HR.HISTORY, FILTER (@RANGE(2,3, ID)); MAP HR.HISTORY , TARGET HR.HISTORY, FILTER (@RANGE(3,3, ID));
Since any column can be specified for this function, any related table with referential integrity must be grouped together into the same process or trail to preserve referential integrity.
@RANGE computes a hash value of the KEYCOLS of the TABLE or MAP statement if one is used. Otherwise, the primary key will be used.
Using the Extract to calculate the ranges is far more efficient than using the Replicat. Calculating ranges at the target requires the Replicat to read through the entire trail to find the data that meets the range specification.