Advanced Replication Question

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 - 15:50:35 CDT