In general extract does not require any tuning. Tuning is mainly required for  replicat as it must reproduce the source operations by constructing the SQL statement  resulting in  random I/O activity.   We were surprised when we  noticed slow performance of extract  after couple of hours and extract hanging issues  after upgrading  from 10.4 to 11.1.1.1.0.

Slow performance of Extract

The slow performance was traced to the new feature where the “ADD extract” command by defaults registers the SCN number with RMAN   to  prevent RMAN from deleting archive log files that extract may need for restart and recovery.  Very good feature but looks like the there is bug that causes the slowness in performance. As per Oracle, Extract is suppose to try to register every 10ms up to 4 hrs , but after 4 hrs the wait time overflows which cause extract to issue the SQL to register the SCN thousands of times a second.

This new feature is disabled with extract parameter “ TRANLOGOPTIONS LOGRETENTION DISABLED”

Extract Hanging

We occasionally noticed that extract stopped extracting  data from redo logs until it became full and archived log file was created. Once archived logfile was created, the extract caught-up on backlog  and started processing OK.

This issue was trace to new ReadAhead feature implemented to gain improvement on the queue reading logic but it lacks some synchronization with regards to checking the queue statuses (empty, full, etc.) and causes intermittent hang issues.

This new feature is disabled with extract parameter “TRANLOGOPTIONS _NOREADAHEAD ANY ” .  This parameter is an underscore parameter  similar to  initialization parameters in Oracle.

Tagged: Extract Hanging, goldengate extract hanging, goldengate extract slow, performance issue with GoldenGate Extract, registers" the SCN number with RMAN, Slow performance of Extract, TRANLOGOPTIONS LOGRETENTION DISABLED, TRANLOGOPTIONS _NOREADAHEAD ANY