Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Logical Standby Issues (cont.)

Re: Logical Standby Issues (cont.)

From: Mark Strickland <strickland.mark_at_gmail.com>
Date: Thu, 10 Aug 2006 12:27:02 -0700
Message-ID: <90ad14210608101227v154c65f4kcc377bc6eb360967@mail.gmail.com>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 10 2006 - 14:27:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US