RE: Logical Standby Issues (cont.)

  • From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
  • To: <strickland.mark@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 25 Jul 2006 22:12:21 -0500

An important concept to understand about Logical standbys in particular
is that the SQL Apply engine on the logical standby creates "equivalent
SQL" to that which was run on the primary.  It is NOT the same SQL.  

Updates shouldn't do full tables scans to update a row UNLESS there
isn't a primary key on the table. 

Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium March 4-8, 2007.  Be there.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark Strickland
Sent: Tuesday, July 25, 2006 1:07 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Logical Standby Issues (cont.)

Re-sending this because it seems to have gotten scrambled:

We added primary key and unique key supplemental logging.

Admittedly, I'm quite ignorant about what exactly goes into the redo
stream so I'm shocked, SHOCKED!, that bulk inserts/updates get
converted into individual row-level statements in a logical standby
(and causes a LOB in the SYSAUX tablespace to grow and grow and
grow...).  I'm about to start a test on the same table that gave our
Production Logical Standby a big hairball Friday night to verify that
the single bulk update statement gets converted into individual
row-level updates and whether those are executed with full table scans
or not.  The table does have a primary key.

So far, Logical Standby in 10.1.0.3 appears to have these unfortunate
"features":

1) Change Data Capture doesn't work (fixed in 10.1.0.5).
2) Automatic Statistics Gathering doesn't work (fixed in 11i?).
3) Bulk inserts/updates are converted into row-level DML.  Updates
possibly do full table scans (need to verify).
--
//www.freelists.org/webpage/oracle-l


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


Other related posts: