Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Advanced Replication Question

Re: Advanced Replication Question

From: Peter Sylvester <peters_at_mitre.org>
Date: Wed, 15 Jul 1998 08:57:24 -0400
Message-ID: <35ACA733.DCE4A91@mitre.org>


You can use procedural replication to just sent the PL/SQL call (to purge the old records) and its parameters across to the other system. You need to make a special call (dbms_reputil.replication_off) within the procedure so the deletes don't get picked up by the multi-master replication triggers. See the fine manual.

-Peter

nasof_at_hotmail.com wrote:

> Hi all,
>
> Any one have any comments on this:
>
> I have an Asynchronous Multi-Master Replication setup with 2 NT server. One
> is the Primary and the other is the Secondary (for failover.)
>
> A set of tables is replicated via one master group. The Primary database
> (Oracle 8.0.4) keeps 7 days worth of active data. Every night, after
> warehousing, the oldest days worth of data is purged.
>
> For the sake of simplicity, lets say I have a single table with 50,000 rows
> inserted per day. My cleanup might look like:
>
> delete from singe_table where the_date < sysdate - 7;
>
> On my Primary, this is a single DML statement that completes relatively
> quickly (a few seconds.) Meanwhile this same statement produces 50,000
> mini-delete statements that are placed into the deferred transaction que.
> Now I have turned one delete statement into 100,001 statements! How you ask?
> Well, one for the original delete; 50,000 procedure calls for the Primary
> database and 50,000 deletes for my Secondary! What a mass of memory, IO and
> disk utilization this causes. It probably flushes most of my SGA. The
> secondary practically chokes during this time, falling behind in its regulary
> processing.
>
> I am interested in any views into this problem. Are there alternatives? Why
> cant replication just replicate the original DML statement?
>
> -Frank
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Wed Jul 15 1998 - 07:57:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US