===============
General
===============
Golden Gate Extract was running slow, lag increasing, until it was several days
GGSCI (geqaa-1-aps-01) 2> SEND EXT_P_07 status
Sending STATUS request to EXTRACT EXT_P_07 ...
EXTRACT EXT_P_07 (PID 5256)
Current status: Recovery complete: Processing data
Current read position:
Redo thread #: 1
Sequence #: 9610
RBA: 869956208
Timestamp: 2021-12-20 00:43:58.000000
SCN: 1.1180047274 (5475014570)
Current write position:
Sequence #: 73
RBA: 4711299
Timestamp: 2021-12-26 14:41:42.940217
Extract Trail: /software/ogg/191/dirdat/07/out/ep
No errors are in extract log.
Running top, iostat, sar, free - do not show that server is low on CPU/memory/IO
Why extract is slow?
===============
Investigation
===============
Extract was set with these parameters:
EXTRACT ext_p_07
setenv (ORACLE_SID="igt")
setenv (ORACLE_HOME="/software/oracle/193")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID gg_user, PASSWORD AACAAAAAAAAAAAIAZEDCPHICXGPEQCED ENCRYPTKEY DEFAULT
EXTTRAIL /software/ogg/191/dirdat/07/out/ep
CACHEMGR CACHESIZE 1024M
NODYNAMICRESOLUTION
-- Bi-direction replication
TRANLOGOPTIONS EXCLUDEUSER ogg
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 256)
include ./dirprm/PROVtables_07.inc
Per Oracle recommendation:
MAX_SGA_SIZE: Controls the amount of shared memory used by the logmining server.
The default is 1 GB.
So the current value of 256M looks too small
The system in question is running in total 6 Extract processes.
In order to make use of the MAX_SGA_SIZE parameter, need to set accordingly the STREAMS_POOL_SIZE
Per Oracle Note STREAMS_POOL_SIZE
Products and features that use the Streams pool include Oracle GoldenGate, XStream, Oracle Advanced Queuing, and Oracle Data Pump.
Oracle's Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value.
If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value,
then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.
If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero),
then, by default, on the first request for Streams pool memory in a database,
an amount of memory equal to 10% of the shared pool is transferred from the buffer cache to the Streams pool.
If SGA_TARGET is set to a nonzero value and STREAMS_POOL_SIZE is not specified or is set to a null value,
then Automatic Shared Memory Management uses 0 (zero) bytes as a minimum for the Streams pool.
If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value,
and the SGA_TARGET parameter is set to 0 (zero),
then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes.
In this installation, STREAMS_POOL_SIZE was set to zero, SGA_TARGET was set to zero, so Streams Pool was supposed to use 10% of buffer cache.
But when checking V$SGA_DYNAMIC_COMPONENTS, for current memory allocation, the actual allocation to streams pool is 134Mb out of 16Gb, which is close to 1%.
SELECT component, current_size, max_size
FROM V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE MAX_SIZE
------------------------------ ------------ ---------------
shared pool 1879048192 1879048192
large pool 469762048 469762048
java pool 402653184 402653184
streams pool 134217728 134217728
DEFAULT buffer cache 16508780544 17716740096
Shared IO Pool 536870912 536870912
Per Oracle Technote "Integrated Extract / Replicat and STREAMS_POOL_SIZE (Doc ID 2078459.1)"
For Oracle GoldenGate - Version 11.2.1.0.0 and later
By default, one integrated capture Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB and a PARALLELISM of 2.
Thus, if you are running three Extracts in integrated capture mode in the same database instance, you need at least 3 GB of memory allocated to the Streams pool.
As best practice, keep 25 percent of the Streams pool available.
For example, if there are three Extracts in integrated capture mode, set STREAMS_POOL_SIZE to the following:
3 GB + (3 GB * 0.25) = 3.75 GB
---------------------------------------
The Integrated replicat also needs to have 1GB STREAMS_POOL_SIZE per process and additional 25 percent though it is not been explicitly mentioned in the guides.
The Integrated replicat also needs to have 1GB STREAMS_POOL_SIZE per process and additional 25 percent though it is not been explicitly mentioned in the guides.
So in this installation, the estimate for STREAMS_POOL_SIZE would be:
5 Extract Processes
1Gb Per Extract
1Gb Per Extract
Additional 25% memory overhead
(1Gb*5) + (0.25Gb*5) = 6.25Gb
Setting STREAMS_POOL_SIZE
ALTER SYSTEM SET STREAMS_POOL_SIZE = 6250M SCOPE = BOTH;
The DB was low on memory, so SREAMS_POOL_SIZE was set to 4Gb
COMPONENT CURRENT_SIZE MAX_SIZE
---------------------------------------- ------------ ----------
shared pool 973078528 1006632960
large pool 33554432 33554432
java pool 33554432 33554432
streams pool 4227858432 4227858432
DEFAULT buffer cache 603979776 3355443200
DEFAULT 16K buffer cache 536870912 536870912
Shared IO Pool 134217728 134217728
In-Memory Area 4294967296 4294967296
The extract MAX_SGA_SIZE was increased to 1024
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024)
After above changes + restart to extract, the lag was quickly reduced to zero:
GGSCI (geqaa-1-aps-01) 1> INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPM_I_05 00:00:00 00:00:07
EXTRACT RUNNING DPM_P_05 00:00:00 00:00:05
EXTRACT RUNNING DPM_P_07 00:00:00 00:00:04
EXTRACT RUNNING DPM_S_05 00:00:00 00:00:07
EXTRACT RUNNING DPM_S_07 00:00:00 00:00:07
EXTRACT RUNNING EXT_I_05 00:00:00 00:00:08
EXTRACT RUNNING EXT_P_05 00:00:00 00:00:07
EXTRACT RUNNING EXT_P_07 00:00:00 00:00:01
EXTRACT RUNNING EXT_S_05 00:00:00 00:00:06
EXTRACT RUNNING EXT_S_07 00:00:00 00:00:03
REPLICAT RUNNING REP_I_05 00:00:00 00:00:02
REPLICAT RUNNING REP_P_05 00:00:00 00:00:07
REPLICAT RUNNING REP_P_07 00:00:00 00:00:09
REPLICAT RUNNING REP_S_05 00:00:00 00:00:02
REPLICAT RUNNING REP_S_07 00:00:00 00:00:09
No comments:
Post a Comment