Loading....

We can improve performance by splitting large tables into row ranges and then assign processing of those ranges of rows to two or more Extract or Replicat process groups.

We can use the RANGE function to divide the rows of table across processing groups.

The syntax is @RANGE ({range}, {total ranges} [, {column}] [, {column}] [, …])

For example here the replication workload is split into two ranges between two different Replicat processes based on the table column ‘ORDID’.

The column name is optional. If not specified, GoldenGate uses the primary key of the table to allocate the ranges.

(Replicat group 1 parameter file)

MAP sh.orders, TARGET sh.orders, FILTER (@RANGE (1, 2, ORDID));

(Replicat group 2 parameter file)

MAP sh.orders, TARGET sh.orders, FILTER (@RANGE (2, 2, ORDID));

While the above example shows the use of RANGE in the Replicat process groups, we can also use the RANGE function in the Extract process group.

The GoldenGate documentation also states that it is better to use the RANGE function with Extract as opposed to Replicat process groups.

“Using Extract to calculate the ranges is more efficient than using Replicat. Calculating ranges on the target side requires Replicat to read through the entire trail to find the data that meets each range specification.”

Let us now have a look at an example where we are updating about 10 million rows in a table and we are using the RANGE function on the Replicat side of things to split the replication of these 10 mllion rows among three Replicat groups.

Create the Extract

GGSCI (dvdb01) 2> add extract ext2 tranlog begin now

EXTRACT added.

GGSCI (dvdb01) 3> add rmttrail /u01/oracle/goldengate/dirdat/aa, extract ext2

RMTTRAIL added.

Contents of parameter file for Extract ext2

EXTRACT ext2
USERID prd, PASSWORD prd
RMTHOST sodb02, MGRPORT 7809
RMTTRAIL /u01/oracle/goldengate/dirdat/aa
TABLE prd.ac_entry;

On Target server, add THREE Replicat groups Note that the trail file is the same for ALL the three replicat groups

GGSCI (sodb02) 1> add replicat rep2, exttrail /u01/oracle/goldengate/dirdat/aa

REPLICAT added.

GGSCI (sodb02) 2> add replicat rep3, exttrail /u01/oracle/goldengate/dirdat/aa

REPLICAT added.

GGSCI (sodb02) 3> add replicat rep4, exttrail /u01/oracle/goldengate/dirdat/aa

REPLICAT added.

Note the parameter file contents for each of the three Replicat groups

REPLICAT rep2
ASSUMETARGETDEFS
USERID prd,PASSWORD prd
MAP prd.ac_entry, TARGET prd.ac_entry, FILTER (@RANGE (1,3));

REPLICAT rep3
ASSUMETARGETDEFS
USERID prd,PASSWORD prd
MAP prd.ac_entry, TARGET prd.ac_entry, FILTER (@RANGE (2,3));

REPLICAT rep4
ASSUMETARGETDEFS
USERID prd,PASSWORD prd
MAP prd.ac_entry, TARGET prd.ac_entry, FILTER (@RANGE (3,3));

Start the Extract on source server

GGSCI (dvdb01) 6> start extract ext2

Sending START request to MANAGER …
EXTRACT EXT2 starting

GGSCI (dvdb01) 7> info extract ext2

EXTRACT EXT2 Last Started 2011-03-11 14:24 Status RUNNING
Checkpoint Lag 00:18:36 (updated 00:00:00 ago)
Log Read Checkpoint Oracle Redo Logs
2011-03-11 14:06:02 Seqno 22, RBA 220007440

On target, now start the replicat processes

GGSCI (sodb02) 7> start replicat rep2
Sending START request to MANAGER …
REPLICAT REP2 starting

GGSCI (sodb02) 8> start replicat rep3
Sending START request to MANAGER …
REPLICAT REP3 starting

GGSCI (sodb02) 9> start replicat rep4
Sending START request to MANAGER …
REPLICAT REP4 starting

Update 10 million rows in the table

SQL> update ac_entry set update_date=sysdate where id between 350000 and 11000000;

10193820 rows updated.

SQL> commit;
Commit complete.

After some time we check the status of the Extract process. We find that it has finished (note the EOF) and while the database log sequence has advanced from 22 to 28, GoldenGate has generated 311 trace files of 10MB each (the default size) in the location on the target server specified for the trail files.

GGSCI (dvdb01) 23> send extract ext2 status

