Tuning Golden Extract Memory

===============

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:

Optional Parameters for Integrated Modes

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

Estimate for STREAMS_POOL_SIZE

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.

So in this installation, the estimate for STREAMS_POOL_SIZE would be:

5 Extract Processes
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

Gather Stats Task

=================================
What Is The Default Gather Stats Task?
=================================

Out of the box, Oracle comes with a default job, that gathers statistics. 

What is this task?

Statistics gathering is implemented using the Automatic Optimizer Statistics Collection Maintenance task. 

The name of the task is ‘auto optimizer stats collection‘.

The task is scheduled to run during the maintenance window, and it is supposed to gather statistics on the objects with stale or missing stats.

Why are lots of people referring to this task as a job?
In Oracle 10G there was a default job that was gathering statistics (a dba_jobs job).
Starting with 11G, there is no gather stats job.

The information below applies to 11g and 12c.

=================================
Automatic Optimizer Statistics Collection. 
=================================
Oracle has implemented maintenance tasks, and one of them is the Automatic Optimizer Statistics Collection.
This task runs a program, called gather_stats_prog, which in turn invokes the following plsql block: 
dbms_stats.gather_database_stats_job_proc

SELECT client_name, task_name, status, program_action
  FROM DBA_AUTOTASK_TASK, 
       DBA_SCHEDULER_PROGRAMS
WHERE UPPER(task_name)=UPPER(program_name)
  AND client_name='auto optimizer stats collection';

client_name                     task_name
------------------------------- -------------------    
auto optimizer stats collection gather_stats_prog

status    program_action
--------- -----------------------------------------    
ENABLED   dbms_stats.gather_database_stats_job_proc  

Reference

http://dbaparadise.com/2017/11/how-are-statistics-gathered-in-the-database/

In case DBMS_STATS.GATHER_DATABASE_STATS_JOB is slow and using a lot of CPU, it might be because of missing dictionary stats.

 

SOLUTION: Gather dictionary stats and fixed object stats by executing following: 

EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

When working with FETCH cursor BULK COLLECT INTO, where to put the EXIT WHEN cursor%NOTFOUND; ?

==============

General

==============

When working with FETCH cursor BULK COLLECT INTO, where to put the EXIT WHEN cursor%NOTFOUND; ?

For Example:

The Limit is 20000 but actual data is 20

Where do you put the EXIT WHEN  cursor%NOTFOUND ?

==============

Code Example 1 - not good

==============

Example 1 - after the FETCH

This is not good - as the BEGIN Block of FORALL is never reached.

 PROCEDURE purge_temp_keys(v_return_code OUT NUMBER, 

                           v_affiliate_id IN NUMBER )IS

  TYPE ARR_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  tb_rows    ARR_ROWID;
  row       PLS_INTEGER;
  v_counter INTEGER;
  
  CURSOR purge_population_cur (v_affiliate_id in number) IS
  SELECT rowid FROM SFI_SPARX_TEMP_KEYS
    WHERE affiliate_id = v_affiliate_id;

  v_bulk    NUMBER(10):=0;
  -- ora-24381 error(s) in array dml in oracle
  dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
  v_msg_str varchar2(200):='';

BEGIN
  v_return_code:=0;
  OPEN purge_population(v_affiliate_id);
  LOOP
    v_bulk := v_bulk+1;    
    FETCH purge_population_cur bulk collect into tb_rows limit 20000;
    EXIT WHEN purge_population_cur%NOTFOUND;
    BEGIN
      FORALL row IN 1 .. tb_rows.count()
      DELETE SFI_SPARX_TEMP_KEYS WHERE rowid = tb_rows(row);
      COMMIT;
    EXCEPTION
      WHEN dml_errors THEN
        COMMIT;
        RAISE;
      WHEN others THEN -- other exceptions :
        COMMIT;
    END;
    --EXIT WHEN purge_population_cur%NOTFOUND;
  END LOOP;
  CLOSE purge_population_cur;
  commit;
  v_return_code:=0;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      CLOSE purge_population_cur;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    RAISE;
    v_return_code := -1;
END PURGE_SFI_SPARX_TEMP_KEYS;

Example 2 

The EXIT is at the end of the LOOP

This is correct

