Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g571uvb04957 for ; Thu, 6 Jun 2002 21:56:57 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id KAA56171; Thu, 6 Jun 2002 10:50:14 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0047667C; Thu, 06 Jun 2002 09:48:41 -0800 Message-ID: Date: Thu, 06 Jun 2002 09:48:41 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Aponte, Tony" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Aponte, Tony" Subject: RE: I/O contention with external process reading the oracle logs (online redo logs) Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C20D79.9E15737C" ------_=_NextPart_001_01C20D79.9E15737C Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I think they are alluding to UNIX file system contention. If the redo = logs are in regular file systems (not raw, Veritas Quick I/O, etc.) then = UNIX (at least in my Solaris environment) needs to lock the file for = each of the Shareplex capture processes, in addition to LGWR.=20 There will also be some contention inside the source database that is = not mentioned in their response. Shareplex needs to query the source = table to get the primary key value for the row that changed. It does it = using the rowid that was scraped off the redo log. It then uses the = primary key value from the source table to build the insert statement = for the target. In our installation this process amounts to 5% of the = CPU used by this session statistic. Although the blocks needed are = still in the buffer cache, there is some serialization that has to occur = to fulfill the logical I/O. BTW, in 9i the logical standby implementation includes the primary key = value in the redo stream after extended logging is activated. This = relieves the source from the backwards-looking access for the primary = key as done by Shareplex. I doubt that the performance gain of extended = logging is totally free though. HTH. Tony -----Original Message----- Sent: Thursday, June 06, 2002 10:32 AM To: Multiple recipients of list ORACLE-L logs (online redo logs) NB_ RESENDING in plain text - sorry, Outlook keeps seinding in html no = matter what default i set! Hi lists, =20 I am using Quest Shareplex product for Oracle to Oracle one way = replication. I have two systems (source and target) and two = environments (dev, demo). On system one, the environments are setup as = schemas within one oracle instance (therefore each schema will be a = SOURCE in the replication). My other system has each environment set up = a separate Orace Instances (therefore each instance will become a TARGET = in the replication). =20 I am trying to configure 2 separate replication streams (ie so that = each replication process is SEPARATE from the other - one for DEV and = one for DEMO). I will accomplish this by setting up Shareplex to use = mulitple processes. =20 HOWEVER, Quest technical support has told me that this will cause = contention. However, I dont see why is would from an os/oracle point of = view. Basically Shareplex has a process which reads the online redo = logs......... tech support is suggesting that is there a two processes = trying to access the same block in the logs that contention can occur. = This does not make sense to me. Below is the blurb from techincal = support when I questioned their initial repsonse: =20 *************************************************************************= **************************************************** The reason you might run into a contention is because multiple captue = processes may be reading the same data block in the redo log. Since = there is only one process that can access a single block, the other = process may have to wait. Contention is a possibilty, and you will need to run some bench marks to = find out how much, if any, contention you will have. *************************************************************************= **************************************************** =20 I would find it HARD to believe that only ONE process can read a block = at a time. If this were true, then OLTP system would FAIL miserably! =20 Anyone have any ideas/comments regarding the OS and Oracle interaction = .... I mean are not the logs at this pointa UNIX file? and can't = multiple processes read a single unix file without bringing the whole = system to its knees? Also, I am NOT knocking the techincal support, but I believe that the = opinion was formulated on an incorrect assumption on the operating = system and Oracle. Thoughts/comments? =20 Thanks in advance.=20 =20 Hannah =20 =20 =20 =20 =20 --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20 Author:=20 INET: johanna.doran@sungard.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C20D79.9E15737C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: I/O contention with external process reading the oracle logs = (online redo logs)

I think they are alluding to UNIX file system = contention.  If the redo logs are in regular file systems (not raw, = Veritas Quick I/O, etc.) then UNIX (at least in my Solaris environment) = needs to lock the file for each of the Shareplex capture processes, in = addition to LGWR.

There will also be some contention inside the source = database that is not mentioned in their response.  Shareplex needs = to query the source table to get the primary key value for the row that = changed.  It does it using the rowid that was scraped off the redo = log.  It then uses the primary key value from the source table to = build the insert statement for the target.  In our installation = this process amounts to 5% of the CPU used by this session = statistic.  Although the blocks needed are still in the buffer = cache, there is some serialization that has to occur to fulfill the = logical I/O.

BTW, in 9i the logical standby implementation includes = the primary key value in the redo stream after extended logging is = activated.  This relieves the source from the backwards-looking = access for the primary key as done by Shareplex.  I doubt that the = performance gain of extended logging is totally free though.

HTH.
Tony

-----Original Message-----
From: johanna.doran@sungard.com [mailto:johanna.doran@sungard.co= m]
Sent: Thursday, June 06, 2002 10:32 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: I/O contention with external process = reading the oracle
logs (online redo logs)


NB_ RESENDING in plain text - sorry, Outlook keeps = seinding in html no matter what default i set!
Hi lists,
 
    I am using Quest Shareplex product = for Oracle to Oracle one way replication.  I have two systems = (source and target) and two environments  (dev, demo).  On = system one, the environments are setup as schemas within one oracle = instance (therefore each schema will be a SOURCE in the = replication).  My other system has each environment set up a = separate Orace Instances (therefore each instance will become a TARGET = in the replication).

 
    I am trying to configure 2 = separate replication streams (ie so that each replication process is = SEPARATE from the other - one for DEV and one for DEMO).  I will = accomplish this by setting up Shareplex to use mulitple = processes.

 
    HOWEVER, Quest technical support = has told me that this will cause contention.  However, I dont see = why is would from an os/oracle point of view.  Basically Shareplex = has a process which reads the online redo logs......... tech support is = suggesting that is there a two processes trying to access the same block = in the logs that contention can occur.  This does not make sense to = me.  Below is the blurb from techincal support when I questioned = their initial repsonse:

 
****************************************************************= *************************************************************

The reason you might run into a contention is because = multiple captue processes may be reading the same data block in the redo = log.  Since there is only one process that can access a single = block, the other process may have to wait.

Contention is a possibilty, and you will need to run = some bench marks to find out how much, if any, contention you will = have.

****************************************************************= *************************************************************

 
I would find it HARD to believe that only ONE process = can read a block at a time.  If this were true, then OLTP system = would FAIL miserably!

 
Anyone have any ideas/comments regarding the OS and = Oracle interaction .... I mean are not the logs at this pointa UNIX = file?  and can't multiple processes read a single unix file without = bringing the whole system to its knees?

Also,  I am NOT knocking the techincal support, = but I believe that the opinion was formulated on an incorrect assumption = on the operating system and Oracle.

Thoughts/comments?
 
Thanks in advance.
 
Hannah
 
 
 
 
 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: johanna.doran@sungard.com

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051
San Diego, = California        -- Public Internet = access / Mailing Lists
----------------------------------------------------------------= ----
To REMOVE yourself from this mailing list, send an = E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of = 'ListGuru') and in
the message BODY, include a line containing: UNSUB = ORACLE-L
(or the name of mailing list you want to be removed = from).  You may
also send the HELP command for other information = (like subscribing).

------_=_NextPart_001_01C20D79.9E15737C-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aponte, Tony INET: AponteT@hsn.net Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).