goldengate extract performance

[Roshan]

OGG 18.1
Solaris 14
DB 12.1.0.2

Hi Erman,

I noticed lag time for extract was increasing. Helathcheck report shows the following:

+ WARNING: 1398 archived logs registered for extracts/captures..
 + A restarting Capture process must mine through each registered archive log.
 + To speedup Capture restart, reduce the amount of disk space taken by the archived
 + logs, and reduce Capture metadata, consider moving the first_scn automatically by
 + altering the checkpoint_retention_time capture parameter to a different value.
 + Include ( or modify an existing line) in the extract parameter file
 + TRANLOGOPTIONS CHECKPOINTRETENTIONTIME number_of_days
 + where number_of_days is the number of days the extract logmining server will retain checkpoints.
 + For more information, see the Oracle GoldenGate for Windows and UNIX Reference Guide
 + Note that once the first scn is increased, Capture will no longer be able to mine prior to
 + this new scn value.
 + Successive moves of the first_scn will remove unneeded registered archive
 + logs only if the files have been removed from disk


The disk space for archive was around 80% free.

Also, the following warning was found:

+ WARNING: Extract EXTDWH is slow to request changes (1932065 chunks available) from capture OGG$CAP_EXTDWH
 + Use the following command to obtain Extract wait statistics
 SEND extract EXTDWH, LOGSTATS
 + Output of above command is written to extract report file
 +
 + The WAITING FOR CLIENT REQUESTS state is an indicator to investigate the extract process rather than the logmining server when there are chunks available from capture.
 + If Integrated Extract is V2 and wait statistics from SEND extract... LOGSTATS are high,
 + add the following line to the extract parameter file and restart extract:
 TRANLOGOPTIONS _READAHEADCOUNT 10
 + The maximum value for _READAHEADCOUNT is 64

SEND extract EXTDWH, LOGSTATS  shows the following:

REDO Log Statistics
  Bytes parsed                    0
  Bytes output            712358785

                                 -- Write Operations ------------------  -- Read Operations -------------------
Queue Name                 Size         Count       Waited     Signaled         Count       Waited     Signaled
------------------------ ------  --------------------------------------  --------------------------------------
AsyncReader 1 Buffers        11          2575            0            0          2565            2            1
AsyncReader 1 Results        22          2525            0            0          2525         3266         2362
IXFormatter Results          66          3155            0            0          3155        15709            0
IXFormatter Buffers          50          2878            0            0          2830            0            0
IXAsyncTrans Buffers        300        452817            0            0        452518          221          458
IXAsyncTrans Results        316        452530            0            0        452530         1996         1143


2020-07-17 09:32:05  INFO    OGG-06507  MAP (TABLE) resolved (entry CBS_CUST.CB_SURCHARE_AND_WAIVEOFF_DTLS): TABLE "CBS_CUST"."CB_SURCHARE_AND_WAIVEOFF_DTLS".

2020-07-17 09:32:05  INFO    OGG-06509  Using the following key columns for source table CBS_CUST.CB_SURCHARE_AND_WAIVEOFF_DTLS: ACCOUNT_CODE_N, REF_TRANS_NUM_V, SURTRANS_NUM_V.

2020-07-17 09:32:06  INFO    OGG-06507  MAP (TABLE) resolved (entry CBS_CUST.CB_SURCHARE_AND_WAIVEOFF_DTLS): TABLE "CBS_CUST"."CB_SURCHARE_AND_WAIVEOFF_DTLS".

2020-07-17 09:32:06  INFO    OGG-06509  Using the following key columns for source table CBS_CUST.CB_SURCHARE_AND_WAIVEOFF_DTLS: ACCOUNT_CODE_N, REF_TRANS_NUM_V, SURTRANS_NUM_V.

2020-07-17 09:54:13  INFO    OGG-01026  Rolling over remote file /ogg/ora12c/dirdat/aa000004451.

2020-07-17 10:18:27  INFO    OGG-01021  Command received from GGSCI: LOGSTATS.

REDO Log Statistics
  Bytes parsed                    0
  Bytes output           1140270229

                                 -- Write Operations ------------------  -- Read Operations -------------------
Queue Name                 Size         Count       Waited     Signaled         Count       Waited     Signaled
------------------------ ------  --------------------------------------  --------------------------------------
AsyncReader 1 Buffers        11          6802            0            0          6792            2            1
AsyncReader 1 Results        22          6682            0            0          6682         9439         6519
IXFormatter Results          66          7828            0            0          7828        51223            0
IXFormatter Buffers          50          6985            0            0          6937            0            0
IXAsyncTrans Buffers        300        828919            0            0        828620          236          483
IXAsyncTrans Results        316        828632            0            0        828632         5685         1766

Kindly advise on this:

+ If Integrated Extract is V2 and wait statistics from SEND extract... LOGSTATS are high

Thanks,

Roshan

[ErmanArslansOracleBlog]

V2 is the Light Weight" Capture.
So It basically suggest you to increase TRANLOGOPTIONS _READAHEADCOUNT.. Maybe to 64..
So if your extract is V2 and if you see high stats in Extract wait statistics, then you may consider this advice.
Your write operations seems good, but you have some waits on reads.

So you may consider this advice.
Also check Oracle Support for similar issues.
For instance this one -> Integrated Extract Is Slow In Capturing Transactions (Doc ID 2346730.1)

[Roshan]

Thanks. It is good now after adding READAHEADCOUNT.