Re: Logical Standby Issues (cont.)

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

--
http://www.freelists.org/webpage/oracle-l