RE: Logical Standby Issues (cont.)

  • From: "Rich Amick" <rAmick@xxxxxxxxxxx>
  • To: <strickland.mark@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 10 Aug 2006 13:34:31 -0700

Have you tried taking snapshots before, during and after on the logical
standby, generating statspack reports and comparing them?

What processes are consuming what resources on the logical standby - ie. is
the apply process consuming the most CPU?

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark Strickland
Sent: Thursday, August 10, 2006 12:27 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Logical Standby Issues (cont.)

I figured out a workaround in processing updates to large tables:
break the update apart into smaller updates with a commit after each
update.  That may seem obvious to many of you.  It just occurred to me
yesterday to try it.  In my testing, an update to a 1,000,000-row
table in the primary database takes about 5 hours to complete in the
logical standby (as compared to 3 minutes in the primary).  If I break
that apart into ten 100,000-row updates, it completes in 23 minutes in
the logical standby.  That's 23 minutes total, not 23 minutes per
batch.  That compares quite favorably to another test I did where I
ran a million row-level updates directly in the logical standby from a
SQL script, which took 50 minutes (a single commit at the end).  I'm
continuing to work with Oracle Support on this but at least I have a
workaround to use the next time we do a mass update to a large
Production table.  I've provided empirical evidence to Oracle Support
showing how SQL Apply slows down.  Not just that it's slow, but that
it increasingly slows down as it processes the large transaction.  In
my testing, it starts out doing about 14,000 transactions per minute.
It slows down linearly until it gets down to about 1,500 transactions
per minute.  Our largest Production table that gets replicated with
logical standby has 43-million rows, so the next time that table has
to be mass-updated, I'll have to create 430 update statements with
appropriate WHERE clauses to set the upper/lower boundaries of the
update.  Inconvenient and stupid, IMHO, but workable.  I'm certainly
all ears if anyone has better ideas, but this does work.

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


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


Other related posts: