Goldengate Interview Questions - Performance

Goldengate Interview Questions - Performance 

List a few parameters which may help improve the replicat performance?

The parameters below can be used to improve the replicat performance:

• BATCHSQL
• GROUPTRANSOPS
• INSERTAPPEND

What are the areas to monitor in Goldengate Replication?

The lag and checkpoint latency of the Extract, pump and Replicat processes are normally monitored.

What is the PASSTHRU mode used for?

In pass-through mode, the Extract process does not look up the table definitions, either from the database or from a data definitions file. This increases the throughput of the data pump, as the object definition look-up is bypassed.

What are the most common reasons of an Extract process slowing down?

Some of the possible reasons are:

• Long running batch transactions on a table.
• Insufficient memory on the Extract side. Uncommitted, long running transactions can cause writing of a transaction to a temporary area (dirtmp) on disk. Once the transaction is committed it is read from the temporary location on the file system and converted to trail files.
• Slow or overburdened Network.

What are the most common reasons of the Replicat process slowing down?

Some of the possible reasons are:

• Large amount of transactions on a particular table.
• Blocking sessions on the destination database where non-Goldengate transactions are also taking place on the same table as the replicat processing.
• If using DBFS, writing & reading of trail files may be slow if SGA parameters are not tuned.
• For slow Replicat’s, latency may be due to missing indexes on target.
• Replicat having to process Update, delete of rows in very large tables.

My extract was running fine for a long time. All of a sudden it went down. I started the extract processes after 1 hour. What will happen to my committed transactions that occurred in the database during last 1 hour?

OGG checkpoint provides the fault tolerance and make sure that the transaction marked for committed is capture and captured only once. Even if the extract went down abnormally, when you start the process again it reads the checkpoint file to provide the read consistency and transaction recovery.

I have configured Oracle GoldenGate integrated capture process using the default values. As the data load increases I see that extract starts lagging behind by an hour (or more) and database performance degrades. How you will resolve this performance issue?

When operating in integrated capture mode, you must make sure that you have assigned sufficient memory to STREAMS_POOL_SIZE. An undersized STREAMS_POOL_SIZE or limiting the streams pool to use a specific amount of memory can cause troubles.

The best practice is to allocate STREAMS_POOL_SIZE at the instance level and allocate the MAX. SGA at GG process level as below:
SQL> alter system set STREAMS_POOL_SIZE=3G

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2048, PARALLELISM 4)

Why would you segregate the tables in a repllication configuration? How would you do it?

In OGG you can configure replicat at the data at the schema level or at the table level using TABLE parameter of extract and MAP parameter of replicat.

For replicating the entire database you can list all the schemas in the database in the extract/replicat parameter file.

Depending the amount of redo generation you can split the tables in a schema in multiple extracts and replicats to improve the performance of data replication. Alternatively youcan also group a set of tables in the confiuration by the application functionality.

Alternatively you may need to remove tables which have long running transactions in a seperate extract process to eliminte lag on the other tables.

Let’s say that you have a schema named SCOTT and it has 100 hundred tables.

Out of these hundred tables, 50 tables are heavily utilized by application.

To improve the overall replication performance you create 3 extract and 3 replicats as follows:

Ext_1/Rep_1 –> 25 tables
Ext_2/Rep_2 –> 25 tables
Ext_3/Rep_3 –> 50 tables
Ext_1/Rep_1 and Ext_2/Rep_2 contains 25 table each which are heavily utilized or generate more redo.
Ext_3/Rep_3 contains all the other 50 tables which are least used.