RE: Logical Standby Issues (cont.)

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Mon, 24 Jul 2006 21:06:27 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF625BEC@WIN02.hotsos.com>

Yes SQL Apply creates an individual SQL statement for each row that gets updated. This was supposed to change in 10 to where a more "bulk like" update would take place, but as I recall there were still some instances where it may still be have to use one update per row. I don't remember all the gory details of what the cases are, but apparently you are hitting at least one of them.

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: Monday, July 24, 2006 5:47 PM
To: oracle-l_at_freelists.org
Subject: Logical Standby Issues (cont.)

10.1.0.3 RAC on Solaris 9 w/ Physical and Logical Standby

Yep, SR is open.

Had another incident Friday night where a column was added to a "large" table (not that large) in the primary database and then the column was set to a value with a single update statement. As before, the DDL got applied to the logical standby immediately but then SQL Apply slowed to a crawl. I'm running a test in a test environment now because I suspect that Oracle is taking that one update statement and converting it into a separate update statement for each row and each of those updates is doing a full table scan (yes, all the tables that are being maintained by SQL Apply have primary/unique keys). I'll know more soon after my testing is complete, but am I insane in thinking that Oracle should just run the single update statement against the entire table rather than converting that into single-row updates? Or am misunderstanding how LogMiner creates Logical Change Records? I'm still learning how to query the various views related to logical standby so I didn't do a great job digging into what was really happening Friday night.

Regards,
Mark Strickland
Next Online Technologies
Seattle, WA
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jul 24 2006 - 21:06:27 CDT