Optimizing Oracle Streams Performance: Addressing Slow Goldengate Integrated Replicat Apply Process

 In high-transaction environments, Oracle Streams can face performance bottlenecks, particularly with the sys.streams$_apply_progress segment growing unpredictably. To maintain efficient replication, it's crucial to periodically check and reorganize this segment. This blog post details a method to perform an offline reorganization of the STREAMS$_APPLY_PROGRESS table, helping ensure smoother and faster replication processes.

Why regular maintenance is important

As transactions accumulate, the sys.streams$_apply_progress segment can grow significantly, leading to increased checkpoint lag on the replication side. Regular reorganization of this segment can prevent performance degradation. In my environment, we perform this maintenance every 20 days, reducing the table size from approximately 10 GB to a more manageable level.

Steps for offline reorginazation

• Check the Initial Size of the Table

Before starting the reorganization, verify the current size of the STREAMS$_APPLY_PROGRESS table:Stop the Replicat Process

SQL> SELECT SUM(bytes/1024/1024) AS size_mb FROM dba_segments WHERE segment_name='STREAMS$_APPLY_PROGRESS';

SIZE_MB
--------
14.105637

• Stop the Replicat Process & Verify the Replicat Process Status

Stopping the replicat process ensures no transactions are applied during the reorganization, Confirm that the replicat process is stopped:

GGSCI (oradb.blog.com) 12> stop REP01
GGSCI (oradb.blog.com) 13> info REP01

REPLICAT REP01 Last Started 2024-05-27 21:26 Status STOPPED

 

• Backup the Apply Progress Table

Create a temporary table to back up the  STREAMS$_APPLY_PROGRESS data:

SQL> CREATE TABLE stream_dbwr AS SELECT * FROM sys.streams$_apply_progress WHERE commit_scn >= (SELECT m.commit_scn FROM streams$_apply_milestone m WHERE m.apply# = p.apply#);

• Compare Table Count and Truncate the Original table

Ensure the backup table contains the same number of rows as the original, Clear the data in the original table:

SQL> SELECT COUNT(*) FROM stream_dbwr;

SQL> TRUNCATE TABLE sys.streams$_apply_progress;

• Reconfigure and Reload the Table

Adjust table settings and reload the data from the temporary table:

SQL> ALTER TABLE STREAMS$_APPLY_PROGRESS INITRANS 8 PCTFREE 10;

SQL> INSERT INTO sys.streams$_apply_progress SELECT * FROM stream_dbwr;

• Enable Row Movement and Shrink the Table

Enable row movement, shrink the space, and then disable row movement:

SQL> ALTER TABLE sys.streams$_apply_progress ENABLE ROW MOVEMENT;
SQL> ALTER TABLE sys.streams$_apply_progress SHRINK SPACE;
SQL> ALTER TABLE sys.streams$_apply_progress DISABLE ROW MOVEMENT;

• Verify the New Size of the Table

Check the table size after the reorganization process :

SQL> SELECT SUM(bytes/1024/1024) AS size_mb FROM dba_segments WHERE segment_name='STREAMS$_APPLY_PROGRESS';

SIZE_MB
--------
0.023581

• Restart the Replicat Process

Finally, restart the replicat process to resume replication:

GGSCI (oradb.blog.com) 12> start REP01
GGSCI (oradb.blog.com) 13> info REP01

REPLICAT REP01 Last Started 2024-05-27 21:27
Status RUNNING
INTEGRATED
Checkpoint Lag 00:16:14 (updated 00:00:01 ago)
Process ID 4589
Log Read Checkpoint File ./dirdat/AC000004728
2024-05-27 21:29:47.516377 RBA 16734

Conclusion

By regularly performing these maintenance steps, you can significantly improve the performance of your Oracle Streams replication setup. This proactive approach helps prevent checkpoint lag and ensures your replicat process runs more efficiently than before. Keep an eye on the sys.streams$_apply_progress segment and incorporate these reorganization steps into your routine database maintenance schedule. 

Read more

All about Add-CredentialStore Command