Re: Logical Standby Issues (cont.)

  • From: "Mark Strickland" <strickland.mark@xxxxxxxxx>
  • To: "Carel-Jan Engel" <cjpengel.dbalert@xxxxxxxxx>
  • Date: Tue, 8 Aug 2006 08:25:34 -0700

To Alex:  in the case of parallel apply, I wish.  The 10.2.0.2 manual
explains that even if I use a PARALLEL hint in the primary update statement,
it will still be serialized in the standby.  Only a single Apply process
gets used in the standby.

To Carel-Jan:  Yes, 10-Mb redo logs is very small.  That's just in my test
databases.  The redo logs in the Production Standby are large enough.  I
guess I can certainly test with larger redo logs but I don't think that
small redo logs would explain why it takes longer and longer for each log
switch, 10 seconds longer each time on average, starting out at 20 seconds
per log and ending out at 8-1/2 minutes per log with roughly equal numbers
of SCNs.  If SQL Apply was slow but the time between log switches was
consistent, then I would suspect the size of the redo logs as a performance
factor.  Still, I might as well test this, too.

I "believe" that my generated update script does the updates in the same
order, but I don't know for sure.  I generated the updates as a select of
literal update statements from the table in the primary.  If the select
processes rows in the same order as the update, then they are in the same
order.  Yeah, I know that LogMiner and SQL Apply add overhead that my simple
update script wouldn't have.  So, if SQL Apply took 100 minutes compared to
50 minutes for an update script, but if the SQL Apply process didn't appear
to slow down, I could live with that.

I did a Cary trace in the Production Logical Standby, but I don't think I
did one in my test environment.   There have been so many tests and so many
traces that I actually don't remember.  Would have to go back and read
through the SR in order to remember.  Won't hurt to do yet another test,
though.  If I come up with insight, I'll be sure to update this thread.

I did find a bug patch for 10.2.0.2 that seemed to exactly fit my problem.
I applied the patch.  Didn't help.

I have found that the 10gR2 Data Guard documentation is a bit more complete
than the 10gR1 docs.  It does explain a bit about how a large transaction is
handled, ie, updating all the rows in a large table.

Thank you!
Mark

Other related posts: