Relicat is running but it is very slow. Transaction apply rate is low and trail files are piling up.

First this we need to check what replicat is currently doing i.e which tables it is updating.

To check that run awr report for the last one hr

In awr report first check db_time. as you can see for one hr period this looks good

now check foreground events

It shows that most of the time is spent on db file seqeuntial read event. This indicate index is being used but it might not be the right index.

so go to the sql ordered by elapsed time section and check the topmost sql

we can see that sql_id=axsbgh684sf7q is the sql with highest elapsed time and most executions. this is running via replicat R1_ICJ01

lets generate teh explain plan for this sql


SQL> select *from table (sys.dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 320515616

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                |     1 |    56 |     5   (0)| 00:00:01 |
|   1 |  UPDATE                       | WW_CONTACTS_V1 |       |       |            |          |
|*  2 |   COUNT STOPKEY               |                |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| WW_CONTACTS_V1 |     1 |    56 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | MAIL_ID_INDEX  |     2 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

as we can see the the index ebing used is MAIL_ID_INDEX. Lets check the definition of this index. It is a non uniue index based on email_id column

SQL> SET LONG 9999999 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
select dbms_metadata.get_ddl('INDEX','MAIL_ID_INDEX','WW_TEMP') from dual;
SQL>

  CREATE INDEX "WW_TEMP"."MAIL_ID_INDEX" ON "WW_TEMP"."WW_CONTACTS_V1" ("EMAIL_IDS")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "WW_TEMP"

now run tunning advisor as per belwo doc

https://dbatracker.com/2021/02/04/run-sql-tunning-advisor-for-sql_id/

  Recommendation (estimated benefit: 99.28%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index WW_TEMP.IDX$$_ADF80001 on
    WW_TEMP.WW_CONTACTS_V1("ROW_ID","W_SPECIAL_AGREEMENT");

so i created the index.

I created index on the GG target side only. but ideal way is to create it on source and let the GG replication bring the same to target side.

  SQL> create index WW_TEMP.W_SPECIAL_AGREEMENT_INDEX on WW_TEMP.WW_CONTACTS_V1("ROW_ID","W_SPECIAL_AGREEMENT") TABLESPACE "WW_TEMP";

Index created.

As you can see plan has definitely improved and newly created index is also getting used


SQL> set lines 300
SQL> set pages 300
SQL> select *from table (sys.dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1948272425

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                           |     1 |    56 |     2   (0)| 00:00:01 |
|   1 |  UPDATE                       | WW_CONTACTS_V1            |       |       |            |          |
|*  2 |   COUNT STOPKEY               |                           |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| WW_CONTACTS_V1            |     1 |    56 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | W_SPECIAL_AGREEMENT_INDEX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

after this i can clearly see that replicat is performing much fater that before.