Re: Advanced Replication Question

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