RE: Parallel Replication of Single Table

> Larry,
>
> Thanks for taking the time to explain this. You are really patient when it
> comes to writing!
> I see now how Oracle does the refresh. I also remember now seeing
> a post in
> this list some time ago asking about the possibility of tuning a similar
> sql. I think it was you :)

It was me. I was asking about reverse key indexes and if anyone had seen issues. The Oracle replication query to resolve what to process was running for a few hours, just the query -- I would pull it out and run it, very bad. But the execution plan was good, what we would want. The PK was a reverse key index based on a sequence. So I understand why the folks were tempted, based on lots of what is written out there, to use a reverse key index. Changed to a normal b-tree, and testing the query, it finished very quickly. Changed back to reverse, the query to resolve what to process ran for ages. And then back to b-tree, good performance again. So I asked the list if anyone had seen similar things with reverse key indexes. You think other people would have seen similar things if it was a problem with the reverse key index. Anyway, we ditched the custom code that had a couple of "issues" -- instead of fixing that code went with a normal b-tree index, ditching the reverse key index, and went with Oracle's standard replication. It has worked fine, until recent changes in a feed that is causing a *lot* of updates. Now it's not so good (but the query still resolves quickly ;-)) Still don't know that we can attribute the query performance issue strictly to a reverse key thing.

> As I see in the sql you sent, all the sql are using bind variables. This
> indicates that changes are done row by row in a procedural method
> instead of
> a single sql (update, insert) that handles all the changes in one shot.

Yep. But it would still be possible, I think, to parellelize that even though it is done a row at a time. Divide and conquer and retain precedence. I've done that in the past with custom code for pushing changes.

> When we talk about parallelizing it, then there are two choices:
> The first one is using parallelism in the sql engine itself and firing one
> sql statement that handles either all the updates or the entire deletes to
> the target table.

And this is where maybe the idea of remote transactions comes in and the comments I referred to in the prior email. Could very well be missing something here about why they serialize when going remotely. But it's still interesting the parallelism you can get with a insert, for example, depending on which side you initiate it, something you touched on in an earlier email.

And the following comment from the 8.1.7 Data Warehousing Guide is interesting and why we (I really, pressing on others) have been pursuing this:

"If the user has done a lot of updates to the table, ... In the warehouse, after a bulk load, the user should enable parallel DML in the session and perform the refresh. Oracle will use parallel DML to do the refresh, which will enhance performance tremendously. There is more to gain if the materialized view is partitioned."

Note that the references are to "a table", and updates (understanding the limitations of parallel updates in 8.1.7 and across partitions, not within) not a group of tables. But the one thing they don't say in that statement is if the MV is local, or if it is remote. So you find the following comment in the Distributed Concepts Manual:

"If the table referenced in the table_expression_clause of an INSERT, UPDATE, or DELETE statement is remote, then execution is serial rather than parallel."

So that tends to make you think the parallelism is not going to happen when refreshing a remote object. But heck, I can't even get a test case using an MV and parallelism with a local object. So that's why I think I'm totally missing something. And the thousands of pages of Oracle doc's is a lot to go through to get the answer, though I'm trying ;-)

> Parallel updates is not available in releases below 9.2.

They are across different partitions, just not in the same partition, or so we read, and I think I tested some time back. And I did a test case in 9.2, not believing just the plan but actually observing, and saw the parallelism within a single partition.

> The second choice is to continue doing it in a procedural way but spawning
> many Oracle processes, and let the master process
> read the rows from the sql and dequeue/distribute them to the parallel job
> processes.
> The parallel jobs processes will do their jobs the same way
> (procedural) the
> single process does.

And that's what we had, though a couple of issues. And once the query resolution performance was resolved, we went with standard replication. But now it looks like we will dig out the old code that performs in the manner you describe, fix the couple of issues with it, and run with that. We are still testing capabilities -- hate to go with a custom solution if standard features can do the job. But we have been unsuccessful with the tests. Maybe instead of my pressing to investigate the capabilities, I should have just agreed to go with a custom solution that we know could work. Just hate re-inventing the wheel. But you have to look at the overall picture and the time spent looking into things -- maybe shouldn't have been so forceful on investigating "native" solutions and just ran with the custom approach that would work.

> I asked a friend who handles replication and here is the thread:

Thanks for taking the time to bounce it off someone else.

Larry G. Elkins
elkinsl_at_flash.net

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