Is there a way to improve REPLICAT performance/throughput?

Is there a way to improve REPLICAT performance/throughput?

 

Cause

Cause

In its normal mode, Replicat applies one SQL statement at a time, in the same order as the changes occurred on the source system.

 

Despite optimizations and statement caching, the overhead of preparing statements, binding column values, and executing statements for each change can cause REPLICAT to be slower than necessary to keep up with the changes arriving in the trail.

 

The use of the HANDLECOLLISIONS parameter can also cause performance issues if there are collisions since there needs to be additional processing done with those records. This parameter should be removed if you are not during your initial load for your tables.

 

Indexing on your target tables will also have an impact on performance, you should have a PK (Primary or UI (unique Index) to quickly apply the updates or deletes to the tables. You should also investigate if there are any FK(foreign Key) relationships and if the referenced columns should also be indexed appropriately, with no INDEX a full table scan will be doing on the referenced table even on insert.

Solution

 

Solution

Three common approaches for increasing throughput are:

1) Run multiple Replicat processes, each applying changes to different tables and/or ranges.

2) Use Replicat’s BATCHSQL mode.

3) Review indexing on your target tables and add if appropriate even on referenced tables such as FK’s

 

Replicat’s BATCHSQL feature causes Replicat to organize similar SQL statements into arrays and apply them at an accelerated rate.

 

When Replicat is in BATCHSQL mode, smaller row changes will show a higher gain in performance than larger row changes. At 100 bytes of data per row change, BATCHSQL has been known to improve Replicat’s performance by up to 300 percent, but actual performance benefits will vary, depending on the mix of operations. At around 5,000 bytes of data per row change, the benefits of using BATCHSQL diminish.

 

To activate this feature using default tuning subparameters, add the following parameter to the REPLICAT parameter file:

 

BATCHSQL


 

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em GOLDENGATE. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s