Re: Logical Standby Issues (cont.)

  • From: "Mark Strickland" <strickland.mark@xxxxxxxxx>
  • To: "Rich Amick" <rAmick@xxxxxxxxxxx>
  • Date: Thu, 10 Aug 2006 13:54:55 -0700

Oh yeah, I've taken AWR snapshots and run the logical standby
diagnostics script provided on Metalink.  I've set logical standby
parameters as recommended by Oracle Support.  I've set various events
and levels of tracing.  Yes, it is the Applier process that is
consuming the CPU.  In 10gR1, there is latch contention while Log
Miner is scraping DMLs out of the archived logs (Log Miner Work Area
latch) but the latch contention goes away once Log Mining finishes.
Log Mining takes about half of the total elapsed time of the test.
However, SQL Apply does not suddenly speed up.  It continues its
linear trajectory toward more slowness.  In my 10gR2 tests, the latch
contention doesn't exist but the test still takes close to the same
amount of time as in 10gR1.  Log Mining takes about 34 minutes which
is much faster than in 10gR1.  I've graphed the amount of time between
log switches in the standby.  It gets longer and longer and the graph
is linear pointing Northeast.  In the 10gR1 tests, each redo log takes
about 28 seconds longer on average to fill than the last one for
roughly the same number of SCNs.  In the 10gR2 tests, each redo log
takes about 10 seconds longer to fill than the last one but there are
more log switches than in 10gR1.  Don't know why and haven't looked
into it.  At any rate 10gR2 is a little faster than 10gR1 but not by
much.  From the AWR reports I can easily see from the number of
executions of the update statement for each time period covered by the
AWR snapshot that SQL Apply is slowing down.

Thanks for responding.

Mark
--
//www.freelists.org/webpage/oracle-l


Other related posts: