Oracle-L: RE: Parallel Replication of Single Table

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 14 Jan 2003 11:34:13 -0800
Message-ID: <F001.0052F85E.20030114113413@fatcity.com>

Hi Larry,

I do not know really how you want to parallelize this kind of operations!

The final goal is to push the changes in site 1 to site 2 and get the data in both tables in sync.

Changes include inserts, updates, and deletes. These changes (dml operations)
could be cascaded on the same row which means that the final image of the data in the table is completely dependent on the order and sequence of these dml operations.

This is why I think that a single table refresh requires it to be a serial operation.

But on the other hand if a single refresh was requested for many tables, then this could be parallelized on the job level not the table level.

Does it make sense or am I missing something?

Regards,

Waleed

-----Original Message-----
Sent: Monday, January 13, 2003 7:51 PM
To: Multiple recipients of list ORACLE-L

Listers,

How can one use parallelism when fast refreshing a *single* table?

Pulling my hair out on this one. We want to use parallelism when replicating updates/inserts from a single partitioned table between DB's -- partitioned on both DB's. Both are 8.1.7.4 Solaris 8 64 bit. Using fast refresh and primary key method. Have the MV in the target, have the source along with the associated MLOG$. The job queues, parallel parameters, etc are all way up there. We manually pull on occasion when doing restructuring or mass mods and have no problems getting parallelism on each side. But we have to initiate from the target side as a pull to get that. Have set degree 8 on MLOG$, source, and target.

Specifically, when I crank up the refresh, with only updates, the update on the target serializes. Info in the docs is a little confusing. Much is made of partitioning a table in the DW guide to allow parallelism of the refresh. But it is unclear if they are talking an MV based on an object in the same DB, or, based on a remote object, or both. In the distributed manual it makes a comment about DML serializing when doing remote operations. Now someone looked into this a while back and opened a TAR to get an explanation -- the analyst said to simply use the parallelism parameter of the DBMS_SNAPSHOT.REFRESH. Well this didn't do it. Then he comes back and says it is possible to replicate a single table using parallelism if you use advanced replication (or maybe we read that somewhere). Guess I'll create a second 8.1.7 DB on my home machine and give that a go.

But I'm probably missing something obvious here. So it's back to a simple question -- how can one use parallelism to refresh a single table? We can write our own routines to do this, but I would rather use native capabilities as opposed to re-inventing the wheel. Feeling pretty stupid here. Oh well, time to setup and test advanced replication.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

--

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

Author: Larry Elkins
INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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.net
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------