RE: Logical Standby Issues (cont.)

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Tue, 25 Jul 2006 22:12:21 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF625D95@WIN02.hotsos.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark Strickland Sent: Tuesday, July 25, 2006 1:07 PM
To: oracle-l_at_freelists.org
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":

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jul 25 2006 - 22:12:21 CDT