Oracle-L: RE: I/O contention with external process reading the oracle logs (online redo logs)

From: Aponte, Tony <AponteT_at_hsn.net>
Date: Thu, 06 Jun 2002 09:48:41 -0800
Message-ID: <F001.0047667C.20020606094841@fatcity.com>

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-----
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,

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_at_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_at_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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Aponte, Tony
INET: AponteT_at_hsn.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------