If you have done all the tuning following instructions in solution 17453 and still have slow read, then consider the following option.
If you notice reader is very slow on pass2 and 'show read internal' shows higher # of updates with key from oracle, this means reader is busy fetching keys from oracle. One way to speed up reader is to update the key column intentionally either in your application or through a before update trigger. The key information will then be stored in redo log and reader does not have to fetch key value from oracle any more.
Here is a sample before update trigger you can create on source on any table that has lots of updates. The example below assumes that you have two key columns. If the key is not changed, it will update the key to its existing value. If the key is changed, it will update to its new value.
CREATE OR REPLACE TRIGGER owner.before_update_xxxxx
BEFORE UPDATE
ON owner.xxxxxxx
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
:NEW.keycol:=:NEW.keycol;
EXCEPTION
WHEN OTHERS THEN
NULL;
END before_update_xxxxx;
/
Once you implement this solution, you should see 'show read internal' - # of Updates with key from oracle reduced.
Updates with complete key : 0
Updates with key in cache : 0
Updates with key from Oracle : 0
Cursor cache hit count : 0
Cursor cache miss count : 0
Number of open cursors : 0
