Extract Slow until it is restarted

Oracle GoldenGate - Version: 11.1.1.1.0 and/or later releases. 




Problem:

An Oracle goldenGate (OGG) extract on RAC is slow from time to time, and restart will resolve the performance problem until next occurence.
OGG version is 11.1.1.1.0, and logretention is not registered.

From OEM History, this PL/SQL procedure was running for over 10 hours and lag was increasing. Some traces of parsing (and no execute) was also seen:

declare 
   extract_name varchar2(100) := :1; 
   min_scn number := :2; 
   capture_found number := 0; 
   alternate_capture_found number := 0; 
   ogg_capture_name varchar2(30); 
   alternate_ogg_capture_name varchar2(30); 
   queue_table_name varchar2(30); 
   queue_name varchar2(30); 
   chosen_capture_name varchar2(30); 
   logminer_session_number number := 0; 
BEGIN /* Construct the capture name and alternate capture 
name */ ogg_capture_name := SUBSTR('OGG$_' || extract_name, 1, 30); 
alternate_ogg_capture_name := SUBSTR('OGG2$_' || extract_name, 1, 30); /* 
Construct the queue table and queue name */ queue_table_name := 
SUBSTR('OGGQT$_' || extract_name, 1, 23); queue_name := SUBSTR('OGGQ$_' 
|| extract_name, 1, 23); /* If the queue doesn't exist, create it. We can 
use the same queue for * both the capture and the alternate capture. 
Note that this function * is idempotent, so it can be called even if the 
queue or queuetable exists */ 
dbms_streams_adm.set_up_queue(queue_table= 
>queue_table_name, queue_name=>queue_name); 
/* See if a dummy capture already exists for the given extract name. * 
We assume that OGG extract names do not have the same canonicalization * 
principles as the RDBMS, so using UPPER is sufficient. */ SELECT 
COUNT(*) INTO capture_found FROM DBA_CAPTURE WHERE capture_name= 
UPPER(ogg_capture_name); SELECT COUNT(*) INTO alternate_capture_found 
from DBA_CAPTURE WHERE capture_name=UPPER(alternate_ogg_capture_name); 
/* Assert that two captures don't exist at the same time */ IF 
capture_found > 0 AND alternate_capture_found > 0 THEN 
RAISE_APPLICATION_ERROR(-20001, 'Multiple 
captures ' || ogg_capture_name || ' and ' || 
alternate_ogg_capture_name || ' exist'); END IF; /* Create the new 
capture process */ IF capture_found > 0 THEN chosen_capture_name := 
alternate_ogg_capture_name; ELSE chosen_capture_name := 
ogg_capture_name; END IF; dbms_capture_adm.create_capture(queue_name => 
queue_name, capture_name => 
chosen_capture_name, first_scn=>min_scn); 
/* If insert_checkpoints is specified, need to alter capture and insert 
* checkpoints. */ /* Get the logminer session# from 
dba_logmnr_session */ SELECT ID INTO logminer_session_number FROM 
DBA_LOGMNR_SESSION WHERE name = UPPER(chosen_capture_name); /* 
insert two checkpoints */ INSERT INTO system.logmnr_restart_ckpt$ 
(session#, valid, ckpt_scn, xidusn, xidslt, xidsqn, session_num, 
serial_num, spare1) VALUES (logminer_session_number, 1, min_scn + 1, 
1, 1, 1, 1, 1, min_scn); INSERT INTO system.logmnr_restart_ckpt$ 
(session#, valid, ckpt_scn, xidusn, xidslt, xidsqn, session_num, 
serial_num, spare1) VALUES (logminer_session_number, 1, min_scn, 1, 
1, 1, 1, 1, 1); /* The applied scn of capture must be > min_scn, so 
alter capture */ UPDATE sys.streams$_capture_process SET spare2= 
min_scn+100 WHERE capture_name = UPPER(chosen_capture_name); /* 
Remove existing capture */ IF capture_found > 0 THEN 
dbms_capture_adm.drop_capture(ogg_capture_name); ELSIF 
alternate_capture_found > 0 THEN 
dbms_capture_adm.drop_capture(alternate_ogg_capture_name); 
END IF; 
END; 





DBA_CAPTURE view should be checked to see if logretention is added to the extract or not.


Cause
This is due to logretention new feature in version 11.1.1.1.
For Oracle RAC, database patch 11879974 is needed if logretention is needed.
As logretention is the default setting, the query will still be parsed even if the extract is not registered for logretention.  Without the database patch, performance will suffer.

To check if an extract is registered:
  select * from dba_capture;

Fix:
  Logretention can be disabled at the extract level. 

The parameter is:

TRANLOGOPTIONS LOGRETENTION DISABLED

1) check DBA_CAPTURE to confirm the logretention is added
2) Ignoring at the extrace level 

$./ggsci

ggsci> stop <extract name>
ggsci> dblogin USERID <username> , PASSWORD <password>
ggsci> unregister extract <extract name> LOGRETENTION
3) add parameter TRANLOGOPTIONS LOGRETENTION DISABLED