Now code in BLOCK is looped once

 PROCEDURE purge_temp_keys(v_return_code OUT NUMBER, 

                           v_affiliate_id IN NUMBER )IS

  TYPE ARR_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  tb_rows   ARR_ROWID;
  row       PLS_INTEGER;
  v_counter INTEGER;
  
  CURSOR purge_population_cur(v_affiliate_id in number) IS
  SELECT rowid FROM SFI_SPARX_TEMP_KEYS
    WHERE affiliate_id = v_affiliate_id;

  v_bulk    NUMBER(10):=0;
  -- ora-24381 error(s) in array dml in oracle
  dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
  v_msg_str varchar2(200):='';

BEGIN
  v_return_code:=0;
  OPEN purge_population_cur(v_affiliate_id);
  LOOP
    v_bulk := v_bulk+1;    
    FETCH purge_population_cur bulk collect into tb_rows limit 20000;
    --EXIT WHEN purge_population_cur%NOTFOUND;
    BEGIN
      FORALL row IN 1 .. tb_rows.count()
      DELETE SFI_SPARX_TEMP_KEYS WHERE rowid = tb_rows(row);
      COMMIT;
    EXCEPTION
      WHEN dml_errors THEN
        COMMIT;
        RAISE;
      WHEN others THEN -- other exceptions :
        COMMIT;
    END;
    EXIT WHEN purge_population_cur%NOTFOUND;
  END LOOP;
  CLOSE purge_population_cur;
  commit;
  v_return_code:=0;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      CLOSE purge_population_cur;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    RAISE;
    v_return_code := -1;
END PURGE_SFI_SPARX_TEMP_KEYS;

Drop Obsolete Tablespaces and Datafiles

===================

General

===================

Drop Obsolete Tablespaces and Datafiles

In case table is partitioned, and a TBS is assigned to each partition, it might be that there are obsolete TBS which are assigned to already dropped partitions

Step 1 - Identify Obsolete Tablespaces

Step 2 - Drop Obsolete Tablespaces and Datafiles

Step 3 - Identify datafiles that are assigned to non existing tablespaces.

===================

Code by Example

===================

Step 1 - Identify Obsolete Tablespaces

SELECT 'DROP TABLESPACE '||obsolete_tbs||' INCLUDING CONTENTS AND DATAFILES;'

FROM (

 SELECT DBA_DATA_FILES.tablespace_name as obsolete_tbs, 

        DBA_DATA_FILES.file_name as obsolete_data_file,

        ROUND(DBA_DATA_FILES.bytes/1024/1024) as Mb

 FROM (

  SELECT tablespace_name as tablespace_name 

    FROM DBA_DATA_FILES 

   WHERE 1=1

     AND file_name LIKE '%20%'

     AND tablespace_name NOT LIKE '%YEARLY%'

  MINUS 

  SELECT DISTINCT  tablespace_name 

   FROM DBA_SEGMENTS

 ) OBSOLETE_TBS_LIST,

   DBA_DATA_FILES

 WHERE OBSOLETE_TBS_LIST.tablespace_name =

       DBA_DATA_FILES.tablespace_name

);

Step 2 - Drop Obsolete Tablespaces and Datafiles

DROP TABLESPACE DWNG_REP_MONTHLY_201701 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE DWNG_REP_MONTHLY_201702 INCLUDING CONTENTS AND DATAFILES;

etc...

Step 3 - Identify datafiles that are assigned to non existing tablespaces.

SELECT * 

  FROM DBA_DATA_FILES 

 WHERE file_name LIKE '%DWNG_REP_MONTHLY_2016%' 

ORDER BY file_name;

ls -ltr | grep 2016

rw-r----- 1 oracle dba     2105344 Dec  6 08:43 DWNG_DAILY_TB_MONTHLY_201601_1.dbf

-rw-r----- 1 oracle dba     2105344 Dec  6 08:43 DWNG_DAILY_TB_MONTHLY_201602_1.dbf

-rw-r----- 1 oracle dba     2105344 Dec  6 08:44 DWNG_DAILY_TB_MONTHLY_201603_1.dbf

-rw-r----- 1 oracle dba     2105344 Dec  6 08:44 DWNG_DAILY_TB_MONTHLY_201605_1.dbf

-rw-r----- 1 oracle dba     2105344 Dec  6 08:44 DWNG_DAILY_TB_MONTHLY_201604_1.dbf

