goldengate extract performance

Posted by Roshan on Jul 17, 2020; 6:45am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/goldengate-extract-performance-tp8519.html

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