We put in crontab restart of apply service every now and again. This was done because of regular ORA-600 otherwise. Perhaps, this technique can also improve performance. ;-) Also, I missed in the thread if you use parallel apply - we saw that some type traffic can be applied in parallel very well and some are better running serial.
I continue to beat this possibly-dead horse...
Over the past two weeks, I've been testing Logical Standby performance in 10.1.0.3, 10.1.0.5, and 10.2.0.2 on Solaris 5.9. I have a table with 1,000,000 rows, I add a new column to the table in the primary database, then set that column equal to another column and commit. Takes about 3 minutes in the primary. That update is converted into separate row-level update statements in the logical standby. Fine. I accept that. However, it takes longer and longer to fill up a redo log as SQL Apply chews through the load, about 10 seconds longer per log on average (10-Mb redo logs). So it starts out taking about 20 seconds to fill up a redo log (as measured by time between log switches reported in the alert.log) and each one takes on average 10 seconds longer until the last one takes about 8 minutes. I've graphed it and the curve is linear with a few shallow dips here and there. The total update in the logical standby takes about four hours to complete. If I simply run 1000000 update statements from a SQL script, it takes 50 minutes. That's 1000000 unique SQL statements, btw. I can live with 50 minutes compared to 3 minutes. I can't live with 4 hours compared to 3 minutes. I'm thinking maybe there's a chain/linked list/whatever of some sort that gets longer and longer and has to be chased down to the end with each update. That sort of analysis is getting beyond my technical skill. Perhaps those of you who are stronger technically might have insight. At least something I can tell Oracle Support to look into. Not getting far with Oracle Support. I have three SRs open regarding Logical Standby right now. Thx.
-- Best regards, Alex Gorbachev