Sending STATUS request to EXTRACT EXT2 …

EXTRACT EXT2 (PID 23821)
Current status: Recovery complete: At EOF

Current read position:
Sequence #: 28
RBA: 1827632128
Timestamp: 2011-03-11 14:39:26.000000

Current write position:
Sequence #: 311
RBA: 1021232
Timestamp: 2011-03-11 14:39:34.393944
Extract Trail: /u01/oracle/goldengate/dirdat/aa

On the target server, we now check the status of the three Replicat groups, We find that all three are running and are currently processing the same trail file sequence # 217 (aa217).

But the important point to note is that the processes are reading from different parts of the same trail file. Note that each process has a different RBA or relative byte address.

GGSCI (sodb02) 31> send replicat rep* status

Sending STATUS request to REPLICAT REP2 …
Current status: Processing data
Sequence #: 217
RBA: 888147

502538 records in current transaction

Sending STATUS request to REPLICAT REP3 …
Current status: Processing data
Sequence #: 217
RBA: 890379
502438 records in current transaction

Sending STATUS request to REPLICAT REP4 …
Current status: Processing data
Sequence #: 217
RBA: 3219241
502782 records in current transaction

After some time, we run the same command again and find that all three replicat processes have now completed and are at trail file sequence 311 which was the last trail file that the extract process had generated. Note the EOF and the fact that the RBA now for all three replicat files is the same.

GGSCI (sodb02) 6> send replicat rep* status

Sending STATUS request to REPLICAT REP2 …
Current status: At EOF
Sequence #: 311
RBA: 2576695
0 records in current transaction

Sending STATUS request to REPLICAT REP3 …
Current status: At EOF
Sequence #: 311
RBA: 2576695
0 records in current transaction

Sending STATUS request to REPLICAT REP4 …
Current status: At EOF
Sequence #: 311
RBA: 2576695
0 records in current transaction

If we chcek the statistics of the three replicat processes, we find that using the RANGE function has spilt the 10 miilion row update task into three – each replicat process here has processed about 3.3 million rows each.

GGSCI (sodb02) 28> stats replicat rep*

Sending STATS request to REPLICAT REP2 …
Start of Statistics at 2011-03-11 14:39:41.
Replicating from PRD.AC_ENTRY to PRD.AC_ENTRY:

*** Total statistics since 2011-03-11 14:36:38 ***
Total inserts 0.00
Total updates 3398392.00
Total deletes 0.00
Total discards 0.00
Total operations 3398392.00

End of Statistics.

Sending STATS request to REPLICAT REP3 …
Start of Statistics at 2011-03-11 14:39:42.
Replicating from PRD.AC_ENTRY to PRD.AC_ENTRY:

*** Total statistics since 2011-03-11 14:36:38 ***
Total inserts 0.00
Total updates 3397822.00
Total deletes 0.00
Total discards 0.00
Total operations 3397822.00

End of Statistics.

Sending STATS request to REPLICAT REP4 …
Start of Statistics at 2011-03-11 14:39:42.
Replicating from PRD.AC_ENTRY to PRD.AC_ENTRY:
*** Total statistics since 2011-03-11 14:36:38 ***
Total inserts 0.00
Total updates 3397822.00
Total deletes 0.00
Total discards 0.00
Total operations 3397822.00

End of Statistics.

Please follow and like us:

Last Update: May 4, 2017  

March 14, 2011 33 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

,

4 thoughts on “GoldenGate performance tuning using the RANGE function

  1. David Mann

    Great technique… I have created multiple replicats which serviced different sets of tables in a trail but had never considered going to a more granular level. I am assuming this wouldn’t necessarily work for all tables… like if transaction serialization needed to be guaranteed or if there were some other tables involved in the transaction that had FKs that would need to be reconciled on the target side.

  2. Mohan Babu

    Will the use of “Range” function in one or more replicat processes on the target DB preserve parent/child foreign key constraints after all the replicat processes complete processing?

  3. Gavin Soorma

    Hi Mohan – if you have tables related to each other like EMP and DEPT, the you will need to include both in the Extract or Replicat configuration when using the RANGE function – will try and do a test of this and send you the result

  4. Gavin Soorma

    Hi David – if you have tables related to each other like EMP and DEPT, the you will need to include both in the Extract or Replicat configuration when using the RANGE function – will try and do a test of this and send you the result

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?