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 220.127.116.11.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”
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.