In case the files from OS do not exist in DB - simply from these files with OS command - as there were assigned to a dropped tablespace.

Error ORA-14405: partitioned index contains partitions in a different tablespace

In case of error ORA-14405 need to find which objects use the same tablespace.

DROP TABLESPACE DWNG_REP_MONTHLY_201901 INCLUDING CONTENTS AND DATAFILES

ERROR at line 1:

ORA-14405: partitioned index contains partitions in a different tablespace

SELECT table_owner,

       table_name,

       partition_name,

       tablespace_name,

       DBA_TAB_PARTITIONS.*

  FROM DBA_TAB_PARTITIONS

 WHERE (table_owner, table_name) IN (

   SELECT table_owner, table_name

    FROM dba_tab_partitions DTB

   WHERE     DTB.tablespace_name = 'DWNG_REP_MONTHLY_201901'

     AND EXISTS

        (SELECT *

           FROM DBA_TAB_PARTITIONS DTB_INNER

          WHERE DTB.table_owner = DTB_INNER.table_owner

            AND DTB.table_name = DTB_INNER.table_name

            AND DTB_INNER.tablespace_name <>  'DWNG_REP_MONTHLY_201901'

    )

      GROUP BY table_owner, table_name)

ORDER BY 1, 2, partition_position;

SOME_USER REP_OUT_DAILY_SMS_TRANSACTIONS

SOME_USER REP_IN_DAILY_SMS_TRANSACTIONS

MY_USER   REP_OUT_DAILY_TRANSACTIONS

MY_USER   REP_IN_DAILY_DN_TRANSACTIONS

===================

Check Tablespace Usage

===================

SELECT 'TABLE' AS type, COUNT(*) AS segments

  FROM DBA_TABLES T

WHERE T.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL  

SELECT  'INDEX' AS type, COUNT(*) AS segments

  FROM DBA_INDEXES I

WHERE I.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL     

SELECT 'LOB' as type, COUNT(*) AS segments

  FROM DBA_LOBS L

WHERE L.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'TABLE_PARTITIONS' as type, COUNT(*) as segments

  FROM DBA_TAB_PARTITIONS TP

WHERE TP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'INDEX_PARTITIONS' as type, COUNT(*) as segments

  FROM DBA_IND_PARTITIONS IP

WHERE IP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'LOB_PARTITIONS' as type, COUNT(*) as segments

  FROM DBA_LOB_PARTITIONS LP

WHERE LP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106';

ORA-14404 and ORA-14405 When dropping a partition

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

ORA-14404: partitioned table contains partitions in a different tablespace

To solve this error, need to free move these objects to a different tablespace.

CREATE TABLESPACE CDROMETER_TEST_TBS DATAFILE '/oracle_db/db1/db_igt/cdrometer_test_data_01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 10000M;

To generate the SQL:

SELECT 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES;' 

FROM DBA_TAB_PARTITIONS 

WHERE tablespace_name = 'CDROMETER_DATA_HIST_202107';

Actual SQL:

ALTER TABLE CDROMETER_TEST.CDROMETER_DATA_HISTORY MOVE PARTITION P_202107 TABLESPACE CDROMETER_TEST_TBS UPDATE INDEXES;

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

ORA-14405: partitioned index contains partitions in a different tablespace

SELECT COUNT(*) FROM DBA_SEGMENTS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

  COUNT(*)

----------

         0

SELECT COUNT(*) FROM DBA_IND_PARTITIONS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

 

  COUNT(*)

----------

         3

To generate the SQL:

SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' TABLESPACE CDROMETER_TEST_TBS;' FROM DBA_IND_PARTITIONS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

ALTER INDEX CDROMETER_TEST.CDROMETER_DATA_HISTORY_A2 REBUILD PARTITION P_202107  TABLESPACE CDROMETER_TEST_TBS;

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped

Checks before dropping tablespace

SELECT * FROM DBA_SEGMENTS       WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

SELECT * FROM DBA_IND_PARTITIONS WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

SELECT * FROM DBA_TAB_PARTITIONS WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

SELECT * FROM DBA_DATA_FILES     WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

DROP TABLESPACE CDROMETER_DATA_HIST_202109 INCLUDING CONTENTS AND DATAFILES;