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.
Mark
--
//www.freelists.org/webpage/oracle-l
